Insert into

[Derek Brown]

Hi All

I am using INSERT INTO to add a record to an existing table. I want to
change only one value that is the linking field to the Master table so that
I dont get message record cannot be added without a reference to the main
table. I already have the nessassary number (an AccountCode) but how do i
stick it in?

strSQL = _
"INSERT INTO Item (" & "AccountCode) Values (NewID); &
ItemQuantity, )" & _
"SELECT " & "AccountCode, ItemQuantity, & _
"FROM Item " & _
"WHERE ItemNumber=" & Me.ItemNumber

Doesn't work though.

Any Ideas?

 

[Douglas J Steele]

You can't mix the VALUES and SELECT methods of INSERT INTO.

strSQL = _
"INSERT INTO Item (AccountCode, ItemQuantity) " & _
"SELECT AccountCode, ItemQuantity, " & _
"FROM Item " & _
"WHERE ItemNumber=" & Me.ItemNumber

or

strSQL = _
"INSERT INTO Item (AccountCode, ItemQuantity) " & _
"Values (" & NewID &", " & ItemQuantity & ")"

 

[Derek Brown]

Thanks Douglas

So how do I append a subform record to a table that has a linked table?.
Help!

 

[Douglas J Steele]

Sorry, your question is too vague.

How about explaining in words what you're trying to do?

 

[Derek Brown]

Hi Douglas

I did respond days ago but do not know what happened to my message.

So how do I append a new record that needs the linking reference. I have the
relevant information (in this case an [Account Code]). I need to append a
record to the existing table but with a particular [Account Code] in the
linking field

Thanks Douglas

 

[Douglas J. Steele]

If you've got the Account code stored in a variable (say, lngAccountCode),
you can use

strSQL = _
"INSERT INTO Item (AccountCode, ItemQuantity) " & _
"SELECT " & lngAccountCode & ", ItemQuantity, " & _
"FROM Item " & _
"WHERE ItemNumber=" & Me.ItemNumber


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas

I did respond days ago but do not know what happened to my message.

So how do I append a new record that needs the linking reference. I have
the relevant information (in this case an [Account Code]). I need to
append a record to the existing table but with a particular [Account Code]
in the linking field

Thanks Douglas

You can't mix the VALUES and SELECT methods of INSERT INTO.

strSQL = _
"INSERT INTO Item (AccountCode, ItemQuantity) " & _
"SELECT AccountCode, ItemQuantity, " & _
"FROM Item " & _
"WHERE ItemNumber=" & Me.ItemNumber

or

strSQL = _
"INSERT INTO Item (AccountCode, ItemQuantity) " & _
"Values (" & NewID &", " & ItemQuantity & ")"

 

[Derek Brown]

Thanks Douglas

I'll try that.

If you've got the Account code stored in a variable (say, lngAccountCode),
you can use

strSQL = _
"INSERT INTO Item (AccountCode, ItemQuantity) " & _
"SELECT " & lngAccountCode & ", ItemQuantity, " & _
"FROM Item " & _
"WHERE ItemNumber=" & Me.ItemNumber


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas

I did respond days ago but do not know what happened to my message.

So how do I append a new record that needs the linking reference. I have
the relevant information (in this case an [Account Code]). I need to
append a record to the existing table but with a particular [Account
Code] in the linking field

Thanks Douglas

You can't mix the VALUES and SELECT methods of INSERT INTO.

strSQL = _
"INSERT INTO Item (AccountCode, ItemQuantity) " & _
"SELECT AccountCode, ItemQuantity, " & _
"FROM Item " & _
"WHERE ItemNumber=" & Me.ItemNumber

or

strSQL = _
"INSERT INTO Item (AccountCode, ItemQuantity) " & _
"Values (" & NewID &", " & ItemQuantity & ")"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi All

I am using INSERT INTO to add a record to an existing table. I want to
change only one value that is the linking field to the Master table so
that
I dont get message record cannot be added without a reference to the
main
table. I already have the nessassary number (an AccountCode) but how do
i
stick it in?

strSQL = _
"INSERT INTO Item (" & "AccountCode) Values (NewID); &
ItemQuantity, )" & _
"SELECT " & "AccountCode, ItemQuantity, & _
"FROM Item " & _
"WHERE ItemNumber=" & Me.ItemNumber

Doesn't work though.

Any Ideas?