3075 Backup Error (1 Viewer)

I have a form linked to a table which is used to edit the table. There is a button to save the table, but I also want to add the function of creating a new entry for a backup table to preserve the old record (with a version number that changes to know which versions are which). I keep hitting error 3075 in my SQL (and other assorted bugs). Here's a shortened version of the VBA (there are about fifty fields, so I'll save you the trouble and give you a few)

Option Compare Database
Dim Rpt_Asset_IDV As Integer
Dim Report_NameV As String
Dim Report_DescriptionV As String
Dim Business_ValueV As String

Private Sub cmdSave_Click()
If MsgBox("Saving the changes will update the record. Are you sure you wish to save?", vbYesNo) = vbYes Then
Rpt_Asset_IDV = Me.Rpt_Asset_ID.Value
Report_NameV = Me.Report_Name.Value
Report_DescriptionV = Me.Report_Description.Value
If Not IsNull(Me.Business_use) Then BusinessValueV = Me.Business_use.Value

Call Master_Asset_Backup

DoCmd.RunCommand acCmdSaveRecord

Exit_CmdSave_Click:
Exit Sub
Err_CmdSave_Click:
MsgBox Err.Description
Resume Exit_CmdSave_Click
End If
End Sub

Public Sub Master_Asset_Backup()
Dim SQLqry As String
DoCmd.SetWarnings False
SQLqry = "INSERT INTO [Master Asset Backup] "
SQLqry = SQLqry & "([Rpt Asset ID], [Report Name], [Report Description]"
If Not Me.Business_use = Null Then SQLqry = SQLqry & ", [Business Uses]"
SQLqry = SQLqry & ") VALUES ("
SQLqry = SQLqry & Rpt_Asset_IDV & ", " & Report_NameV & ", " & Report_DescriptionV
If Me.Business_use = Not Null Then SQLqry = SQLqry & ", " & Business_ValueV

Debug.Print SQLqry
DoCmd.RunSQL SQLqry
DoCmd.SetWarnings True
End Sub

There's a lot more, but it's all very repetitive. The SQL I'm getting is (shortened) this:

INSERT INTO [Master Asset Backup] ([Rpt Asset ID], [Report Name], [Report Description]) VALUES (89, Customer Report, This report is for customers to compare our services);

I've checked for special characters and found none, and as you can see it's ignoring my If Then about Business Uses (And all the other fields), and still not running the SQL. Help?

This should help:

http://www.baldyweb.com/BuildSQL.htm

Noting in particular how text values need to be surrounded by quote delimiters. Also, nothing is "= Null". Use the IsNull() function or this which test for both Null and a zero length string:

If Len(Me.Business_use& vbNullString) = 0 Then

Thanks for the site about quotations; The SQLqry string is now working up to the "Report Description" field, or the last field in the string before the If Then sections and I get no errors when I run the code. One problem solved.

The second problem, however, ugh. I replaced my statement for business use in all three categories with

If Not Len(Me.Business_use & vbNullString) = 0 Then BusinessValueV = Me.Business_use.Value

If Not Len(Me.Business_use & vbNullString) = 0 Then SQLqry = SQLqry & ", [Business Uses]"

If Not Len(Me.Business_Use & vbNullString) = 0, Then SQLqry = SQLqry & "', '" & Business_ValueV

And it still is not including them in, though the field is not null.
I tried IsNull() before I made = Null and got an error expecting an =. Any ideas?

Wait, I now get Business Uses in the SQL (I did have it null, but I changed that and it appeared), however, I get a null for the value. Any reason that might be?

What is the code now, and what does the Debug.Print produce? I'd probably eliminate what to me is confusing and do this:

If Len(Me.Business_use & vbNullString) > 0 Then

The SQL I'm getting now is:

INSERT INTO [Master Asset Backup] ([Rpt Asset ID], [Report Name], [Report Description], [Category], [Customer Flag], [Owner], [Custodian], [Reportl], [Request Method], Frequency, [Detail or Summary Data], [Info], [Technical Authors], [Notes], [Report Delivered To], Recipients, [Report Run Instructions], [Due Date], [Date Created or Release Date], [Date Updated], [Data Source], [Output Text], Table, Universe, [Program Template], [End User Template], [Delivery Instructions], [Calculation Notes], [Constraint Flag], [Constraint Notes], [Version Number]) VALUES (89, 'Customer Report', 'Given to Customers', 'Report', 'Y', 'Consultants', 'Consultants', 'Powerpoint', 'Routine Request', 'Calendar Quarter', 'Detail', 'N', 'Consulting Analytics', 'Commercial', '[EMAIL="'blah@blah.com"]blah@blah.com'[/EMAIL], '"1). For "2.) This report is created from a place. To create ', 'EOM folowing Quarter End', 'N/A', 'N/A', 'SAS', 'Excel', 'TBD', 'N/A', 'Somewhere', 'I', 'Email Excel Output', 'I', 'N', 'N/A', 1);

Like I said, a lot of fields to go through. I kept all the special characters, but got rid of any actual data pertaining to things. Could it be the special characters?

You mean like 'N/A'? No, since it's within the string it should insert fine. It looks like this is goofed though:

'"1). For "2.) This report is created from a place. To create '

Is that supposed to be one string? Are there double quotes within the string to be inserted? This may help:

http://www.baldyweb.com/BuildSQL.htm

Handling quotes can get tricky when there can be single or double quotes in the text.

The double quotes are within the entry in the table. I deleted them, but I'm still getting the error.

What error are you getting? Can you post the db here?

It works now!

It was an error 3134, which I looked up to be use of a reserved name. Which is when I remembered I have a field named table I didn't put in brackets. Fixed that, now she works like a charm. Thanks for all your help PBaldy!

Excellent, glad you got it working!