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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top