Add multiple records to subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

There is are 2 tables "customerdata" and 'partsdata". Form has customer info
text boxes and type of repair is selected from a combo box. Subform shows
parts used in repair and their cost in text boxes. 4-8 parts are used in any
repair type, but same parts are always used in any one repair type. Is there
a way to enter multiple records in the subform showing all the parts used for
the repair type selectd if the parts and their cost have been selected for a
particular repair type using VBA to set up a query of the "partsdata" table
when a particular repair type is selected in the form?
 
Jim,

Yes, this can be done. I assume that your Partsdata table is like a
"master list" of all parts, with a identification code or number, and
the name of the part, and the price, etc. Is this right? And therefore
there must also be a third table that the subform relates to, which
records the parts used in the repair for each customer. Am I right? I
would also assume that any given part can be part of the standard list
of parts for more than one repair type. Is this right? If so, you will
need to set up another separate table to define the standard parts list.
This will need at least 2 fields:
PartID (or whatever is the primary key field of your Partsdata table)
RepairType
and perhaps also...
Quantity

Then, you can make a query which will include this new table and also
the Partsdata table, with a criteria of the RepairType field set to the
Type combobox on your form. This query should then return the standard
parts used for the selected repair type. Add a column to the query to
return the CustomerID of the customer entered on the main form. Make
this into an Append Query to add these PartIDs and this CustomerID to
the CustomerParts table, which the subform relates to. Then you can use
code on the AfterUpdate event of the RepairType combobox to run this
append query, plus a Requery of the subform, and I think this will do
what you want.
 
Steve,
Your assumptions were correct and I will try your solution to the problem.
Thank you for your time,
Jim
 
Back
Top