OpenRecordSet syntax error

[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?

 

[Stuart McCall]

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?

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.

 

[Micki]

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.

 

[Ron Weiner]

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

 

[Micki]

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.

 

[Stuart McCall]

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.

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.

 

[Micki]

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 ='

 

[Stuart McCall]

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)

<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)

 

[Rick Brandt]

<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)

There is no requirement that a field in the WHERE clause must appear in the
SELECT list.

 

[Rick Brandt]

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 ='

The form you are referencing is open right? And there is a numeric value
currently in the Cust field/control?

 

[Stuart McCall]

There is no requirement that a field in the WHERE clause must appear in
the
SELECT list.

You're right, thanks. I couldn't think of anything else.

 

[Ron Weiner]

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