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

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 & "'"

Select all Open in new window

---

»bp

Thanks a great help

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

Select all Open in new window

have an awesome day,
crystal

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

Select all Open in new window