Use of combo box question

[cookie5]

I have a form called workorders based on a table. The form has a subform with 2 combo boxes on it. The first combo box is the category for the part that is going to be used based on a category table. The second combo box list the parts within that category based on a parts table. I can add a new record to the workorders table by using these combo boxes. My problem is that when I open the form to review the workorder form the combo box for the category shows only the category for the first record. Even though in the table the subsequent records show the category, nothing displays in the combox box on the form after the first one. I'm sure I missed something simple. I thought setfocus would help but I either miscoded or should use something else. Any ideas?

 

[lameid]

You must have some sort of criteria in the query on the combobox. Either it is valid or it isn't.

Also you may need to use the requery method on the combobox on the form's on current event if it is based on a value in the form.

 

[TheAceMan1]

How are ya cookie5 . . .

In thread702-1380565 see my post dated 21 Jun 07 10:35 . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TheAceMan1]

Also have a look here: Data in a combo box control on a continuous form/datasheet disappears

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[cookie5]

Hi Lameid & TheAceMan1,
I modified and put both combos on the same subform. That works great for records that have already been written to the table. Both the Category and the Partnames are fine.BUT, (isn't there always a but?) If I want to add a new record, I select the category from the category combo box but the list of parts are not just for that category.Even though I have the following sql for the partsnames.

SELECT DISTINCTROW Parts.PartID, Parts.PartName, Parts.Catid, [Parts Category].CatID
FROM Parts INNER JOIN [Parts Category] ON Parts.Catid = [Parts Category].CatID
WHERE ((("Catid")="CatID"))
ORDER BY Parts.Catid, [Parts Category].[Category Name];

It shows all the part names regardless of the category. Any ideas?
Thanks in advance for any suggestions

 

[lameid]

Fix your where clause...

Perhaps something like:

SELECT DISTINCTROW Parts.PartID, Parts.PartName, Parts.Catid, [Parts Category].CatID
FROM Parts INNER JOIN [Parts Category] ON Parts.Catid = [Parts Category].CatID
WHERE (((Parts.Catid)=Forms!Frm!subform!CatID))
ORDER BY Parts.Catid, [Parts Category].[Category Name];

 

[TheAceMan1]

cookie5 . . .

Are these comboboxes [blue]bound[/blue] or [blue]unbound[/blue]?

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[cookie5]

Thanks Lameid & TheAceMan1,
Lameid, I changed the where but that did not help. With that it shows the category put leaves the part list blank.
TheAceMan1, these are bound combo boxes to a table that has the partid and the category id for each record. At this time the past entered records display both the category and part name correctly. It is when I try to enter a new record that things go wrong. I select the category from the category combox then the parts combo box lists all of the part names. Is the problem because they are bound to the table?
Any other suggestions would be appreciated and if you need more code just let me know

 

[lameid]

Perhaps you are not requerying the combobox on the after update event of the category combo box...

Me!Frm!subform!PartID.Requery


If that isn't it...

Can you write a query that gives you all the parts for a category you enter an exact category for?

If so, post it.

If not, you will have to list all the tables and fields involved for me to help.

 

[cookie5]

lameid,
here is the sql for a query that list all the parts with the category for the part. Is that what you need?

SELECT [Parts Category].[Category Name], Parts.PartName
FROM Parts INNER JOIN [Parts Category] ON Parts.Catid = [Parts Category].CatID
ORDER BY [Parts Category].[Category Name], Parts.PartName;

I have a requery afterupdate for the category combo box
Remember, this is a subform with 2 combo boxes that are bound to data in a table.
thanks for any help.

 

[lameid]

Looks basically like the previous SQL except it does not have criteria.

Maybe I am missing the big picture... What is the recordsource for the form the combo's are on and the parent or sub forms?

 

[cookie5]

Lameid,
The main form is frmWorkorders_Form2. It is the parent.There are several subforms but subworkorders_parts_detail is the subform giving me the problem. The record source for this subform is a table called tblworkorders_parts_detail. There are 2 combo boxes on subworkorders_parts_detail - CategoryName2 which is bound to catid on the table tblworkorders_parts_detail and Parts_List_1 which is bound to Parts_List_1 on tblworkorders_parts_detail.
tblworkorders_parts_detail has the following fields
Workorder id - number
Parts_List_1 - number
Quantiry1 - number
Catid - number

There is 1 category and part for each record in the table tblworkorder_parts_detail and there can be multiple subworkorder_parts_detail records for each frmWorkorders_Form2.
Clear as mud?
Thanks again

 

[lameid]

Better.

I think I got lost somewhere... You use the comboboxes to add new records? They should be unbound. Having them bound changes the existing record before adding a new one.

Is that the purpose of the combos, just to add new records?

That said it still should have worked to display the right values in the drop down unless you are only trying to display parts for records you alread have in the subform...

Row source for Parts_List_1 which you have to change the name of because of the control/field name conflict. Say it is named cboPartsList for one of the code examples below.

SELECT DISTINCTROW Parts.PartID, Parts.PartName, Parts.Catid, [Parts Category].CatID
FROM Parts INNER JOIN [Parts Category] ON Parts.Catid = [Parts Category].CatID
WHERE (((Parts.Catid)=Forms!frmWorkorders_Form2!subworkorders_parts_detail!CategoryName2))
ORDER BY Parts.Catid, [Parts Category].[Category Name];

Then the afterupdate event for category2 should work...

Sub CategoryName2_afterupdate
     Me!cboPartsList.requery
End Sub

 

[cookie5]

Lameid,
Thanks, I WANT to use the combo boxes to both display records already written to the table tblworkorders_parts_detail AND to add new records to the table. Can that happen?

 

[lameid]

On the form's on current event you could populate the unbound comboboxes with the data from the record. Just remember to requery the parts combo after you set the category combo.

 

[cookie5]

Lameid,
Thanks, the combo boxes are currently bound to the table, if I make them unbound, how do I populate them with the data from the right record?
Thanks

 

[lameid]

on current event you could populate the unbound comboboxes
with the data from the record

You would set the combobox equal to the value of the field.

Me!ControlNAme = Me!FieldName

 

[cookie5]

Thank you lamid, I'll give it a try. Thanks for sticking with me. I may be trying to do too much with too little knowledge.