Insert not populating new fields (1 Viewer)

[Angelflower]

I have an insert query that has been working just fine. I added three new columns (Paid, Balance, Invoiced) to both tables that I am using in my query. The query will insert the new records that don't exist in the one table just fine but it is not populating the new columns. :banghead:

Any ideas what's going on and how I can fix it? Thanks in advance for your help in this matter.

' Append records from aud_ck_reg (local table) to dbo_aud_ck_reg (table on server)

DoCmd.RunSQL "INSERT INTO dbo_aud_ck_reg ( audType, audDate, audUser, transid, customer_number_parent, transdate, create_1131, check_number, check_date, PW_voucher_date, " & _
"voucher_date, voucher_number, customer_number, credit, debit, fiscal_yr, month_number, calendar_month, calendar_year, calculated_cost, notes, CIT_number, Quarter, Hyperlink, Paid, Balance, Invoiced)" & _
"SELECT aud_ck_reg.audType, aud_ck_reg.audDate, aud_ck_reg.audUser, aud_ck_reg.transid, aud_ck_reg.customer_number_parent, aud_ck_reg.transdate, " & _
"aud_ck_reg.create_1131, aud_ck_reg.check_number, aud_ck_reg.check_date, aud_ck_reg.PW_voucher_date, aud_ck_reg.voucher_date, aud_ck_reg.voucher_number, " & _
"aud_ck_reg.customer_number, aud_ck_reg.credit, aud_ck_reg.debit, aud_ck_reg.fiscal_yr, aud_ck_reg.month_number, aud_ck_reg.calendar_month, aud_ck_reg.calendar_year, " & _
"aud_ck_reg.calculated_cost, aud_ck_reg.notes, aud_ck_reg.CIT_number, aud_ck_reg.Quarter, aud_ck_reg.Hyperlink, aud_ck_reg.Paid, aud_ck_reg.Balance, aud_ck_reg.Invoiced FROM aud_ck_reg LEFT JOIN dbo_aud_ck_reg ON " & _
"(aud_ck_reg.transid = dbo_aud_ck_reg.transid) AND (aud_ck_reg.audUser = dbo_aud_ck_reg.audUser) AND (aud_ck_reg.audType = dbo_aud_ck_reg.audType) " & _
"AND (aud_ck_reg.audDate = dbo_aud_ck_reg.audDate) WHERE (((dbo_aud_ck_reg.audType) Is Null) AND ((dbo_aud_ck_reg.audDate) Is Null) AND ((dbo_aud_ck_reg.audUser) Is Null) AND ((dbo_aud_ck_reg.transid) Is Null));"

 

[CJ_London]

You query is very difficult to read (have you never thought of aliasing to make it simpler?) but on the face of it seems OK and if it is not generating an error it implies there is something wrong with the data - so is there data in the new columns?

 

[Cronk]

Sometimes with complex queries, whether the issue is with the data (as suggested in the previous post) or the VBA syntax or different field types in the destination table, I put a break point in the code and find the SQL in the immediate window and post it into a new query. It will normally point up the error.

 

[CJ_London]

Also suggest disable setwarnings if you are using them until the query is working as expected

 

[Angelflower]

Thanks for the input.... Sorry I was out of the office for a few days and just now getting back to this problem. Anyway… what I ended up doing was creating a new MS Access database and importing all my stuff into that. I had to relink all my tables and views (very time consuming setting up primary keys and stuff) and that seemed to fix the problem. All I can think of is that I have made so many changes and updates that something was corrupted on the back end that wasn’t allowing the new columns in the table to be populated. It made no sense to me. It was a very simple insert query from a local table to my table on the SQL server. When I convert the DoCmd.RunSQL query into an actual query and just use the docmd.openquery "qryname" it still doesn't populate the new columns in the tables but if I just execute the query by double clicking it works fine.