Using SQL command with DoCmd.RunSQL

[vakjay]

I have a form displaying values from a number of tables.(Test Cases, Users, Method, Levels, Priority, Builds). TID is a field linking the table "Test Cases" to another table. Normally when this form is accessed TID value is automatically filled in.
If there is a record in Test Case table which is linked to this TID, it will be displayed. When the button "Add Record" is selected a new record is added by copying appropriate fields from the currently displaying record.

The command used to append a record is as follows:

DoCmd.RunSQL "Insert Into [Test Cases] (Name, AuthorID, MethodID, LevelID, PriorityID, BuildID, TestID) VALUES (" & _
TCName.Value & "," & _
IIf(Len(TCAuthor.Value) = 1, DLookup("ID", "Users"), TCAuthor.Value) & "," & _
IIf(Len(TCMethod.Value) = 1, DLookup("ID", "Method"), TCMethod.Value) & "," & _
IIf(Len(TCLevel.Value) = 1, DLookup("ID", "Levels"), TCLevel.Value) & "," & _
IIf(Len(TCPriority.Value) = 1, DLookup("ID", "Priority"), TCPriority.Value) & "," & _
IIf(Len(TCBuild.Value) = 1, DLookup("ID", "Builds"), TCBuild.Value) & "," & _
Str(TID.Value) & ");"

When I run the code I am getting a syntex error. I tried to search instructions of using SQL commands in "DoCmd.RunSQL" on the web. But, I could not find any document which clearly provides instuctions. Eg. When to use "&", double quoting, etc. Could you please help me in
- telling me the syntex error in the above line of code
- directing me to a document, site, or a book that clearly explains how to use SQL commands in "DoCmd.RunSQL".

I have already spent many hours in fixing this line of code. Please help me to get out of this problem.

VAKJay

 

[PHV]

Your instruction have no syntax problem for VBA, but for SQL, ie the use of " and & is correct.
Are you sure the DLookup returns only one value without the Criteria parameter ?
Furthermore if you insert strings value you have to enclose it with single quote, like this:
VALUES ([highlight]'[/highlight]" & _
TCName.Value & "[highlight]'[/highlight],[highlight]'[/highlight]" & _
...

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi vakjay,

I suspect your problem is quotes.

When your line of code is run , two things happen:

1) VBA builds the SQL string from all the functions you have coded.
2) Jet interprets the VBA-built string and needs to find quotes round anything it, in turn, has to treat as a string.

I don't know the nature of your data, but let's say that Name is Text. The (partial) result of your VBA will be ..

[purple][tt]Insert Into [Test Cases] (Name, ...)
VALUES(VAKjay, ...[/tt][/purple]

.. but what it needs to be is ..

[blue][tt]Insert Into [Test Cases] (Name, ...)
VALUES([highlight]'[/highlight]VAKjay[highlight]'[/highlight], ...[/tt][/blue]

To generte this from VBA you must use ..

[blue]DoCmd.RunSQL "Insert Into [Test Cases] (Name, AuthorID, MethodID, LevelID, PriorityID, BuildID, TestID) VALUES (" & _
                      [highlight]"[red]'[/red]" & [/highlight]TCName.Value & "[highlight][red]'[/red][/highlight]," & _
etc. ...[/blue]

And similarly for all your fields which are text.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.