Aghh, another combo problem

[Markvand]

I've posted a thread before but no one could help or I wasn't clear enough. I want to create a simple search form using combo.

The table looks like this tblMaterials (material_id, name, group, vendor)

The querry I use (qryTest): SELECT tblMaterials.material_id, tblMaterials.name, tblMaterials.vendor, tblMaterials.Group
FROM tblMaterials
GROUP BY tblsMaterials.material_id, tblMaterials.name, tblMaterials.vendor, tblsMaterials.Group
HAVING (((tblsMaterials.vendor)=[Forms]![Test]![vendor])) OR (((tblMaterials.Group)=[Forms]![Test]![Group]))
ORDER BY tblMaterials.material_id;

(The red part doesn't work)

I have 2 unbound combo,

1st. Get's all Vendors from table: SELECT tblvendors.vendor_id, tblvendors.vendor_name FROM tblvendor ORDER BY tblvendor.vendor_name;

2nd Has Groups: SELECT tblGroups.Group_id, tblGroups.Group_name FROM tblGroups ORDER BY Group_name;

and third combo linked to a querry: Format source is set to qryTest.name and Row Source: SELECT DISTINCT qryTest.name FROM qryTest ORDER BY name

Let's concentrate on a problem now, I want user to have an options like that, list the materials by Vendor - just pick a vendor from a list and see the results in third combo, if there is a need narrow the search with a group, the result: all groups of materials available from particular vendor.

Another user may want to search only by the group skipping the vendor combo and get the results.

The reason why I want to have the results in a combo is that I need to have a further pick option.

Hope I gave you the whole picture now.

Any help would be highly appreciated

Mark

Sorry for posting the thread few times

 

[WayneRyan]

Mark,

If you can post a sample, it'd be easier.

Wayne

 

[stepone]

There are dynamic combo box examples in the Sample Code bit I think - or find a post by Mile-o-Phil (he has the link in his message footer).

Basically you need to update the rowSource property of the third combo from within the afterupdate event of the Vendor combo, and Group combo.

e.g. in the vendor combo the logic would be something like

IF groupcombo is null then
thirdcombo.rowsource = SELECT <whatever>
FROM <where
WHERE vendor = vendorcombo.value
ELSE
thirdcombo.rowsource = SELECT <whatever>
FROM <where
WHERE vendor = vendorcombo.value
AND group=groupcombo.value
ENDIF

or something like that. You need to do something similar in the afterupdate event of the group combo.

Cheers,
StepOne

 

[Markvand]

Thnks for your replies, guys.

I've just poped in to the office, Wayne I'll prepare a sample in a bit, first I'll chew what StepOne suggested.

I'll be in touch.

Cheers

Regards

Mark

 

[Markvand]

I'm getting a sort of idea where to go and this moment I'm stuck with this code:

If Grupa Is Null Then
Nazwa.rowsource = SELECT [tab_materialy1].Indeks_mat, [tab_materialy1].nazwa_plFROM [tab_materialy1]
WHERE Producent = Producent.Value
Else
Nazwa.rowsource = SELECT [tab_materialy1].Indeks_mat, [tab_materialy1].nazwa_plFROM [tab_materialy1]
WHERE Producent = Producent.Value
AND Grupa=Grupa.value
End If

The debuger points out at SELECT and prompts: Expected Expression, I've checked few combinations with brackets, etc. no result but I'm thinking it's something simple,

Please help

Cheers

 

[stepone]

Try putting the whole SQL string in quotes.

 

[Markvand]

Yep, it did the trick, Cheers, mate

One more thing, cause I'm kind of newbie to Access, got some idea but sometimes got stucked on simple things.

It's from my yesterday thread

2nd Has Groups: SELECT tblGroups.Group_id, tblGroups.Group_name FROM tblGroups ORDER BY Group_name

the results I'm getting in this combo are just Group_id although I have ColumnCount = 2, ColumnWidths = 0; 4,

Any idea what it might be?

Mark

 

[Mod]

A width of 0 hides a column.

 

[Markvand]

I know, that's why I wanted to hide Group_ID and get the name, instead I get only ID

 

[Mod]

oh, sorry. I didn't gather that from your post. I can't make my Access behave tha way.

 

[Markvand]

Somebody help

thnx anyway,

Anybody have an idea?

 

[WayneRyan]

Mark,

I agree with Mod, I can't make Access do that either. are you sure about
the order of items in your SQL AND the widths?

Wayne

 

[Markvand]

Damn, it's strange, tbl structure might be a proble, thus I'll show you how they look like.

TblMaterials

MaterialID text (pk)
Name text
Vendor text
Group number (SELECT TblMaterialGroup.GroupID, TblMaterialGroup.groupname FROM TblMaterialGroup ORDER BY [groupname]; Columncount 2, columnwidth 0cm;3cm)


TblMaterialGroup

GroupID autonumber (pk)
Groupname text


and once again the combo, little bid different now

SELECT tblMaterials.Group FROM tblMaterials ORDER BY Group


What do you think?

Regards

Mark