Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2013
    Posts
    81

    Question Unanswered: MS Access 03 - insert queries

    Sorry if this is a really silly question, but I would be grateful for assistance.

    I've built an insert query into the VBA coding behind a database. The idea is, when the user carries out certain actions an update is made into tblHistory identifying the action. I've noticed that when the insert query enters text into the table it adds a space at the start of the text, so it will say " Metric 22" instead of "Metric 22". Generally speaking this isn't an issue (so far). However, I've then built a select query (which links in to a report) to pull out an records related to "Metric 22" (which is the unique ID of the equipment the user is working with). I've built a command button into the form (which shows the equipment information) to run the report and thus the query, and the paramater is passed from the form (I think I've described that the right way). When the parameter is passed from the form, the space at the start of the ID isn't passed so Access doesn't pull out any records. Any idea how to fix this?

    The VBA coding for the insert query is (I can't post the whole thing in this post - its too long):

    Code:
    '(user clicked ok without entering details) enter details into history table
    'first set strSetID, strAction, dateInput
      strSetID = Me.SetID
      strAction = "Set Out of Service"
      dateInput = Format(strInput, "yyyy-mm-dd")
        'run INSERT query to enter details in history table
            CurrentDb.Execute _
            "INSERT INTO tblHistory (SetID, HistoryAction, " & _
            "HistoryActionComments, HistoryDate, HistoryDateRecord) " & _
            "VALUES (' " & strSetID & " ', ' " & strAction & " ', " & _
            " 'No reason given', ' " & dateInput & " ', ' " & Format(LDate, "yyyy-mm-dd") & " ' )"
    The criteria I've entered into the Select query (qrySetHistory) - which links to the report - is "[Forms]![frmSet].[SetID]" The full SQL code (as written by Access) reads:

    Code:
    SELECT tblHistory_1.SetID, tblWeight.WeightSerialNumber, tblWeight.WeightQuantity, tblHistory_1.HistoryAction, tblHistory_1.HistoryActionComments, tblHistory_1.HistoryDate, tblHistory_1.HistoryDateRecord
    FROM tblHistory AS tblHistory_1 LEFT JOIN tblWeight ON tblHistory_1.WeightID = tblWeight.WeightID
    GROUP BY tblHistory_1.SetID, tblWeight.WeightSerialNumber, tblWeight.WeightQuantity, tblHistory_1.HistoryAction, tblHistory_1.HistoryActionComments, tblHistory_1.HistoryDate, tblHistory_1.HistoryDateRecord, tblHistory_1.HistoryID, tblHistory_1.WeightID
    HAVING (((tblHistory_1.SetID)=[Forms]![frmSet].[SetID]))
    ORDER BY tblHistory_1.HistoryDate;
    Any assistance would be greatly appreciated. I'm still fairly new to Access and some of the simplest things have given me major issues lol. Usually feel a right idiot when I finally work it out lol.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    find out why its adding a space.. these things don't happen by stealth, its addign a leading space because of something you have done, or possibly noit done. check the bit of code that writes the data

    check your 'VALUES' statement
    you are adding an uneccesary space there
    "VALUES ('" & strSetID & "', '" & strAction & "', " & _
    " 'No reason given', '" & dateInput & "', '#" & Format(LDate, "yyyy-mm-dd") & "#' )"

    what is dateinput?
    what is strSetID?
    you need to delimit values (show where they start and end) IF:-
    they are text/string/alphanumeric, use ' or "
    they are dates/temporal values use #

    to clear up your current data (ONCE you have fixed the problem above) run an update query which TRIMs off leading and trailing spaces

    UPDATE mytable SET mycolumn = trim(mycolumn)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Code:
    "VALUES ('*" & strSetID & "*', '*" & strAction & "*', " & _
    Why are you inserting spaces in the columns if you don't want them? every * is a place holder for a space in your original query string.
    Have a nice day!

  4. #4
    Join Date
    Jun 2013
    Posts
    81

    Talking

    Ahaaa that would explain it. I knew it would be something really silly like that, but you know how it is, easy when you know how lol.

    I'm not entirely certain why I've added the extra spaces, maybe a typo originally that I later thought was the correct method (I'm building the database around my usual job so it can be weeks or months before I come back to it and I've usually forgotten how to do things again lol).

    Thank you both for your help I really really appreciate it. Having somewhere like this I can come when I get stuck is really useful and saves my sanity (and hairline lol).

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    these sort of things are relatively easy to find
    the fundamental problem is that when examining code that generates SQL its all to easy glass over the detail because you know what your SQL should do (as opposed to what its told to do)

    So when you get this sort of error the first thing to do is to check what you are actually sending to the SQL engine

    So thats why I always recommend that you prove your SQL is right.
    first off I strongly recommend that you assign your SQL to a variable, with modern computers the extra processing overhead and memory is irrelevant. if this code was targetting an old slow or limited computer then you'd do things differently but if it were then you wouldn't be suing Access. the reason being that you can see exactly what is being sent to the SQL engine as opposed to copying & modifying the expression
    eg:-

    Code:
    strSQL = "INSERT INTO tblHistory (SetID, HistoryAction, " & _
            "HistoryActionComments, HistoryDate, HistoryDateRecord) " & _
            "VALUES (' " & strSetID & " ', ' " & strAction & " ', " & _
            " 'No reason given', ' " & dateInput & " ', ' " & Format(LDate, "yyyy-mm-dd") & " ' )"
    CurrentDb.Execute strSQL
    then:-
    either set a break point and examine what happnes
    OR
    repeat the SQL using a message box eg:-
    msgbox "Trying" & vbcrlf strSQL
    say set ID was "ABC12345", strAction was "PUSH"
    youd see immediately
    Code:
    ....VALUES (' ABC12345 ', ' PUSH ', ............
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2013
    Posts
    81
    Thanks for that healdem, that helps.

    I think I had initially assigned the SQL to a variable (based on an example I found online), but then found a other examples which didn't assisgn a variable. Its really useful to understand why assigning the variable is a good idea.

    One question with assigning variables. I have 3 versions of the INSERT query in one bit of coding (associated with an on_click event). Each time you click the button only one of those three queries will be run. Based on this does each of the three INSERT queries need its own variable name (i.e. strSQL1, strSQL2....) or could you Dim a single variable name (e.g. strSQL) for use with all three queries (if that makes sense).

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nah just use a single variable
    if you need to use more than one variable then do so, the only reason I can think why you would if is say you were doing a transaction and building more than one SQL statement concurrently.

    as ever with this things there isn't a single right (or wrong answer), some of its down to style (or lack of), some of its down to bitter experience, some of it is because thats the way that person does things. one thing I would also suggest is if you've done something fiendish clever in code comment it, because next time you or someone else revisits that code they will spend hours trying to work out what was going on in in your mind

    but be aware of the limitations of code found here or online. sometimes there is not enough time to fully 'bullet proof' code, cover every option, bear in mind that you usually get a response that meets your question, but unless you question is throughly explained then the response may not meet your needs. the response is only as good as your question
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2013
    Posts
    81
    Brilliant thanks. I may just go back and add variables to my SQL coding, having said that if it could slow things down a bit, I may be better of as I am. Our computers are getting on a bit now and don't always cope very well (particularly when we've forgotten to feed the hamsters again lol).

    Thanks for your comments healdem I'm hoping that I'm explaining my questions reasonably well. But I'm bound to miss things out, simply due to lack of knowledge. I really appreciate any and all advice I recieve here (and elsewhere). If the code works I'm happy, I figure I can always work on bullet proofing it later on when I've learned a bit more.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •