Auto Populate a Form? 2

[belstoy]

I apologize, this seems like it should be easy, but I'm new to this....
I have a form which folks are entering info to create a db.
I have a combo box with "Company ID" for folks to choose the appropriate ID on the form. The "Company ID" combo box gets its data from a separate table; "Company Data".

Also located in the "Company Data" table is the field "Company Name".

Desired Outcome: I would like when folks choose the appropriate "Company ID" from the combo box on the main form, that the "Company Name" box on the form would autopopulate with the correct name from the "Company Data" table.

Thanks,
Belstoy

 

[Remou]

Hi
Check out Dlookup, it may suit.

Dlookup ("Company Name", "Company Data", "[Company ID]='" & Me![Company ID] & "'")

The above assumes that Company ID is a text field.

 

[jebry]

Hi!

Put Remou's code in the After Update event of the combo box.

Me!YourTextBox.Value = Dlookup("Company Name", "Company Data", "[Company ID]='" & Me![Company ID] & "'")

Alternatively you can put the code into the control source of the text box:

=Dlookup("Company Name", "Company Data", "[Company ID]='" & Me![Company ID] & "'")

But then you will need to put a Me.Refresh in the After Update event of the combo box.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[belstoy]

Thanks!

I have tried both suggestions, but I think I missed a step?. I keep getting the error...

"Access cannot find the macro"

Any advice?

Thanks,
Belstoy

 

[jebry]

Hi!

It sounds like you are typing the code into the After Update box on the event tab in the property sheet. If that is the case then erase that from the box and you should have and elipse (...) at the right side of the box. Click on this and you will get a pop-up box from which you should choose code builder. This will take you to the VB interface with the lines Private Sub YourComboBox_AfterUpdate() and End Sub. Put the code between these lines.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[belstoy]

Thanks again, However, I'm still struggling....

I have created the code in the code builder as suggested, but I am still receiving an error... The error states:

"Run time Error '3075'
Syntax error (missing operator)in query expression 'Company Name'.

The debugger highlights the following...

Private Sub Combo145_AfterUpdate()
Me!txtBox147.Value = Dlookup("Company Name", "Company Data", "[Company ID]='" & Me![Company ID] & "'")
End Sub

Any ideas? Thanks.

 

[PHV]

Me!txtBox147.Value = Dlookup("[Company Name]", "[Company Data]", "[Company ID]='" & Me![Company ID] & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

Hi
My fault:
Dlookup("[Company Name]", "Company Data", "[Company ID]='" & Me![Company ID] & "'")
You need the brackets because of the space in Company Name.

 

[belstoy]

Thanks to all. I have success!!

 

[Lightning]

As an alternative to DLOOKUP, you could try using a two-column combobox. Then in the After Update event of the combobox, set the Company Name field to the value of the combobox's second column.

The Rowsource property for your combobox would be

SELECT [CompanyData].[CompanyID, [CompanyData].[Company Name] From CompanyData

In the Combobox's Property sheet, set the Column Count property to 2, the Bound Column property to 1. Set the Column Width property to an appropriate width, separating the two columns with a semi-colon. For instance:

1.0cm;3.5cm

In the After Update event of the combobox some code similar to

Private Sub TextBox147_AfterUpdate()
    Me.[Textbox147] = Me.[YourComboboxName].Column(1)
End Sub

When you click on the combobox, it would display the Company ID, followed by the Company Name for that ID. Something like
001 Acme Chemicals

Once you have selected a company from the list and either pressed [ENTER] or moved the focus away from the combobox, the combobox will display the Company ID and your textbox will display the Company Name.

Combobox columns are zero-indexed, meaning that a two-column combobox has columns indexed as 0 and 1. So to refer to the second column you use Column(1).

For more information on using these, have a look at the Column Property topic in the Access Help file.

HTH
Lightning