OpenRecordSet syntax error

  • Thread starter Thread starter Micki
  • Start date Start date
M

Micki

I am trying for a simple way to have an unbound control auto-filled based on
another field on the form. I keep getting a syntax error no matter how I
have manipluated my OpenRecordSet statement. Currently, I have

Private Sub Form_Current()
Dim strClient As DAO.Recordset

Set strClient = CurrentDb.OpenRecordset ("SELECT Customer" & "FROM
tblCustomers" & "WHERE CustomerID=" & "Forms!frmJobs!Cust")
Me.ClientName = strClient

End Sub

Can some one please point out my error?
 
Micki said:
I am trying for a simple way to have an unbound control auto-filled based
on
another field on the form. I keep getting a syntax error no matter how I
have manipluated my OpenRecordSet statement. Currently, I have

Private Sub Form_Current()
Dim strClient As DAO.Recordset

Set strClient = CurrentDb.OpenRecordset ("SELECT Customer" & "FROM
tblCustomers" & "WHERE CustomerID=" & "Forms!frmJobs!Cust")
Me.ClientName = strClient

End Sub

Can some one please point out my error?
Click to expand...

Remove the quotes surrounding:

Forms!frmJobs!Cust

otherwise VBA will interpret that as a literal string, ie:

F - o - r - m - s - ! - f - r - m etc.
 
Unfortuantely, that doesn't work either. The debug error I'm getting is

Run-time error '3075':
Syntax error (missing operator) in query expression
'CustomerFromtblCustomersWHERECustomerID='.

Further help would be greatly appreciated.
 
Your are missing a bunch of spaces in your sql statement Change...

"SELECT Customer" & "FROM tblCustomers" & "WHERE CustomerID=" &
"Forms!frmJobs!Cust"

To

"SELECT Customer FROM tblCustomers WHERE CustomerID=" & Forms!frmJobs!Cust

Rdub
 
No, I have spaces in the VBA (see below); it's just that the debug message
runs everything together. I tried your quotes configuration as well & I'm
still getting a missing operator error.
 
Micki said:
No, I have spaces in the VBA (see below); it's just that the debug message
runs everything together. I tried your quotes configuration as well & I'm
still getting a missing operator error.
Click to expand...

Debug messages from Access do not remove spaces, so no matter that you think
you've included spaces, you haven't.

If it's true you have a table called tblCustomers that contains fields
CustomerID and Customer, and it's also true that you have a frmJobs which
contains a control Cust, then Ron's code will function perfectly.
 
I don't know what to tell you, guys. I am still getting a missing operator
message. Currently my statement is:

Set strClient = CurrentDb.OpenRecordset("SELECT Customer FROM tblCustomers
WHERE CustomerID =" & Forms!frmJobs!Cust)

And the debug message is:

Run-time Error '3075'
Syntax error (missing operator) in query expression 'CustomerID ='
 
Micki said:
I don't know what to tell you, guys. I am still getting a missing operator
message. Currently my statement is:

Set strClient = CurrentDb.OpenRecordset("SELECT Customer FROM tblCustomers
WHERE CustomerID =" & Forms!frmJobs!Cust)
Click to expand...

<slapping forehead> Try:

Set strClient = CurrentDb.OpenRecordset("SELECT CustomerID,Customer FROM
tblCustomers WHERE CustomerID =" & Forms!frmJobs!Cust)

(sometimes VBA reports Missing Operator when it really means Missing
Operand)
 
Stuart said:
<slapping forehead> Try:

Set strClient = CurrentDb.OpenRecordset("SELECT CustomerID,Customer FROM
tblCustomers WHERE CustomerID =" & Forms!frmJobs!Cust)

(sometimes VBA reports Missing Operator when it really means Missing
Operand)
Click to expand...

There is no requirement that a field in the WHERE clause must appear in the
SELECT list.
 
Micki said:
I don't know what to tell you, guys. I am still getting a missing
operator
message. Currently my statement is:

Set strClient = CurrentDb.OpenRecordset("SELECT Customer FROM tblCustomers
WHERE CustomerID =" & Forms!frmJobs!Cust)

And the debug message is:

Run-time Error '3075'
Syntax error (missing operator) in query expression 'CustomerID ='
Click to expand...

The form you are referencing is open right? And there is a numeric value
currently in the Cust field/control?
 
Rick Brandt said:
There is no requirement that a field in the WHERE clause must appear in
the
SELECT list.
Click to expand...

You're right, thanks. I couldn't think of anything else.
 
I was just looking over this thread and your code again.

Even if you do manage to get the sql statement working, the program will
bomb on your very next line of code. "Me.ClientName = strClient" will fail!
Even though strClient looks like it might be a string variable, it was
declared as a DAO.Recordset. You can not assign a recordset as the value of
(I am guessing here) a textbox on your form.

You will need to:

1) Figure out what value is in Forms!frmJobs!Cust. Based on your Sql
statement it MUST be NUMERIC. It can not be Null

2) Write real code that assign's the value in Customer to the (I am
still guessing here) a textbox named ClientName.

3) Then you should explicitly close the recordset and destroy the object

Rdub
 
Back
Top