asked on

Access VBA Refresh Table from with Userform

Hi,

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));"

    OpenConnection
    
    Set rs = New ADODB.Recordset
    rs.Open strSQL, cn, adOpenKeyset
    
        If rs.EOF = False Then
            rs.MoveFirst
            ReDim Securities(1 To rs.RecordCount)
                For i = 1 To UBound(Securities)
                    Securities(i) = rs![Security]
                    rs.MoveNext
                Next
            
            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"
        Else
            
            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

mbizup

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

mbizup

Alternatively:

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.

mcs26

ASKER

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

mbizup

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

mbizup

Are you able to upload a sample with just the relevant forms/tables? (Mask or remove any sensitive data)

mbizup

Also try revising your code like this:

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

mcs26

ASKER

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.

Cheers,
forms---code.doc

mbizup

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

mcs26

ASKER

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

mbizup

Are you able to upload your .mdb file (with no sensitive data)? That would make it easier to spot something we are missing here...

mbizup

<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?

mcs26

ASKER

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.
dbFX-1Min-db.mdb

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial