Dlookup Syntax Error

[CharlieT302]

Hi Folks,

I have a primary form which contains a button that compares data in its subform to the data in another table.

Here is the code:
If DLookup("Site_ID", "tbl_Calendar_Exceptions", "Site_ID='" & Forms!frm_Copy_Order![frm_Copy_Order_Detail].Form.[Site_ID] & "'" & " AND Except_Date=#" & Forms!frm_Copy_Order![frm_Copy_Order_Detail].Form.[New_Date] & "#") Then...

At the top of the code, I have an "On Error Goto Verify" command. I keep getting the message that I have a syntax error. If I remove the error message from the Verify: line at the bottom of the screen, the If statements (I have others) do their job. However, the end of the code should return the focus to the first record. This does not happen, so the error is having an effect albeit a slight one.

By the way, I have this same code attached to the On Open event of the subform, which works flawlessly. The only diff is that the On Open code obviously does not need to reference a subform (it is the subform):

Subform (On Open code) This works without problem.
If DLookup("Site_ID", "tbl_Calendar_Exceptions", "Site_ID='" & Me![Site_ID] & "'" & " AND Except_Date=#" & Me![New_Date] & "#") Then...

Can any see a syntax error in the top code?

Thanks

 

[lameid]

Two thoughts...

You are not really testing anything. Dlookup may return 0 for some particular thing which is the only way you will get a false unless there are no values in which case you get a null which I believe would generate an error. Perhaps you want the Dcount function instead?

Secondly, you mention that this code is on the parent form. What event is it on? depending on when it runs the values in the subform may not be available. For example they are not during the main form's on open event. I'm not sure much after that. try displaying the values in message boxes to check it out.

 

[CharlieT302]

Lameid

To answer your questions, the code in the parent form is attached to a command button.

The code tests to see if a match exists between two fields in the tbl Calendar Exceptions table and that of the active subform record. If a match exists, it flags it as invalid.

This code comes into play in two instances:
1. I have a command button an unrelated form (we'll call it Form A) which generates the records contained in the mentioned Main/subform (Form B). After the records are generated, Form B displays. Form B's subform has the same code on the On Open event. It works perfectly here and accurately tests and flags invalid records.

2. On Form B, I have a button that performs the same test. This is to allow the user to correct invalid records and then retest. If I remove the "On Error goto.." statement from the code, it accurately tests records, although it will not return the focus to record 1 when complete. When I keep the "On Error goto..." statement, it stops the code and flags it as invalid syntax.

Note: there is much more code in the command button's On Click Event. However, it is not relevant to problem. Only the line stated is flagged as invalid syntax.

 

[lameid]

The code tests to see if a match exists between two fields in the tbl Calendar Exceptions table and that of the active subform record. If a match exists, it flags it as invalid.

Relying on null meaning false makes me nervous so I would still use Dcount but I see that it should work. Syntax looks good so..

Start trying this list.

Compact/Repair
Start Access with the decompile switch and the database
Compact/Repair
Import Everything into a new MDB file
Compile