Yet another combobox problem!

[SeadnaS]

Hi,

Just merged all my tables into 6 down from nearly 150 tables. Obviously my form is now useless. I need my combo boxes to work again.

Ok.
I have 4 combo boxes: classcombo, typecombo, sizecombo and Combo105

I have 6 tables named after six classes of product: LAX, LAC, DFX, VIV, LAH, LAO.

-classcombo lists the six product classes (above). The rowsource is a table with the six 3 character class codes in a field.
-typecombo lists BP, RBPOD, NOD, and QOD (these are types data displayed on my form). The rowsource was typed into the combox wizard.
-sizecombo lists sizes of product. This previously listed tables (as I had a table for every size originally). Now I only want it to list records limited to the type selected in typecombo. I have a field in each table called Size/Type each entry is in this format: "LAC_BP_13010" LAC is the class, BP is the type and 13010 is the size.
-Combo105 lists Lot numbers. Once i have a size selected in the sizecombo i want that to limit the results of Combo105 to records that are the same size as the size selected in sizecombo. The lot numbers are listed in their own field LOT_NO

Once i select a lot number thats the record i want the form to display.

Thanks, and any help much appreciated!

 

[SeadnaS]

Where I am so far. I have the class combo working. It changes the record source to the corresponding table. Now I'm stuck, I need the sizecombo to only list values from the Size/Type column of the recordsource table which contain the letters selected by the typecombo.

 

[SeadnaS]

Ok I got the Combo105 box working. I can select lot numbers from the record source selected by the classcombo. Now im struggling to filter Combo105 with sizecombo and typecombo. At least i can access my records through Combo105 (I should really rename that one!).



I have a feeling I can set the row source of sizecombo to only display records that contain the letters in typecombo and then i can set the rowsource of Combo105 to some SQL that will limit what shows up to only records that include the values in typecombo. If someone could provide the right type of code to get me going it would be great. I'm struggling here trying to figure it out!

 

[SeadnaS]

Should this work?


Private Sub sizecombo_AfterUpdate()

With Me![typecombo]
If IsNull(Me!sizecombo) Then
.RowSource = ""
Else
.RowSource = "SELECT [Size/Type] " & "FROM" & Me!classcombo & "WHERE [Size/Type]Like" & Me!classcombo & "_" & Me!typecombo & "*;"
End If
Call .Requery
End With


End Sub

 

[SeadnaS]

Realized sizecombo was the wrong name i actually had it called size! I changed the code a little bit but i'm still doing something wrong!

Private Sub sizecombo_AfterUpdate()

With Me![type]
If IsNull(Me!sizecombo) Then
.RowSource = ""
Else
.RowSource = "SELECT " & Me.classcombo & ".[Size/Type] FROM" & Me.classcombo & " WHERE [Size/Type] LIKE" & Me.classcombo & "_" & Me.type & "_*;"
End If
Call .Requery
End With



End Sub

 

[SeadnaS]

I mean type not size lol

 

[TheAceMan1]

How are ya SeadnaS . . .
[ol][li]It would be easier on programming if each table had three additional fields: [blue]Class[/blue], [blue]Type[/blue], [blue]Size[/blue] ... instead of trying to break them out of the [blue]Size/Type[/blue] field. You could always build your format wherever needed or just keep the field.[/li]
[li]You'll need dynamic SQL to update the SizeCombo since your changing tables. Post a typical SQL for SizeCombo.[/li]
[li]To get the proper size, the SizeCombo needs to be filtered with both [blue]class & type![/blue]. Something like:

[blue]WHERE (Left([Size/Type],3)= Forms!FormName.ClassCombo) AND 
(InStr([Size/Type], Forms!FormName.TypeCombo)>0")[/blue]

[/li]
[li]Some assemblence of the tables and fields is in order if were to help you better/faster. Take a printscreen of the relationships window and upload to 4Shared (its free). You'll be provided with a [blue]link[/blue] you can post for viewing.[/li][/ol]
Let me know if you intend to add the three fields.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SeadnaS]

Is there a way of doing it with just the Size/Type field?

My relationships window is blank. So no point in uploading it. So can you see something that needs changing in the code I posted before your last post?

 

[PHV]

.RowSource = "SELECT [Size/Type] FROM [" & Me!classcombo & "] WHERE [Size/Type] LIKE '" & Me!classcombo & "_" & Me!type & "_*'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886