Access VBA Refresh Table from with Userform


I have a userform which contains a subform which is a table of different currency pairs and another column which the user clicks to tick the box if they wish to select that currency pair or untick to deselect that currency pair.

When the Ok button is clicked by the user I have the code below. When the userform loads if the table had say two currency pairs selected last time it will load up with both currency pairs selected. The issue I have is that if one is unselected the code below will select it, so it basically seems like the table is not updated straight away after the user clicks.

Any help would be great. thanks

Private Sub cmdOk_Click()

Dim i As Integer

    'run button
    strSQL = "SELECT [_Securities].* " & _
                "FROM [_Securities] " & _
                "WHERE ((([_Securities].[Select])=Yes));"

    Set rs = New ADODB.Recordset
    rs.Open strSQL, cn, adOpenKeyset
        If rs.EOF = False Then
            ReDim Securities(1 To rs.RecordCount)
                For i = 1 To UBound(Securities)
                    Securities(i) = rs![Security]
            rs.Close: Set rs = Nothing
            cn.Close: Set cn = Nothing
            With Me
                If .chkIndicator_Output = True Then IndicatorOutput = True
                If .chkSynthetic_Bars = True Then SyntheticBars = True
                If .chk5Min = True Then Run5Min = True
                If .chk15Min = True Then Run15Min = True
                If .chk30Min = True Then Run30Min = True
                If .chk60Min = True Then Run60Min = True
            End With
            DoCmd.Close acForm, "frmSecurities"
            DoCmd.OpenForm "frmCashValues"
            Set rs = Nothing
            cn.Close: Set cn = Nothing
            MsgBox "No securities selected", vbInformation, "Select Securities"
            Exit Sub
        End If

End Sub

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try adding a refresh immediately before your Select statement is created:

Private Sub cmdOk_Click()

Dim i As Integer

  Me.Refresh '<--- *** Add this here.

Open in new window


RunCommand acCmdSaveRecord

Open in new window

The point is that the value is not committed to the table until it is actually saved, which occurs when the user changes records or leaves the form.  In a case like this without any user action causing the record to get saved, you need to force a save through code.
mcs26Author Commented:
Hi mbizup,

Thanks for the replies. Have tried both your suggestions but the issue still seams to remain. Is there a way to refresh the underlying table?

Thanks again
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

The code should work for updating the value in the table - as long as it is placed correctly.

When does the user click the checkbox(?) in relation to when the code you posted gets executed?

Another thing you can try is forcing the record save in the Click or AfterUpdare event of the checkbox using the same code:

docmd.RunCommand acCmdSaveRecord
Are you able to upload a sample with just the relevant forms/tables?  (Mask or remove any sensitive data)
Also try revising your code like this:

                "WHERE ((([_Securities].[Select])=True));"

mcs26Author Commented:
I've attached a word document outlining how it works. All the code is in my first post. The code is in the Ok button of my userform. If the word document is not clear please let me know.

Did you try the suggestion I posted last - forcing the record save as soon as the user clicks the "Select" checkbox?

This would mean placing the code in the Click or AfterUpdate event of the checkbox in your subform:

Docmd.RunCommand  acCmdSaveRecord

mcs26Author Commented:
Sorry should have explained it's not actually a checkbox is a field in table (Yes/No) so I do not know if there is such an event for it? But yes i did try your suggestion.

thanks again
Are you able to upload your .mdb file (with no sensitive data)?  That would make it easier to spot something we are missing here...
<it's not actually a checkbox is a field in table (Yes/No) >

So the user edits data directly in the table, not through your subform?
mcs26Author Commented:
I've attached the database. There are some linked table in there which obivoulsy will not work but they shouldn't have any impact on the form. Yes the user edits data directly in the table not through the subform.
If I'm understanding you correctly, the user opens the table directly, checks or unchecks the Select field, and then either that user or another user clicks the "OK" button.

That direct data entry into a table is generally not advisable for reasons such as this - you have no control through event code over the data being entered, and you can't follow up data entry with event procedures to "do stuff" such as processing the input through Event Procedures.  You can get the same visual effect by using a form in datasheet view, with a lot more control over what happens when the user enters data.

That said, in order to refresh your subform data so that it reflects the data changes in your table, you could use the following code at the very beginning of your OK button's Click Event:
    Me.[_Securities subform].Requery

The data of course will not be visible in the subform instantaneously, but with your current setup, there really is no way to do that.  The alternatives are to requery the subform in some event procedure, or close and reopen your form (which will also refresh what is seen).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.