Too few parameters when using strSQL to update field

[JenniferM]

I'm having a problem with an SQL string that updates a field (RefPhysicianID) in the table TblPatientDemographics. The user navigates through a search, the results are displayed in a list box (in which the bound column is RefPhysicianID in TblRefPhysician), and the following code is used to update the field that links the records between two tables.

Private Sub BtnSelectPhysician_Click()
 
If MsgBox("Assign physician to patient?", vbYesNo + vbQuestion) = vbYes Then
DoCmd.SetWarnings False
 
Dim strSQL As String
Dim db As DAO.Database
 
Set db = CurrentDb
 
strSQL = "UPDATE [TblPatientDemographics] " & _
    "SET [RefPhysicianID] = Me.LbxPhysicianSearchResults " & _
    "WHERE ([PtID] = "
strSQL = strSQL & [Forms]![FrmPatientDemographics].[PtID] & ")"
 
Call db.Execute(strSQL)
DoCmd.SetWarnings True
End If
 
End Sub

I'm getting a "too few parameters, Expected: 1" Error message. I find this odd because when I used

SET [RefPhysicianID] = Null

in my SQL String, it worked fine (updated the field to Null).

Any ideas on what I'm doing wrong?

[Delerna]

Try this

strSQL = "UPDATE [TblPatientDemographics] " _ 
       & "SET [RefPhysicianID] =" & Me.LbxPhysicianSearchResults & " " _ 
       & "WHERE ([PtID] = " & [Forms]![FrmPatientDemographics].[PtID]
 

[JenniferM]

 & "WHERE ([PtID] = " & [Forms]![FrmPatientDemographics].[PtID] & ")"

I had to change the last line to this, but that's only because the string it made was missing a ")"..... Thanks a lot for your help!!

[NeoPa]

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[mshmyob]

Good point Neo.

Whenever I build my SQL strings and have a problem I have used the MSGBOX method for years (I also use it to see values of my variables - it is quicker and easier than the immediate window sometimes). It will display the completed SQL string and usually I see that I missed a quote or forgot to put in a space or I am passing a NULL value etc.

Then it is easy to fix.

cheers,