Combo Box Duplicates

[pipestew]

JobSetup Table contains the following columns....

JobID (Primary Key, Autonumber), JobNum, SubCode, Customer, Project_Lease, JobDetails

*The database will contain many duplicate Job Numbers(all with the same Customer/Project_Lease), but the JobDetails within the JobNumbers will ALWAYS be unique per SubCode.

I have a Daily Production Form that feeds data to a Daily Production Table.

I want a combobox on the form that allows me to select the JobNum. I want a 2nd combobox that allows me to select from a list of JobDetails based on the JobNum that I select in the 1st combobox. Based on the selections of the 2 comboboxes, I want to store the JobID in the Daily Production Table.

Also, I do not want duplicate Job Numbers to show up in the combobox.

HELP Please.

 

[boblarson]

JobSetup Table contains the following columns....

JobID (Primary Key, Autonumber), JobNum, SubCode, Customer, Project_Lease, JobDetails

*The database will contain many duplicate Job Numbers(all with the same Customer/Project_Lease), but the JobDetails within the JobNumbers will ALWAYS be unique per SubCode.

I have a Daily Production Form that feeds data to a Daily Production Table.

I want a combobox on the form that allows me to select the JobNum. I want a 2nd combobox that allows me to select from a list of JobDetails based on the JobNum that I select in the 1st combobox. Based on the selections of the 2 comboboxes, I want to store the JobID in the Daily Production Table.

Also, I do not want duplicate Job Numbers to show up in the combobox.

HELP Please.

Use a query for the rowsource and use the Sigma button

to Group the number.

 

[pipestew]

Current Row Source...

SELECT DISTINCT [JobSetup].[JOBNum], [JobSetup].[Customer], [JobSetup].[Project_Lease] FROM JobSetup ORDER BY [JobNum];


Only Customer and Project_Lease are showing up in the ComboBox. JobNum needs to be included also...unduplicated.

 

[boblarson]

Current Row Source...

SELECT DISTINCT [JobSetup].[JOBNum], [JobSetup].[Customer], [JobSetup].[Project_Lease] FROM JobSetup ORDER BY [JobNum];


Only Customer and Project_Lease are showing up in the ComboBox. JobNum needs to be included also...unduplicated.

For a combo box you also need to set the Number of Columns property to the number of columns in your query. Also, remember to set the widths of each column because if your number is the first column Access will by default try to set it to 0". So, change your 0";1";1" (or similar) to 1";1";1".

 

[pipestew]

Row Source in 2nd ComboBox...

SELECT [JobSetup].[OD], [JobSetup].[Wall] FROM JobSetup WHERE (((JobSetup.JobNum)=[Forms]![DailyProduct]![JobNumCombo]));

The JobNum ComboBox works perfect. Once a JobNum is selected, the 2nd ComboBox displays the correct information, however, if a different JobNum is selected, the 2nd Box continues to list the details associated with the orignal JobNum.

 

[boblarson]

In the AFTER UPDATE event of the first combo box you need a:

Me.YourComboBox2Name.Requery

 

[pipestew]

Me.ODWallCombo.Requery


"The expression After Update you entered as the event property setting produced the following error: The object doesn't contain the Automation object 'Me.'.

 

[boblarson]

Me.ODWallCombo.Requery


"The expression After Update you entered as the event property setting produced the following error: The object doesn't contain the Automation object 'Me.'.

Then you put it in the PROPERTY dialog and not in the code window.
These screenshots show you what I believe you did, but they aren't on the correct event as they were created for someone else. But, the concept is the same (Just remember you want the AFTER UPDATE event:

 

[pipestew]

You should be an Access Forum Moderator when you grow up. You know a few things. Thanks for the help.

 

[pipestew]

OK....One more minor problem....

I select JobNum in Box 1, and then select the Job Details from Box 2. When I attempt to save the data, I get the following error...

"You must enter a value in the 'DailyProduct.JobID' Field"

The JobID refers to the ID autonumber assigned to every JobNum/JobDetail Combination. My goal is to populate a JobID field in a DailyProduct Table that is based on the Box1/Box2 selection in the DailyProduct Form