Solved

Link fields on a form depending on what is chosen from a dropdown

Posted on 2006-11-21
11
600 Views
Last Modified: 2008-02-20
I'm sorry, I don't really know how to word the title for this (let alone google it), but here's what I'm trying to do:
On a form, someone chooses a customer ID from a combo box that is bound on the Customer ID field of tblIPCustomer, and then depending on what is chosen, will populate the name and address to other textboxes on the same form and also populate to the same table that everything on the form will populate to: UNEP.

I thought that I would be able to add a query to the Row Source of the combo box that I want to update so that you could choose the one customer name that the query would return from the dropdown.  I used this in the Row Source:
SELECT tblIP_Customer.Customer_Name, tblIP_Customer.Address FROM tblIP_Customer WHERE (((tblIP_Customer.Customer_id) Like Forms!frm_Add_UNEP_Order![Customer ID]));
This just gives me a blank field.  When I run the query on it's own, I still just get a blank record.  I thought maybe it's because I hadn't saved the record yet, but even after that, it still just gives a blank record.  However, if I change the query to always find whatever customer ID that I have in the dropdown, it finds it.

Another option I can think of to accomplish the same thing is to choose a customer ID from the dropdown and just have it populate a name and address box and then later copy those values to the UNEP table with an On Close or something. (I realize that the fact that the customer name and the customer ID are both in both tables makes the database horribly un-normalized, but there's nothing I can do about that.  I inherited this when I came to work here.)

If anyone has any suggestions to make what I've already thought of work, or a totally different way to do it, I'm all ears!

Blair
0
Comment
Question by:brownlb
  • 6
  • 3
  • 2
11 Comments
 
LVL 8

Expert Comment

by:Pigster14
ID: 17989709
You need to set the recordsource of the second combo box, on the afterupdate event of the first combo box.

Hang on I will find you some code.

Thanks.
0
 
LVL 8

Expert Comment

by:Pigster14
ID: 17989850
There are a lot of posts I have seen for this type of question.

http://www.experts-exchange.com/Databases/MS_Access/Q_20162999.html?query=combo+box+rowsource&topics=39
Here is one

Also, I wrote up some code, it's not pretty, but it gives you the idea.

Private Sub Combo0_AfterUpdate()
Dim sql As String
Dim value As String
Dim rs As Recordset
sql = "Select * From Table3 Where Fld1 = " & Me.Combo0 & ";"
Set rs = CurrentDb.OpenRecordset(sql)
With rs
.MoveFirst
value = !Fld1
End With
rs.Close
Me.Combo2.RowSource = value
Refresh
Requery
End Sub

You take the value from Combo Box 1, find the value you want from a table in this case and populate the rowsource from the next combo box (2).

Thanks.
0
 
LVL 58

Accepted Solution

by:
harfang earned 250 total points
ID: 17990599
Hello brownlb

I'm not sure if you only want to display customer information or if you want to actually copy the current customer information into the current record (e.g. an order or invoice). If that is what you are looking for, find or install and find on your hard-drive the file Northwind.mdb -- the sample database that comes with Access. The form "orders" does just that: when the user selects a customer, relevant information is copied from the customers table into the order.

Also, it's perfectly natural (and normalized) to have CustomerID and Customer Name in the same table. Formally, the second field is indeed a secondary key candidate (if customer names are required and unique) but it's much more efficient to use the short ID for relationships.

Cheers!
(°v°)
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:brownlb
ID: 17997380
Thank you....I'm trying both suggestions now
0
 

Author Comment

by:brownlb
ID: 17999431
Okay, here's what I've got on the customer ID combo box:
Dim sql As String
Dim value As String
Dim rs As Recordset
sql = "SELECT tblIP_Customer.Customer_Name FROM tblIP_Customer WHERE tblIP_Customer.Customer_id  = " & Me.Customer_id & ";"
Set rs = CurrentDb.OpenRecordset(sql)
With rs
.MoveFirst
value = tblIP_Customer.Customer_Name
End With
rs.Close
Me.Combo2.RowSource = value
Refresh
Requery

I get this error:
Run-time error '3075':
Syntax error (missing operator) in query expression
'tblIP_Customer.Customer_id = <whatever the address of the customer ID I chose is>'

I'm googling now, but if anyone happens to read this...
0
 

Author Comment

by:brownlb
ID: 17999524
So does anyone know why it's telling me the address of the record I chose although I don't mention the address field anywhere in the SQL statement?
0
 

Author Comment

by:brownlb
ID: 17999546
Also, if I change the combo box to a text box, it hangs up on the Me.Combo2.RowSource = value statement.  (The name of the text box is still Combo2, even though it's not a combo)  I guess then, that RowSource has something to do with a combo box.  That's cool if that's what it is....I'm just wondering.
0
 

Author Comment

by:brownlb
ID: 17999574
Okay, I got the error to go away by changing Me.[Customer_ID]  to Me.[Customer ID]. I had it as the name of the control, not of the field the control was bound to.  Now it just doesn't update Combo2.
0
 
LVL 58

Expert Comment

by:harfang
ID: 18001600
brownlb,

Let's say you are creating a new order, and you have a combo box to select the customer for the order.

The combo should not return the name or the address of the selected customer, but just its number. That would be the first column of the combo, which is very often hidden (width 0).

You can then use a query similar to the one you suggest to find the correct customer record and copy any fields to actual fields of your new order. In your example, you copy only the customer name, which makes the excercise a little vain, as the combo already displays the customer name.

In a multi-column combo, you could have Combo.Value = Combo.Column(0) = 1234, Combo.Column(1) = "Happy Coders Inc.", Combo.Column(2) = "Canada", etc... However, this gets a little old with more than a few columns, and combo columns are all converted to text (dates and numbers are formatted, memo fields are truncated). For this reason, your method (finding the actual record from the table) is often used.

Finally, you do not need all that if you only want to display the information, as opposed to copying it to the new record. For display only, it's often best to include the table customers in the query for orders, for example.

Again, all this is very clear if you study Northwind.mdb's Orders form. Both methods: displaying customer information from the table and copying customer information to the new order (to fields called ShippingAddress, etc.).

Cheers!
(°v°)
0
 

Author Comment

by:brownlb
ID: 18008843
Once I really *looked* at what Northwind was doing, I figured it out. It's really quite simple!  Here's the code I ended up with:
Private Sub cmbCustomer_AfterUpdate()
Me!txtAddress = Me![cmbCustomer].Column(2)
Me!txtCustomerID = Me![cmbCustomer].Column(1)
End Sub
0
 
LVL 58

Expert Comment

by:harfang
ID: 18015533
I'm glad you did. This sample db is worth studying!
Good luck,
(°v°)
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question