Solved Compile Error - Variable not defined

[Ashfaque]

Hello,

I am trying to get proper data thru following recordset line if 2 conditions are true i.e. CDepartment and PWDMger. These both fields are in T_Dept tbl.

But each time it satisfy fisrt condition and for second condition it says "Compile Error - Variable not defined'

Set rst = CurrentDb.OpenRecordset("Select * from T_Dept where CDepartment= '" & Forms!F_EmpOTHeader!EmpDept & "'" And PWDMgr = "Forms!F_EmpOTHeader!OTApprovedBy ", dbOpenDynaset, dbSeeChanges)

Even I tried replacing fields with each others..mean placed first condition at second place and vice versa. This time it stops at second condition with same error.

'Set rst = CurrentDb.OpenRecordset("Select * from T_Dept where PWDMgr= '" & Forms!F_EmpOTHeader!OTApprovedBy & "'" And CDepartment = " & Forms!F_EmpOTHeader!EmpDept & ", dbOpenDynaset, dbSeeChanges)

Both CDepartment and PWDMgr are text fields.

Any perticular reason?

 

[The_Doc_Man]

But each time it satisfy fisrt condition and for second condition it says "Compile Error - Variable not defined'

This is confusing because if I take this literally, I don't think it can happen. However, I believe that English is not your primary language so I have to figure that you are telling us something else.

A compile error occurs with "variable not defined" when you have Option Explicit turned on and something isn't defined in VBA expression context.

I am going to GUESS because I'm not 100% sure on this, but I would bet that you have a quoting error in that particular recordset statement in that you have exposed CDepartment to VBA. I THINK that the sequence " ' " to the left of And CDepartment really SHOULD be " '

That would probably bring your reference to CDepartment back into the SQL statement, which is where you want it... I think.

 

[Ashfaque]

This is confusing because if I take this literally, I don't think it can happen. However, I believe that English is not your primary language so I have to figure that you are telling us something else.

A compile error occurs with "variable not defined" when you have Option Explicit turned on and something isn't defined in VBA expression context.

I am going to GUESS because I'm not 100% sure on this, but I would bet that you have a quoting error in that particular recordset statement in that you have exposed CDepartment to VBA. I THINK that the sequence " ' " to the left of And CDepartment really SHOULD be " '

That would probably bring your reference to CDepartment back into the SQL statement, which is where you want it... I think.

Thanks The_Doc_Man,

Your GUESS for quotation was correct. Below worked well.

where CDepartment=' " & Forms!F_EmpOTHeader!EmpDept & " ' And PWDmgr = ' " & Forms!F_EmpOTHeader!OTApprovedBy & " ' ", dbOpenDynaset, dbSeeChanges)

 

[The_Doc_Man]

As I said, only a guess - but I'm glad it was good enough that it helped you.