If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append Records??
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 |
#2
|
|||
|
|||
Append Records??
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. "PHisaw" wrote: 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 |
#3
|
|||
|
|||
Append Records??
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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 |
#4
|
|||
|
|||
Append Records??
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. "PHisaw" wrote: 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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 |
#5
|
|||
|
|||
Append Records??
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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 |
#6
|
|||
|
|||
Append Records??
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. "PHisaw" wrote: 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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 |
#7
|
|||
|
|||
Append Records??
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" wrote: 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. "PHisaw" wrote: 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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 |
#8
|
|||
|
|||
Append Records??
I do not know VBA but is either tsparepartsmainform.quotenumber or
tsparepartstemplate.model) a number instead of text? You have their values in quotes as for text. -- Build a little, test a little. "PHisaw" wrote: 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" wrote: 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. "PHisaw" wrote: 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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 |
#9
|
|||
|
|||
Append Records??
Karl,
Quotenumber is a number field - only model and module are text fields. I've read so may posts and changed code for text and number quotes and nothing works. I know you said you don't know VBA, but do you have any other suggestions? Thanks, Phisaw "KARL DEWEY" wrote: I do not know VBA but is either tsparepartsmainform.quotenumber or tsparepartstemplate.model) a number instead of text? You have their values in quotes as for text. -- Build a little, test a little. "PHisaw" wrote: 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" wrote: 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. "PHisaw" wrote: 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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 "KARL DEWEY" wrote: 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. "PHisaw" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|