Karl,
Thank you for listing the tables. While waiting for a reply, I tried using
a command button to insert the data. I think I'm close as it is pulling the
correct data, but I'm having trouble with a syntax error. When I run the
following code from a command button on the main form:
Private Sub Command24_Click()
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, Module,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT DISTINCT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.Module, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.Module = tSparePartsTemplate.Module) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE (((tSparePartsMainForm.QuoteNumber)= '" & (Me.QuoteNumber) & "' AND "
& "((tSparePartsTemplate.Model)= '" & (Me.Model) & "' AND " &
"((tSparePartsTemplate.Module)= '" & (Forms!fsparepartsmainform!Module) & "'
));"
db.Execute strSQL, dbFailOnError
db.Close
Set db = Nothing
End Sub
it throws the Run-time error 3075
Syntax error in query expression
'(((tsparepartsmainform.quotenumber)= '25' AND
((tsparepartstemplate.model)='111' AND
((tsparepartstemplate.module)='a'));'
All three fields are populated with the information on the form, but I can't
determine what is causing the error. Can you please tell me what I'm missing?
Thanks for your continued help.
Phisaw
KARL DEWEY said:
How can I relate tTemplate to tsubform3 in a way that tsubform3 will
relate to tmainform?
First you have to relate tables to each other.
I think your tables need to be like this --
tblQuotes
PK - QuoteID
Customer
QuoteDate
Multiplier
FK - EquipID
tblPartslist
PK - PartID
Manufacturer
Partnumber
Item -- what is this?
Description
Listprice
tblEquipment
PK - EquipID
Manufacturer
Model
Module
Serial
tblEquipParts
PK - EqPartID
FK - EquipID
FK - PartID
QTY
A quote has equipment, equipment has parts, and parts are identified.
--
Build a little, test a little.
:
Karl,
I have my tables set up a little different than you suggested due to other
variables, but I can get the information to insert into tsubform3 using an
append query. I just can't get it to pull to the form - which I think is in
the linkage. Will you please tell me what I am missing based on the tables
and query below?
I have db of equipment listed as such and am trying to get standard parts
list for each module of each serial number based on model and module so users
won't have to type same parts every time. Then they will also need to add
parts specific to each serial number.
Model Module Serial
111 A 123
111 B 123
222 A 456
222 B 456
111 A 789
111 B 789
tmainform
PK - quote#
manufacturer
model
module
serial#
customer
date
tsubform1
PK - record#
quote#
multiplier
tsubform3
PK - record#
quote#
model
module
serial#
partIDnumber
qty
class1
class2
class3
delwks
listprice
ttemplate (holds records for parts specific to each model and module)
PK - record#
manufacturer
model
module
partID#
qty
class1
class2
class3
delwks
tpartslist
PK - partID#
manufacturer
partnumber
item
description
listprice
The appending query that will put parts to tsubform3 is as follows:
INSERT INTO tsparepartssubform3 ( Model, [Module], PartIDNumber, Qty,
Class1, Class2, Class3, DelWks )
SELECT DISTINCT tSparePartsTemplate.Model, tSparePartsTemplate.Module,
tSparePartsTemplate.PartIDNumber, tSparePartsTemplate.Qty,
tSparePartsTemplate.Class1, tSparePartsTemplate.Class2,
tSparePartsTemplate.Class3, tSparePartsTemplate.DelWks
FROM tSparePartsTemplate
WHERE (((tSparePartsTemplate.Model)=[forms]![fsparepartsmainform]![model])
AND ((tSparePartsTemplate.Module)=[forms]![fsparepartsmainform]![module]));
How can I relate tTemplate to tsubform3 in a way that tsubform3 will relate
to tmainform? I've worked with Access for a while, but having a standard set
of records to avoid typing same info for every model is really throwing a
wrench in my plans. I hope this isn't too confusing.
I appreciate any help/suggestions you may have.
Phisaw
:
You will need another table for the quotes like this --
tblQuotes --
QuoteID - autonumber - primary key
ModelID - number - integer - foreign key
QuoteDate - DateTime
Amount - Currency
Contact -
etc. -
Put another subform in the main form with Master/Child link set using
ModelID.
--
Build a little, test a little.
:
Karl,
Will this also tie the list of parts to the quote number for the serial
number of each piece of equipment? I'm sorry but I failed to mention this is
not a one-time list, but user wants a history of quotes for each serial
number to keep track of number of times and dollar amounts quoted for the
parts tied to each serial number. Does this change anything?
Thank you for taking the time to respond.
Phisaw
:
Create a one-to-many relationship between Model_Serial table and Parts_List
using AutoNumber (ModelID) as primary key field in Model_Serial table and
Number-Integer as foreign key in Parts_List. Create an unique index of Model
plus Serial. When creating the relationship select Referential Integerity
and Cascade Update.
Your main form to have text box [forms]![myform]![ModelID] hidden (Visible
property set to No.). Have a button to add 'Full Parts List' and run query
below.
INSERT INTO Parts_List ( PartNum, etc, ModelID )
SELECT DISTINCT Parts_List.PartNum, PartNum.etc, [forms]![myform]![ModelID]
AS ModelID
FROM Parts_List;
Then you need to delete parts not required.
--
Build a little, test a little.
:
I have need of a database that will hold information about equipment based on
model and serial number along with parts list for each. Each model has a
list of parts specific to that model and can have additional parts specific
to the serial number for each model. The model parts list (templatelist) may
also need to be changed once pulled into form due to specifics of serial
number for each model. So while I have a template list, it is not a final
list only a suggested parts list.
The problem I'm running into is how to get the standard list (templatelist)
into a table which I can then use as a subform relating it to the main form
where the user can then add additional parts as needed. Should I use some
sort of append query which I am not familiar with at all or if possible,
should I use a list box that will filter parts based on the model number on
the main form and select all. I've read thru many posts and can now say I'm
very confused on getting anything to work properly.
If anyone has any suggestions, I would greatly appreciate it.
Thanks
Phisaw
