Insert Into Where Clause Issue - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Insert Into Where Clause Issue

Nathan Maturine

2 years ago

I'm certain the solution is simple, but I'm unable to get this to work.
Here is the offending code:
DoCmd.RunSQL "INSERT INTO quotationMasterT SELECT * FROM quotationT WHERE quotationNum = " & TempVars!GlobalQuoteNum
This variable used to be numeric, but it was necessary to change it
a string. The code above worked before.
I thought it was just a matter of changing to quotationNum = '" & TempVars!GlobalQuoteNum & "'", but that does not work.
I am not getting any errors, it simply does not work.
Any assistance would be appreciated.

	Sami Shamma @Reply 2 years ago
this is the wrong format for INSERT INTO. Access SQL Seminars second level

	Sami Shamma @Reply 2 years ago
you have to specify what field you are inserting and then give their values.

	Nathan Maturine OP @Reply 2 years ago
I'm wanting to insert all fields and they are about 40 fields, which I'm hoping I would not have to specify.

	Nathan Maturine OP @Reply 2 years ago
Also, the code had worked when the variable was numeric.

	Kevin Robertson @Reply 2 years ago
You say the field is now a string - Double Double Quotes

	Nathan Maturine OP @Reply 2 years ago
Thanks... This is the code now... DoCmd.RunSQL "INSERT INTO quotationMasterT SELECT * FROM quotationT WHERE quotationNum = """ & TempVars!GlobalQuoteNum & """" Still nothing. This is an example of what the TempVars value is: 24/07395

	Kevin Robertson @Reply 2 years ago
I just tested a similar SQL statement and it worked perfectly so your code is fine. Check that your TempVars has a value (and the value you were expecting - is it a valid value?). Use MsgBox to display the value stored in TempVars.

	Richard Rost @Reply 2 years ago
Sami, a "VALUES" insert is just one method. You can also use a SELECT statement like he has, provided the field names all match up exactly. I agree with Kevin. Give that TempVar a MsgBox and see what you're dealing with.

	Nathan Maturine OP @Reply 2 years ago

	Nathan Maturine OP @Reply 2 years ago
If the issue isnt the concatenation, is there something else I should be looking at?

	Nathan Maturine OP @Reply 2 years ago
Also, when I look at quotationT, 24/07395 is a value in field quotationNum

	Richard Rost @Reply 2 years ago
Are ALL of your field names in the source and destination tables exactly the same? Are you trying to insert into an Autonumber field? These can both cause problems.

	Sami Shamma @Reply 2 years ago
Richard I need to go back a watch SQL Seminar 2 for I do not remember the SELECT statement method. Must be getting old.

	Nathan Maturine OP @Reply 2 years ago
After QuotationT was finalized, I made a duplicate of it, to ensure all the fields were the same and the spelling were the same. As I said earlier, this code worked up to when the QuotationNum filed was changed from numeric to text.

	Nathan Maturine OP @Reply 2 years ago
Question, if the fields were different, wouldn't access 'complain'?

	Richard Rost @Reply 2 years ago
Did you change it in BOTH tables?

	Richard Rost @Reply 2 years ago
Complain? Not necessarily. It might just do nothing, especially if you have warnings turned off.

	Nathan Maturine OP @Reply 2 years ago
Yes...I did...I just checked again to be double sure.

	Richard Rost @Reply 2 years ago
Put the whole SQL statement in a string: `S = "INSERT INTO quotationMasterT SELECT * FROM quotationT WHERE quotationNum = """ & TempVars!GlobalQuoteNum & """"` MsgBox it so we can see it. Now take that statement, and put it exactly as it appears in the SQL view of a query. Try running it. That might give you more insight as to what's wrong.

	Nathan Maturine OP @Reply 2 years ago
I do have warnings turned off! Let me comment out the warnings to see if it will give me an error

Nathan Maturine OP @Reply

2 years ago

I ran the code as you suggested and no change.

I have a bit code after the Insert part that checks if the copy was successful.

myCopyCheck = DLookup("ID", "quotationMasterT", "quotationNum= " & TempVars!GlobalQuoteNum)
If myCopyCheck > "1" Then 'The Copy was successful
MsgBox "Yahoo!"
Else
MsgBox "A DoCmd.RunSQL copy to QMasterT failed! Contact IT"
Exit Sub
End If

I get the error code everytime

	Richard Rost @Reply 2 years ago
If your ID is an autonumber, you can't compare it to a string. You're also not treating your TempVar as a string. The right way to check would be: `X = Nz(DLookup("ID", "quotationMasterT", "quotationNum= """ & TempVars!GlobalQuoteNum & """"),0) If X = 0 then 'not found End if`

	Nathan Maturine OP @Reply 2 years ago
Ah Ha...I'll make the change. Thanks

	Richard Rost @Reply 2 years ago
Is that why you thought it wasn't working, or are the records actually not getting inserted into your table?

	Nathan Maturine OP @Reply 2 years ago
NO. When I check the table, the record is not there

	Richard Rost @Reply 2 years ago
Copy the source table so you have an exact replica of it, and try running the insert query to insert records into that new table. This might at least tell you if you have any inconsistencies in the naming of your fields. Aside from that, I'm stumped. Without being able to see your database, it's very hard to troubleshoot.

	Nathan Maturine OP @Reply 2 years ago
It's working....When I entered your Insert code, I did not Dim the "S". I just did and ran the code and it works!! Thank you soooo much!

	Kevin Robertson @Reply 2 years ago
When you copied the main table did you change the ID from an AutoNumber to a Number?

	Richard Rost @Reply 2 years ago
Ahhh... classic mistake. Make sure you have Option Explicit in all of your code modules!

	Nathan Maturine OP @Reply 2 years ago
No, I had it set to an Autonumber

	Nathan Maturine OP @Reply 2 years ago
Guys, I cannot say thank you enough for the assistance and your patience!!!! It is appreciated!

	Richard Rost @Reply 2 years ago
You're very welcome. Like American Express, membership has its privileges. :)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View