Database

odin1177 · Jun 30, 2011

I am almost done with this database, but I need help with a macro that will help me auto-populate 5 fields after a certain field is populated. I have I have part number data in a separate table tblPartNumbers and I have a form frmRipForm. There are 6 fields alike between these two unbound objects, as the rip form is bound to tblRipForm and the Part Numbers table is bound to nothing, and needs to stay that way. I want to push data based on a record when the the Part number field is populated, I know it will be an after update macro, but I am lost after that. The fields are description, tool number, cavities, cycle time, and customer. I have this data all stored on tblPartNumbers, and want to push these to the form, which has 6 identical fields, plus many more for data entry. Any help would be appreciated, as I am new to Access, and this is my first database.

Thank you for your time, and have a great day!

Montez659 · Jun 30, 2011

More than likely, you will want to use the DLookup in VBA to get what you want. Here is an example of what it looks like. I couldn't get a good read from your description of your exact variables so this is pretty generic.

Code:

variable1 = DLookup("[theFieldYouWantToReturn]", "yourTable", "[ID]= " & [Forms]![YourFormName]![YourTextBoxName].Value)

Some pointers: "theFieldYouWantToReturn - replace this with the field that you are looking for in the table. In this case, if you are wanting to populate 5 different controls based off of one textbox input, you will have 5 Dlookups total, and each of these with different field names here.
"yourTable" - self explanatory.
"[Forms]![YourFormName]![YourTextBoxName].Value" - This can get tricky depending on whether the field is text or numerical. The above example is for a number. If text, there are a few extra quotes (double and single) that need to be added. For example:

Code:

"[ID]= '" & [Forms]![YourFormName]![YourTextBoxName].Value & "'")

is what the last part will look like for text.

There is probably a quicker way to pull all five fields that you want, but this is one way and the only one I know right now.

And I would use Lost Focus instead of After Update, and make sure that you select Event Procedure or Code instead of Macro.

odin1177 · Jun 30, 2011

Thank you, I will try that. I am looking to populate 5 fields after 1 field is populated, trying to limit key strokes when entering manufacturing data.

odin1177 · Jun 30, 2011

Do I do one of these for each entry on the form, example... Customer field properties I would do a Dlookup, and then cycle time etc....

boblarson · Jun 30, 2011

Whenever someone asks to automatically populate data when something else is chosen, it raises red flags for me. This strikes me as a potential non-normalized situation. So I am not sure which fields you are trying to populate as you haven't stated. So what are they?

odin1177 · Jun 30, 2011

When a part number is entered, I want the Customer, Description, Cycle Time, Tool Number, and Cavities fields to populate from the tblPartNumbers table.

Montez659 · Jun 30, 2011

If you are trying to popualte five different fields, based off of the entry of one single textbox, then yes, you would have 5 Dlookups (unless there is a better way, and I certainly defer to Bob on that).

I also forgot to add that after you get the value into the variable, move that into the textbox that you are trying to populate. i.e.;

Code:

 Me.txtCustomer.Value =  variable1

You could even put the Me.txtCustomer.Value = Dlookup... to save space and haivng to declare variables.

odin1177 · Jun 30, 2011

Actually the part numbers field is a combo drop box. but the other fields are just text boxes.

odin1177 · Jun 30, 2011

Me.txtCustomer.Value = Dlookup...variable1 = DLookup("[theFieldYouWantToReturn]", "yourTable", "[ID]= " & [Forms]![YourFormName]![YourTextBoxName].Value & "'")

Is that what the code looks like assembled????

boblarson · Jun 30, 2011

Well, from what I see, you should not be storing most of that data again as it is already in the other tables and you can get it at any time with a query. If customer is stored in the parts table (that brings up other normalization quesitons) and if every one of those fields is linked to the part number then NONE of them should be stored again. Only the part number.

You don't store data multiple times. With the exception of ID's for the Primary key of a record, data should be stored ONCE, not multiple times.

Database

odin1177

New Member

Excel Facts

Montez659

Well-known Member

odin1177

New Member

odin1177

New Member

boblarson

MrExcel MVP

odin1177

New Member

Montez659

Well-known Member

odin1177

New Member

odin1177

New Member

boblarson

MrExcel MVP

Similar threads

Forum statistics

Share this page

Database

New Member

Excel Facts

Well-known Member

New Member

New Member

MrExcel MVP

New Member

Well-known Member

New Member

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock