I need this bad! Form design help!

[aejester]

Ok,

I need a 'maintenance entry form' designed so that when the user selects an inventory type from the inventory combo box, the inventory item combo box automatically loads up all the inventory items that belong to that type.

A similar situation and easier to understand scenario is as follows:

User is entering different events for an entertainer. They enter an event name, for example "Awards Ceremony" then select a province from the province combo box, for example "Ontario", and then I need all the cities that belong to that province to show up in the City combo box.

Is this possible?

I'm clueless as to how to do this I've tried everything, and whenver I set the source of the cities combo box, it changes the values of ALL the records that are displayed, not just the current record!!!

If anyone wants, I could email you a sample access 97 database of what I want.

Thanx in advance!

 

[AlexMidd]

In the AfterUpdate event for the first combo, have code like this:

Private Sub cbo1_AfterUpdate
cbo2.RowSource = "SELECT [Table2].[Field1]
WHERE ((([Table1].[Field1])=[cbo1]));"
cbo2.Requery
End Sub

[Table2].[Field1} is the field that you want data to come from in the second table, e.g. the inventory items. [Table1].[Field1] is the field that you want to match with, e.g. the Inventory ID. Requerying the second combo box lists the appropriate items for the selection in the first combo box.
Have fun! )

Alex Middleton

 

[aejester]

This works fine in Single Form view. (1 record at a time). But I really need this for continuous forms view (multiple records per page). In multiple record view it resets every other record after I change the inventory type.

Can I do this so that it changes only the current record?

 

[aejester]

actually

even after i change the type in single record view it resets every other value. So this is not what I need.

....arghhhhh

 

[AlexMidd]

Sorry, I got it wrong again (I'm having a bad day).

Forget what I said before (hope it hasn't caused too much problems).

I have set the RowSource of the second combo to the following:

SELECT AreaSubs.SubHeading, AreaSubs.MainHeadingIndex, Areas.AreaNo FROM Areas INNER JOIN AreaSubs ON Areas.AreaNo = AreaSubs.MainHeadingIndex WHERE (((Areas.AreaNo)=[Forms]![frmEquipmentSub]![cboArea])) ORDER BY AreaSubs.SubHeading;

The code field names are specific to my database but basically it looks for Sub Headings that fall under a Main Heading (Area). The tables are "Areas" (for main headings) and "AreaSubs" for sub headings. cboArea is the first combo which has a list of Areas. In the AfterUpdate event of the first combo, requery the second one to change the list.

Hopefully you can amend this to suit your purposes. Have fun! )

Alex Middleton

 

[aejester]

Thanx that did most of the job, I just added the same code to the gotfocus event of the second combo box and it works perrrrrfect now.. Thanx a TON!