Any VBA/Access coders out there?

Dr. Frenchenstein · January 2015

I inherited an Access database that has some convoluted and funky VBA going on. i don't want to just rebuild from scratch, as it's getting replaced in a year anyways. but anyway, the issue is that the code is calling for an INSERT INTO command, which updates an existing table. Simple enough, but there are two Memo fields (and a Hyperlink field further along in the code) that are not getting pulled into the table.

Here's the offending piece of code.

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

I'm assuming there is some trick or something to get these types of fields to work?

BitTWist · January 2015

Not sure what version of Access you are running, but there could be a number of issues.

Some questions though:
Is the application crashing on the DoCmd?
Is a record getting added to the table, but a couple fields are blank, even though there is data in the control?

First thing that comes to mind is it doesn't look like your checking for single quotes within the text your trying to add to the database. This can screw up data adds in VBA. Memo fields are notorious for this.

Pailryder · January 2015

did your table data types change? Memo datatype in the source AND the target? That's the first thing i'd check. if the target field data type is not a memo then they aren't getting inserted because they won't fit. or you could have garbage data in the source i guess.
*i read it that the records are inserted but some fields are blank. can you confirm that the source has data? also if that's a copy/paste i don't think it should matter but there looks like something funky going on here "Forms! [frmInputBills]![CFBillNum]"

Spectral Swallow · January 2015

You might also check for caps. Ie. 'Notes' instead of 'notes'.

Ghoti · January 2015

I would be curious to know the names of the fields you are saying are not importing. From the code above, I am not necessarily seeing a field which would be attributed to a hyperlink (I cannot make the same assumption with the memo fields). It could be that these fields were added at a later time and this function was never updated to include them.

Pailryder · February 2015

@Dr. Frenchenstein don't leave us hanging!

Penny Arcade

Quick Links

Any VBA/Access coders out there?

Posts