open a record if it exists; otherwise add new record

  • Thread starter Christopher Glaeser
  • 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
 
K

Klatuu

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
 
C

Christopher Glaeser

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

Does the function IsNull() accept three arguments?

Best,
Christopher
 
C

Christopher Glaeser

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

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

Best,
Christopher
 
Top