trying to run vba code that will use a value from an input box in a query

Here is the code.
Dim PriAcct, FamAcct As String
    '
    ' Get the data
    '
    PriAcct = InputBox("ENTER THE PRIMARY ACCOUNT:", "PRIMARY ACCOUNT INPUT BOX")
    '
    ' Check to see if any data was entered
    '
    If PriAcct <> "" Then
        '
        ' Run the table updates
        '
        SQL_Update_Appt_Qry = "UPDATE ClientInfo INNER JOIN tblAppointments ON ClientInfo.ClientID = tblAppointments.ClientID_FK SET tblAppointments.DisplayName = [ClientInfo]![DisplayName], tblAppointments.PrimaryClientName_C = [ClientInfo]![PrimaryClientName_C], " & _
        " tblAppointments.DisplayNameM = [ClientInfo]![DisplayNameM], tblAppointments.DisplayNamePC = [ClientInfo]![DisplayNamePC], tblAppointments.DisplayNameFM = [ClientInfo]![DisplayNameFM], tblAppointments.MaritalStatus = [ClientInfo]![MaritalStatus], tblAppointments.FamilyMember_C = [ClientInfo]![FamilyMember_C] " & _
        " WHERE (((tblAppointments.RIGAcct)= PriAcct))"
        DoCmd.RunSQL SQL_Update_Appt_Qry

This is giving me a syntax error (missing operator)
VGuerra67Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Assuming that is a string datatype column, then try:

SQL_Update_Appt_Qry = "UPDATE ClientInfo INNER JOIN tblAppointments ON ClientInfo.ClientID = tblAppointments.ClientID_FK SET tblAppointments.DisplayName = [ClientInfo]![DisplayName], tblAppointments.PrimaryClientName_C = [ClientInfo]![PrimaryClientName_C], " & _
" tblAppointments.DisplayNameM = [ClientInfo]![DisplayNameM], tblAppointments.DisplayNamePC = [ClientInfo]![DisplayNamePC], tblAppointments.DisplayNameFM = [ClientInfo]![DisplayNameFM], tblAppointments.MaritalStatus = [ClientInfo]![MaritalStatus], tblAppointments.FamilyMember_C = [ClientInfo]![FamilyMember_C] " & _
" WHERE (((tblAppointments.RIGAcct)='" & PriAcct & "'))"

Open in new window

if it's a numeric type, then drop the single quotes, like:

SQL_Update_Appt_Qry = "UPDATE ClientInfo INNER JOIN tblAppointments ON ClientInfo.ClientID = tblAppointments.ClientID_FK SET tblAppointments.DisplayName = [ClientInfo]![DisplayName], tblAppointments.PrimaryClientName_C = [ClientInfo]![PrimaryClientName_C], " & _
" tblAppointments.DisplayNameM = [ClientInfo]![DisplayNameM], tblAppointments.DisplayNamePC = [ClientInfo]![DisplayNamePC], tblAppointments.DisplayNameFM = [ClientInfo]![DisplayNameFM], tblAppointments.MaritalStatus = [ClientInfo]![MaritalStatus], tblAppointments.FamilyMember_C = [ClientInfo]![FamilyMember_C] " & _
" WHERE (((tblAppointments.RIGAcct)=" & PriAcct & "))"

Open in new window


»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill PrewIT / Software Engineering ConsultantCommented:
And for readability I might format the query this way in the code.

SQL_Update_Appt_Qry = "UPDATE clientinfo " & _
                      "       INNER JOIN tblappointments " & _
                      "               ON clientinfo.clientid = tblappointments.clientid_fk " & _
                      "SET    tblappointments.displayname = [clientinfo]![displayname], " & _
                      "       tblappointments.primaryclientname_c = [clientinfo]![primaryclientname_c], " & _
                      "       tblappointments.displaynamem = [clientinfo]![displaynamem], " & _
                      "       tblappointments.displaynamepc = [clientinfo]![displaynamepc], " & _
                      "       tblappointments.displaynamefm = [clientinfo]![displaynamefm], " & _
                      "       tblappointments.maritalstatus = [clientinfo]![maritalstatus], " & _
                      "       tblappointments.familymember_c = [clientinfo]![familymember_c] " & _
                      "WHERE  tblappointments.rigacct = " & PriAcct & "'"

Open in new window


»bp
VGuerra67Author Commented:
Thanks a great help
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
adding on ...
this:
Dim PriAcct, FamAcct As String
is dimensioning PriAcct as a variant, not a string -- As String needs to be specified each time
... is that the data type? or is it a number?

>  " WHERE (((tblAppointments.RIGAcct)= PriAcct))"

inside the SQL string, PriAcct cannot be determined.  Its value was gotten with VBA and needs to be inserted into the SQL*. When that happens, the value needs delimiters around it if it is not a number.  Strings use ' or " to delimit (mark beginning and end).

if PriAcct is a number, delimiters will not be needed -- Bill gave you examples for each case (string and number -- data type). Just because InputBox always returns a string does not mean that only a string can be handled. If the return value is assigned to a variable with a specified data type, Access will implicitly do the conversion

* there are other ways to get the value into the query such as setting a database property or global variable and having the query use a wrapper function to get it

~~~
better to use
CurrentDb.Execute SQL_Update_Appt_Qry
than
DoCmd.RunSQL SQL_Update_Appt_Qry

performance is better and no need to worry about warnings and echo ;)

if CurrentDb will be used more than once in a procedure, it is also a good idea to:

   'dimension database object variable
   dim db as dao.database
   'set database object variable to current database
   set db = CurrentDb
   'execute SQL statement
   db.Execute strSQL 
   ' ... more code ...
   'release database object variable
   set db = nothing

Open in new window


have an awesome day,
crystal
Fabrice LambertConsultingCommented:
Hi,

If I can add mygrain of sand:
Concatenate string variables with queries have issues, especially if the variable contain a quotes, double quotes or a mix a single quotes and double quotes.

Let's say you have a table representing books, with an ID, an ISBN and a TITLE column.
What will you do if the user provide as title: Tom Clancy's "Splinter cell" ?
You could replace double quotes by single quotes, but the user expect retrieving the same title he entered earlyer.

The solution is to use a parameterised query (the query builder is of great help here):
- Write your query
- Create a query def
- Provide parameters
- Run the query def
- Destroy the query def

In code, it can look like Something like this:
Public Sub test()
On Error GoTo Error
    Dim strSQL As String
    Dim db As DAO.database
    Dim qd As DAO.QueryDef
    
        '// define the query with parameters
    strSQL = vbNullString
    strSQL = strSQL & "PARAMETERS p_ISBN Text (255)," & vbCrLf
    strSQL = strSQL & "           p_Title Text (255);" & vbCrLf
    strSQL = strSQL & "INSERT INTO Book (ISBN," & vbCrLf
    strSQL = strSQL & "                  Title)" & vbCrLf
    strSQL = strSQL & "VALUES ([p_ISBN]," & vbCrLf
    strSQL = strSQL & "        [p_Title]);"

    Set db = CurrentDb
        '// create a temporary query def
    Set qd = db.CreateQueryDef("tempQd", strSQL)
        '// provide parameters
    qd.Parameters("p_ISBN").value = "978-2-8688-9006-1"
    qd.Parameters("p_Title").value = "Tom Clancy's ""Splinter cell"""
        '// run the query def
    qd.Execute dbFailOnError
        '// destroy the querydef
    qd.Close
    db.QueryDefs.Delete qd.name
        '// cleanup
    Set qd = Nothing
    Set db = Nothing
Exit Sub
Error:
        '// cleanup
    If Not (qd Is Nothing) Then
        qd.Close
        db.QueryDefs.Delete qd.name
        Set qd = Nothing
    End If
    If Not (db Is Nothing) Then
        Set db = Nothing
    End If
        '// display the error (or raise it)
    MsgBox Err.Description
End Sub

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.