Database

[odin1177]

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!

 

[xenou]

RE Post 39:

Private Sub partNumber_LostFocus ()
Me.Customer.Value = DLookup("[Customer]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Customer].Value & "'")
Me.Description.Value = DLookup("[Description]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Description].Value & "'")
Me.Tool_Number.Value = DLookup("[ToolNumber]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![ToolNumber].Value & "'")
Me.Cavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Cavities].Value & "'")
Me.Cycle_Time.Value = DLookup("[CycleTime]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![CycleTime].Value & "'")
End Sub

If the part number is entered on ripForm and the data is in the Part table then we want to lookup all the values in the part table based on the part number entered:

Private Sub partNumber_LostFocus ()
Me.Customer.Value = _
    DLookup("[Customer]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.[[COLOR="RoyalBlue"]PartNumber[/COLOR]].Value & "'")
Me.Description.Value = _
    DLookup("[Description]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.[[COLOR="RoyalBlue"]PartNumber[/COLOR]].Value & "'")
Me.Tool_Number.Value = _
    DLookup("[ToolNumber]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.[[COLOR="RoyalBlue"]PartNumber[/COLOR]].Value & "'")
Me.Cavities.Value = _
    DLookup("[Cavities]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.[[COLOR="RoyalBlue"]PartNumber[/COLOR]].Value & "'")
Me.Cycle_Time.Value = _
    DLookup("[CycleTime]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.![[COLOR="RoyalBlue"]PartNumber[/COLOR]].Value & "'")
End Sub

It is assumed the part numbers are text since we are quoting them.
You can put these formulas directly in the unbound textboxes - you don't have to use code.

----------------------------------------------------
Also, if no data is returned, you can (and should) test the DLookup directly with a Query using the same part number:

Me.Customer.Value = _
    DLookup("[Customer]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.[PartNumber].Value & "'")

Test with (Assume Part Number 12345678 has been entered):

SELECT Customer 
FROM tblPartNumbers 
WHERE PartNumber = '12345678';

 

[odin1177]

Well, before we do that, let me show you something else that is wrong. Each of those DLookups needs to be in the Lost Focus property of the combobox that is holding the value of the part number. You have them each assigned to their own Lost Focus, and that is not correct because you are never "tabbing" or "focusing" on those fields to be able to lose the focus.

It should be something like

Private Sub partNumber_LostFocus ()
Me.Customer.Value = DLookup("[Customer]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Customer].Value & "'")
Me.Description.Value = DLookup("[Description]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Description].Value & "'")
Me.Tool_Number.Value = DLookup("[ToolNumber]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![ToolNumber].Value & "'")
Me.Cavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Cavities].Value & "'")
Me.Cycle_Time.Value = DLookup("[CycleTime]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![CycleTime].Value & "'")
End Sub

Go back to the original control source and see if this code works. And remember to change Private Sub partNumber_LostFocus() to whatever that combobox is. And make sure that the Lost Focus property on the combobox where the Lost Focus is is set to Event Procedure.

ok I will do that, sorry, I was at verizon my Droid X in in the fritz

 

[odin1177]

1) why are customers associated with Part Numbers? Can't more than one customer buy a part? (or if parts are purchased, more than one supplier supply a part?)

2) You're question isn't clear. I don't see "multiple names the same based on part number". Each part number in your example is shown once only, so only one name would go with it.

This is a manufacturing data set. Inteva has over 500 parts made for them by the company I am making this database for. and what I want to have happen on the rip form which belongs to tblRipForm, is to have Customer, CycleTime, Description, ToolNumber, and Cavities all auto populate when a part number, and that is where the tblPartNumbers comes into place, I want the for to autopop based on the row with the coresponding data from the table, much like this. I enter 516301600001 and it looks back to the table, and says basically OH I know this number, it has this description, cycle time, toolnumber, cavities, and is this customer. which saves a lot of time, as this company makes parts 24/7 and each part number is going to be entered 3 times per day minimum based on 3 8 hour shifts.


BTW I tried it that way putting into partnumber lost focus and does not work

 

[odin1177]

RE Post 39:

Private Sub partNumber_LostFocus ()
Me.Customer.Value = DLookup("[Customer]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Customer].Value & "'")
Me.Description.Value = DLookup("[Description]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Description].Value & "'")
Me.Tool_Number.Value = DLookup("[ToolNumber]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![ToolNumber].Value & "'")
Me.Cavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Cavities].Value & "'")
Me.Cycle_Time.Value = DLookup("[CycleTime]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![CycleTime].Value & "'")
End Sub

If the part number is entered on ripForm and the data is in the Part table then we want to lookup all the values in the part table based on the part number entered:

Private Sub partNumber_LostFocus ()
Me.Customer.Value = _
    DLookup("[Customer]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.[[COLOR=RoyalBlue]PartNumber[/COLOR]].Value & "'")
Me.Description.Value = _
    DLookup("[Description]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.[[COLOR=RoyalBlue]PartNumber[/COLOR]].Value & "'")
Me.Tool_Number.Value = _
    DLookup("[ToolNumber]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.[[COLOR=RoyalBlue]PartNumber[/COLOR]].Value & "'")
Me.Cavities.Value = _
    DLookup("[Cavities]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.[[COLOR=RoyalBlue]PartNumber[/COLOR]].Value & "'")
Me.Cycle_Time.Value = _
    DLookup("[CycleTime]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.![[COLOR=RoyalBlue]PartNumber[/COLOR]].Value & "'")
End Sub

It is assumed the part numbers are text since we are quoting them.
You can put these formulas directly in the unbound textboxes - you don't have to use code.

----------------------------------------------------
Also, if no data is returned, you can (and should) test the DLookup directly with a Query using the same part number:

Me.Customer.Value = _
    DLookup("[Customer]", "tblPartNumbers", _
    "[PartNumber]= '" & Me.[PartNumber].Value & "'")

Test with (Assume Part Number 12345678 has been entered):

SELECT Customer 
FROM tblPartNumbers 
WHERE PartNumber = '12345678';

I tried this and got a compile error, not sure why though??? this code stuff is baffling me.

I appreciate all of the help you guys are giving me, and thank you for your time also.

Anyone want me to email this to them, it is an Access 2010, and is 4.54MB

I only need help with tblPartnumbers, connecting with frmRipForm, which is bound to tblRipForm, everything else works great. I just have to get the autopop feature working, if anyone would like to take a peek at my baby, my first database creation my email is odin1177@hotmail.com

 

[Montez659]

Xen, I am glad that you caught that. We had fixed that several posts ago. Not sure how that old code got rehashed.

 

[Montez659]

What exactly is the compile error that you are getting?

 

[odin1177]

What exactly is the compile error that you are getting?

it is a dialog box that says compile error, and highlights .CycleTime and all of the other ones




Private Sub Combo605_LostFocus()
Me.Customer.Value = _
DLookup("[Customer]", "tblPartNumbers", _
"[PartNumber]= '" & Me.[PartNumber].Value & "'")
Me.Description.Value = _
DLookup("[Description]", "tblPartNumbers", _
"[PartNumber]= '" & Me.[PartNumber].Value & "'")
Me.Tool_Number.Value = _
DLookup("[ToolNumber]", "tblPartNumbers", _
"[PartNumber]= '" & Me.[PartNumber].Value & "'")
Me.Cavities.Value = _
DLookup("[Cavities]", "tblPartNumbers", _
"[PartNumber]= '" & Me.[PartNumber].Value & "'")
Me.CycleTime.Value = _
DLookup("[CycleTime]", "tblPartNumbers", _
"[PartNumber]= '" & Me.[PartNumber].Value & "'")
End Sub

says method or data member not found, I had this when we first began, and fixed it with the spelling, but the spelling in this code is right.

 

[xenou]

Then you don't have a control named CycleTime. Or you haven't put your code in the right place.

I suggest you create a test form. Put in it two textboxes. On will hold a part number you enter. The other will hold a DLookup formula that looks up a value in another table, based on the part number you enter. Slave over this until you understand how to use DLookup in a textbox on a form. It's not something that's very hard and there's many examples on the web. You have to learn these fundamentals and it's easier in a more simplified test environment. Once you know how it works, you can come back to your more complicated form with better knowledge of how to work with it. Access is a complex program and you shouldn't try too much all at once.

ξ

 

[xenou]

Here is a sample database as described above (1 table, 1 form). There is a textbox in the form to enter a Product ID. The two textboxes below show the Product Description corresponding to the Product ID - one uses a formula in the textbox (no code), the other uses the after update event of the textbox where the ID is entered. Since we need to trigger the after update event, I added a button simply to give the user something to do.

Work through the examples. Read the help files on DLookup. Create a test sample of your own. Then return to your form armed with better knowledge.

<a href="http://northernocean.net/etc/mrexcel/20110702_db.zip">Sample Database</a>
sha256 checksum (zip file): c502055e38640d9e1bdc86f140fd9596fb5e3ac37b679ef2f22d4fb231edda8b

 

[odin1177]

Here is a sample database as described above (1 table, 1 form). There is a textbox in the form to enter a Product ID. The two textboxes below show the Product Description corresponding to the Product ID - one uses a formula in the textbox (no code), the other uses the after update event of the textbox where the ID is entered. Since we need to trigger the after update event, I added a button simply to give the user something to do.

Work through the examples. Read the help files on DLookup. Create a test sample of your own. Then return to your form armed with better knowledge.

Sample Database
sha256 checksum (zip file): c502055e38640d9e1bdc86f140fd9596fb5e3ac37b679ef2f22d4fb231edda8b

I do not know how to do that, make any testing databases, this is my first one, and outside of the auto population, I did pretty well, but this puts me in over my head.