Access Buttons

[Guest]

I have a button that opens an address form fron an Order form by finding
matching OrderID numbers in the address and order tables.
I need to get the VBA code that looks to see if there is already a record in
the address table with the same ID number and open the form on that record,
and if there is no existing record in the address table, open a new form
creating a new address record carrying across the Order ID number.

Can you help?

 

[fredg]

I have a button that opens an address form fron an Order form by finding
matching OrderID numbers in the address and order tables.
I need to get the VBA code that looks to see if there is already a record in
the address table with the same ID number and open the form on that record,
and if there is no existing record in the address table, open a new form
creating a new address record carrying across the Order ID number.

Can you help?

[OrderID] is a Number datatype?
Something like this?

If DCount("*","[AddressTable]","[OrderID] = " & Me![OrderID]) > 0 Then
'There is an existing order
DoCmd.OpenForm "AddressFormName", , , "[OrderID] = " & Me![OrderID]
Else
' No existing order
DoCmd.OpenForm "AddressFormName", , , , , acDialog,[OrderID]
' The address form will remain open until you enter the new data and
close the form.
End If

Code the "FormName" Load event:
If Not IsNull(Me.OpenArgs) then
DoCmd.RunCommand acCmdRecordsGoToNew
Me![OrderID] = Val(Me.OpenArgs)
End If

 

[Guest]

Fred,

Thanks for your help, it's worked a treat.

Regards

Martin

I have a button that opens an address form fron an Order form by finding
matching OrderID numbers in the address and order tables.
I need to get the VBA code that looks to see if there is already a record in
the address table with the same ID number and open the form on that record,
and if there is no existing record in the address table, open a new form
creating a new address record carrying across the Order ID number.

Can you help?

[OrderID] is a Number datatype?
Something like this?

If DCount("*","[AddressTable]","[OrderID] = " & Me![OrderID]) > 0 Then
'There is an existing order
DoCmd.OpenForm "AddressFormName", , , "[OrderID] = " & Me![OrderID]
Else
' No existing order
DoCmd.OpenForm "AddressFormName", , , , , acDialog,[OrderID]
' The address form will remain open until you enter the new data and
close the form.
End If

Code the "FormName" Load event:
If Not IsNull(Me.OpenArgs) then
DoCmd.RunCommand acCmdRecordsGoToNew
Me![OrderID] = Val(Me.OpenArgs)
End If