Combo Box Lookup

  • Thread starter Randy Christiansen
  • Start date

R

Randy Christiansen

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.
 
Ad

Advertisements

J

Jeanette Cunningham

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
 
R

Randy Christiansen

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


Randy Christiansen said:
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.
 
Ad

Advertisements

J

Jeanette Cunningham

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.
 

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

Top