Make Fields Autopopulate from a ComboBox Selection based on a Separate Table?

[Tim Mullin]

Hello, I'm kind of a newbie with Access (2007) and Visual Basic and am wondering if someone could help me out. I've searched all over for an answer to this and haven't found one that's worked out for me.

I have a form titled "Contracts" which contains all of the information for customer contracts. On the Contracts form there is a combo box linked to a "Customers" table to provide the data for the list of customers in that combo box.

Customer account numbers are kept on the "Customers" form.

I was wondering if there is a way (I would imagine there is) to have a text field populate with a corresponding customer account number on the Contracts form after the Customer is selected from the combo box? Any help would be greatly appreciated, I've been trying to figure this out for days!

[Jerry Maiapu]

You can just use inbuilt wizards to do exactly that but anyway try this:
Make sure that there is a common field value (unique-primary key) in combo and the form. (for example employee Id)

imagine that your;
1. combo box is cmboname
2. common Id is EmployeeID

Paste this on the After Update event of the combo box.

Private Sub cmboname_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[EmployeeID] = " & str(Nz(Me![cmboname], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
end sub

Post back if any query

[Tim Mullin]

I received an error when I tried the code you provided...don't I have to point the afterupdate to a textbox to populate it?

I tried using:

Private Sub ClientName_AfterUpdate()
Me!Text71 = Me!ClientName.Column(2)
End Sub

Which got the account number to generate, however the text field that was populated did not change when switching records. So whenever I updated the combobox on any record that would generate the number for all of the records.

Any other suggestions? You mentioned I could use the built-in wizards to accomplish this, but I have had no luck...

[Jerry Maiapu]

I need to see you record source structure of the forms (sub-form and main form)
Attached your db and post it. I'll see what i can do for you

[Tim Mullin]

Thanks again for your reply, sorry it took so long to respond. I can't send out the database (confidentiality reasons), but I have included some screenshots that hopefully help explain my problem.

The first is the clients form, where the three fields I'm dealing with have all the data initially entered in.

The second is the contracts form, which has a combobox to select from client name's that have been listed in the client form. I've added two text fields in hopes to have them generate with the data (account# and meter#), also from the clients form and specifically related to the client that is selected from the combobox.

[Jerry Maiapu]

Sorry I cannot see your screenshots

[OldBirdman]

I'm not exactly sure what you can or can't do from your screenshots. I am attaching a demo of what I think you can use. I hope it helps

[NeoPa]

The usual, easier, approach, would be to have a multi-column ComboBox that displays both the ID and the name.

If it's necessary, for some reason, to maintain the name in a separate TextBox control, then it should be unbound and it would be updated by code from the value in the ComboBox (It should be Multi-Column whether or not you intend to display the data as such) from within the AfterUpdate() event procedure of the ComboBox.

Welcome to Bytes!

[Jerry Maiapu]

There is no realtionship specified yet for the tables, nevertheless like Neo says try thsi:


Qute busy right now for further explaination.

Private Sub Combo27_AfterUpdate()
        Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[MonthNumber] = " & Str(Nz(Me![Combo27], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

[NeoPa]

Jerry:
like Neo says try this:

I'm not sure you followed my point exactly Jerry. The most important part of my point, as I see it, is that when the data is loaded from the table into the ComboBox control, as it has to be anyway to load the IDs, then if it also has the associated names loaded it will always have the data available already, without need for extra resources to manage a recordset of any kind. It would simply be picked up from the ComboBox, which we saw earlier, need only be queried the once at the very start.

I hope this makes it all a little clearer.