Auto-fill textboxes on form based on selection from Listbox

[iansan]

Hi

I want to construct a form with a textbox receiving user input(AccountNumber-from tblMembers). On enter, I want to display Fields from tblCharges in a listbox. I then want to be able to select one row from the listbox, use the field values to partially populate a new form, frmPayments, only to add the current date and amount paid.
Any help on this one?
Thanks.
Ian Smook.

 

[JerryDennison]

There are several ways to do this. One is the use of a Form/SubForm/LinkedForm, one is the use of a Form/SubForm, and one is the use of only a single Form and two Comboboxes.

The Form/SubForm/LinkedForm could probably play off of an existing Form/SubForm you have for display/add/edit of the charges. But if not, you would have a form bound to tblMembers with a subform bound to tblCharges linked to each other via AccountNumber. Use the find record wizard to create a combobox for the user to select the member in the main form, this will bring up all related records in the subform. Add either a button or double click event to open another form for adding payment info specific to the charge ID. This is done by linking the charge subform to the payment form. It is possible to bypass having to open another form completely if you'll use a sub/subform. Access supports up to two nested subforms provided the first subform is a continuous form and the second subform is a datasheet.

The second scenario is a simple Form/SubForm. The form would be based on tblCharges with comboboxes in the header to facilitate finding the charges for a specific AccountNumber. It should be set as a continuous form with a subform for then entering the related payments.

The last scenario is a form that is based solely on the payments table with two comboboxes in the header to facilitate assigning payments to the specific charge. The first combobox would be used to limit the second combobox and would be based on the member. Once a member is selected in the first combobox, the second comboboxe's rowsource would be limited to only records that are related to that account. Then once an account is selected in the charges combobox, it's afterupdate event procedure would filter the form's recordsource to only those payments that match the charge. The field that links the payments to charge should have it's default value set to the appropriate column of the charge combobox.

 

[iansan]

JerryDennison
Thank you for your advice. I opted for the single form and two comboboxes. It works perfectly.
I only had to ad a cboCharges.Requery IN THE AFTER UPDATE EVENT of the cboMember.
Thanks again
Ian Smook

 

[JerryDennison]

You're welcome.