Using VBA to auto-complete a form

[tranchemontaigne]

PROBLEM: I cannot figure out how to set the RecordSource property of a pop up form based upon an IsLoaded test.

APPROACH: I want to use an IsLoaded test to
1) determine which form is calling a lookup form and then 2) set the RecordSource property based upon the form that is calling it.

IMPLEMENTATION GOAL: The specific application is to populate the txtCounty and txtState text boxes on the value selected within the cboCity combo box on a calling form.

DESIRE: Rather than have a separate "lookup form" to correspond to each calling form, I want to only have only one lookup form and dynamically set the RecordSource property of that form each time. (Explicitly setting the criteria equal to [Forms]![frmCallingForm]![cboCity].value control where [frmCallingForm] would be set to the name of the calling form.)

OBSERVATION: My failed efforts involved the creation of a strSQL string containing an SQL statement and then setting the RecordSource property equal to strSQL. According to the documentation I have read, anytime you assign a new RecordSource property to a control or form, the control or form is requeried. My code fails to get this far along though. It produces application errors as soon as the strSQL string is defined.

SECONDARY QUESTION: I know that using a pop up form is a clunky way of completing this task. Does anyone have thoughts on a pure VBA approach to auto-completing fields that does not involve opening lookup forms?


Thanks in advance

 

[Axoliien]

Sounds like you are using bound controls. Bound controls are loaded and values are validated prior to form events. The way I dynamically load a control to use multiple forms is to unbind the control, build the SQL query, then bind the sql query to the recordsource.

This way you are not evaluating anything until during the form load, at which time you have available the option to retrieve passed parameters and do pretty much anything you normally would.

You can pass the calling form name if you wish, or you can pass the control value and build the sql string that way. If you pass the form name, be sure to set the control name you will reference to the same name on every form or you will get errors.

A pure VB approach for your example would be to put in an unbound combo box and set it disabled. When the user selects a value in the parent box, you can build the SQL query and bind the query to the combo box recordsource.

You can also use an ado recordset and query every value, then filter and update the child combo box as items are selected in the parent combo box.