Help with a smarter form

[m.hatter]

Not sure what the "key" words are to this problem, so if this has already been addressed please just give me the link. Probably a common issue.



On a data entry form, I want a second field to automatically populate based on the choice that is made in a previous field. I guess the way to phrase the question is, how do I use a combo box to launch a query that takes as a parameter the value that was just entered into another field?

The form facts are basically this:
Box 1 --- Select a name from the drop down list (this part works)
(each person's name in the list from the first box is associated with one and only one particular business. I want that business to appear in box two.
Box 2 --- Associated business name automatically would appear here.

Thanks for the help. I am completely new to Access so please don't assume very much.
m.hatter

[RuralGuy]

Is the name you want in the RowSource of the first ComboBox? Are both controls comboboxes? If so, why?

[m.hatter]

The first box is a simple drop down that is based on a table. The second is currently a drop down based on a second table.

More details: The first drop down gives a business name. eg. Acme, Inc. The second drop down gives a person's name. eg. John Smith. Currently, the second drop down includes the names of all people that work at all companies. (this is because the second drop down is based on the "people" table.) What I want to happen on the form is that the first selection, Acme, is made from the drop down. Then, the "people" box will get filled with John Smith because Access knows that John Smith is associated with Acme. There currently are no combo boxes being used. Just drop downs.
m.hatter

[RuralGuy]

I believe those "dropdowns" *are* ComboBoxes. You should really base them on queries rather than tables directly. You are describing Cascading ComboBoxes and here's a link that might help. There are other links available.

[m.hatter]

That us exactly what I am talking about. Hopefully, I will be about to dig through the example code I found at the link you gave and make sense of it. Cascading Combo Boxes.....I have the massive Access 2007 Inside and Out (Viescas/Conrad) and I don't even see that phrase in the index. Odd. Either way, thanks a lot for putting me on the right trail. I appreciate it.
m.hatter.

[RuralGuy]

Here's another link you might find useful: http://www.fontstuff.com/access/acctut10.htm

[m.hatter]

OK, after several hours of banging my head against the wall I finally got the cascading combo boxes to work. (Note to all...get rid of those drop down boxes that are really an ID column but display actual contents....they screw up the cascading combo boxes apparently)

Problem is that my final result is a little sloppy because there is only possible answer in the dependent combo box.

for example:
Lets say the state of California only had one city, San Francisco. therefore...
combo box 1 -> please choose a State. (of the several options, you choose California)
combo box 2 --> please choose a City. Drop down menu presents the one and only option, which is of course San Francisco.

This is how my form is currently working. Box2 always has a single choice (which is what it should have)

What I need to do is somehow take the result of the SQL query that was run after "After Update" of Box 1 (the SQL query returns a table with just one row and one column. (in this case row 1 column 1 = "San Francisco") and automatically populate box 2 with the result of that query so you don't have to bother with a combo box that by definition will always present only one possible option.

I have tried setting the sql query to a string and then setting box2.value equal to the string and that does not seem to be working. Do you have any ideas? Thanks in advance.
m.hatter

[RuralGuy]

Are you per chance using the evil LookUp Fields?

[m.hatter]

I was, then I figured out it is best just to put those sort of functions directly in the forms themselves, along with all other forms of validation. Is there a general rule against look ups?

[RuralGuy]

Lookup's on forms with ComboBox controls is just fine.Lookup Fields in tables is not recommended as the link pointed out.

[m.hatter]

Alright, figured out a few things but still confused on others.

This is for a "simple" data entry form.

WHAT I WANT:
1. Open combo box (created on form, no lookups,etc on table) choose a contact name.
2. Upon choosing name, a second Text Box will automatically display the business that the contact is associated to. Text Box should not be editable.
The first combo box works I think. Problem is with second box (Text Box).

WHAT I HAVE TRIED SO FAR:
First box is a combo box that looks up ContactID but displays the available list of Contact Names. This was arranged via the Wizard. This control is bound to a field in the tblContacts and so that table gets updated.

IN an attempt to get the Text Box to display the the associated business I ran an event off of the first combo box. (Code shown below)

My two cents: I think it has something to do with the data type being returned by the SQL query being of the wrong sort. I tried to return the result into one element of a string array because the query would ordinarily return a table. In this case, the answer will always be a 1 row/1 column table. I get no errors running this code. It just does nothing. Interestingly, if I change the array type to Integer a "0" is printed in the Text Box instead of the usual nothing.

Tables are as follows:

tblBusinesses
BusinessID, BusinessName

tblContacts
ContactID, BusinessID, ContactName

Private Sub ContactID_AfterUpdate()
     On Error Resume Next
     
     Dim MyTest(0)  As String
     
     MyTest(0) = "SELECT   BusinessName " & _
                 "FROM     tblBusinesses INNER JOIN tblContacts" & _
                 "WHERE    BusinessID IN " & _
                                               "(SELECT BusinessID " & _
                                               "FROM tblContacts " & _
                                               "WHERE ContactID = '" & ContactID.Value & "');"
      Text7.Value = MyTest(0)

End Sub

[RuralGuy]

How about posting the SQL for the RowSource of the ContactID ComboBox?

[m.hatter]

SELECT [tblContacts].[ContactID], [tblContacts].[ContactName] FROM tblContacts ORDER BY [ContactName];

[m.hatter]

I don't have it all figured out yet, but I have discovered that there is a problem in that Text7.Value has to be a number or (oddly) a number in quotes(a string of a number). If you just hard code a number into Text7.Value then it will show that value on the form at the right time.

So what remains to be figured out is how to make Text7, which is keyed to BusinessID (a number) display BusinessName (a string) in the same way that wizard will allow you to select an ID field but display the more friendly string that is associated with it instead.
m.hatter

[RuralGuy]

Why not base your ComboBox RowSource of a query that joins the two tables on the BusinessID field and then include the BusinessName in the query?