Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jun 2013
    Posts
    81

    Question Unanswered: MS Access 03 - UPDATE query in VBA

    Hi All, I would appreciate it if someone could advise if this is possible. I would like to run an update query which gives the user the option of removing particular records from the query before it runs (if that makes sense). For example, after triggering the query a box pops up showing the following records:

    Record No, Date, Officer No, Weight Quantity
    1 22/2/14 959 10g
    2 22/2/14 959 20g
    3 22/2/14 959 50g
    4 22/2/14 959 100g
    5 22/2/14 959 200g

    The user can then look at the list and opt to carry out the update for records 3 - 5 only. After making the selection the update query is triggered, updating the date to (for example) 28/2/14 for records 3 - 5.

    Logically the first step would be a select query with perhaps a checkbox for each record which the user can use to select the appropriate records. Quite what you would do after that is beyond me at the moment (if it is even possible).

    Thanks in advance. Sam

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Use a listbox with it's MultiSelect property set to Simple (1 in VBA), so that the user can select several rows in the list.
    2. Set the RowSource property of the listbox either to the name of the query that returns the data set in your example or to a SQL sentence returning the same data set (SELECT [Record No], [Date], [Officer No], [Weight Quantity] FROM ...).
    3. Place a command button (cmd_OK) on the form to allow the user to confirm his/her choice and link its OnClick event to this code:
    Code:
    Private sub cms_OK_OnClick()
    
        Dim varItem As Variant
        Dim strList As String
        Dim strSQL As String
        
        For Each varItem In Me.ListName.ItemsSelected
    	If Len(strList) > 0 then strList = strList & ","
    	strList = strList & Me.ListName.ItemData(varItem)
        Next varItem
        If Len(strList) > 0 Then
            strSQL = "UPDATE ... SET ... WHERE [Record No] IN (" & strList & ");"
            CurrentDb.Execute strSQL, dbFailOnError
        End If
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jun 2013
    Posts
    81
    Thanks for that Sinndho I really appreciate your help.

    Just to make sure I've understood this right, heres how I think your suggestion would work:
    1. User enters a new test result date in the test result box (text box, linked to table)
    2. VBA triggers a msgbox asking the user to ID whether they want to update one record, several records or cancel
    3. the user opts to update many records
    4. VBA triggers a form to pop up with the list box,
    5. user selects whichever records they want to update and click OK (cmdButton)
    6. VBA behind cmdButton, triggers update query based on the selections made (thanks for the code you've written)


    Theoretically the intial lot of VBA code would exit the sub after triggering the pop up form with list. Obviously where a msgbox is in use you cannot access any other part of the database (good idea really), but once you've completed the sub the freeze (so to speak) would end, even though (in this case) your only part way through the full procedure. I understand there is a way of essentially mimicking the 'freeze'. Should the code to minic the freeze be under the pop up form or the initial lot of VBA? Logically I would expect to include it (presuming I can find it again) with the pop up form. Then again I could be talking complete rubbish and not really need the 'freeze' at all lol. what can I say its been a really long few days.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. You can open a "pop up" form (i.e. a from that "freezes" the execution of the code anywhere but in its own module: such a form is said to be modal) by passing a parameter to the Docmd.Openform method. The full syntax of this method is:
    Code:
    DoCmd.OpenForm "FormName", DisplayMode, "FilterName", "WhereCondition", DataMode, WindowMode, "OpenArg"
    All the parameters are optional, except the first (FormName). See in Access help for a full explanation. To open a modal form, you use (beware of the numer of commas):
    Code:
    DoCmd.OpenForm "FormName",,,,, acDialog
    2. As the modal form will be the one to perform the query UPDATE, it needs to know what value to update (more exactly, it needs to know the new value). You can use the last argument of the DoCmd.OpenForm method (i.e. OpenArg) to pass that value to the modal form. From what I understand in what you posted, it should be something like (adapt to the actual names in your project):
    Code:
    DoCmd.OpenForm "FormName",,,,, acDialog, Me.Controls("test result date").Value
    3. When the modal form opens, it will first save the OpenArg argument received:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_strArgument As String
    
    Private Sub Form_Open(Cancel As Integer)
    
        m_strArgument = Nz(Me.OpenArgs, "")
        If Len(m_strArgument) = 0 Then 
            Cancel = True ' No argument received --> cannot continue.
        Else
            '  If the column to update is not numeric, now is a good time to format it properly.
            ' e.g. for a string: 
            m_strArgument = "'" & m_strArgument & "'"
        End If
    
    End Sub
    4. The event handler for the command button now becomes (again, adapt to the actual names in your project):
    Code:
    Private sub cms_OK_OnClick()
    
        Const c_SQL As String = "UPDATE TableName SET ColumnName = @A WHERE [Record No] IN (" @L ");"
    
        Dim varItem As Variant
        Dim strList As String
        Dim strSQL As String
        
        For Each varItem In Me.ListName.ItemsSelected
    	If Len(strList) > 0 then strList = strList & ","
    	strList = strList & Me.ListName.ItemData(varItem)
            ' If [Record No] is not numeric, use:
    	' strList = strList & "'" & Me.ListName.ItemData(varItem) & "'"
        Next varItem
        If Len(strList) > 0 Then
            strSQL = Replace(Replace(c_SQL, "@A", m_strArgument), "@L", strList) 
            CurrentDb.Execute strSQL, dbFailOnError
        End If
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Jun 2013
    Posts
    81
    Wow Sinndho you are an absolute star. Thank you so much for taking them time to do that.

    Presumably you can pass multiple open arguments when opening the form. Can I just check I've got the right syntax. I think it would be something like this (picked random control (etc) names):

    Code:
    DoCmd.OpenForm "frmPopUp",,,,, acDialog, Me.Controls("ResultTestDate").Value & Me.Controls("ResultTestDate").OldValue & Me.Controls("SetID").Value
    Would you use the "Controls" part of the code even though the bits (minds gone blank can't think of the word lol) your calling (so to speak) are linked to a form. Lol I think its safe to say I've just demonstrated how blonde I am. To try give an example of what I mean Me.Controls("SetID").Value refers to the primary key SetID from tblSet, (which is also used as a foreign key in pretty much every other table in the database) rather than what I would think of as a control.

    I've read through the code for use with the pop up form and command buttong and I think I follow it (maybe). I've not had chance to test any of the code yet since my workload has suddenly spiked again (why is it you either have masses to do or nothing, but never middle ground). When I've got the bulk of my jobs done I'll have a go. I'm bound to have a few more questions then.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You don't need to use a reference to the Value property of a control if the form is bound and if the desired value is in a field of the form RecordSet. In such a case, you can use:
    Code:
    Me!SetID
    The full syntax (i.e. not using the default reference or property of the objects) being:
    Code:
    Me.RecordSet.Fields("SetID).Value
    Come back for more info if you need to. You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Jun 2013
    Posts
    81
    Brilliant thanks for your help Sinndho, I really appreciate it. If by some miracle I get it working without many problems I'll post on here to let you know.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  9. #9
    Join Date
    Jun 2013
    Posts
    81

    Question

    Hi Sinndho, I've finally got a bit of time to have a play with code and have run into my first problem.

    I've built a pop up form with a list box running off a select query (which I also haven't quite got working as I want yet - question for another time maybe). I've then gone back to the original form and added your suggested code to open the pop up form and pass the open arguments (this is part of the after update event on the ResultDate field). The full code is:
    Code:
    Private Sub ResultDate_AfterUpdate()
    
    Dim UResponse As Integer
    Dim ResultDateOld As Date
    Dim strWeight As String
    Dim strUpdateDateSQL As String
    Dim ResultDateNew As Date
    Dim strSetID As String
    Dim strOpenArg As String
    Dim lOfficerNo As Long
    
    'sub to cascade update to result date (allows for users to change the test date if system entered it
    'incorrectly.  MsgBox to give users the option to cascade the update to all related records (i.e. records
    'made on the same date, and part of the same set) or update only this record or cancel and revert to the
    'original entry
    
    'set record identifiers
    strWeight = Me.weightSerialNumber & " " & Me.WeightQuantity
    
    'ask user whether to cascade update or issue to single record  suggest altering comments to ID exact
    'record e.g. update 500g weight with test date X only or all test results for metric 22 with test date
    'x
    
    UResponse = MsgBox("What do you want to do?" & vbCrLf & vbCrLf & _
    "Update the record for the " & strWeight & " weight only - Click 'Yes'" & vbCrLf & vbCrLf & _
    "Update a selection of test records linked to the record for the " & strWeight & " weight - Click 'No'" & _
    vbCrLf & vbCrLf & _
    "Cancel the update - Click 'Cancel'", _
    vbYesNoCancel Or VbMsgBoxStyle.vbQuestion)
    
    'if the user clicks vbYes (weight only) accept the changes and close
    If UResponse = vbYes Then
        Exit Sub
    'if the user clicks vbNo (cascade update), accept change, create update query to alter related records
    'and close
    ElseIf UResponse = vbNo Then
        'Set variables (updated result date, original result date and set ID)
        ResultDateNew = Me.ResultDate
        Debug.Print ResultDateNew
        ResultDateOld = Me.ResultDate.OldValue
        Debug.Print ResultDateOld
        strSetID = Me.tblWeight_SetID
        Debug.Print strSetID
        lOfficerNo = Me.OfficerNumber
        Debug.Print lOfficerNo
        strOpenArg = Me!tblWeight_SetID & ", " & Me!ResultDate & ", " & ResultDateOld
        Debug.Print strOpenArg
        'open pop up form with list box so use can select what records to alter
        'need to put acdialog back (comma before Me!tblWeight...)
        DoCmd.OpenForm "frmPopUpTestDateUpdate", , , , , acWindowNormal, Me!SetID & Me!ResultDate & ResultDateOld
        
        End If
        Exit Sub
    End Sub
    I've then gone into the form open event in VBA for the pop up form and entered:

    Code:
    Private Sub Form_Open(Cancel As Integer)
    
     m_strArgument = Nz(Me.OpenArgs, "")
     Debug.Print Me.OpenArgs
        If Len(m_strArgument) = 0 Then
            Cancel = True ' No argument received --> cannot continue.
        Else
            '  If the column to update is not numeric, now is a good time to format it properly.
            ' e.g. for a string:
            m_strArgument = "'" & m_strArgument & "'"
        End If
    
    End Sub
    I'm not certain I fully understand what the code is doing quite yet, but for some reason the open arguments aren't passing to the pop up form. Its reading as "Null" in the debug window. I've tried cutting open arguments down to simply "Me!SetID" and got the same result. I've also tried switching to strOpenArg with the same result. Any idea what I've missed?

    I am working on this from my own laptop, which is running Office 2013 (risky I realise since the work computers run Office 2003) - could this have something to do with my issues?

    Thanks

    Sam

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't think Acc2013 is in cause. Check what you're passing:
    Code:
    Debug.Print Me!SetID & Me!ResultDate & ResultDateOld
    DoCmd.OpenForm "frmPopUpTestDateUpdate", , , , , acWindowNormal, Me!SetID & Me!ResultDate & ResultDateOld
    Moreover, if you want to pass several arguments to the form, you need to separate them (e.g. by a ";") and dissociate the string when the form is open:
    Code:
    DoCmd.OpenForm "frmPopUpTestDateUpdate", , , , , acWindowNormal, Me!SetID & ";" & Me!ResultDate & ";" & ResultDateOld
    Then:
    Code:
    '
    ' Declaration section of the class module of the form frmPopUpTestDateUpdate.
    '
    Private m_strArgument As String
    Private m_VarArgument As String
    
    Private Sub Form_Open(Cancel As Integer)
    
     m_strArgument = Nz(Me.OpenArgs, "")
     Debug.Print Me.OpenArgs
        If Len(m_strArgument) = 0 Then
            Cancel = True ' No argument received --> cannot continue.
        Else
            m_varArgument = Split(m_strArgument , ";")
            '
            ' Now SetID is in m_varArgument(0)
            '     ResultDate is in m_varArgument(1)
            '     ResultDateOld is in m_varArgument(2)
        End If
    
    End Sub
    Have a nice day!

  11. #11
    Join Date
    Jun 2013
    Posts
    81
    Thanks Sinndho, must admit I wasn't certain I'd got the multiple arguments listed correctly.

    I've tried your suggested changes. When stepping through the code, the Set ID, result date and result date old show up correctly (in both the immediate window and when hovering over the parameters in the code), yet for some reason its still going through to the pop up form as Null.

    Forgot to say before, once its gone all the way through the code it gives me a run time error 2501: the open form action was cancelled. Debugging takes it back to the DoCmd.OpenForm..... code in the main form. It makes sense it would come up with this error though seeing as the code cancels the application if a null open argument is passed.

    I'm bound to be missing something really daft here, but I haven't a clue where.

    Really appreciate your help with this.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you compile the project without error?
    Have a nice day!

  13. #13
    Join Date
    Jun 2013
    Posts
    81
    Sorry I'm having a thick moment again, not sure what you mean by that.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Open the VBA Editor (Ctrl+G).
    2. Open the Debug menu and select Compile ...
    If there is a syntax error somewhere in the application, it is possible that the form could not be open properly, that's why I'm asking.
    Have a nice day!

  15. #15
    Join Date
    Jun 2013
    Posts
    81
    Oooh didn't know you could do that, that's a really handy little button. Quite helpfully found an example of me failing to Dim one of my variables (in a different bit of code) and an syntax error elsewhere that I've created today (having some issues with an update/inner join query in vba - I'll get there eventually).

    I've re-run the event (after correcting the errors) but the open arguments are still going across as null. However, I have now spotted a strange issue. The pop up window form will only open in design mode, regardless of whether I'm accessing it from the forms list or via the code. When its in full screen you can see something opening (looks like a datasheet view maybe) but within a second its back to design view. I've looked through the form properties and can't see anything obvious.

Posting Permissions

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