I am trying to use DLOOKUP to check another table for the current form record, and if it returns null (ie other table doesn't already have it) then to proceed with Appending the current record. I have two tables, both connected to their respective Forms.
Source Table is Jobtkt, with it's PK being [Job No]. Destination Table is Scheduled Items with it's PK also being [Job]. I have inherited this database that is older than I am, which also was not built in a relational way whatsoever, and I am currently trying to restructure what I can to a more correct process (this previously was done with a copy & paste macro!).
My issue here is that I don't know what to put for the false side of the expression in my IIF statement. So far I have:
IIf((DLookUp(JOB, [SCHEDULED ITEMS], "JOB NO = " & [JOB NO])), Error(3012]),
My plan to execute this is to make a macro that runs an append query, and then in VBA run that macro on button click in the form. Any assistance would be very appreciated as I am out of my depth with this one.
If there is any more information that would help you help me please ask, I am currently stuck and a critical part of our processes are down while the old macro is broken.
It could be something like:
If IsNull(DLookUp("[JOB]", "[SCHEDULED ITEMS]", "[JOB NO] = " & Me![JOB NO].Value & "")) Then
' Run append query.
Else
' Job No. is OK.
End If
If Job No is text, then wrap in single-quotes:
"[JOB NO] = '" & Me![JOB NO].Value & "'"