Auto Populate fields based on Combo Box selection

[abernut]

I have been trying to figure this out for a few hours now, and I am still getting my rear kicked.

I have three tables with the following columns:
MoldReq

1. Mold_No
2. Part_Name
3. Part_No
4. Material
5. Date
6. Work_Ord(Pri Key)

MoldLog

1. Log_ID
2. Mold_No(Pri Key)
3. Part_Name
4. Log_Date
5. Time_In
6. Time_Out
7. Hours

Molds

1. Mold_No(Pri Key)
2. Part_Name
3. Part_No

I am trying to build a form in the MoldReq section so when the user selects the mold number(combo box) it will auto populate the Part_Name and Part_No fields by pulling the data from the Molds table.

I have tried following this video where I create a change event:

Private Sub cboCombo31_Change()


Me.txtPart_Name.Value = Me.cboMold_No.Column(3)
Me.txtPart_No.Value = Me.cboMold_No.Column(4)


End Sub

Any help would be greatly appreciated.

[pbaldy]

You haven't said what the problem is: error, wrong result, etc. What is the row source of the combo, and what is the column count property?

[abernut]

Sorry,
It just doesnt work.
When I select the Mold Number from the Mold_No combo box, the Part Name and Part Number fields do not auto populate.

[ssanfu]

What is the Row source SQL of the control "cboMold_No"?

What about formatting?
Bound column =
Column count =

[pbaldy]

Did you miss:

What is the row source of the combo, and what is the column count property?

[abernut]

Sorry but I am a noob with access.
Where can I find that?

[pbaldy]

They are properties of the combo.

[ssanfu]

Did you miss:

@Paul..... Dooh!!! Apparently I need to refresh my browser when I get back or learn to read better....

[pbaldy]

Sorry Steve, I didn't mean that for you, though I was curious why you asked for the same info.

[ssanfu]

@Paul, Actually I had to go to a short meeting and didn't refresh my browser before answering, so I didn't see your post til after I posted...

[alcapps]

MoldReq

1. Mold_No
2. Part_Name
3. Part_No
4. Material
5. Date
6. Work_Ord(Pri Key)

MoldLog

1. Log_ID
2. Mold_No(Pri Key)
3. Part_Name
4. Log_Date
5. Time_In
6. Time_Out
7. Hours

Molds

1. Mold_No(Pri Key)
2. Part_Name
3. Part_No

you might try making the the Mold_No field a lookup field in the form by chaning it to a combo box.
make the combo box have 3 columns. tied to column 1 with width set to 1;0;0
source = select [Mold_No], [Part_Name], [part_No] from Molds
Event After Update on the combo box .. replace cboMold_no below with the field name for the combo box.
put in ..
Me.[part_name] = me.cboMold_No.column(1)
Me.[Part_No] = me.cboMold_No.Column(2)

that should do it.

hope that works for you..

[ssanfu]

I quit thinking about making the combo box function and started looking at the table structure. I think you should fix the structure before going any further.
Table "Molds" is OK, but the other two are not correct.

I tried to change the tables but I don't know enough about your data/process....

[abernut]

I tried to change the tables but I don't know enough about your data/process....

A little background...(The sounds of chimes in the background)

I am the only IT for a small manufacturing company. My background is in Routing/Switching, LAN Admin, etc... Well being the only IT means I should have all answers to anything that has 1 and 0 flowing through it

I was recently approached by the molding department and asked if I could help streamline the mold request process and get rid of the paper work.

The process is as follows:

• They have an excel sheet of all of the molds they use. This sheet contains the Mold Numbers, Part Name, and Part Number for each mold.
• If someone needs a part made that uses one of the molds they fill out a Mold Request sheet. This sheet asks for the Part Name, mold number , and Part Number (Which all comes from the Mold Excel sheet), Material to be used, Date, and Work Order.
• After the mold is used, the Tooling department fills out a Mold Log form. This form has the Mold Number, Part Name, Part Number, Work Order, Date, Time in, Time out, Total Hours.

There are also some comments areas on each sheet but above is the main process.


My thought was to have three tables (Molds, MoldReq, and MoldLog) I have already entered about 50% of the data into the Mold table (Mold Numbers, Part Names, and Part Numbers)

I was then hoping to have three forms.

1. The first form will be for the Mold Request. The user can choose the Part Name from a combo box which gets its data from the Molds table. Once they select an Part Name the Mold Number and Part Number auto populate. They then can fill out the remaining data.
2. The next form will be the Mold Log form. The user types in the Work Order Number which ties back to the Mold Request table. It then auto populates the Mold number, Part Name, and Part Number.
3. The last form is to enter new Molds into the system. This would just contain three fields Mold Number, Part Name, and Part Number.

So what I thought would be an easy task is snowballing into a giant poop sandwich.

I'm not even sure if I have my relationships set up correctly to be honest with you.

But once again, any help with this is GREATLY appreciated.

Mike

[alcapps]

can you post your database with the tables and the forms that you created. If you don't feel comfortable sharing the database. Make a copy of your real database and remove all but a few recrods so that we have something to go by. I think this is a real easy fix for you.

[alcapps]

MoldReq

1. Mold_No
2. Part_Name
3. Part_No
4. Material
5. Date
6. Work_Ord(Pri Key)

MoldLog

1. Log_ID
2. Mold_No(Pri Key)
3. Part_Name
4. Log_Date
5. Time_In
6. Time_Out
7. Hours

Molds

1. Mold_No(Pri Key)
2. Part_Name
3. Part_No

your structrure can be as simple as
Molds

1. Mold_No(Pri Key)
2. Part_Name
3. Part_No

MoldLog

1. Log_ID
2. Mold_No(Pri Key)
3. request_date
4. Log_Date
5. Time_In
6. Time_Out
7. Hours(calculated if you want).. not really needed
8. do you need a requested by
9. work done by?
10. comments

users table if you need to track requested by and work done by.. fields..



Initially you would have a request form. that they pick the Mold and you create a log entry where the Requestdate is filled in and the mold number.
your form for request doesn;t need partname and partno you can look that up based on the Mold_No.
logdate is blank on a new request.
On your Log form you then allow the log date to be filled out and then time .. and so forth.

pretty easy.. I think..