INSERT INTO VBA statement not working for Memo or Hyperlink fields

[MBresnan]

I'm trying to run some VBA code to update some Access tables from a form, everything seems to be working except for the Memo, and Hyperlink fields i'm trying to update. the code just skips those fields, and then the user has to go back in and update via another form pointed towards the table i was trying to update originally. Here's a chunk of what i'm using:

 Dim sqlAddBill As String
        sqlAddBill = "INSERT INTO tblBills ([BillNum], [Sorter], [Sponsor], [FileCode], [Title], [Synopsis], [Type], [Priors], [PriorPosition], [Notes], [CFBillNum], [Week] ) " _
        & "VALUES (Forms![frmInputBills]![BillNum], Forms![frmInputBills]![Sorter], Forms![frmInputBills]![Sponsor], Forms![frmInputBills]![FileCode], Forms![frmInputBills]![Title], Forms![frmInputBills]![Synopsis], Forms![frmInputBills]![Type], Forms![frmInputBills]![Priors], Forms![frmInputBills]![PriorPosition], Forms![frmInputBills]![Notes], Forms![frmInputBills]![CFBillNum], Forms![frmInputBills]![Week])"
        DoCmd.RunSQL sqlAddBill

The Synopsis and Notes fields are the Memo fields in Access, and they just come through blank after the code runs. i don't get any errors or anything, it seems to complete just fine.

is there some kind of special code i am missing here? i'm not too well versed on VBA. The hyperlink comes in later in the code, there are a few more tables being updated as well.

 

[SkipVought]

Hi,

Does this SQL code manually run as expected in Access?

 

[dhookom]

Consider rewriting your code like

  Dim sqlAddBill As String
   sqlAddBill = "INSERT INTO tblBills ([BillNum], [Sorter],  " & _
      "[Sponsor],[FileCode], [Title], [Synopsis], [Type], " & _
      "[Priors], [PriorPosition], [Notes], [CFBillNum], [Week]) " & _
      " VALUES (" & Forms![frmInputBills]![BillNum] & ", '" & _
      Forms![frmInputBills]![Sorter] & _
      "', '" & Forms![frmInputBills]![Sponsor] & "', '" & etc & ")"
   Debug.Print sqlAddBill
   DoCmd.RunSQL sqlAddBill

If the code is running in frmInputBills, you can replace Forms!frmInputBills with Me. You also need to understand the different delimiters and how to handle cases where a quote is included in the control value.

Duane
Hook'D on Access
MS Access MVP

 

[MBresnan]

so I need the &"','"& between each VALUE field? I'm even less knowledgeable about VBA than I thought...

 

[MBresnan]

   Dim sqlString As String
       sqlString = "INSERT INTO tblBillInput ([BillNum], [Sorter], [Sponsor], [FileCode], [Title], " & _
       "[Synopsis], [Type], [Priors], [PriorPosition], [Notes], [CFBillNum], [Week], [Committee], " & _
       "[HearingDate], [Status], [BillURL]) " & _
       " VALUES (" & Me![BillNum] & "', '" & Me![Sorter] & "', '" & Me![Sponsor] & "', '" & Me![FileCode] & "', '" & _
        Me![Title]&"', '"& Me![Synopsis]&"', '"& Me![Type]&"', '"& Me![Priors]&"', '"& Me![PriorPosition]&"', '"& _
        Me![Notes]&"', '"& Me![CFBillNum]&"', '"& Me![Week]&"', '"& Me![Committee]&"', '"& Me![HearingDate]&"','"& _
        Me![Status]&"', '"& Forms![frmInputBills]![BillURL]")"
       Debug.Print sqlString
       DoCmd.RunSQL sqlString

Above is what I entered, and i'm getting a Compile error: syntax error when I try to compile or run the code.

 

[MBresnan]

The break seems to be happening in the beginning of the code now (but the sqlString = part of the code is red) Here's a little more including the prompt that comes up when you kick off the code.

Private Sub cmdAddAndQuit_Click()
Dim intMsgAnswer As Integer
    intMsgAnswer = MsgBox("Update the database?", vbYesNoCancel, "DB Update")
        If intMsgAnswer = vbYes Then
         DoCmd.SetWarnings True
       
       ' added the following code
       Dim sqlString As String
       sqlString = "INSERT INTO tblBillInput ([BillNum], [Sorter], [Sponsor], [FileCode], [Title], " & _
       "[Synopsis], [Type], [Priors], [PriorPosition], [Notes], [CFBillNum], [Week], [Committee], " & _
       "[HearingDate], [Status], [BillURL]) " & _
       " VALUES (" & Me![BillNum] & "', '" & Me![Sorter] & "', '" & Me![Sponsor] & "', '" & Me![FileCode] & "', '" & Me![Title] & "', '" & Me![Synopsis] & "', '" & Me![Type] & "', '" & Me![Priors] & "', '" & Me![PriorPosition] & "', '" & _
        Me![Notes] & "', '" & Me![CFBillNum] & "', '" & Me![Week] & "', '" & Me![Committee] & "', '" & Me![HearingDate] & "','" & _
        Me![Status] & "', '" & Forms![frmInputBills]![BillURL]")"
       DoCmd.RunSQL sqlString

the Private Sub line is where the break is showing after running the code from the form.

 

[Andrzejek]

The very tail end of your sqlString:

Forms![frmInputBills]![BillURL][red] & [/red]")"

and also try this:

Me![Status] & "', '" & Forms![frmInputBills]![BillURL] & ")"[blue]
Debug.Print sqlString [/blue]
DoCmd.RunSQL sqlString

This way you can SEE your insert statement.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MBresnan]

Made the above change and things seemed to compile correctly! however, when I ran the code from the form, I got an error.

Run-time error '3075'

Syntax error(missing operator) in query expression: (lists out each field and stops at the URL)

so it looks like the memo fields are coming over now, but the URL field is killing the process. are those handled differently from even memo fields?

Here's the printed debug:
INSERT INTO tblBillInput ([BillNum], [Sorter], [Sponsor], [FileCode], [Title], [Synopsis], [Type], [Priors], [PriorPosition], [Notes], [CFBillNum], [Week], [Committee], [HearingDate], [Status], [BillURL]) VALUES (555555', '555555', 'TEST', 'Agriculture', 'TEST', '', 'Active', 'TEST', 'Monitor', '', 'TEST', '3', 'APP', '1/21/2015','1st Reading (Introduction)', ')

 

[dhookom]

[highlight #FCE94F]Unbalanced single quote, is this numeric or text[/highlight]
[highlight #E9B96E]Possible zero-length string issue[/highlight]
[highlight #8AE234]Access dates are delimited with # not '[/highlight]
[highlight #729FCF]Extra , '[/highlight]
You have 16 fields but are inserting only 15 values.

INSERT INTO tblBillInput ([BillNum], [Sorter], [Sponsor], [FileCode], [Title], [Synopsis], [Type], [Priors], [PriorPosition], [Notes], [CFBillNum], [Week], [Committee], [HearingDate], [Status], [BillURL]) VALUES (555555[highlight #FCE94F]'[/highlight], '555555', 'TEST', 'Agriculture', 'TEST', [highlight #E9B96E]''[/highlight], 'Active', 'TEST', 'Monitor', [highlight #E9B96E]''[/highlight], 'TEST', '3', 'APP', [highlight #8AE234]'1/21/2015'[/highlight],'1st Reading (Introduction)'[highlight #729FCF], '[/highlight])

Duane
Hook'D on Access
MS Access MVP

 

[MBresnan]

yes, there should be a URL field at the end (the 16th field), but it's not bringing anything in and (I believe) triggering the error.

As far as your highlighting questions, all the commas seem to be framed with the apostrophes (', ' for example), not the fields themselves. is that incorrect? I was following the above code examples.

as far as the double apostrophe issues, i'm not sure what's happening there. my code is:

me![Synopsis] & "', '" & Me![Type]

but the output is throwing two sets of "', '" in there for some reason. [Synopsis] is a memo field, but it happens again BEFORE the [Notes] memo field.

 

[MBresnan]

I also tried to delimit the [HearingDate] field with '#'s and it errored out.

 

[dhookom]

You didn't state whether BillNum is numeric or text. If numeric, try:

    Dim sqlString As String
    sqlString = "INSERT INTO tblBillInput ([BillNum], [Sorter], [Sponsor], [FileCode], [Title], " & _
      "[Synopsis], [Type], [Priors], [PriorPosition], [Notes], [CFBillNum], [Week], [Committee], " & _
      "[HearingDate], [Status], [BillURL]) " & _
      " VALUES (" & Me![BillNum] & ", '" & Me![Sorter] & "', '" & Me![Sponsor] & "', '" & Me![FileCode] & _
      "', '" & Me![Title] & "', '" & Me![Synopsis] & "', '" & Me![Type] & "', '" & Me![Priors] & "', '" & Me![PriorPosition] & "', '" & _
      Me![Notes] & "', '" & Me![CFBillNum] & "', '" & Me![Week] & "', '" & Me![Committee] & "', #" & Me![HearingDate] & "#,'" & _
      Me![Status] & "', '" & Forms![frmInputBills]![BillURL] & "')"
[COLOR=#4E9A06]    ' replace zero-length strings with NULL[/color]
    sqlString =  Replace(sqlString, "''","NULL")
    Debug.Print sqlString
    DoCmd.RunSQL sqlString

Duane
Hook'D on Access
MS Access MVP

 

[MBresnan]

sorry those are text fields. my test data is all numeric for those but normally it's a combination of alpha and numeric (hb12 or something similar)

argh, I should have entered into my test fields more clearly. Those null fields are the memo fields i'm missing (and the URL at the end) I could have sworn it was working a minute ago...

also, ignore my calendar delimit comment, I had done it wrong.

 

[dhookom]

By "my calendar delimit comment" are you referring to the date delimiters?

Are you still having issues?
Do you understand when to use quotes and when not?
Do you know how to handle the possibility of a field value containing a quote?

Duane
Hook'D on Access
MS Access MVP