Autofill from table 1

[mirgss]

Hi:

I have a table "Parts" that has Part number, Part description, and price. I have a form "Estimate" which I would like the user to be able to select the part number and have the form autofill the description and the price. I have part number, description, and price all as combo boxes. In the part description "row source" box, I have

SELECT [Parts].[PartNumber], [Parts].[PartDescription] FROM Parts ORDER BY [PartNumber];

(I have no idea if this is required.)

I also have:

Private Sub PartNumber1_AfterUpdate()

    PartDescription1 = PartNumber1.Column(1)
    Price1 = PartNumber1.Column(2)
    
End Sub

in VBA. This works for the part description field, but for some reason I cannot get it to autofill the price option. Does anyone have any suggestions?

Thanks!

 

[PHV]

Add the price in the row source of the combo.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gammachaser]

You do not have a PartsNumber1.Column(2) -- you only have two columns and they are numbered (0) and (1). If you want to get the Price from the Combo Box, you need to include it in your record source

SELECT [Parts].[PartNumber], [Parts].[PartDescription], [Parts].[Price] FROM Parts ORDER BY [PartNumber];

Also make sure you change the 'Column Count' Property for the combo box to '3' to reflect the extra column. If you don't want the price to show in the combo box, just make the third 'Column Width' (which unfortunately refers to Column(2)) 0".

 

[mirgss]

Okay, so I changed my column # to 3 and added your code in my price combo box. Now it autofills my price category, but not my description category (with the exception of my "labor" item, for which it works perfectly).

Thanks for your help; I'm afraid I'm not very good at this stuff yet.

 

[Gammachaser]

Is 'Labor' a PartsDescription? If you give us some more information about your table (like the structure and a few rows of the contents) we may be able to help... also show us what your modified code looks like.

 

[mirgss]

Thanks Gammachaser! I have "Labor" as a PartNumber and PartsDescription (wasn't sure if this was the best way to account for labor but I'm still in the beginning stages) with a price of 0 (since I don't yet know what the dept charges for labor).

Here are a few records:

My Parts table has PartNumber, PartDescription, and Price.
PartNumber PartDescription Price
1000 ft GEN12981099 PROFUSION ACCESS CABLE 711.96
1298109 18/4 WIRE 1000' 805.35
151205 CARD DISPENSER, CRYSTAL CLEAR, RIGID PLASTIC, VERTICAL FORMAT/SIDE LOAD 0.54

The only thing I have in VBA is the following:

Private Sub PartNumber1_AfterUpdate()

    PartDescription1 = PartNumber1.Column(1)
    Price1 = PartNumber1.Column(2)
    
End Sub

Thanks for your patience!

 

[PHV]

And what is the actual SQL code of the PartNumber1's RowSource property ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gammachaser]

I see problems in your future if your PartNumber field in your table is a mixed type... some are numbers, some are text, some are both. Also you have what looks like a description (1000 ft GEN12981099) as a PartNumber. Access likes things much better (and therefore will work faster) if things are more uniform. It is also much easier to work with later, particularly if someone other than you is going to work with it. I would also add a column to the table that has a unique identifier in it such as an autonumber field. As the database grows you will undoubtedly want to relate this table to another and using a field like '1000 ft GEN12981099' will really screw you up. Labor could be a 'part' without much problem as long as there is one rate or several 'parts' with different 'numbers' and corresponding rates.

Now to the question asked... the code I posted is for the 'PartNumber1' combo box, not the others. In fact, the other combo boxes should be text boxes unless you want to be able to pick a part by description or price and have the other boxes filled as a result. Frankly, I can't see wanting to use price to pick the part in this particular case. Make the price combo into a text box (on the menu pick 'Format' 'Change To' ' Text Box') and make the control source '[PartNumber].[Column](2)' (no quotes.) If you want to be able to pick by either Part Number or Part Description (probably a good idea unless you have the part numbers memorized) they should be combo boxes with similar Row Sources (with different sorts.) For example, your row source for Part Number would be:

SELECT [Parts].[PartNumber], [Parts].[PartDescription], [Parts].[Price] FROM Parts ORDER BY [PartNumber];

and for Part Description would be

SELECT [Parts].[PartNumber], [Parts].[PartDescription] FROM Parts ORDER BY [PartDescription];

In the PartNumber combo you would hide the Part Description by setting the Column Width to something like 1";0";0" and in the Part Description you would hide the Part number by using 0";2" (or however wide you want the column based on your longest description.)

If the 'Bound Column' for each is 1 (not 0, which causes Access to store the position in the list, not the value) then you could just use

Private Sub PartNumber1_AfterUpdate()

    PartDescription1 = PartNumber1
    
End Sub

and

Private Sub PartDescription1_AfterUpdate()

    PartNumber1 = PartDescription1
    
End Sub

to update your form. Since your Price Text Box has '[PartNumber1].[Column](2)' as its record source it will automatically update as you change PartNumber1.

If you are binding this form to a table (e.g. 'Estimates') then you would want to make the Bound Column for the PartDescription ComboBox '2' to save the proper information. You would then change the VBA to

Private Sub PartNumber1_AfterUpdate()

    PartDescription1 = PartNumber1.Column(1)
    
End Sub

and

Private Sub PartDescription1_AfterUpdate()

    PartNumber1 = PartDescription1.Column(0)
    
End Sub

because the default column is the bound column.

Totally confused now? Good. My job is done, then.

 

[mirgss]

PHV:

Here is the row source for PartNumber:

SELECT [Parts].[PartNumber], [PartDescription], [Price] FROM Parts ORDER BY [PartNumber];

Gammachaser, it SORT of makes sense I'll give this a try and see if I can figure it out.

I know that the part numbers have no consistency; the people who will be using the database refer to vendor part numbers and we don't have any standard naming convention for part numbers. As we are not a retail business, I'm not sure we need standard part numbers; it might end up confusing the users more. I will certainly have to clarify some of the part numbers with the users...since as you mentioned, that one actually looks like a description. Basically they sent me a ton of spreadsheets with the estimates they had done for people, and they are not the most accurate recordkeepers (some records have no part number, and some have no description). So, this is going to be a long process in any case.

Thank you!!

 

[Gammachaser]

Even if you can't make the Part Numbers consistent, I highly recommend having a simple, unique identifier for each distinct part. Autonumber works well as a Primary Key.

 

[mirgss]

Thanks so much for your help! I will be sure to add a primary key.