Queries on Forms via LookUp

[geospatial]

Trying to find if it is possible without a macro to select a query value to run that would configure a lookup dropdown box.

For instance if I am tracking automation equipment I have a drop down for equipment type (monitor, desktop, notebook, etc.)

I also have a dropdown for Manufacturers and one for model.

Say I choose monitor then it queries all the monitors I have and changes the Manufacturers lookup to the manufacturers that we have monitors for (HP, Dell, Samsung).

Once I choose a Manufacturer the dropdown for models change to lets Say models of HP monitors in my inventory.

Any examples or links for explanations on how or why you cant do this would be appreciated.

 

[xenou]

Hi,

This is explained fairly well here I think:
http://www.fontstuff.com/access/acctut10.htm

See if that helps and post back if you still have more questions.
(If anyone has another good suggestion please chime in).

ξ

 

[alansidman]

For cascading combo boxes I always used the method in the reference by Xenou. Recently I happened to stumble across another methodology that only requires one table.

http://www.access-programmers.co.uk/forums/showthread.php?t=223980

Alan

 

[geospatial]

Hi,

This is explained fairly well here I think:
http://www.fontstuff.com/access/acctut10.htm

See if that helps and post back if you still have more questions.
(If anyone has another good suggestion please chime in).

ξ

I got this to sort of work. I created a query to pull in the information into one table. The problem is when I choose say Monitor I get the results in another combobox that says HP, Dell, Dell, HP, Samsung. It didnt filter out unique records only.

 

[geospatial]

Sorry about the last post. Did some research and found SELECT DISTINCT which works fine.

 

[geospatial]

Now I do have a legitimate question. Got this to work in my form. Once I choose the Monitor Type the Manufacturer chooses the distinct options that match the type. Here is what didnt work and I am glad I made a backup before continuing. Not only does this change the current record only but every record in the database in my table my form links to.

 

[xenou]

Well that's curious. That kind of thing can happen if you have the records in a subform. Or if you have Continous Form instead of Single Form (all records vs. one record). Queries shouldn't be changing anything anyway, strictly speaking. What are you trying to do? Is this an update form? An entry form?

 

[geospatial]

Its an entry/update form for doing inventories.

My form is a split form. I have the combobox called cboEquipType and cboManufacturer.

I did what it said for row source on cboEquiptype where I placed the SQL code:

SELECT DISTINCT qryEquipment.EquipmentType FROM qryEquipment ORDER BY qryEquipment.EquipmentType;

I also have in code builder the following code:

Private Sub cboEquipType_AfterUpdate()
 End Sub
     On Error Resume Next
    cboManufacturer.RowSource = "SELECT DISTINCT qryEquipment.Manufacturer " & _
       "FROM qryEquipment " & _
       "WHERE qryEquipment.EquipmentType = '" & cboEquipType.Value & "'" & _
       "ORDER BY qryEquipment.Manufacturer;"
 End Sub

Say I go to record 1 and realize I made a mistake and it is not a desktop but a notebook. If I change it to desktop all 93 records change to desktop.

 

[geospatial]

Update In the split form the upper portion is showing the one record at a time but the lower form shows the entire table with all records. Could that be the problem?

 

[xenou]

I have things like this happen to me but not for some while. I think it's because nowadays I just use Access' built in filtering far more often than trying to create filters with comboboxes (I would use that for data entry only).

I guess this begs the question of what you mean by "doing inventories" and why your form is split. If you just need to see filtered data, you first filter by equipment type, then by manufacturer. Usually right clicking in a field is enough for that. But this sounds like more than that.