I will write it out step by step.
It will probably still need some tweaking to work on your form.
When learning, it is always easier to do one step at a time.
Instead of trying to code it for all of billing address, zip, phone and
contact at once, comment out the code for zip, phone and contact, so you can
concentrate on the billing address.
When you have the billing address code working OK, then try the next one.
If you are new to DLookup, look up the help for it.
Ctl+G will open the immediate window, type dlookup in it, put your cursor in
the word and press f1.
Where I have put
"[KeyID] = " & Me.txtKeyID & ""
you will use whatever is the primary key for the table your form is based
on - it might be called BillingCompanyID, I don't really know what you
called it.
replace KeyID with the name of your table's primary key field
replace txtKeyID with the name of the control with the primary key in it.
Private Sub BillingCompany_AfterUpdate
'see if there is a billing address in the company info table
If Not IsNull(DLookup("[BillingAddress]", "CompanyInfo", " "[KeyID] = "
& Me.txtKeyID & "")) then
Me!BillingAddress = DLookup("[BillingAddress]", "CompanyInfo", "
"[KeyID] = " & Me.txtKeyID & "")
Else
Me!BillingAddress = Me![BillingCompany].Column(1)
End If
repeat the above code for zip, phone and contact, just change the field
names and combo columns
something like the example below.
If Not IsNull(DLookup("[Name of your zip field]", "CompanyInfo", "
"[KeyID] = " & Me.txtKeyID & "") ) then
Me![Name of your zip field]", = Me![CompanyInfo].Column(2)
Else
Me!BillingAddress = Me![BillingCompany].Column(2)
End If
Post back when you need to
Jeanette Cunningham
Randy Christiansen said:
Thank you Jeanette. I couldn't get your code to work probably because I
don't understand coding welling enough yet to tweak it.
Jeanette Cunningham said:
Hi Randy,
You can use a DLookup to see if there is a record in the Billingcompany
table.
example
warning: untested air code.
Private Sub BillingCompany_AfterUpdate()
Dim strBillingAddress as String
strBillingAddress = Nz(DLookup("[BillingAddress]", "tblBillingCompany",
"[KeyID] = " & Me.txtKeyID & "") , '")
Me!BillingAddress = IIf (Len(strBillingAddress) >0, strBillingAddress,
Me![BillingCompany].Column(1))
Jeanette Cunningham
in
message news:
[email protected]...
I have a combo box on a form that connects to table 'CompanyInfo' and
updates
other fields on the form.
Code Sample ****
Private Sub BillingCompany_AfterUpdate()
Me!BillingAddress = Me![BillingCompany].Column(1)
Me!BillingCityStateZip = Me![BillingCompany].Column(2)
Me!BillingPhone = Me![BillingCompany].Column(3)
Me!BillingContact = Me![BillingCompany].Column(4)
End Sub
END Code Sample ******
The update information is written to a table 'BillingCompany'
What I would like to do is have the combo box look in the
BillingCompany
table first and if the record exists in BillingCompany table, us that
record... if not, look up the information in CompanyInfo and update the
form.
I don't know if this is possible. Thanks in advance for any
suggestions.
