Populating Drop Down Field in Form

[harry t.]

The project I’m working on is done in Access 2003.

I’m trying to setup a form where they select a category from the 1st drop
down box and it filters the keywords that are in the 2nd drop down box and
it’s based on what’s selected in the 1st. There are other fields in this
form, but it’s not found in other tables. The user has to populate these
other fields.

I’ve got 3 tables setup. The first contains the categories. The second has
the keywords. These 2 tables have a 1 to many relationship. The 3rd table
is what the form is populating. The 3rd table has some other data in it they
have to input, but it has no bearing on my problem here.

Any suggestions?

Thanks,

Harry

 

[Douglas J Steele]

What you're trying to do is normally referred to as "cascade combo boxes"

See whether http://office.microsoft.com/en-us/assistance/HA011730581033.aspx
helps.

 

[harry t.]

Okay...I've got that to work, but I do have 1 other question about this.

If for some reason, they've entered the Category, then select a product, and
realize the wrong category was selected. How can I tweak this to where if
they change the category to re-populate the Products?

Harry

 

[Douglas J. Steele]

The Requery in the AfterUpdate event of the Category box should take care of
that.

Are you saying it isn't working for you?

 

[harry t.]

When I setup a requery in the AfterUpdate Event of the category box, and try
it, it sends me to the top of the table that the data is going in.

I may have the requery macro set wrong though. What I set up the action to
requery, what control name or setting should I refer to? I've only got the 1
action (requery) in the macro.

 

[Douglas J Steele]

I think you should be using VBA, not macros for this.

In the AfterUpdate event for the Category combo box, you should be
requerying the other combo box.

Incidentally, I just noticed there's an error in the code that does this in
the sample.

The routine should be:

Private Sub Category_AfterUpdate()
Me.Product = Null
Me.Product.Requery
Me.Product = Me.Product.ItemData(0)
End Sub

(there's an underscore between the control name and the event name)

BTW, the third line is optional: it sets the first row as being selected.