Auto add new line/record? 1

[mirgss]

Hi:

I have a table and form "Estimates" for quoting installation prices. Currently I have quantity, manufacturer, part #, part description, and price 1-8, for entering up to 8 parts on one estimate. However, using this method I can only enter 8 parts per estimate, and if I have fewer than 8 parts, I have a bunch of empty fields in my record. Is there a way to use only the number of parts I need on the form and when the user enters the last piece of information, there is a way to add another part to the same estimate? Is this a design flaw in my database or something that can't be helped? Any thoughts are appreciated, and I apologize for my ignorance but I'm pretty new at this.

Thanks!

Miranda

 

[dhookom]

I suggest (as per a previous thread) you read up on normalization. each part should be stored in a separate record.

Duane
Hook'D on Access
MS Access MVP

 

[mirgss]

I do have a separate record for each part, in tblParts. What I'm trying to figure out is if there is a way I can have only as many lines appear in my form as I need, instead of having extra or not enough rows in my form to accommodate the number of parts in each estimate. This means that tblEstimates would be able to add fields as necessary for more than 8 parts at once.

I know I am not an expert in this stuff but I think my tables are all normalized now.

 

[dhookom]

Use a continuous subform on an estimate main form. You would have tblEstimates and tblEstimateDetails. This allows anywhere from 0 to 100s of details for one estimate.

Duane
Hook'D on Access
MS Access MVP

 

[mirgss]

Awesome. I will give this a try. Thank you!

Miranda

 

[mirgss]

Okay; I created a continuous form and it mostly works. I'm having just a couple of problems when I add more than one part:

1. In VBA, I have

Private Sub PartDescription_AfterUpdate()

    PartNumber = PartDescription
    
End Sub

Private Sub PartNumber_AfterUpdate()

    PartDescription = PartNumber
    
End Sub

which worked in my last form. However, now when I add a second PatNumber, it grabs from the manufacturer in the first PartNumber regardless of what I have in the second manufacturer field. The row source for the PartNumber field is

SELECT tblParts.PartNumber, tblParts.PartDescription, tblParts.Price
FROM tblParts
WHERE (((tblParts.Manufacturer)=[Forms].[frmEstimateDetail].[Manufacturer]))
ORDER BY tblParts.PartNumber;

2. My second problem is my primary key (WorkOrder). Every time I go to a new row with the parts, it acts as though I am entering another entire record. What I want is ONE record with the estimate number, showing what the customer ordered, without having excessive lines.

I'm sure that I'm just missing something simple, but please take pity on me.

Thanks!

 

[dhookom]

Don't use any code. Your Parts should be selected from a combo box [PartNumber]. Then, to display the PartDescription, use a locked text box with a control source of:

=PartNumber.Column(1)

If you want to display the price, you would do the same with a control source of:

=PartNumber.Column(2)

You are entering new records for every part in the workorder/estimate. What is the problem with "excessive lines"? Is this on a report or what?

Duane
Hook'D on Access
MS Access MVP

 

[mirgss]

Okay, I got rid of the code per your suggestion and added the proper control sources, but I still have the same problem. For example, if I select "Bosch" as the mfgr for the first part, I get options that are manufactured by Bosch. However, in the next record, if I select GE, it still gives me a list of parts manufactured by Bosch.

Every time I go to a new row, the work order number disappears. I'm assuming this is because the system thinks I am entering a new work order. I want it to be the same work order.

The finished product is a report that goes to the customer. I originally had a form with the 8 lines for part numbers; I figured that there was a way to do it so I only had as many lines as I had parts that I was ordering.

 

[dhookom]

You could change the SQL of the combo box to still show all the parts but the current mfg will be at the top:

SELECT tblParts.PartNumber, tblParts.PartDescription, tblParts.Price
FROM tblParts
ORDER BY tblParts.Manufacturer = [Forms].[frmEstimateDetail].[Manufacturer], tblParts.PartNumber;

You would need to add code to requery the combo box in the After Update of the manufacturer control as well as in the On Current event of the form.

Can you explain your current table structures and the Link Master Child properties of your form and subform?

Duane
Hook'D on Access
MS Access MVP