open a record if it exists; otherwise add new record

  • Thread starter Thread starter Christopher Glaeser
  • Start date Start date
C

Christopher Glaeser

I am using the following code to open an existing invoice for a workorder
....

DoCmd.OpenForm "frmInvoice", , , "WorkOrderID=" & Me.WorkOrderID

and the following code to create a new invoice for a workorder ...

DoCmd.OpenForm "frmInvoice", DataMode:=acFormAdd

What is a good method to check first to see if an invoice exists for a
workorder before creating a new invoice?

Best,
Christopher
 
You can use the DLookup to determine whether it exists. I am guessing you
will want to look in the invoice table to see if one has the workorderid. If
this is not correct, you will have to modify the DLookup to make it work for
you, but this will give you the idea:

If IsNull("[InvocieNumber]", "InVoiceTable", "[WorkOrderId] = '" _
& Me.WorkOrderID & "'") Then
DoCmd.OpenForm "frmInvoice", DataMode:=acFormAdd
Else
DoCmd.OpenForm "frmInvoice", , , "WorkOrderID=" & Me.WorkOrderID
End If
 
If IsNull("[InvocieNumber]", "InVoiceTable", "[WorkOrderId] = '" _
& Me.WorkOrderID & "'") Then
Click to expand...

Does the function IsNull() accept three arguments?

Best,
Christopher
 
Many thanks!! For future reference, I added the call to DLookup ...

If IsNull(DLookup("[InvocieNumber]", "InVoiceTable", "[WorkOrderId] = '" _
& Me.WorkOrderID & "'")) Then

Best,
Christopher
 
Back
Top