Pre-selected list fields based on form selection

[ayonae]

Hey everyone,

This problem is driving me crazy and it's probably very simple. First, I'll give you an idea of how my database is structered.

Database summary:

This is an estimation database that uses tagged equipment and quantities, man hours, etc. to produce an estimation of cost for the client.

Each piece of equipment has a tag (unique), a product name, description and electrical ratings (volts, amps, etc.) that changes depending on the type of equipment.

Structure:
I created a table (called Equipment) that has the tags, name, descriptions and ALL the ratings of every piece of equipment (some are the same between equipment) all in one table. I have 2 extra fields in the Equipment table, "Type" and "Input OR Load" that would define which equipment I'm entering data for.

I created FORMS for all the different equipment types (motor, MCC, Vaccuum, etc.) since they have some different ratings that the user must input to.

I have created a switchboard that allows the user to choose which equipment type & load/input to edit or add new records to via the forms that I've created.

Problem:
When the user selects a form to edit/create new records for whichever equipment type, I want to PRESELECT the TYPE field (value list that has motor, mcc etc.) and INPUT/LOAD field (list of 2 selections, Input or Load).

This is because I want to FILTER the TAGS so that the user, when scrolling through records, ONLY sees the equipment type they are currently editing/adding records.

Hopefully I was clear! If not, let me know!

 

[SydneyGeek]

You could try creating an unbound combo box on the form's Header section, which selects all of the possible equipment types. Call it cmbSearch. Then, in the AfterUpdate event of the combo, code like this:

Me.Filter = "[Type]='" & cmbSearch.Value & "'"
Me.FilterOn = True

To use the AfterUpdate event, go to the Properties of the combo.
Select the Events tab, double-click After Update so you see Event Procedure, click the ... button at the end of the row, and you'll be in the code window. The code I provided goes into the blank line before End Sub.

Denis

 

[ayonae]

That works - but how do I remove the duplicates in the cmbSearch so I when I use the drop-down menu, I do not see more than one of the same type?

 

[SydneyGeek]

In the combo, go to the RowSource property and click the ... at the end of the row to go to the query.
Go into SQL view. You will see SELECT ... FROM ...
Change it to SELECT DISTINCT ... FROM ...
That will select unique entries.

Denis