Access VBA help needed please...

G

Guest

I have a form that has a number of controls on it plus a button that, when
pressed, needs to save the data selected by the user on the form to the
database. I have two use to different insert statements since I have to
insert a parent record before I insert a child. After the first insert is
successful I get a message box that has the following error message: The
expression On Click you entered as the event property setting produced the
following error: Member already exists in an object module from which this
object module derives. Any ideas? My code is as follows:
Code: 
Option Compare Database
Dim strNumber As String, strName As String, strDescription As String,
strImageName As String
Dim intCat_1 As Integer, intCat_2 As Integer, intCat_3 As Integer, intCat_4
As Integer, intCat_5 As Integer


Private Sub cmdAddRecord_Click()
Dim strSQL As String
Dim intAffected, intOriginal As Integer

strNumber = txtNum.Text
strName = txtName.Text
strDescription = txtDescription.Text
strImageName = txtImageName.Text

strSQL = "INSERT INTO tblProduct (strNumPK, strName, strDescription,
strImageName)" & _
"VALUES ('" & strNumber & "', '" & strName & "', '" & strDescription
& "', '" & strImageName & "')"


intCat_1 = cboCat_1.Column(0)(cboCat_1.ListIndex)
intCat_2 = cboCat_2.Column(0)(cboCat_2.ListIndex)
intCat_3 = cboCat_3.Column(0)(cboCat_3.ListIndex)
intCat_4 = cboCat_4.Column(0)(cboCat_4.ListIndex)
intCat_5 = cboCat_5.Column(0)(cboCat_5.ListIndex)
intCat_6 = cboCat_6.Column(0)(cboCat_6.ListIndex)

strSQL = "INSERT INTO tblProductJoin (intCategory_1FK, intCategory_2FK,
intCategory_3FK, intCategory_4FK, intCategory_5FK, intCategory_6FK)" & _
"VALUES (" & intCat_1 & "," & intCat_2 & "," & intCat_3 & "," &
intCat_4 & "," & intCat_5 & "," & intCat_6 & ", '" & strNumber & "')"


End Sub
 
G

Guest

You have, I think, an ambiguous naming problem. You have field names in your
table that are the same as the variables you are passing as values. Try
putting brackets [] around the field names.
 
G

Guest

Thanks for the reply, but I'm still having the same problem... :-(

Klatuu said:
You have, I think, an ambiguous naming problem. You have field names in your
table that are the same as the variables you are passing as values. Try
putting brackets [] around the field names.

Jim said:
I have a form that has a number of controls on it plus a button that, when
pressed, needs to save the data selected by the user on the form to the
database. I have two use to different insert statements since I have to
insert a parent record before I insert a child. After the first insert is
successful I get a message box that has the following error message: The
expression On Click you entered as the event property setting produced the
following error: Member already exists in an object module from which this
object module derives. Any ideas? My code is as follows:
Code: 
Option Compare Database
Dim strNumber As String, strName As String, strDescription As String,
strImageName As String
Dim intCat_1 As Integer, intCat_2 As Integer, intCat_3 As Integer, intCat_4
As Integer, intCat_5 As Integer


Private Sub cmdAddRecord_Click()
Dim strSQL As String
Dim intAffected, intOriginal As Integer

strNumber = txtNum.Text
strName = txtName.Text
strDescription = txtDescription.Text
strImageName = txtImageName.Text

strSQL = "INSERT INTO tblProduct (strNumPK, strName, strDescription,
strImageName)" & _
"VALUES ('" & strNumber & "', '" & strName & "', '" & strDescription
& "', '" & strImageName & "')"


intCat_1 = cboCat_1.Column(0)(cboCat_1.ListIndex)
intCat_2 = cboCat_2.Column(0)(cboCat_2.ListIndex)
intCat_3 = cboCat_3.Column(0)(cboCat_3.ListIndex)
intCat_4 = cboCat_4.Column(0)(cboCat_4.ListIndex)
intCat_5 = cboCat_5.Column(0)(cboCat_5.ListIndex)
intCat_6 = cboCat_6.Column(0)(cboCat_6.ListIndex)

strSQL = "INSERT INTO tblProductJoin (intCategory_1FK, intCategory_2FK,
intCategory_3FK, intCategory_4FK, intCategory_5FK, intCategory_6FK)" & _
"VALUES (" & intCat_1 & "," & intCat_2 & "," & intCat_3 & "," &
intCat_4 & "," & intCat_5 & "," & intCat_6 & ", '" & strNumber & "')"


End Sub
Click to expand...
Click to expand...
 
G

Guest

Thanks for the reply, but I'm still having the same problem... :-(

Klatuu said:
You have, I think, an ambiguous naming problem. You have field names in your
table that are the same as the variables you are passing as values. Try
putting brackets [] around the field names.

Jim said:
I have a form that has a number of controls on it plus a button that, when
pressed, needs to save the data selected by the user on the form to the
database. I have two use to different insert statements since I have to
insert a parent record before I insert a child. After the first insert is
successful I get a message box that has the following error message: The
expression On Click you entered as the event property setting produced the
following error: Member already exists in an object module from which this
object module derives. Any ideas? My code is as follows:
Code: 
Option Compare Database
Dim strNumber As String, strName As String, strDescription As String,
strImageName As String
Dim intCat_1 As Integer, intCat_2 As Integer, intCat_3 As Integer, intCat_4
As Integer, intCat_5 As Integer


Private Sub cmdAddRecord_Click()
Dim strSQL As String
Dim intAffected, intOriginal As Integer

strNumber = txtNum.Text
strName = txtName.Text
strDescription = txtDescription.Text
strImageName = txtImageName.Text

strSQL = "INSERT INTO tblProduct (strNumPK, strName, strDescription,
strImageName)" & _
"VALUES ('" & strNumber & "', '" & strName & "', '" & strDescription
& "', '" & strImageName & "')"


intCat_1 = cboCat_1.Column(0)(cboCat_1.ListIndex)
intCat_2 = cboCat_2.Column(0)(cboCat_2.ListIndex)
intCat_3 = cboCat_3.Column(0)(cboCat_3.ListIndex)
intCat_4 = cboCat_4.Column(0)(cboCat_4.ListIndex)
intCat_5 = cboCat_5.Column(0)(cboCat_5.ListIndex)
intCat_6 = cboCat_6.Column(0)(cboCat_6.ListIndex)

strSQL = "INSERT INTO tblProductJoin (intCategory_1FK, intCategory_2FK,
intCategory_3FK, intCategory_4FK, intCategory_5FK, intCategory_6FK)" & _
"VALUES (" & intCat_1 & "," & intCat_2 & "," & intCat_3 & "," &
intCat_4 & "," & intCat_5 & "," & intCat_6 & ", '" & strNumber & "')"


End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I do not know anything about DLookup, crap.

Jim said:
Thanks for the reply, but I'm still having the same problem... :-(

Klatuu said:
You have, I think, an ambiguous naming problem. You have field names in your
table that are the same as the variables you are passing as values. Try
putting brackets [] around the field names.

Jim said:
I have a form that has a number of controls on it plus a button that, when
pressed, needs to save the data selected by the user on the form to the
database. I have two use to different insert statements since I have to
insert a parent record before I insert a child. After the first insert is
successful I get a message box that has the following error message: The
expression On Click you entered as the event property setting produced the
following error: Member already exists in an object module from which this
object module derives. Any ideas? My code is as follows:
Code: 
Option Compare Database
Dim strNumber As String, strName As String, strDescription As String,
strImageName As String
Dim intCat_1 As Integer, intCat_2 As Integer, intCat_3 As Integer, intCat_4
As Integer, intCat_5 As Integer


Private Sub cmdAddRecord_Click()
Dim strSQL As String
Dim intAffected, intOriginal As Integer

strNumber = txtNum.Text
strName = txtName.Text
strDescription = txtDescription.Text
strImageName = txtImageName.Text

strSQL = "INSERT INTO tblProduct (strNumPK, strName, strDescription,
strImageName)" & _
"VALUES ('" & strNumber & "', '" & strName & "', '" & strDescription
& "', '" & strImageName & "')"


intCat_1 = cboCat_1.Column(0)(cboCat_1.ListIndex)
intCat_2 = cboCat_2.Column(0)(cboCat_2.ListIndex)
intCat_3 = cboCat_3.Column(0)(cboCat_3.ListIndex)
intCat_4 = cboCat_4.Column(0)(cboCat_4.ListIndex)
intCat_5 = cboCat_5.Column(0)(cboCat_5.ListIndex)
intCat_6 = cboCat_6.Column(0)(cboCat_6.ListIndex)

strSQL = "INSERT INTO tblProductJoin (intCategory_1FK, intCategory_2FK,
intCategory_3FK, intCategory_4FK, intCategory_5FK, intCategory_6FK)" & _
"VALUES (" & intCat_1 & "," & intCat_2 & "," & intCat_3 & "," &
intCat_4 & "," & intCat_5 & "," & intCat_6 & ", '" & strNumber & "')"


End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Okay, here is the problem (I am pretty sure). You said after the first
INSERT, it gets crabby with you, right? So what is happening,is you are
trying to insert a
record with primary key values that already exist. I can't be absolutely
sure, because all the code is not there, for example I don't see where you
execute the SQL statement.

You can get the details on the DLookup in Access Help. But basically, this
is looking to see if the primary key you have is already in the table. If it
is not found, Null is returned, so you need to do the INSERT. If a value is
returned, then it is already there, and you can't do an insert without an
error.

If IsNull(DLookup("[strNumPK]", "tblProduct", "[strNumPK] = '" & strNumber &
"'") _
& Then
strSQL = "INSERT INTO tblProduct (strNumPK, strName, strDescription,
trImageName)" & _
"VALUES ('" & strNumber & "', '" & strName & "', '" &
strDescription & "', '"
& strImageName & "')"
CurrentDB.Execute(strSQL)
End If

Jim said:
I do not know anything about DLookup, crap.

Jim said:
Thanks for the reply, but I'm still having the same problem... :-(

Klatuu said:
You have, I think, an ambiguous naming problem. You have field names in your
table that are the same as the variables you are passing as values. Try
putting brackets [] around the field names.

:

I have a form that has a number of controls on it plus a button that, when
pressed, needs to save the data selected by the user on the form to the
database. I have two use to different insert statements since I have to
insert a parent record before I insert a child. After the first insert is
successful I get a message box that has the following error message: The
expression On Click you entered as the event property setting produced the
following error: Member already exists in an object module from which this
object module derives. Any ideas? My code is as follows:
Code: 
Option Compare Database
Dim strNumber As String, strName As String, strDescription As String,
strImageName As String
Dim intCat_1 As Integer, intCat_2 As Integer, intCat_3 As Integer, intCat_4
As Integer, intCat_5 As Integer


Private Sub cmdAddRecord_Click()
Dim strSQL As String
Dim intAffected, intOriginal As Integer

strNumber = txtNum.Text
strName = txtName.Text
strDescription = txtDescription.Text
strImageName = txtImageName.Text

strSQL = "INSERT INTO tblProduct (strNumPK, strName, strDescription,
strImageName)" & _
"VALUES ('" & strNumber & "', '" & strName & "', '" & strDescription
& "', '" & strImageName & "')"


intCat_1 = cboCat_1.Column(0)(cboCat_1.ListIndex)
intCat_2 = cboCat_2.Column(0)(cboCat_2.ListIndex)
intCat_3 = cboCat_3.Column(0)(cboCat_3.ListIndex)
intCat_4 = cboCat_4.Column(0)(cboCat_4.ListIndex)
intCat_5 = cboCat_5.Column(0)(cboCat_5.ListIndex)
intCat_6 = cboCat_6.Column(0)(cboCat_6.ListIndex)

strSQL = "INSERT INTO tblProductJoin (intCategory_1FK, intCategory_2FK,
intCategory_3FK, intCategory_4FK, intCategory_5FK, intCategory_6FK)" & _
"VALUES (" & intCat_1 & "," & intCat_2 & "," & intCat_3 & "," &
intCat_4 & "," & intCat_5 & "," & intCat_6 & ", '" & strNumber & "')"


End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import Complex info from Excel via VBA 6
HELP NEEDED WITH REPORT 1
Compile Error ?? 2
Help fixing my NotInList code for a combo box 3
Type mismatch error - new to VBA help 2
Access access vba multiline textbox 2
Excel Help needed with Excel 2007 Macro to send contents of a column to the previous column as comments. 0
Error Adding to Spreadsheet Please help!!! Complex ? 1

Top