?
Solved

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

Posted on 2006-11-21
11
Medium Priority
?
607 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 750 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

649 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