Solved

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

Posted on 2006-11-21
11
598 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:brownlb
Comment Utility
Thank you....I'm trying both suggestions now
0
 

Author Comment

by:brownlb
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:brownlb
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I'm glad you did. This sample db is worth studying!
Good luck,
(°v°)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now