Please Help With Combo Box Linking

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Six hours to no avail. Pls help. I have a combo box on a subform. I want this
combo box to only contain information that is specific to the record that is
selected on the main form. Can anyone tell me what the proper code would be
and where to put it? I have tried everything. Rather than digressing, please
take a quick look at the screen shot I uploaded to the server. Thanks.

http://www.chemreport.com/dump/new_page_1.htm
http://www.chemreport.com/dump/new_page_2.htm
 
MBoozer said:
Six hours to no avail. Pls help. I have a combo box on a subform. I want this
combo box to only contain information that is specific to the record that is
selected on the main form. Can anyone tell me what the proper code would be
and where to put it? I have tried everything. Rather than digressing, please
take a quick look at the screen shot I uploaded to the server. Thanks.

http://www.chemreport.com/dump/new_page_1.htm
http://www.chemreport.com/dump/new_page_2.htm
Click to expand...
In both examples (new_page_1.htm and new_page_1.htm), "Lucent1" is in
the "LocationName" field of a NEW record.

So it seems to me that there is a default value set or somewhere in code
you set the default value for that field.
 
SteveS said:
In both examples (new_page_1.htm and new_page_1.htm), "Lucent1" is in
the "LocationName" field of a NEW record.

So it seems to me that there is a default value set or somewhere in code
you set the default value for that field.
Click to expand...
Oops, hit the send button too soon.

If you don't have a default value set, what is the rowsource for the
combo box?

Does each customer have their own locations or are all locations in one
table?
 
There is a table for customers, locations and departments. All locations are
stored in a locations table. The 3 fields are LocationID, LocationName and
CustomerID. The CustomerID field is populated from the Customer Table when a
new location for a customer is added to the locations table. See
http://www.chemreport.com/dump/new_page_3.htm for relationship picture

The entries Lucent1 are not default values. They are just sample text that I
entered for both the Location and Department table data and are tied to
customer record Lucent.
The rowsource for the combo box is:
SELECT Location.LocationID, Location.LocationName FROM Location ORDER BY
[LocationName];
 
I constructed a quick test form that filters.
It's rowsource looks like:
SELECT DISTINCTROW tblContacts.CustomerID, tblContacts.FirstName,
tblContacts.LastName FROM tblContacts WHERE
(((tblContacts.CustomerID)=[Forms]![MainForm]![CustomerID])) ORDER BY
tblContacts.FirstName;
Hope this helps.

BR
Ted


MBoozer said:
There is a table for customers, locations and departments. All locations are
stored in a locations table. The 3 fields are LocationID, LocationName and
CustomerID. The CustomerID field is populated from the Customer Table when a
new location for a customer is added to the locations table. See
http://www.chemreport.com/dump/new_page_3.htm for relationship picture

The entries Lucent1 are not default values. They are just sample text that I
entered for both the Location and Department table data and are tied to
customer record Lucent.
The rowsource for the combo box is:
SELECT Location.LocationID, Location.LocationName FROM Location ORDER BY
[LocationName];

SteveS said:
Oops, hit the send button too soon.

If you don't have a default value set, what is the rowsource for the
combo box?

Does each customer have their own locations or are all locations in one
table?
Click to expand...
Click to expand...
 
Thanks Ted and Steve. Still can't get it. Tried variations of Ted's script
but still does not work. I posted what my form looks like in design view at
http://www.chemreport.com/dump/new_page_4.htm

SELECT DISTINCTROW Location.LocationID, Location.LocationName,
Location.CustomerID FROM Location WHERE
(((Customer.CustomerID)=[Forms]![MainDataEntry]![WebDataSubform]![LocationID])) ORDER BY Location.LocationName;

Ted Stilwell said:
I constructed a quick test form that filters.
It's rowsource looks like:
SELECT DISTINCTROW tblContacts.CustomerID, tblContacts.FirstName,
tblContacts.LastName FROM tblContacts WHERE
(((tblContacts.CustomerID)=[Forms]![MainForm]![CustomerID])) ORDER BY
tblContacts.FirstName;
Hope this helps.

BR
Ted


MBoozer said:
There is a table for customers, locations and departments. All locations are
stored in a locations table. The 3 fields are LocationID, LocationName and
CustomerID. The CustomerID field is populated from the Customer Table when a
new location for a customer is added to the locations table. See
http://www.chemreport.com/dump/new_page_3.htm for relationship picture

The entries Lucent1 are not default values. They are just sample text that I
entered for both the Location and Department table data and are tied to
customer record Lucent.
The rowsource for the combo box is:
SELECT Location.LocationID, Location.LocationName FROM Location ORDER BY
[LocationName];

SteveS said:
SteveS wrote:
MBoozer wrote:

Six hours to no avail. Pls help. I have a combo box on a subform. I
want this combo box to only contain information that is specific to
the record that is selected on the main form. Can anyone tell me what
the proper code would be and where to put it? I have tried everything.
Rather than digressing, please take a quick look at the screen shot I
uploaded to the server. Thanks.

http://www.chemreport.com/dump/new_page_1.htm
http://www.chemreport.com/dump/new_page_2.htm



In both examples (new_page_1.htm and new_page_1.htm), "Lucent1" is in
the "LocationName" field of a NEW record.

So it seems to me that there is a default value set or somewhere in code
you set the default value for that field.

Oops, hit the send button too soon.

If you don't have a default value set, what is the rowsource for the
combo box?

Does each customer have their own locations or are all locations in one
table?
Click to expand...
Click to expand...
Click to expand...
 
MBoozer said:
Thanks Ted and Steve. Still can't get it. Tried variations of Ted's script
but still does not work. I posted what my form looks like in design view at
http://www.chemreport.com/dump/new_page_4.htm

SELECT DISTINCTROW Location.LocationID, Location.LocationName,
Location.CustomerID FROM Location WHERE
(((Customer.CustomerID)=[Forms]![MainDataEntry]![WebDataSubform]![LocationID])) ORDER BY Location.LocationName;
Click to expand...

The WHERE clause doesn't look right.
You are using CustomerID = LocationID.

Try this:

SELECT DISTINCTROW Location.LocationID, Location.LocationName,
Location.CustomerID FROM Location WHERE
Location.CustomerID=[Forms]![MainDataEntry]![WebDataSubForm]![CustomerID]
ORDER BY Location.LocationName;


Also, you should set SubDataSheet to [None] for all of your tables,
especially those that are displayed in DataSheet mode.
 
You have no idea of how grateful I am to both of you and this group. The code
now works great!! Syntax! Gotta have it! Thank you so much.

SteveS said:
MBoozer said:
Thanks Ted and Steve. Still can't get it. Tried variations of Ted's script
but still does not work. I posted what my form looks like in design view at
http://www.chemreport.com/dump/new_page_4.htm

SELECT DISTINCTROW Location.LocationID, Location.LocationName,
Location.CustomerID FROM Location WHERE
(((Customer.CustomerID)=[Forms]![MainDataEntry]![WebDataSubform]![LocationID])) ORDER BY Location.LocationName;
Click to expand...

The WHERE clause doesn't look right.
You are using CustomerID = LocationID.

Try this:

SELECT DISTINCTROW Location.LocationID, Location.LocationName,
Location.CustomerID FROM Location WHERE
Location.CustomerID=[Forms]![MainDataEntry]![WebDataSubForm]![CustomerID]
ORDER BY Location.LocationName;


Also, you should set SubDataSheet to [None] for all of your tables,
especially those that are displayed in DataSheet mode.
Click to expand...
 
Back
Top