Help with Form events

[utzja1]

Greetings, I have a form with a table as the datasource. The source table is large enough to make searching for individual records unwieldy, so I have the User enter a specific value and then click a macro button. The macro that executes first validates the filter value, then filters the data set. The User uses the record-selector buttons to move through the data set.

I have one value on the form that comes from another table. I built a quick Query to pull this value with the criteria on another field in the form (basic SELECT/FROM/WHERE build). In order to update the field with each new record, I was trying to have an event-handler run this query each time the User moved to another record. So far, I've tried several event handlers but haven't been able to get any of the events to fire (MouseUp, AfterUpdate, Change). I'm thinking now that after the filter is applied, Access doesn't really see a change or update if the User is simply scrolling through and not manually changing fields. Is there an event that's a good fit for this, or a better way to accomplish this? Many thanks for your help and consideration.

 

[pbaldy]

The current event fires when the user changes records.

 

[Cronk]

The OnCurrent event occurs each time a new record is displayed.

 

[Pat Hartman]

Rather than binding the form to a table, create a query that joins the main table to the lookup table so you can select columns from both. The lookup columns will automatically populate as you scroll through records. No code is required. When I do this, I set the Locked property to Yes for the lookup fields to prevent accidental update.

If your tables are large, filtering is inefficient since the entire table must be downloaded. Add selection criteria to the query you made above and have it reference the unbound "filter" controls on your form header. Add a button, if you don't already have one, to apply the filter. The only code will be to requery the form.

Me.Requery

 

[utzja1]

Thanks to everyone for the suggestions.

I'm going to build a new query and bind the table to that. I think it's the cleanest route.

Thanks again to all.

 

[utzja1]

New day, new challenges.

I added the necessary tables to the query and got all the necessary fields included in the query. I changed the form's record source from the table to the query. The form is visible in Design view but when I move to Form view, the entire form disappears (grey screen, tab still shows the form is loaded). If I change the record source back to the table, the form comes back.

Also, if I double-click the query once the District field is filled with a valid name, the query returns the proper dataset. If I bind the form to the query, can I get the query to run in the background so that the user never sees the query (only the form)? I suspect if I use the DoCmd.OpenQuery command that the User will see the query results in a table.

Thanks for the time and consideration.

 

[utzja1]

Solved the first item by researching the FAQ over at AllenBrowne.com

If both of these conditions are met, then the form will be blank:
a) There are no records to display
b) The form settings do not allow you to make additions

There is a simple workaround on his site that involves setting the ALLOW ADDITIONS to Yes, then adding a very short piece of code launched by the BeforeInsert event handler.

I moved the second question over the VBA forum because I felt it was more appropriately located there.

Thanks to those who posted here.