Object variable or block variables not set

[Bobby1st]

Hello
After spending a lot of time figuring this out, I think it time to call on the experts. I can't make my sql work on rs(1).value. I keep on saying "object variable or block variable not set" I copied one in my module that works but won't work here. I am lost in translation.

Private Sub cmdRefreshData_Click()
On Error GoTo Err_cmdRefreshData_Click
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim ID As String
Dim Site As String
Dim Name As String

ID = Text7.Value
Set db = CurrentDb
strSQL = "SELECT * FROM [tblRecord] "
strSQL = strSQL & "WHERE [SID]= " & ID

Set rs = db.OpenRecordset(strSQL)
Site = rs(4).Value ' Location
Name = rs(5).Value ' SID name
'=====================
'Text7.Value = login id box

Thank you.

 

[odin1701]

What line is it erroring on?

 

[Bobby1st]

Set rs = db.OpenRecordset(strSQL)

After this line, F8 will jump to Error Handling message. I forced it to go resume next. The Site = rs (4). value have the object variable not set message. If I let it go to Error Handling, the message will state "too few parameter. expected 1."

Thank you.

 

[georgedwilkinson]

That means your SQL is bad. Before the line with the error (Set rs = db.OpenRecordset(strSQL)), put:

Debug.Print strSQL

run it again, and copy the results from the immediate window back to us so we can see it.

 

[Bobby1st]

Immediate window:

SELECT * FROM [tblSupervisorCurrMnth] WHERE [SuprvID]= lh


For simplicity I changed the table to tblRecord and SuprvID to SID.
The = lh is the ID on text7 to find on the table Record in what location is the user is from then it will continue to run a Select Case.

Thanks

 

[pbaldy]

Since the value is text, try

strSQL = strSQL & "WHERE [SID]= '" & ID & "'"

 

[georgedwilkinson]

Bobby,

Typically, any field named "ID" is an artificial primary key and is of number type, which means it has no meaning to the users and is used by the system to uniquely identify a record. Had we known that you had assigned ID a type of "text" this would have been resolved a long time ago.

 

[Bobby1st]

To: Pbaldy, georgedwilkinson

You're the men! Another rescue accomplished, thank you!

I have declared ID as a string in numereous occasions and sometimes I don't state the type, just Dim ID and it works. The value is associated mostly to employee ID, a text (alpha-numeric), that's why it didn't clicked on me that's where the problem is coming from.

I should learn from this, maybe use - strID next time.

Thank you

 

[georgedwilkinson]

Glad we could help! Just remember me when I need help.