VBA and Update Query Revisited

eusanpe · June 12th, 2008, 09:46 AM

Hello All:

I have a form and what I am trying to do is perform an update to the table if a physician is pulled up and there is no data in the specialty table. For instance I have the following code listed below (Thanks SerranoG) What it does is when I click the combo box and select a physician it will display their specialty in the specialty field. If a physician is not listed, I can type in a physician and specialty and when I click the "Save" button, I have an append query that adds the data to the table. What I didn't take into account is if the existing physicians listed do not have a specialty defined in the table. I can go through and add them manually in the table or create an update query to do it. How would I create the update query to do this, I guess is the question?

I want to be able to select a physician and if he doesn't have a specialty, type it in, click the 'Save' button and an update query is run.

Thanks, Tony

Code:

Private Sub cboPhysician_AfterUpdate()
Dim strRowSource As String

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

Me.cboPhysician.RowSource = strRowSource
Me.cboPhysicianSpecialty.ControlSource = "PhysicianSpecialty"

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

cboPhysician = StrConv(cboPhysician, vbProperCase)

End Sub

SerranoG · June 13th, 2008, 09:19 AM

With no specialties, the combobox for the physician specialty will have no items in it. You can check for that and then use an append query to add a record to the specialty table. Without knowing your table's layout, I'll post fake code that you'll have to redo to match your table structure.

Note that in e-mail the CODE tags used in this post will look odd. So you may want to view this in the forum itself to get the proper look and indentation, etc.

Also note that one of your lines has an apostrophe in front rendering it a comment: 'Me.cboPhysicianSpecialty.RowSource = strRowSource. Why is it there?

OK, here goes...

Code:

Private Sub cboPhysician_AfterUpdate()

    Dim strRowSource As String, strSpecialty

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

    Me.cboPhysician.RowSource = strRowSource
    Me.cboPhysicianSpecialty.ControlSource = "PhysicianSpecialty"

    If Me.cboPhysicianSpecialty.ListCount = 0 Then
        'The physician has NO specialties... yet.
        If MsgBox("There are no specialties for this physician.  Would you like to enter one?", vbQuestion + vbYesNo, "No Specialty!") = vbYes Then
            strSpecialty = Trim(Nz(InputBox("Enter Specialty", "New Specialty"), ""))

            If Len(strSpecialty) = 0 Then
                'User enters nothing.
                Msgbox("No specialty specified.", vbCritical, "Alert!"
            Else
                'User enters specialty.
                DoCmd.SetWarnings False
                    DoCmd.RunSQL "INSERT INTO [BHCS_Physicians] (Physician, Specialty) SELECT '" & Me.cboPhysician & "' as Physician, " & strSpecialty & " as Specialty);"
                DoCmd.SetWarnings True
         
                'This next line refreshes the specialty combobox to add the new specialty.
                Me.cboPhysicianSpecialty.Requery
            End If
        End If
    End If

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

    cboPhysician = StrConv(cboPhysician, vbProperCase)

End Sub

I have not tested this. If you have more fields in the table BHCS_Physicians other than an autonumbered primary key, then you have to include them in the SELECT statement.

SerranoG · June 13th, 2008, 09:23 AM

Oops... I forgot to put something in the INPUT BOX prompt.

strSpecialty = Trim(Nz(InputBox("Enter Specialty.", "New Specialty"), ""))

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

eusanpe · June 16th, 2008, 09:29 AM

Thank you for the advice and code. I will play with it.

I have removed the specialty table for now. I just have a table called Physicians that has the fields (Physicians and Specialty).

I am not interested in a msgbox. I have code already to Insert a physician and specialty if there isn't a physician listed. I just have a few physicians in the table that never had a specialty associated with them. I just want to select one in the combo box and type in the specialty in the specialty combo box, press 'save' and the table is updated.

Thank you for your help,
Tony

SerranoG · June 16th, 2008, 09:42 AM

OK, in that case, instead of the INPUT BOX you'd you have a MESSAGE BOX saying that there is no specialty and to type one into the combobox. You'd have to set the combobox's LIMIT TO LIST property to NO so that people can type in new specialties. You'll still have to requery the combobox so that the new specialty will show up if you add another new record with that same physician.

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

eusanpe · June 16th, 2008, 10:00 AM

I tried modify the code to look like this but it didn't work.

If Me.cboPhysicianSpecialty.ListCount = 0 Then
    If Len(Nz(Me.cboPhysicianSpecialty, "")) > 0 Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE BHCS_Physicians SET
        BHCS_Physicians.Specialty = '" & Me.cboPhysicianSpecialty & "' WHERE
        [Physician] = '" & Me.cboPhysician & "'"
        DoCmd.SetWarnings True

           'This next line refreshes the specialty combobox to add the new specialty.
        Me.cboPhysicianSpecialty.Requery
    End If
End If

SerranoG · June 16th, 2008, 04:04 PM

For the DoCmd statement, try

DoCmd.RunSQL "INSERT INTO [BHCS_Physicians] (Physician, Specialty) SELECT '" & Me.cboPhysician & "' as Physician, " & strSpecialty & " as Specialty);"

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

eusanpe · June 17th, 2008, 06:51 AM

I have a couple of questions:

1. I am not using an input box so 'strSpecialty' is not being used
2. What happens if there is a physician already in the table and no specialty assigned to them? The table would need to be Updated instead of Inserted right? I already have code for an insert of the (Physician and Specialty) to the table.

With your example, the strSpecialty variable is assigned from an input box. I was just using me.cboSpecialty after I typed the specialty in the specialty combo box but it is not working.

Thanks for all of your help.

My subroutine is below:

Private Sub cboPhysician_AfterUpdate()
Dim strRowSource As String

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

Me.cboPhysicianSpecialty.RowSource = strRowSource
Me.cboPhysicianSpecialty.ControlSource = "PhysicianSpecialty"

If Me.cboPhysicianSpecialty.ListCount = 0 Then
    If Len(Nz(Me.cboPhysicianSpecialty, "")) > 0 Then
        DoCmd.SetWarnings False
        'DoCmd.RunSQL "UPDATE BHCS_Physicians SET BHCS_Physicians.Specialty = '" & Me.cboPhysicianSpecialty & "' WHERE [Physician] = '" & Me.cboPhysician & "'"

        DoCmd.RunSQL "INSERT INTO [BHCS_Physicians] (Physician, Specialty) SELECT '" & Me.cboPhysician & "' as Physician, " & cboPhysicianSpecialty & " as Specialty);"
        DoCmd.SetWarnings True
        Me.cboPhysicianSpecialty.Requery
    End If
End If

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

cboPhysician = StrConv(cboPhysician, vbProperCase)

End Sub

SerranoG · June 17th, 2008, 07:11 AM

Oh, sorry... I didn't notice the strSpecialty. You're right. Instead of that you'd use the contents of the combobox.

To Append:

DoCmd.RunSQL "INSERT INTO BHCS_Physicians (Physician, Specialty) SELECT '" & Me.cboPhysician & "' as Physician, " & Me.cboPhysicianSpecialty & " as Specialty);"

To Update:

DoCmd.RunSQL "UPDATE BHCS_Physicians SET [Specialty] = '" & Me.cboPhysicianSpecialty & "' WHERE [Physician] = '" & Me.cboPhysician & "'"

In your code, you seem to have both sets of code back-to-back. Why? You do either one OR the other, not both. If you have a specialty table, why would you have a doctor's name in it with no specialty? I would expect the doctor to be in the physicians table and be in the specialty table ONLY if there is at least one specialty. If the doctor has no specialty yet, he or she should not be in the specialty table at all. Therefore, no need to UPDATE... just APPEND.

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

eusanpe · June 17th, 2008, 08:59 AM

Thank you.. I took out the Specialty table and just have a Physicians table with the Physician and Specialty fields only.

I have both in there because one is commented out.It is hard to see the apostrophe.

In the Physician table, I have some physicians listed that don't have a specialty yet because I don't know what they are just yet. I wanted to have them update it on the form if they select a physician that doesn't display a specialty for them.

Does that make sense? I just want to update the data if a specialty is added for a physician that doesn't have a specialty associated with them.

Edit...This is my final code but it isn't updating the table.

================================================== ===========
Private Sub cboPhysician_AfterUpdate()
Dim strRowSource As String

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

Me.cboPhysicianSpecialty.RowSource = strRowSource
Me.cboPhysicianSpecialty.ControlSource = "PhysicianSpecialty"

If Me.cboPhysicianSpecialty.ListCount = 0 Then
    If Len(Nz(Me.cboPhysicianSpecialty, "")) > 0 Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE BHCS_Physicians SET [Specialty] = '" & Me.cboPhysicianSpecialty & "' WHERE [Physician] = '" & Me.cboPhysician & "'"
        DoCmd.SetWarnings True
        Me.cboPhysicianSpecialty.Requery
    End If
End If

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

cboPhysician = StrConv(cboPhysician, vbProperCase)
================================================== ==========