Append with VB

[voskouee]

I am trying to append from a query to a table. The procedure checks a table, finds the value and goes and appends to the table found.

i liste the fields of the query and the fields of the table.. but something is wrong...

When my fields are numbers i put them in brackets?

IN this case i have three number fields and the rest are text.

Dim rs As DAO.Recordset
Dim strSQL As String
' mismatched

Set rs = CurrentDb.OpenRecordset("SELECT SOBP FROM Countries;")
Do While Not rs.EOF

'debit for normal case
DoCmd.SetWarnings (False)
strSQL = "INSERT INTO " & rs!SOBP & " (Org),(FACCT),(CSUB),(P/S),(SOBP),(Cntry),(debits)" &
" SELECT [Query1.Org],[Query1.FACCT],[Query1.CSUB],[Query1.P/S],[Query1.SOBP],[Query1.Cntry],[Query1.Debits] FROM Q_Normal WHERE trans='dr' and " & _
" [query2.sobp]='" & rs!SOBP & "';"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings (True)

rs.MoveNext

Loop

thanks in advance

 

[voskouee]

I tried everything and my query doesnt work.

i also changed all the fields to text exept one and it seems giving an error message...

Someone please help...

thanks

 

[Bodisathva]

strSQL = "INSERT INTO " & rs!SOBP & " (Org),(FACCT),(CSUB),(P/S),(SOBP),(Cntry),(debits)" &
" SELECT [Query1.Org],[Query1.FACCT],[Query1.CSUB],[Query1.P/S],[Query1.SOBP],[Query1.Cntry],[Query1.Debits] FROM Q_Normal WHERE trans='dr' and " & _
" [query2.sobp]='" & rs!SOBP & "';"

From what you have provided, you are attempting to insert values from Query1 (Query1.Org, Query1.FACCT, etc) but your FROM statement refers to a different table (Q_Normal), and your where clause refers to a third data source, query2.

 

[voskouee]

well this is my query with one field of the table and it works fine. i am just checking a table which has values then appends to that tables (that values has its corresponding table) and the criteris is a union query..

this works fine.. why is not accepting more than one fields?

DoCmd.SetWarnings (False)
strSQL = "INSERT INTO " & rs!SOBP & "(credits)" & _
" SELECT [Query2.credits] FROM Q_Normal WHERE trans='cr' and " & _
" [query2.sobp]='" & rs!SOBP & "';"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings (True)

 

[Bodisathva]

well this is my query with one field of the table and it works fine.

then I will assume that query1 and query2 are part of a third query, Q_Normal.

this works fine.. why is not accepting more than one fields?

because your syntax is wrong.

you are using
INSERT INTO table (field1), (field2), (field3), (field4)...

should be
INSERT INTO table (field1, field2, field3, field4)...

personally, I'd use:
strSQL = "INSERT INTO " & rs!SOBP & " SELECT [Query1.Org],[Query1.FACCT] ,[Query1.CSUB], [Query1.P/S], [Query1.SOBP],[Query1.Cntry], [Query1.Debits] FROM Q_Normal WHERE trans='dr' and " & _
" [query2.sobp]='" & rs!SOBP & "';"

 

[voskouee]

then I will assume that query1 and query2 are part of a third query, Q_Normal.
because your syntax is wrong.

you are using
INSERT INTO table (field1), (field2), (field3), (field4)...

should be
INSERT INTO table (field1, field2, field3, field4)...

personally, I'd use:
strSQL = "INSERT INTO " & rs!SOBP & " SELECT [Query1.Org],[Query1.FACCT] ,[Query1.CSUB], [Query1.P/S], [Query1.SOBP],[Query1.Cntry], [Query1.Debits] FROM Q_Normal WHERE trans='dr' and " & _
" [query2.sobp]='" & rs!SOBP & "';"

I know what you mean.. but my destination table is not the same. it has more fields so i want to tell my query where to put the fields. and yes i have a query 1 and a query 2 and they make then i ihave Q_normal

whats wrong with this systax?

whatever i do it gives me a Intert Into Syntax error.. it doesnt like the way i list the fields?

'debit for normal case
DoCmd.SetWarnings (False)
strSQL = "INSERT INTO " & rs!SOBP & "(Org),(FACCT),(CSUB),(P/S),(SOBP),(Cntry),(Debits)" & _
" SELECT (Query1.Org),(Query1.FACCT),(Query1.CSUB),(Query1.P/S),(Query1.SOBP)," & _
"(Query1.Cntry),(Query1.Debits) FROM Q_Normal WHERE trans='dr' and " & _
" [query2.sobp]='" & rs!SOBP & "';"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings (True)

 

[voskouee]

Problem solved!

I was using stupid names for my fields. With characters that were not acceptaple.

Do not use / or space in your fields.


thanks for the guidance...