CurrentRecord.Field#

  • Thread starter Thread starter Bret Hilton
  • Start date Start date
B

Bret Hilton

When designing a form, how do I refer to a field in the
record currently being edited?

Specifically, if I specify "CustomerZ" on an Order form,
how do I make only the contacts who work for "CustomerZ"
show up in the "Contacts" combo box?

This is an age-old question, I'm sure, but it is one I
have been struggling with for months!

Thanks for your help,

Bret
brethiltonAThotmail
 
Firstly, some assumptions
1. You have a Customer table (lets call it tblCustomer)
with a primaryKey (lets call it customerId) and a name
column (lets call it customerName)
2. You have a Contacts table (lets call it tblContact) with
a primary key (contactId), a name column (contactName) and
the foreign key to the Customer table (customerId)
3. You have a comboBox (cmbCustomer) for selecting the
Customer. It's row source will be "SELECT customerName,
customerId FROM tblCustomer;"
4. You have a comboBox (cmbContact) for selecting the Contact.

The method I prefer is to produce the RowSource SQL of the
combo box cmbContact on the fly in the AfterUpdate event
handler of cmbCustomer e.g.

Private Sub cmbCustomer_AfterUpdate()
cmbContact.RowSource = "SELECT contactName FROM tblContact
WHERE customerId = " & cmbCustomer.Column(1)
End Sub

Obviously you will have to change all the control names and
table names to fit your own application.

Hope That Helps
Gerald Stanley MCSD
 
By Grepthar's hammer, I found the answer!

Refer to post ...
Subject: combo box query
From: "lynn atkinson" Sent: 3/11/2004 5:11:56 AM

My final sub looks like this:

Private Sub CustomerID_AfterUpdate()
ContactID.RowSource = "SELECT [ContactID] FROM tblContacts
WHERE [CustomerID] = " & CustomerID.Value
End Sub

Thanks for your help! This newsgroup has been my biggest
help.
 
Now when I scroll through the records using the form the
Contacts field correctly displays the contact name ONLY IF
the contact is associated with the last CustomerID used.
In other words, if I selected "Customer1" last then the
only valid contact rowsource are the records that are
related to "Customer1". I hope all that makes sense.

Thanks again for your help!

Bret
 
Try moving the code from the customer comboBox AfterUpdate
eventhandler to the Contact comboBox GotFocus eventHandler.

Alternatively, put the SQL in to the contact comboBox
RowSource property at the design stage and put
cmbContact.Requery in the customer comboBox AfterUpdate
eventhandler.

Hope This Helps
Gerald Stanley MCSD
 
Hey Gerald,

I really appreciate your help.

I moved the code to the gotFocus event for the ContactID
combo box, but still have essentially the same problem.

I'm not sure what you mean with your second suggestion.
Maybe you could say it a different way? I'm ready to try
anything to get this to work.

Thanks again!

Bret

By the way, it's a very small database (~512KB), so I
could email it or post it for downloading if anyone would
like to take a look at the problem I'm trying to describe.
 
Back
Top