Club PA 2.0 has arrived! If you'd like to access some extra PA content and help support the forums, check it out at
The image size limit has been raised to 1mb! Anything larger than that should be linked to. This is a HARD limit, please do not abuse it.
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

Any VBA/Access coders out there?

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?


  • BitTWistBitTWist Kitsap CountyRegistered User regular
    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.

    3DS:2552-1222-3225 / PSN:BitTwist
    bowenSpectral Swallow
  • PailryderPailryder Registered User regular
    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 SwallowSpectral Swallow Registered User regular
    You might also check for caps. Ie. 'Notes' instead of 'notes'.

  • GhotiGhoti Registered User regular
    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.

  • PailryderPailryder Registered User regular
    @Dr. Frenchenstein don't leave us hanging!

Sign In or Register to comment.