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

    Question Unanswered: Access 03 VBA and SQL query

    Hi All, its the pest again. I'm having a bit of trouble writing an update query (SQL) in VBA. The update query itself works beautifully, but I'm hoping I can make it a little bit more clever.

    I've got 2 forms (linked to 2 tables) frmSet (linked to tblSet) and subfrmWeightA (linked to tblWeight. The two forms are linked via the Set ID PK from tblSet. The basis is each Set (in tblSet) can contain main weights (in tblWeight), if you tell the system that the set has gone missing, I need the system to automatically update tblWeight to show the weights as also being missing. This bit I've got to work. What I also want to do is put a comment into the Set's comments box and each of the weights comment box saying when the weight went missing.

    I've got a bit of code to update the set comments box (without ovewriting any exisiting comments) as follows:

    Code:
     'prompt user to enter date set went missing
        LDate = Date
        strInput = InputBox("Enter date the set went missing", "Set Missing", LDate)
    'prompt user to enter details of loss
        strInputReason = InputBox("Please note any comments regarding the loss of set " & Me.setID, "Set Missing")
        Me.SetComments = Me.SetComments & " " & "Date missing: " & strInput & " - " & strInputReason & "."
    I would like to use similar code within my SQL update query to update all of the weight comments boxes without overwiting any exisiting information. My current code (see below) enters the same comments into the Weight comments box as is entered into the set comments box, but it overwrites any exisiting data. I haven't yet worked out how to alter the code so it will add data to the field not replace.

    Code:
    'now find all weights linked to this set and update to show as "Missing" also enter date set went missing into weight comments box  (need to do the same update on return to service - awaiting response from DB forum
    ' first set strSetID and strWeightComments
        strSetID = Forms!frmSet!subfrmWeightA!setID.Value
        strWeightComments = Me.WeightComments & "Date Set Missing: " & strInput & " - " & strInputReason & "."
    ' create UPDATE query to check missing box on all weights with same set ID
         SQL = "UPDATE tblWeight " & _
         "SET tblWeight.[WeightMissing] = True AND tblWeight.[WeightComments] = ('" & strWeightComments & "')" & _
         "WHERE tblWeight.[SetID] = ('" & strSetID & "')"
         DoCmd.RunSQL SQL
    My usual methods of identifying the field to be altered (i.e. Me.WeightComments or Forms!frmSet!subfrmWeightA!WeightComments) do't work (presumable because the update query doesn't open the individual records as such) and I haven't been able to find any information which even hints at how to do this. Everything I've found seems to relate to adding whole records rather than editing and adding data to an exisiting record field.

    Any suggestions would be greatly appreciated (otherwise if it isn't possible, please let me know).

    Ooh forgot to add, the Dims for the above code are:

    Code:
    Dim strInput As String
    Dim LDate As Date
    Dim strInputReason As String
    Dim strSetID As String
    Dim SQL As String
    Dim strWeightComments As String

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think your SQL is off, that you want a comma rather than AND, like

    SET Field1 = True, Field2 = 123

    To add comments to the field, you'd do this type of thing:

    ...[WeightComments] = [WeightComments] & ('" & strWeightComments & "')" & _
    Paul

  3. #3
    Join Date
    Jun 2013
    Posts
    81
    Thats works brilliantly, thank you so much for your help I really appreciate it.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

  5. #5
    Join Date
    Jun 2013
    Posts
    81
    I've got a second query relating to the SQL code I've used.

    All through my code I have a series of input boxes. I've set the code so that if the user clicks the cancel button on the input box the VBA code pretty much tells Access to reset whatever it was doing. For example when clicking set missing, the system asks for a date, if you click cancel, it sets the missing checkbox as false and exits the sub.

    Now that I've got the SQL code updating several records simultaneously you get a message asking if your sure you want to change the records. If you click no you get a runtime error stating the DoCmd was canceled. However, the set is still put as missing.

    Is it possible to bind VBA code to the "no I don't want to update these records" which prevents the query from updating records, resets any other actions taken and doesn't result in an error code.

    Presumably its similar to the code I use elsewhere:

    Code:
    If StrPtr(strInputReason) = 0 Then
    Followed by the instructions i.e. set checkbox as true and exit sub. But what should strInputReason be changed to (N.B. strInputReason is used in the code earlier for an Input Box, instructing the user to enter data)

    Any suggestions would be greatly appreciated.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not clear on the issue, but personally I don't use the built-in "are you sure" SQL warnings. If I want to let the user confirm, I put up a Yes/No message box, and if they say yes I execute the SQL without any warnings. Gives me more control over what goes on. More info on warnings if appropriate:

    SQL Warnings
    Paul

  7. #7
    Join Date
    Jun 2013
    Posts
    81
    Oh right thanks, I didn't realise you could do that. Thanks for the link I'll give it a go see if I can work it out.

    Thanks for your help, I really appreciate it.

  8. #8
    Join Date
    Jun 2013
    Posts
    81
    Sucess, it works. I've managed to get the system to run the SQL code without warnings (thanks to your CurrentDb.Execute advice) and prompt a message box to warn users than cannot undo changes to associated records

    For future reference the code I've used is

    Code:
    'warn users this action will update records and you cannot undo changes with undo command plus option to cancel
    SQLResponse = MsgBox("This action will update several records.  Once you click yes you cannot use the undo command to reverse the " & _
    "changes.  Are you sure you want to update these records?", vbYesNo)
    
    'if user is happy to make changes
    If SQLResponse = vbYes Then
    
        Me.SetComments = Me.SetComments & " " & "Date missing: " & strInput & " "
        'also update all associated weights to show as missing
        'first set strSetID, strWeightCommentsA
        strSetID = Forms!frmSet!subfrmWeightA!setID.Value
        strWeightCommentsA = "Date Set Missing: " & strInput & " "
        'create update query to check missing box on all weights with the same Set ID and add the date missing
         'to the weight comments box
         
         CurrentDb.Execute _
               "UPDATE tblWeight " & _
               "SET tblWeight.[WeightMissing] = True, tblWeight.[WeightComments] = [WeightComments] & ('" & strWeightCommentsA & "')" & _
                "WHERE tblWeight.[SetID] = ('" & strSetID & "')"
          Exit Sub
    
    'if user isn't happy to make changes, uncheck missing box and exit sub
    Else
    Me.SetMissing = False
    End If
    Many thanks for your help, I really appreciate it. Such a simple little thing, but almost impossible when you don't know how.

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!

Posting Permissions

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