Add Data Automatically In Table

[RIck5150]

I am working with a table where I want to be able to add choose the customer name from a dropdown and have the customer number automatically populate. I set up the dropdown already using the query builder for customer name and it works perfectly. I also chose customer number in the query, but it does not show.

Even better, in case of duplicate names, I would like to be able to select the customer number and have the name populate.

So ultimately, when I select either field, I want the other to auto populate...

 

[Mihail]

Select your combo.
Property Sheet -> Format ->
Column Count: 2
Column Widths: 3;2
List Width: 5

 

[RIck5150]

I am using Access 2007, does that matter? I cannot find "format" in the property sheet.

 

[Pat Hartman]

If you want to enter a customer either by number or name, you will need two textboxes or combos neither of which will be bound. The bound field should be a combo with the ID as the bound field and the name as the visible field. In the AfterUpdate event of the customer number field copy the ID to the bound combo. In the AfterUpdate event of the unbound name field, use a DLookup() to find the customerID and copy that to the bound combo. You have to be extra careful with this since names are frequently duplicated. You should probably also have a DCount() and when the count exceeds 1, populate an pop up form to let the user select the customer. Bottom line is I would never use this method for data entry. I do use multiple search boxes for finding records either by number or name. To help my customers choose from similar or even duplicate names, I include other columns in the combo such as ID or phone number or city/state or street address. Whatever will help the user choose among options.

 

[Mihail]

"Format" is the tab name. And you already find it if you find the other items (Column Count, Column Widths, List Width). This is only for combo's columns visibility.
As approach, read very carefully what Pat say.

 

[RIck5150]

Sorry, but the only tab in the property sheet for a table is "GENERAL."

I wanted to know if this was possible to do within a table, but instructions look like they are telling me how to do this in a form. Unless I am reading something wrong.

 

[Mihail]

Oups.
Yes. We talk about Forms.
And NO. As far as I know, you can't do what you try at table level.
Pat will say you that even that drop down list is a very bad practice.

 

[Pat Hartman]

@Rick - Mihail is correct. I thought we were talking about a form since this is not even possible on a table. NEVER, NEVER, EVER expose users to tables or queries for data entry. You have no control over what they do. Always use forms. That allows you to do validation and control whether delete is allowed, etc.

 

[RIck5150]

Thank you, I will do this in a form. I am the only one using this table/database.

 

[Mihail]

Even you are a danger for your database if you try to manipulate data at table level
And this is the top of the iceberg. You have one table. What about 10, 50, 100 or more ?

 

[Pat Hartman]

I make forms for my own personal databases also. The wizard generates them for you and since they are for yourself, you don't need to make them pretty. They just need whatever code is necessary to ensure valid data entry.