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!

odin1177 · Jul 5, 2011

xenou said:
There's two problems with this formula (and the others like it):
Customer = DLookup (“Customer” , “PartNumbersT” , “PartNumber=” & PartNumber)

1)
you said your table is named tblRipform. so the second argument should be "tblRipform", not "PartNumbersT")

2)
if the part number is text it must be enclosed in single quotes:
PartNumber = '12345678'
Which in the formula looks like:
"PartNumber = '" & [MyControl] & "'"

Your quotes also look funny and may be bad too - not sure what's up with that.

Here's a new sample database. I have spaces in the field names so they may not match yours. My "part number" control is also called Text0, which is not what your's is called. Make the necessary changes:
Sample Database
sha256sum (zip file): 299ecd56b51a8356a2a5d8637a7d67170b8c71be4e2d82db76e7a38678c723bc

No code was required to fill the text boxes - the lookup formulas can go right in them on the form.

I am working on it, but would like to make the Part Number a Drop Down Combo Box that is Tied to the tblPartNumbers, would these still work.

I think I got it, will post once I have it working, or not to see where I went wrong.

UPDATE: I still do not have it working. My part data(Like I gave you is in tblPartNumbers) I am still working on it, and playing with entries, has to do with my names. I am going to keep working, and figure this out.

odin1177 · Jul 5, 2011

IT FINALLY WORKS, now to change the Part Number from a Text Box to a Combo Box, and change the code, to see if that works too.

Thank you for all of your help everyone!

odin1177 · Jul 5, 2011

odin1177 said:
IT FINALLY WORKS, now to change the Part Number from a Text Box to a Combo Box, and change the code, to see if that works too.

Thank you for all of your help everyone!

UPDATE, Converted the text box to a Combo Box, and altered the DLookup, and it works. I have to have the Part Number a Drop Box, there are 721 part numbers, and growing!!!!

xenou · Jul 5, 2011

If you right-click on the textbox in design view, there is an option to "Change To" and you can change it to a combobox.

For a combobox:

1) in Properties goto to the Data tab
2) on the Data tab be sure that the Row Source Type is Query/Table
3) in the Row Source type the SQL to get part numbers.

For number three, possibly just:
SELECT DISTINCT PartNumbers FROM tblRipform ORDER BY PartNumbers

odin1177 · Jul 5, 2011

xenou said:
If you right-click on the textbox in design view, there is an option to "Change To" and you can change it to a combobox.

For a combobox:

1) in Properties goto to the Data tab
2) on the Data tab be sure that the Row Source Type is Query/Table
3) in the Row Source type the SQL to get part numbers.

For number three, possibly just:
SELECT DISTINCT PartNumbers FROM tblRipform ORDER BY PartNumbers

I got it all to work, when I close Access and re-open it, it will not work, all of the stuff is still there. Could it be a bug in the software?

This is irritating, to get a crap ton of help from you guys, only to have it just stop working after Access is closed and re-opened!

xenou · Jul 5, 2011

Not sure. Unlikely to be a bug but you could try a compact and repair for kicks. Sometimes it just takes fiddling (actually, often that's what I do - keep messing about until something works).

odin1177 · Jul 6, 2011

Thank you!

Thank you to all that have put in their time, and their Efforts, the Database is Finished, and working, and I would not be done without all of the constant help that you have given me, Thank you for all of your time!

Montez659 · Jul 6, 2011

Amen! Glad you got it.

odin1177 · Jul 6, 2011

One last small Buggy Type Issue, I am having an issue with a piece of data saving. It is the Efficiency field. its formula is [FONT=&quot][Parts Per Shift]/[Part Capacity] Part capacity is one of the fields that has a formula, and auto fills when Cavities, and Cycle Time is populated, and those 2 are one of the ones that auto fill when part number is inputted. Here are the fields in the tblRipForm:
[/FONT] [FONT=&quot]Customer[/FONT]
[FONT=&quot]Date[/FONT]
[FONT=&quot]Employee Name[/FONT]
[FONT=&quot]Part Number[/FONT]
[FONT=&quot]Description[/FONT]
[FONT=&quot]Cycle Time[/FONT]
[FONT=&quot]Cavities[/FONT]
[FONT=&quot]Efficiency[/FONT]
[FONT=&quot]Tool Number[/FONT]
[FONT=&quot]Down Time[/FONT]
[FONT=&quot]Down Time Codes[/FONT]
[FONT=&quot]Part Capacity[/FONT]
[FONT=&quot]Parts Per Shift[/FONT]
[FONT=&quot]Color[/FONT]
[FONT=&quot]Machine (Make Combo Box 1-19)[/FONT]
[FONT=&quot]Scrap[/FONT]
[FONT=&quot]Bad Parts[/FONT]
[FONT=&quot]OP Name[/FONT]
[FONT=&quot]Production Minutes[/FONT]
[FONT=&quot]Production Shift (Combo Box 1-3)[/FONT]
[FONT=&quot]Mold Number[/FONT]
[FONT=&quot]Hour 1[/FONT]
[FONT=&quot]Hour 2[/FONT]
[FONT=&quot]Hour 3[/FONT]
[FONT=&quot]Hour 4[/FONT]
[FONT=&quot]Hour 5[/FONT]
[FONT=&quot]Hour 6[/FONT]
[FONT=&quot]Hour 7[/FONT]
[FONT=&quot]Hour 8[/FONT]
[FONT=&quot]OEE[/FONT]
[FONT=&quot]FTT[/FONT]
[FONT=&quot]5-Scrap Codes Drop Boxes[/FONT]

Here are the formulas:


[FONT=&quot]Efficiency % [Parts Per Shift]/[Part Capacity][/FONT]
[FONT=&quot]Down Time 480-[Production Minutes][/FONT]
[FONT=&quot]Part Capacity 28800/[Cycle Time]*[Cavities][/FONT]
[FONT=&quot]Parts Per Shift [Hour 1]+[Hour 2]+[Hour 3]+[Hour 4]+[Hour 5]+[Hour 6]+[Hour 7]+[Hour 8][/FONT]
[FONT=&quot]Scrap % [Bad Parts]/[Parts Per Shift][/FONT]
[FONT=&quot]OEE [Production Minutes]/480[/FONT]
[FONT=&quot]FTT 1-[Scrap][/FONT]

The formulas work, as all the reports I made work, but the Efficiency field does not save to the tblRipForm like it should. This seems like a bug, I have compacted and repaired several times. I think that because it is dependent on the auto-filled fields based on part number it remains a ghost if you will. Can anyone help me with a solution to this. I have tried anything I can think of to fix this.

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

[FONT=&quot]
[/FONT]

Montez659 · Jul 6, 2011

Well, first thing that I would suggest is to not actually save this field at all. The reason being that it is a calculcated field and you *usually* do not want to store this information, as you can always calculate it like you mentioned.

Does anything depend upon this field being saved, or could it just be recalculated everytime?

Database

odin1177

New Member

odin1177

New Member

Excel Facts

odin1177

New Member

odin1177

New Member

xenou

MrExcel MVP

odin1177

New Member

xenou

MrExcel MVP

odin1177

New Member

Montez659

Well-known Member

odin1177

New Member

Montez659

Well-known Member

Similar threads

Forum statistics

Share this page

Database

New Member

New Member

Excel Facts

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Well-known Member

New Member

Well-known Member

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