ComboBox info linked to other comboBox 2

[SidLancing]

I have a form with a combobox that displays info from a query. It's a list of Companies from the Company table and and their various locations from the Locations table. I have the row source as CompanyLocations which is a query.
When a user makes a selection(i.e. Company and location) I'd like to capture that data and use it in another combobox in the same form; this next combobox is a contact list. I would like it to be the list of contact for the Company and location previously selected not a list of all compamies and their locations.
Can anyone help?...

P.S. How do I save the selected value of the combobox in a field of the form where it's located.

 

[scriverb]

The rowsource for your second combobox should be a query from the table Contacts with a criteria reference by to the first combobox:

EXAMPLE:

Select C.ContactName 
FROM Contacts as C 
WHERE C.[CompanyLocation] = FORMS![yourformname]![Combo1]
ORDER BY C.ContactName;

This query is assuming that the bound column for combo1 is a value that is also stored as a field anmed CompanyLocation in the Contacts table.

Put the following Code in the AfterUpdate event procedure of the control Combo1:

Me.Combo1.Requery
Me.Combo.SetFocus
Me.Combo1.DropDown

Let me know if you have any further questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[barcode711]

I was looking for information on how to do this exact same thing.

I have a table called tblMain, and with 2 fields, Building and Office. I have a form called frmData, that is referencing the 2 fields Building and Office.

When Building is selected in the first combo box, only the offices located in the selected building will populate the second combo box. I was able to create a form that does this,called frmSyncCombo, and it works, it is retrieving the data from a query called qrySync. Now I am having problems getting this to work on my main form, frmData. I tried inserting frmSyncCombo as a subform, but keep getting parameter issues.

 

[scriverb]

References in a query to a control on a single main form are referred using the following syntax:

FORMS![mainformname]![controlname]

But, as soon as you use that form as a subform on a different mainform the reference to same control becomes a little more complex in its syntax. The following would have to be used in the query:

Forms![Orders]![Orders Subform].Form![Controlname]

Hope this helps you understand your problem. You see you have nested your original mainform into a subform control on a new mainform. Thus, the reference to its controls when used in a query must be modified.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SidLancing]

I've included your suggested code as follows:

SELECT C.LocationName 
FROM locations AS C 
WHERE C.CompanyName=[FORMS]![Order]![ReceivingcompanyID] ORDER BY C.LocationName;

The CompanyName field is in the Locations table and does have data...however when I select a company name from my Order form the location comboBox is now blank
..and yes I did add the following in the ReceivingcompanyID comboBox afterupdate event procedure...

Private Sub ReceivingCompanyID_AfterUpdate()
Me.ReceivingCompanyID.Requery
Me.ReceivingCompanyID.SetFocus
Me.ReceivingCompanyID.Dropdown
End Sub

Any idea why this is coming out blank?...

 

[PHV]

Have you tried this just before the Requery ?
Me.ReceivingCompanyID.RowSource = "SELECT LocationName" _
& " FROM locations WHERE CompanyName='" _
& FORMS!Order!ReceivingcompanyID & "'" _
& " ORDER BY LocationName;"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

PHV has suggested the necessity for quotes around the value of the combobox. This may be so if the field CompanyName is a text field and the Bound Column of the ComboBox is a correstponding value. So, check your bound field to see what you have as a value for the combobox and check the field type. If it is a text field then PHV's post has to be used as the value of the combobox must be bracketed by quotes to denote a string value.

Waiting for your response.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SidLancing]

thanks PHV and scriverb...

I added that code to the Afterupdate of the event procedure for the ReceivingCompanyID combobox and it made that combobox blank... so now ReceivingCompanyID and ShipingLocationID(the 2nd combobox) are blank.
Forgive my inexperiece ... this is all new to me..
I'm trying to make this work but I hit a brick wall.
Any more suggestions!

 

[scriverb]

Your ComboBox1 should not be affected by anything that we have done here. Copy and paste the RowSource SQL here so I may see what you have there. Let's take it a step at a time. Also, what is in the Bound Column property of the first combobox and what is in the column count, column widths properties.

Post this info and I will work with you to get it working as you requested.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SidLancing]

ok... here is the code that I have...

For Combobox1

Name: ReceivingCompanyID
ControlSource: ReceivingCompanyID
RowSource Type: TABLE/QUERY

RowSource:SELECT [Company].[CompanyID], [Company].[CompanyName] FROM Company;

Column count: 2
Column Heads: No
Column widths:0";1.3021"
Bound Column:1
List rows: 8
List width:1.3021
Limit to list: Yes
AfterUpdate: Eventprocedure

  Private Sub ReceivingCompanyID_AfterUpdate()
    Me.ReceivingCompanyID.Requery
    Me.ReceivingCompanyID.SetFocus
    Me.ReceivingCompanyID.Dropdown
  End Sub

For combobox2

Name: ShipingLocationID
ControlSource: ShipingLocationID
RowSource Type: TABLE/QUERY

RowSource: SELECT C.LocationName FROM locations AS C WHERE C.CompanyName=[FORMS]![Order]![ReceivingcompanyID] ORDER BY C.LocationName;

Column count: 3
Column Heads: No
Column widths:1.5";2"
Bound Column:1
List rows: 8
List width:2
Limit to list: Yes

P.S. my form is running of a table name Order that has the fields ReceivingCompanyID and ShipingLocationID where I'd like to save to values selected by the user.

Thanks

 

[SidLancing]

By the way. scriverb.. when you get a chance ... take a look at the code I previously listed and let me know what you think... I also would like to know whether or not there's a limit to what I'm trying to do here...because once I get this working I'll need to do it over to list all the contacts people for that company at that location.

Thanks

Sid

 

[scriverb]

Provlem here is that the Bound Column for ComboBox1(ReceivingCompanyID) is column 1. Column 1 is the CompanyID. So, when you use a direct reference to ComboBox1(ReceivingCompanyID) in the RowSource SQL for ComboBox2(ShippingLocationID) you are comparing apples to oranges.

Change the RowSource SQL for ComboBox2 to the following:

SELECT C.LocationName FROM locations AS C WHERE C.CompanyName=[FORMS]![Order]![ReceivingcompanyID][red].column(1) [/red]ORDER BY C.LocationName;

Now the ComboBox2 will select all Location records for CompanyNames that match. The only problem with this is if you have identical company names in your database. If the Location table has a CompanyID file then I would change the expression to look for matching CompanyID values. This would require the following SQL:

SELECT C.LocationName FROM locations AS C WHERE C.CompanyID=[FORMS]![Order]![ReceivingcompanyID] ORDER BY C.LocationName;

You statement that both comobox's are now empty doesn't make any sense. Certainly the first one should have records because there is no selection going on in the SQL for the RowSource.

Post back with your testing results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SidLancing]

I opted for the second code

SELECT C.LocationName FROM locations AS C WHERE C.CompanyID=[FORMS]![Order]![ReceivingcompanyID] ORDER BY C.LocationName;

It works but doesn't refresh if I go to another selection... so if by mistake the users chose a company and shipping location then before exiting the form they decide to go back to the company comboBox and change it... when they go to select the new Shipping location the old values remain. The shipping comboBox is not refreshed with the new company selected. How can I get this to happen?
Should I have a reference like you had mention above for shippingLocationId i.e.

( Me.ShippingLocationID.Requery
Me.ShippingLocationID.SetFocus
Me.ShippingLocationID.Dropdown )

if so where should this go..On Enter, On GotFocus ..or somewhere else?

 

[scriverb]

You should put those three lines of code in the AfterUpdate event procedure of ComboBox1(ReceivingCompanyID). This way whenever the user makes a choice the second combo gets requeried with the new data.

Let me know if this all now works okay.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SidLancing]

Hello again...

I did have those lines in the AfterUpdate event procedure of ComboBox1(ReceivingCompanyID)... as you had previously suggested... It looks like this...

Private Sub ReceivingCompanyID_AfterUpdate()
   Me.ReceivingCompanyID.Requery
   Me.ReceivingCompanyID.SetFocus
   Me.ReceivingCompanyID.Dropdown
End Sub

When I first open the form and select a company one of the correct locations comes up in the comboBox2(ShipingLocations). If a company has 2 locations they should both show up instead I have only one...and If I choose another company without exiting the form then the Locations for that company don't come up at all... The old location from the first choice is what remains available.

Any other suggestions?...


P.S. My company table has the following fields
CompanyID, Companyname

and my Locations table has LocationID,LocationName,
CompanyID,CompanyName

The Rowsource for combobox1(ReceivingCompany) is:
SELECT [Company].[CompanyID], [Company].[CompanyName] FROM Company;

The Rowsource for combobox2(ShipingCompany) is:
SELECT C.LocationName FROM locations AS C WHERE C.LocationID=[FORMS]![Order]![ReceivingcompanyID] ORDER BY C.LocationName;

The BoundColumn for combobox1(ReceivingCompany)is: 1

The BoundColumn for combobox2(ShipingCompany)is: 1

 

[PHV]

In the ReceivingCompanyID_AfterUpdate event procedure:
ShippingLocationID.RowSource = "SELECT LocationName FROM locations WHERE LocationID='" & ReceivingcompanyID & "' ORDER BY 1"
ShippingLocationID.Requery
ShippingLocationID.SetFocus
ShippingLocationID.Dropdown

If LocationID is defined as numeric, get rid of the single quotes in the where clause

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SidLancing]

I added the following in the ReceivingCompanyID_AfterUpdate event procedure:

Private Sub ReceivingCompanyID_AfterUpdate()

ShipingLocationID.RowSource = "SELECT LocationName FROM locations WHERE LocationID=" & CompanyID & " ORDER BY 1"
ShipingLocationID.Requery
ShipingLocationID.SetFocus
ShipingLocationID.Dropdown
 
end Sub

I did remove the quote because LocationID is a numeric field it's set to Autonumber.
I also changed the '" & ReceivingcompanyID & section to
& CompanyID &
because in the Locations table the field name is CompanyID.

What happens now ... is that when I select a company the first time I still get only one location per company even in case where there are multiple locations to be listed and if I go back and select another company then I get a blank in the location field...even if I try to get back the very first selection that I made. I have to exit the form completely to make a change.

 

[PHV]

Your previous message make me mistake.
You may try this:
ShipingLocationID.RowSource = "SELECT LocationName FROM locations WHERE CompanyID=" & ReceivingcompanyID & " ORDER BY 1"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

I have been away awhile and I see that your have changed the query SQL slightly. But, I still think that the WHERE clause must compare the tables CompanyID equal to the value of the first Combobox selection(ReceivingCompanyID). I don't understand the need to reset the RowSource at the time of the pic. The SQL if setup correctly in the secomd comobox with the proper reference to the first combobox value should work just fine.

Comments??

Select C.LocationName FROM Locations as C WHERE 
C.CompanyID = FORMS![order]![ReceivingCompanyID] ORDER BY 1;

Just copy and paste in the Row Source and there should not be a need to reinstall it each time.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SidLancing]

WOW!... WORKS LIKE A CHARM!... Sorry for addingg too much info and confusing things... I tried to give as much info as possible ...not knowing how quickly I would get an answer back...
OK.. I do have a question about the select statement.
Does it override the statetement that I actually have specified in the Rowsource for ShipinglocationID?

I'm now trying to implement this again this time for Contat information. I want the combobox(Recipient) to look at the ShipinglocationID and select all contacts for that location.

I have the following code:

Private Sub ShipingLocationID_AfterUpdate()

Recipient.RowSource = "SELECT LastName FROM Contact WHERE LocationID=" & ShipingLocationID & " ORDER BY 1"
Recipient.Requery
Recipient.SetFocus
Recipient.Dropdown
End Sub

What am I missing? I thought for sure this should work... It comes out blank! I compared the field types are the same and the bound column is 1.

Hope you can answer this tonight
Thanks