Help with RunSQL string copied from an update query

[PlamenGo]

Hi,



I have an small old Access application, my VBA is using a lot of Queries - all types.
I am trying to make some changes that will allow some day to go Web based. Most of the queries have been transformed to VBA code, using the DoCmd.runSQL statement, but now I am starting with some of the more complex ones - an here is a problem:

My sql string is like this:

    strSQL = "INSERT INTO tblNewData (RefNo, SeqProc, Descr ) SELECT tblInitial.UNIQ, tblInitial.PP_Seq, [FirstNo]" & " (" & [FirstText] & ")" & "AS Txt1 FROM tblInitial LEFT JOIN C0_texts ON tblInitial.txtType = C0_texts.txtCode;"

I need to insert 3 colums , the UNIQ goes to RefNo, PP_Sec goes to SecPro and the 3rd into DEscr. The problem obviously is with the Descr, as it is a result of 2 fields from 2 different tables : FirstNo from the basic table (tblInitial, and FirstText vrom C0_texts ) . the result is (for example) : 12 (Switch of!) .
As a query it works perfect, but as a SQL statement I get an error : " Appl can't find the field '|1' referred to in your expression"
I am new in creating the Sql string, and maybe there is an error in my string, although the debugger accepts it ... I have just copied the SQL from the Query ...
As a query it works perfect, but as a SQL statement I get an error : " AppName can't find the field 'I1' referred to in your expression"

[lfpm062010]

It looks like is missing the single or double quotes in your 3rd field Descr. It also needs a space before "AS Txt1....

Currently is --> [FirstNo] ([FirstText])
Should be --> "[FirstNo] ([FirstText])"

[PlamenGo]

Thanks for your suggestion, Ifpm062010, but I cannot understand it. .
Actually, my Descr fiels is constructed the following way : Txt1: [FirstNo] & " (" & [FirstText] & ")"
Thia is the expression that I cannot convert to SQL string ... Looking at it now, I thnk it may be the way the 3 "&" signs are introduced ... Shall start reading more ...

[lfpm062010]

Not sure what you are trying to do. There is not enough information to form correct syntax. Missing other table column info.

The strSQL will translate to
-------------------------------------------------------
INSERT INTO tblNewData (RefNo, SeqProc, Descr ) SELECT tblInitial.UNIQ, tblInitial.PP_Seq, [FirstNo] ()AS Txt1 FROM tblInitial LEFT JOIN C0_texts ON tblInitial.txtType = C0_texts.txtCode;

If you are trying to convert the [FirstNo] & "(" [FirstText] & ")" to a function call, then the string is ok (as long as the function exists).

If you are trying to convert the [FirstNo] & "(" [FirstText] & ")" to a string and store it in field "Descr", then the quotes are needed.

I hope this will make it clearer.

[rpeare]

strSQL = "INSERT INTO tblNewData (RefNo, SeqProc, Descr ) "
strSQL = strSQL & "SELECT tblInitial.UNIQ, tblInitial.PP_Seq, '" & [FirstNo] & " (" & [FirstText] & ")' AS Txt1 "
strSQL = strSQL & "FROM tblInitial LEFT JOIN C0_texts ON tblInitial.txtType = C0_texts.txtCode;"

debug.print strSQL

Use the debug.print statement to see if your SQL string is getting created correctly.

[PlamenGo]

tblInitial has UNIQ (Autonumber), PP_Seq (text) , FirstNo (Text, it has nothing to do with numbers, but in some cases is a number, stored as text ), txtType (integer, showing the code of the text message, stored in C0_Texts), other columns ....
C0_texts has txtCode (integer, code of the text message), FirstText (Text, message) ...
What I need is to make a Text string, containing teh content of FirstNo + (the corresponding) FirstText in brackets "()" ... and place this string in Descr (Text).

Is it clearer now?

[PlamenGo]

Sorry rpeare, it still gives me the same error " Appl can't find the field '|1' referred to in your expression", and debugger stops at the second line ...

[June7]

Why are you trying to save calculated value (the concatenated string)?

Test the SELECT subquery in Access query designer first.

Don't see anything wrong with syntax. What does the constructed sql look like in the debugger? Are you sure about the spelling of field names?

How does transforming queries into VBA help prepare for web delivery?

[PlamenGo]

June 7:
1. I use the new table further on to extract data from other tables and to show the result in a form. Until now it works well with queries ...
2. The SELECT subquery is tested and works in query design
3. the names are Ok - tested. In the debugger it just gives me an error, the strSQL is highlighted and there is no indication where the error comes from (and there is no clue where the Filed '|1' shown in the error message comes from - my suggestion is it refers to Txt1 (the concantenated field).

As I have copied the SQL string from the SQL view in query design, is it possible that in SQL str there is another method or way to concantenate fields?
4. As for the Web - a friend, quite advanced in VBA told me that it is better to avoid macros and queries . So this is more a trial version I am working on.

[rpeare]

strSQL = "INSERT INTO tblNewData ( RefNo, SeqProc, Descr ) "
strSQL = strSQL & "SELECT tblInitial.UNIQ, tblInitial.PP_Seq, [tblinitial]![firstno] & "" ("" & [c0_texts]![FirstText] & "")"" AS Expr1 "
strSQL = strSQL & "FROM tblInitial LEFT JOIN C0_Texts ON tblInitial.txtType = C0_Texts.txtCode"

Debug.Print strSQL

[June7]

How do you plan to deliver db to web? AFAIK, SharePoint uses web forms and AFAIK, web forms don't work with VBA.

Are FirstNo and FirstText included in the form RecordSource? That's where the VBA is trying to pull values from. Every new record will have the same Descr.

Why is this INSERT needed?

rpeare, think that structure is not right. Missing quote mark and & and text delimiters misplaced.

strSQL = strSQL & "SELECT tblInitial.UNIQ, tblInitial.PP_Seq, '" & [tblinitial]![firstno] & " (" & [c0_texts]![FirstText] & ")'" AS Expr1 "

[PlamenGo]

It finally worked!
strSQL = "INSERT INTO tblNewData (RefNo, SeqProc, Descr ) "
strSQL = strSQL & "SELECT tblInitial.UNIQ, tblInitial.PP_Seq, (FirstNo & Chr(32) & chr(40) & FirstText & chr(41)) AS Txt1 "
strSQL = strSQL & "FROM tblInitial LEFT JOIN C0_texts ON tblInitial.txtType = C0_texts.txtCode;"
No square brackets, and using the Chr codes solved the problem, may be too many "" and ' ....

And maybe placing the concantenated string between ( ) also helped, or maybe this was the reason ...

[June7]

The big difference is the VBA is no longer trying to find FirstNo and FirstText from the form. This should work as well:

"... FirstNo & ' (' & FirstText & ')' AS Txt1 "

[PlamenGo]

June 7, thank you very much, your posts were useful for me.
As for the Web delivery - I have not started to plan it yet, We are thinking of SharePoint, but the actual planning will start at the end of the year, even beginning of 2015 and will decide which Access to use 2010 or 2013 ....
And the INSERT - as I wrote - I am transforming ready queries to VBA SQL strings - the Application (designed and created in Access 97) uses working tables to combine and process the data and then display in form or report ...

[June7]

Still don't understand why the data needs to be duplicated to another table as opposed to just using a query as the form or report RecordSource.

All this transformation might be wasted effort if you go with SharePoint.