D
DawnTreader
Hello all
i have 2 tables that are in a one to one relationship. the main table is the
product table with a ProductID and a ProductTypeID. these 2 fields are the
primary key on the tblProductList. i have another set of tables that stores
the configuration of each type of product. the other table that i am trying
to update through code at the same time as the tblProductList is the
subdatatblCompressor. the ProductID in the tblProductList is an autonumber
field. the ProductTypeID is a text fields that can be a few different values,
"Compressor" is one such value. when a compressor is added to the database it
puts one record in the tblProductList and the related configuration is stored
in the subdatatblCompressor. the autonumber value can only be used once in
relationship to the related record in the subdatatblCompressor.
i hope all that makes sense.
when we add new products we use a form that allows us to do a lot of other
things other than just adding a new product, it also allows us to import
related information to other tables and fields. this new product "building"
form is unbound and therefore it uses code to manage the additions and
"linking" of the other information.
this code was working fine. i have been working on it for over 3 hours
trying to figure out why it has stopped working. at some point in the last
couple of weeks i made a change to allow for some additional new features to
the form and in doing so i broke something.
i keep getting an error message of duplicate key values being created. i
checked out the line that the debugger points to and have played with it as
much as i can.
here is the relevant area of code:
'gather variables and insert into the product table.
If (error = False) Then
If Me.cboProductType = "Compressor" Then
sql = "INSERT INTO tblProductList (ProductTypeID,
SerialNumber, CustomerOrder, SiteID) VALUES ('" & Me.cboProductType & "', '"
& Me.SerialNumber & "', '" & Me.CustomerOrder & "', " & site_id & ")"
MsgBox sql
DoCmd.RunSQL sql
Else
sql = "INSERT INTO tblProductList (ProductTypeID,
SerialNumber, CustomerOrder, SiteID) VALUES ('" & Me.cboProductType & "', '"
& Me.SerialNumber & "', '" & Me.CustomerOrder & "', " & site_id & ")"
MsgBox sql
DoCmd.RunSQL sql
End If
' find the last productid that was created and store it into the
variable product_id
Set cn = CurrentProject.Connection
Set rst_newproduct = cn.Execute("select MAX(ProductID) from
tblProductList")
product_id = CInt(rst_newproduct(0)) ' convert the number into
an integer
MsgBox "ProductID = " & product_id
rst_newproduct.Close
Set rst_newproduct = Nothing
cn.Close
'if the user entered information into the CBA #, Electrical
Panel WO, and the PLC Panel WO fields insert them into the compressor
information table
newCompressorCheck = Me.CBANumber & Me.ElecPanelWO & Me.PLCPanelWO
MsgBox newCompressorCheck, vbOKOnly, newCompressorCheck
If (Not IsNull(newCompressorCheck)) Then
'find out the subdatatblCompressor highest productid in
subdatatblCompressor just to know if it will be duplicated
Set cn = CurrentProject.Connection
Set rst_newproduct = cn.Execute("select MAX(ProductID) from
subdatatblCompressor")
subdataproduct_id = CInt(rst_newproduct(0)) ' convert the
number into an integer
MsgBox "ProductID = " & subdataproduct_id
rst_newproduct.Close
Set rst_newproduct = Nothing
cn.Close
sql = "INSERT INTO subdatatblCompressor (ProductID,
CBBAEngMaster, ElectricalPanelWO, SkidPanelWO) VALUES ('" & product_id & "',
'" & Me.CBANumber & "', '" & Me.ElecPanelWO & "', '" & Me.PLCPanelWO & "')"
MsgBox sql
DoCmd.RunSQL sql
End If
is there anything immeadiately obvious that is wrong with this?
i thought maybe the quotes around my SQL statements might be wrong, but
after playing with them for a while i havent been able to make a change in
the error i am getting.
if there is any other information i should post let me know. thanks in
advance for any help with this.
i have 2 tables that are in a one to one relationship. the main table is the
product table with a ProductID and a ProductTypeID. these 2 fields are the
primary key on the tblProductList. i have another set of tables that stores
the configuration of each type of product. the other table that i am trying
to update through code at the same time as the tblProductList is the
subdatatblCompressor. the ProductID in the tblProductList is an autonumber
field. the ProductTypeID is a text fields that can be a few different values,
"Compressor" is one such value. when a compressor is added to the database it
puts one record in the tblProductList and the related configuration is stored
in the subdatatblCompressor. the autonumber value can only be used once in
relationship to the related record in the subdatatblCompressor.
i hope all that makes sense.
when we add new products we use a form that allows us to do a lot of other
things other than just adding a new product, it also allows us to import
related information to other tables and fields. this new product "building"
form is unbound and therefore it uses code to manage the additions and
"linking" of the other information.
this code was working fine. i have been working on it for over 3 hours
trying to figure out why it has stopped working. at some point in the last
couple of weeks i made a change to allow for some additional new features to
the form and in doing so i broke something.
i keep getting an error message of duplicate key values being created. i
checked out the line that the debugger points to and have played with it as
much as i can.
here is the relevant area of code:
'gather variables and insert into the product table.
If (error = False) Then
If Me.cboProductType = "Compressor" Then
sql = "INSERT INTO tblProductList (ProductTypeID,
SerialNumber, CustomerOrder, SiteID) VALUES ('" & Me.cboProductType & "', '"
& Me.SerialNumber & "', '" & Me.CustomerOrder & "', " & site_id & ")"
MsgBox sql
DoCmd.RunSQL sql
Else
sql = "INSERT INTO tblProductList (ProductTypeID,
SerialNumber, CustomerOrder, SiteID) VALUES ('" & Me.cboProductType & "', '"
& Me.SerialNumber & "', '" & Me.CustomerOrder & "', " & site_id & ")"
MsgBox sql
DoCmd.RunSQL sql
End If
' find the last productid that was created and store it into the
variable product_id
Set cn = CurrentProject.Connection
Set rst_newproduct = cn.Execute("select MAX(ProductID) from
tblProductList")
product_id = CInt(rst_newproduct(0)) ' convert the number into
an integer
MsgBox "ProductID = " & product_id
rst_newproduct.Close
Set rst_newproduct = Nothing
cn.Close
'if the user entered information into the CBA #, Electrical
Panel WO, and the PLC Panel WO fields insert them into the compressor
information table
newCompressorCheck = Me.CBANumber & Me.ElecPanelWO & Me.PLCPanelWO
MsgBox newCompressorCheck, vbOKOnly, newCompressorCheck
If (Not IsNull(newCompressorCheck)) Then
'find out the subdatatblCompressor highest productid in
subdatatblCompressor just to know if it will be duplicated
Set cn = CurrentProject.Connection
Set rst_newproduct = cn.Execute("select MAX(ProductID) from
subdatatblCompressor")
subdataproduct_id = CInt(rst_newproduct(0)) ' convert the
number into an integer
MsgBox "ProductID = " & subdataproduct_id
rst_newproduct.Close
Set rst_newproduct = Nothing
cn.Close
sql = "INSERT INTO subdatatblCompressor (ProductID,
CBBAEngMaster, ElectricalPanelWO, SkidPanelWO) VALUES ('" & product_id & "',
'" & Me.CBANumber & "', '" & Me.ElecPanelWO & "', '" & Me.PLCPanelWO & "')"
MsgBox sql
DoCmd.RunSQL sql
End If
is there anything immeadiately obvious that is wrong with this?
i thought maybe the quotes around my SQL statements might be wrong, but
after playing with them for a while i havent been able to make a change in
the error i am getting.
if there is any other information i should post let me know. thanks in
advance for any help with this.