Hello, I am trying to to pull information from a record in a table into a form based on the entry of data in another field in the form that exists in the table.
E.g., if I type in a product number, I want other attributes/details of the product pulled into the form via autopopulation as well. I am trying to use the afterUpdate event, but I am not sure how to get the information to pull over.
Make sense? Any help is appreciated.

Matt Nemec
Business Developer
Cardinal Health

I do a similar thing with a form to edit Insurance Policy information. I have a combobox with each possible Policy Number and then run the following code on the OnClick event.

Private Sub cboFindPolicy_Click()
    DoCmd.GoToRecord , , acGoTo, cboFindPolicy.Value
End Sub

I would think that you could probably use a textbox as well, but you might need to add some additional validation code.

Hope that helps, let me know if I need to elaborate.

Mike

Mike
EchoVue.com

I should probably have added that the form was set up with the data table containing all the details as the recordsource for the table, so when you first open the form, the first policy is displayed on the screen.

Mike

Mike
EchoVue.com

echovue, there isn't an OnClick event on a combo box. Do you mean the AfterUpdate event, or is there another button to click to get the update to appear?

This all depends on whether this is a bound or unbound form.

Bound form:
If the detail you are trying to pull in exists in the same record as the Product number, then use the combo look up wizard to find a record in your form, and then just drop the requisite text box on the form and go to Properties>Data and select Enabled-No, Locked-Yes. This will display the data without allowing users to modify it. Don't forget to add this to the form's On Current Event:

    cboCustomerLookUp = CustomerID

This will keep the combo box current as you scroll through records.

Unbound form:
Create the form as bound first, and then delete the recordsource.
Create a combo box to look up the Customers in your Customers table, and then on the combo box's After Update event put this sort of code:

   Me.RecordSource = "SELECT * from tblCustomer WHERE CustomerID = " & Str(cboCustomerLookUp)

I use a look up on my main form where the user selects a Customer BEFORE they open the form, and then give them a second look up on the form. This keeps the data calls to a minimum since they are only pulling one record at a time.

I hope this helps.


mmcdonal

I forgot to mention that if the data you want to display is in a second table, then you can create a subform and lock it. You can even format it to look like text on the main form if you flatten the subform and make the border invisible, etc. I use this on some of my forms.

I create a query with the data I want to display, then create a signle form view of the query. Then drop it on the main form, hide the outlines, and lock the data. It looks like text printed on the main form.

HTH


mmcdonal

That is definitely a better solution - I may have to implement it instead of my other one!

I am curious though, my ComboBox control has an OnClick event listed. I am using 2003 but I seem to remember 2000 having it as well, although I could well be mistaken.

Thanks

Mike

Mike
EchoVue.com

Good grief, you are right about the On Click event. I stand corrected.

mmcdonal

No worries!! The rest of the suggestion more than made up for it!

Mike

Mike
EchoVue.com

BTW, the code for the main form goes like this:

Combo Box: Look up meaningful data in the main table, like Customer Name (or Lastname, FirstName)
Button Text: Find this Customer
Button On Click Event:

'=====
    Dim stDocName As String
    Dim inCustID As Integer
    Dim stLinkCriteria As String

    inCustID = Me.cboCustomerLookUp

    stLinkCriteria = "[CustomerID] = " & inCustID
    stDocName = "frmCustomers"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
'=====

This opens the form with one record, then I use the code above to look up additional customers once the form is opened.

mmcdonal