insert into

S

short

I have a database that the users want to have a seperate table that holds the
primary keys of two other tables. So I create a table with both tables
primary keys in it:
Software_Node
NodeID
SoftwareID
(made both pk both numbers)
which is connected to the Software and Node tables by their pk's.

Now on the node form I have it where the user selects the software, and then
enters the node and then pressed a button to add the node which is supposed
to put in the PK's of the tables. So this is my code: (I posted this a few
days ago, but am now getting an error)

Private Sub cmdNode_Click()
Dim NodeID As Integer
Dim SoftwareID As Integer
NodeID = ID
SoftwareID = "SELECT Software.[ID] FROM Software where Software.[Name] = '"
+ cmbSoftware.Value + " AND ServerID = '" + ServerID = "';"

Dim sql As String

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES ("" &
NodeID & "", "" & SoftwareID & "")"

MsgBox ("You have added a node")
DoCmd.RunSQL sql

End Sub

I keep getting an error message that MS Access can't append all the records
in the appened query.. set 1 field(s) to Null due to conversion failure

anyone have any idea what I did wrong?
 
K

Ken Snell \(MVP\)

SoftwareID is a string variable, which is being put into the SoftwareID
field, so I assume that the SoftwareID field is a text field. Therefore, you
must delimit the string text with " characters so that the insert query will
recognize the data as text:

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES ("" &
NodeID & "", """" & SoftwareID & """")"
 
S

short

by text field does that include numbers?

Ken Snell (MVP) said:
SoftwareID is a string variable, which is being put into the SoftwareID
field, so I assume that the SoftwareID field is a text field. Therefore, you
must delimit the string text with " characters so that the insert query will
recognize the data as text:

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES ("" &
NodeID & "", """" & SoftwareID & """")"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



short said:
I have a database that the users want to have a seperate table that holds
the
primary keys of two other tables. So I create a table with both tables
primary keys in it:
Software_Node
NodeID
SoftwareID
(made both pk both numbers)
which is connected to the Software and Node tables by their pk's.

Now on the node form I have it where the user selects the software, and
then
enters the node and then pressed a button to add the node which is
supposed
to put in the PK's of the tables. So this is my code: (I posted this a few
days ago, but am now getting an error)

Private Sub cmdNode_Click()
Dim NodeID As Integer
Dim SoftwareID As Integer
NodeID = ID
SoftwareID = "SELECT Software.[ID] FROM Software where Software.[Name] =
'"
+ cmbSoftware.Value + " AND ServerID = '" + ServerID = "';"

Dim sql As String

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES ("" &
NodeID & "", "" & SoftwareID & "")"

MsgBox ("You have added a node")
DoCmd.RunSQL sql

End Sub

I keep getting an error message that MS Access can't append all the
records
in the appened query.. set 1 field(s) to Null due to conversion failure

anyone have any idea what I did wrong?
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

A text field is one where the Data Type property of the field in the table
is Text or Memo. A Text field can contain numeric values, but they are not
numbers, they are characters that look like numbers.

I see that I misread your original post. SoftwareID is dim'd as an Integer,
not a Text -- my mistake.

Are you sure that ID field is not NULL when your code runs?

What are the Data Type properties of the two fields in the Node_Software
table?

Also, the error message suggests that it did add the new record, but that
one field was given a NULL value. Which field is being assigned NULL by the
query when it runs and gives you the error? Look at the table after you run
the query and see.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


short said:
by text field does that include numbers?

Ken Snell (MVP) said:
SoftwareID is a string variable, which is being put into the SoftwareID
field, so I assume that the SoftwareID field is a text field. Therefore,
you
must delimit the string text with " characters so that the insert query
will
recognize the data as text:

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES ("" &
NodeID & "", """" & SoftwareID & """")"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



short said:
I have a database that the users want to have a seperate table that
holds
the
primary keys of two other tables. So I create a table with both tables
primary keys in it:
Software_Node
NodeID
SoftwareID
(made both pk both numbers)
which is connected to the Software and Node tables by their pk's.

Now on the node form I have it where the user selects the software, and
then
enters the node and then pressed a button to add the node which is
supposed
to put in the PK's of the tables. So this is my code: (I posted this a
few
days ago, but am now getting an error)

Private Sub cmdNode_Click()
Dim NodeID As Integer
Dim SoftwareID As Integer
NodeID = ID
SoftwareID = "SELECT Software.[ID] FROM Software where Software.[Name]
=
'"
+ cmbSoftware.Value + " AND ServerID = '" + ServerID = "';"

Dim sql As String

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES ("" &
NodeID & "", "" & SoftwareID & "")"

MsgBox ("You have added a node")
DoCmd.RunSQL sql

End Sub

I keep getting an error message that MS Access can't append all the
records
in the appened query.. set 1 field(s) to Null due to conversion failure

anyone have any idea what I did wrong?
Click to expand...
Click to expand...
Click to expand...
 
S

short

Both fields in the Node_software table are number fields. When I run the
query and look in the Node_software table, I get the nodeId number but no
softwareID

Ken Snell (MVP) said:
A text field is one where the Data Type property of the field in the table
is Text or Memo. A Text field can contain numeric values, but they are not
numbers, they are characters that look like numbers.

I see that I misread your original post. SoftwareID is dim'd as an Integer,
not a Text -- my mistake.

Are you sure that ID field is not NULL when your code runs?

What are the Data Type properties of the two fields in the Node_Software
table?

Also, the error message suggests that it did add the new record, but that
one field was given a NULL value. Which field is being assigned NULL by the
query when it runs and gives you the error? Look at the table after you run
the query and see.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


short said:
by text field does that include numbers?

Ken Snell (MVP) said:
SoftwareID is a string variable, which is being put into the SoftwareID
field, so I assume that the SoftwareID field is a text field. Therefore,
you
must delimit the string text with " characters so that the insert query
will
recognize the data as text:

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES ("" &
NodeID & "", """" & SoftwareID & """")"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a database that the users want to have a seperate table that
holds
the
primary keys of two other tables. So I create a table with both tables
primary keys in it:
Software_Node
NodeID
SoftwareID
(made both pk both numbers)
which is connected to the Software and Node tables by their pk's.

Now on the node form I have it where the user selects the software, and
then
enters the node and then pressed a button to add the node which is
supposed
to put in the PK's of the tables. So this is my code: (I posted this a
few
days ago, but am now getting an error)

Private Sub cmdNode_Click()
Dim NodeID As Integer
Dim SoftwareID As Integer
NodeID = ID
SoftwareID = "SELECT Software.[ID] FROM Software where Software.[Name]
=
'"
+ cmbSoftware.Value + " AND ServerID = '" + ServerID = "';"

Dim sql As String

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES ("" &
NodeID & "", "" & SoftwareID & "")"

MsgBox ("You have added a node")
DoCmd.RunSQL sql

End Sub

I keep getting an error message that MS Access can't append all the
records
in the appened query.. set 1 field(s) to Null due to conversion failure

anyone have any idea what I did wrong?
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

OK, I see the problem. You cannot set the SoftwareID variable to a SQL
statement string when the SoftwareID variable is Dim'd as an Integer.

I assume that you want to read the value of the SoftwareID field from the
Software table and use that value as the value of the SoftwareID variable;
is that correct? If yes, replace this line of code

SoftwareID = "SELECT Software.[ID] FROM Software where Software.[Name] = '"
+ cmbSoftware.Value + " AND ServerID = '" + ServerID = "';"


with these lines of code:

SoftwareID = DLookup("ID", "Software", "[Name] = '" & _
cmbSoftware.Value & "' AND [ServerID] = '" & _
ServerID & "'")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


short said:
Both fields in the Node_software table are number fields. When I run the
query and look in the Node_software table, I get the nodeId number but no
softwareID

Ken Snell (MVP) said:
A text field is one where the Data Type property of the field in the
table
is Text or Memo. A Text field can contain numeric values, but they are
not
numbers, they are characters that look like numbers.

I see that I misread your original post. SoftwareID is dim'd as an
Integer,
not a Text -- my mistake.

Are you sure that ID field is not NULL when your code runs?

What are the Data Type properties of the two fields in the Node_Software
table?

Also, the error message suggests that it did add the new record, but that
one field was given a NULL value. Which field is being assigned NULL by
the
query when it runs and gives you the error? Look at the table after you
run
the query and see.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


short said:
by text field does that include numbers?

:

SoftwareID is a string variable, which is being put into the
SoftwareID
field, so I assume that the SoftwareID field is a text field.
Therefore,
you
must delimit the string text with " characters so that the insert
query
will
recognize the data as text:

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES ("" &
NodeID & "", """" & SoftwareID & """")"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a database that the users want to have a seperate table that
holds
the
primary keys of two other tables. So I create a table with both
tables
primary keys in it:
Software_Node
NodeID
SoftwareID
(made both pk both numbers)
which is connected to the Software and Node tables by their pk's.

Now on the node form I have it where the user selects the software,
and
then
enters the node and then pressed a button to add the node which is
supposed
to put in the PK's of the tables. So this is my code: (I posted this
a
few
days ago, but am now getting an error)

Private Sub cmdNode_Click()
Dim NodeID As Integer
Dim SoftwareID As Integer
NodeID = ID
SoftwareID = "SELECT Software.[ID] FROM Software where
Software.[Name]
=
'"
+ cmbSoftware.Value + " AND ServerID = '" + ServerID = "';"

Dim sql As String

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES (""
&
NodeID & "", "" & SoftwareID & "")"

MsgBox ("You have added a node")
DoCmd.RunSQL sql

End Sub

I keep getting an error message that MS Access can't append all the
records
in the appened query.. set 1 field(s) to Null due to conversion
failure

anyone have any idea what I did wrong?
Click to expand...
Click to expand...
Click to expand...
 
S

short

Last question, I have a severId that I'm storing as well do I need to use the
DLookup for that as well?

ServerID = "SELECT Server.[ID] FROM Server where Server.[Name] = '" +
cmbServer.Value + "';"

Thank you very much for you help!


Ken Snell (MVP) said:
OK, I see the problem. You cannot set the SoftwareID variable to a SQL
statement string when the SoftwareID variable is Dim'd as an Integer.

I assume that you want to read the value of the SoftwareID field from the
Software table and use that value as the value of the SoftwareID variable;
is that correct? If yes, replace this line of code

SoftwareID = "SELECT Software.[ID] FROM Software where Software.[Name] = '"
+ cmbSoftware.Value + " AND ServerID = '" + ServerID = "';"


with these lines of code:

SoftwareID = DLookup("ID", "Software", "[Name] = '" & _
cmbSoftware.Value & "' AND [ServerID] = '" & _
ServerID & "'")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


short said:
Both fields in the Node_software table are number fields. When I run the
query and look in the Node_software table, I get the nodeId number but no
softwareID

Ken Snell (MVP) said:
A text field is one where the Data Type property of the field in the
table
is Text or Memo. A Text field can contain numeric values, but they are
not
numbers, they are characters that look like numbers.

I see that I misread your original post. SoftwareID is dim'd as an
Integer,
not a Text -- my mistake.

Are you sure that ID field is not NULL when your code runs?

What are the Data Type properties of the two fields in the Node_Software
table?

Also, the error message suggests that it did add the new record, but that
one field was given a NULL value. Which field is being assigned NULL by
the
query when it runs and gives you the error? Look at the table after you
run
the query and see.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


by text field does that include numbers?

:

SoftwareID is a string variable, which is being put into the
SoftwareID
field, so I assume that the SoftwareID field is a text field.
Therefore,
you
must delimit the string text with " characters so that the insert
query
will
recognize the data as text:

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES ("" &
NodeID & "", """" & SoftwareID & """")"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a database that the users want to have a seperate table that
holds
the
primary keys of two other tables. So I create a table with both
tables
primary keys in it:
Software_Node
NodeID
SoftwareID
(made both pk both numbers)
which is connected to the Software and Node tables by their pk's.

Now on the node form I have it where the user selects the software,
and
then
enters the node and then pressed a button to add the node which is
supposed
to put in the PK's of the tables. So this is my code: (I posted this
a
few
days ago, but am now getting an error)

Private Sub cmdNode_Click()
Dim NodeID As Integer
Dim SoftwareID As Integer
NodeID = ID
SoftwareID = "SELECT Software.[ID] FROM Software where
Software.[Name]
=
'"
+ cmbSoftware.Value + " AND ServerID = '" + ServerID = "';"

Dim sql As String

sql = "INSERT INTO Node_Software([NodeID], [SoftwareID]) VALUES (""
&
NodeID & "", "" & SoftwareID & "")"

MsgBox ("You have added a node")
DoCmd.RunSQL sql

End Sub

I keep getting an error message that MS Access can't append all the
records
in the appened query.. set 1 field(s) to Null due to conversion
failure

anyone have any idea what I did wrong?
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

help with insert into statement 2
Many-to-many on form 2
How to automatically Insert Incrementing Values into my Database 12
DLookup Syntax Question 4
DLookup in a SubForm 2
touring database design ... 4
qry for Excel report with weekly data in a vertical column 1
cannot define circular relation. pretty sure it's a bug. 2

Top