SOLVED - VBA Help For Row Source and Record Source

eusanpe · May 12th, 2008, 10:26 AM

Hello All:

I am trying to write some code to do try append a value in Control Source or Row Source depending on what it finds. So basically what I have is:

Private Sub cboPhysician_Dirty(Cancel As Integer)
    Dim strRowSource As String
    strRowSource = "SELECT Physicians.Specialty " _
                    & "FROM Physicians " _
                    & "WHERE Physicians.Physician= " + "'" +
                       Forms!Order_Form.cboPhysician + "'"""

     Forms!Order_Form!cboPhysicianSpecialty.RowSource = strRowSource

When the focus is in the Physican combo box and a selection is made, I want it to do the query. If it finds a physician, put this in the
Control Source for the cboPhysicianSpecialty:

=DLookUp("Specialty","Physicians","Physician = '" & Form!cboPhysician & "'")

This will autofill the Specialty combox with there specialty. Unfortunately if it doesn't find the physician, then that field is not editable if the Control Source property is filled in.

So I would like to figure out how to write it so if it doesn't find a physician, add the above 'Select' statement to the Row Source, that way I can edit the Specialty combo box.

I hope this makes sense.

Thanks,
Tony

End Sub

SerranoG · May 12th, 2008, 04:06 PM

The rowsource would be

strRowSource = "SELECT [Specialty] FROM Physicians WHERE [Physician] = '" & Me.cboPhysician & "'"

Me.cboPhysicianSpecialty.RowSource = strRowSource

If this code is in the form Order_Form, you can use the Me shortcut. The plus sign + is for number addition. The ampersand & is for string concatenation.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

eusanpe · May 12th, 2008, 04:22 PM

Thank you SerranoG for the reply. I guess I didn't clarify this right.My apologies.

What i am trying to do is if the a physician is selected from the dropdown then add the DLookup string to the Control Source property for the Specialty Combo box.

If the physician is not listed in the combo box, then they will have to input the physician. Once the physician is entered, I want the Control Source proprty cleared and the SQL string added to the Specialty Row Source property instead.

I have everything working without VBA but the issue is that leaving the DLookup string in the control source makes the Specialty field uneditable.

Thanks,
Tony

SerranoG · May 13th, 2008, 07:01 AM

I would put the code in the cboPhysician's AfterUpdate event, not in the Dirty event. You cannot place a control source on a textbox at design view because it will prevent you from assigning it a value on the fly. If the textbox is bound to the data table, you assign a value to it in VBA at runtime.

Code:

Dim strRowSource as String

If Len(Nz(Me.cboPhysician, "")) = 0 Then
    'No Physician Chosen.  Make the rowsource ALL specialties.
    strRowSource = "SELECT DISTINCT [Specialty] FROM Physicians ORDER BY [Specialty]"
Else
    'Physician chosen.  Find his/her specialty.
    strRowSource = "SELECT DISTINCT [Specialty] FROM Physicians WHERE [Physician] = '" & Me.cboPhysician & "' ORDER BY [Specialty]"
End If

Me.cboPhysicianSpecialty.RowSource = strRowSource

If Me.cboPhysicianSpecialty.ListCount = 1 Then
    'The physician has only one specialty.  Enter it automatically.
    Me.cboPhysicianSpecialty = Me.cboPhysicianSpecialty.Column(0,0)
End If

Note the use of DISTINCT and ORDER BY in the rowsource assignment.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

eusanpe · May 13th, 2008, 11:58 AM

SerranoG:

Thank you so much for the advice and the code. It worked perfectly.

Tony