What is wrong with this statment?

[Joel]

I am trying to use a SQL query as the parameter to open a recordset.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

Set strSQL = "SELECT TOP 1 Vehicles.EmployeeID, Vehicles.InsuCompany,
Vehicles.InsuFirstName, Vehicles.InsuLastName, Vehicles.InsuAddress,
Vehicles.InsuCity, Vehicles.InsuState, Vehicles.InsuZip,
Vehicles.PolicyNumber, Vehicles.InsuEffectiveDate,
Vehicles.InsuExpirationDate " & _
"FROM Vehicles " & _
"WHERE [Vehicles].[EmployeeID] = " & [Forms]![Employees]![tbEmployeeID] & ";"

Set rst = db.OpenRecordset(strSQL)

I get the error "Compile Error: Object required" at this part
" & [Forms]![Employees]![tbEmployeeID] & ";"

I setup a msgbox to display tbEmployeeID and it displays it so I don't why
its giving me an object error.

Can anyone explain this?

Thanks

 

[Douglas J Steele]

It's hard to tell with the word wrap whether the rest of strSQL is correct
or not.

Are there only 3 lines in your code (the first two ending with & _)?

You could always declare a new variable

Dim strEmployee As String

strEmployee = [Forms]![Employees]![tbEmployeeID]

and then use strEmployee in the SQL statement.


(BTW, it's not necessary to have the closing semi-colon)

 

[Joel]

Thanks for the reply Doug.

Are there only 3 lines in your code (the first two ending with & _)?

Yes

I tried to set it as a String:
strEmployee = [Forms]![Employees]![tbEmployeeID]

Then it gives me the same error but for: ( error in quotes)
"strSQL" = ...

I have never had this problem declaring a SQL query before.

Thanks

It's hard to tell with the word wrap whether the rest of strSQL is correct
or not.

Are there only 3 lines in your code (the first two ending with & _)?

You could always declare a new variable

Dim strEmployee As String

strEmployee = [Forms]![Employees]![tbEmployeeID]

and then use strEmployee in the SQL statement.


(BTW, it's not necessary to have the closing semi-colon)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to use a SQL query as the parameter to open a recordset.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

Set strSQL = "SELECT TOP 1 Vehicles.EmployeeID, Vehicles.InsuCompany,
Vehicles.InsuFirstName, Vehicles.InsuLastName, Vehicles.InsuAddress,
Vehicles.InsuCity, Vehicles.InsuState, Vehicles.InsuZip,
Vehicles.PolicyNumber, Vehicles.InsuEffectiveDate,
Vehicles.InsuExpirationDate " & _
"FROM Vehicles " & _
"WHERE [Vehicles].[EmployeeID] = " & [Forms]![Employees]![tbEmployeeID] & ";"

Set rst = db.OpenRecordset(strSQL)

I get the error "Compile Error: Object required" at this part
" & [Forms]![Employees]![tbEmployeeID] & ";"

I setup a msgbox to display tbEmployeeID and it displays it so I don't why
its giving me an object error.

Can anyone explain this?

Thanks

 

[Chaim]

Don't 'set strSQL'. strSQL is not an object. 'SET' is used to initialize an
object, not a scalar or string. Just use

strSQL = <Your sql string>

--

Chaim

Thanks for the reply Doug.

Are there only 3 lines in your code (the first two ending with & _)?

Yes

I tried to set it as a String:
strEmployee = [Forms]![Employees]![tbEmployeeID]

Then it gives me the same error but for: ( error in quotes)
"strSQL" = ...

I have never had this problem declaring a SQL query before.

Thanks

It's hard to tell with the word wrap whether the rest of strSQL is correct
or not.

Are there only 3 lines in your code (the first two ending with & _)?

You could always declare a new variable

Dim strEmployee As String

strEmployee = [Forms]![Employees]![tbEmployeeID]

and then use strEmployee in the SQL statement.


(BTW, it's not necessary to have the closing semi-colon)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to use a SQL query as the parameter to open a recordset.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

Set strSQL = "SELECT TOP 1 Vehicles.EmployeeID, Vehicles.InsuCompany,
Vehicles.InsuFirstName, Vehicles.InsuLastName, Vehicles.InsuAddress,
Vehicles.InsuCity, Vehicles.InsuState, Vehicles.InsuZip,
Vehicles.PolicyNumber, Vehicles.InsuEffectiveDate,
Vehicles.InsuExpirationDate " & _
"FROM Vehicles " & _
"WHERE [Vehicles].[EmployeeID] = " &

[Forms]![Employees]![tbEmployeeID] &
";"

Set rst = db.OpenRecordset(strSQL)

I get the error "Compile Error: Object required" at this part
" & [Forms]![Employees]![tbEmployeeID] & ";"

I setup a msgbox to display tbEmployeeID and it displays it so I don't why
its giving me an object error.

Can anyone explain this?

Thanks

 

[Van T. Dinh]

Get rid of the keyword Set" from

Set strSQL = ....

strSQL is a String variable, not an Object variable so you don't use Set
with it.

--
HTH
Van T. Dinh
MVP (Access)

Thanks for the reply Doug.

Are there only 3 lines in your code (the first two ending with & _)?

Yes

I tried to set it as a String:
strEmployee = [Forms]![Employees]![tbEmployeeID]

Then it gives me the same error but for: ( error in quotes)
"strSQL" = ...

I have never had this problem declaring a SQL query before.

Thanks

 

[Joel]

DOh! I cant beleive i missed that...

Thanks alot guys!

 

[Douglas J Steele]

You and me both, Joel! <hangs head in shame...>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

DOh! I cant beleive i missed that...

Thanks alot guys!

I am trying to use a SQL query as the parameter to open a recordset.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

Set strSQL = "SELECT TOP 1 Vehicles.EmployeeID, Vehicles.InsuCompany,
Vehicles.InsuFirstName, Vehicles.InsuLastName, Vehicles.InsuAddress,
Vehicles.InsuCity, Vehicles.InsuState, Vehicles.InsuZip,
Vehicles.PolicyNumber, Vehicles.InsuEffectiveDate,
Vehicles.InsuExpirationDate " & _
"FROM Vehicles " & _
"WHERE [Vehicles].[EmployeeID] = " & [Forms]![Employees]![tbEmployeeID] & ";"

Set rst = db.OpenRecordset(strSQL)

I get the error "Compile Error: Object required" at this part
" & [Forms]![Employees]![tbEmployeeID] & ";"

I setup a msgbox to display tbEmployeeID and it displays it so I don't why
its giving me an object error.

Can anyone explain this?

Thanks