open a record if it exists; otherwise add new record

[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

 

[Guest]

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

 

[Christopher Glaeser]

If IsNull("[InvocieNumber]", "InVoiceTable", "[WorkOrderId] = '" _

& Me.WorkOrderID & "'") Then

Does the function IsNull() accept three arguments?

Best,
Christopher

 

[Christopher Glaeser]

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

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

Best,
Christopher

 

[Guest]

Oops!, Sorry about that. I guess maybe I should read before I post.