Cascading Combo Boxes not updating

[mysterybks]

I have a form with 2 combo boxes: AuthorID and SeriesName

I have SeriesName tied with a query whose SQL is:

SELECT Series.AuthorID, Series.SeriesName
FROM Series
WHERE (((Series.AuthorID)=[Forms]![Books inputting]![AuthorID]))
ORDER BY Series.AuthorID, Series.SeriesName;


The first record I go to on the FOrm and select the AuthorID, Then SeriesName will show the right list. When I go to another record and select AuthorID, the SeriesName combo box never changes.

Help!

 

[John Big Booty]

You will need to force the cascading combo to requery in the forms On Current event, otherwise it will only show the data relevant to the last time it was requried.

Me.SeriesName.Requery

 

[mysterybks]

Tried that, but it says "The object doesn't contain the Automation object 'Me .'

 

[John Big Booty]

Tried that, but it says "The object doesn't contain the Automation object 'Me .'

As you have typed above, you have a space between the "Me" and the "." if that is the way you a written the code then remove the space.

When you are in the code window as you type the full stop after the "Me" argument, a drop down list should appear with the available arguments one of which will be your Combo box.

 

[mysterybks]

Here's what is in the Code Window properties:

=[Me].[SeriesName].[Requery]

 

[John Big Booty]

Where have you put this code? The "=" sign is not required. The code needs to be in the Form's On Current event and should look like;

[COLOR="DeepSkyBlue"]Private Sub[/COLOR] Form_Current()

     Me.SeriesName.Requery

[COLOR="DeepSkyBlue"]End Sub[/COLOR]

To get the form events you need to put the form in Design view you will notice that there is a small square in the upper left hand corner of the design window right where the horizontal and vertical rulers would intersect. When it has a black square inside it you are dealing with Form properties, if that square is not there you are dealing with Control properties. If the small black square is not there simply click on the grey square to get the black square to appear within it.

 

[boblarson]

Tried that, but it says "The object doesn't contain the Automation object 'Me .'

That's because you put it in the PROPERTIES dialog and not the VBA Window where it is supposed to go.

See here for a visual:
http://www.btabdevelopment.com/main/QuickTutorials/Wheretoputcodeforevents/tabid/56/Default.aspx

 

[mysterybks]

THANK YOU! I fixed it. I had typed it in the On Current in the Properties dialog not the VBA Window. You are right. I wasn't sure which way you meant at first. Thank you!!!! I am still learning VBA.

 

[mysterybks]

Now it's broken again. It won't change the data in the combo box when I select an author for a new record. Only goes to the record that ties to the first record. I've tried it on OnCurrent for the form. I've tried it on AfterUpdate on the Author Field that decides which author I want the 2nd box (Book ID) to relate to. Help!

 

[mysterybks]

Only way I can get it to work is if I had Refresh All EVERY time.

It works great in one database but won't in this new database. I've tried EVERYTHING.

 

[mysterybks]

Got it to work using some macros instead.