[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

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

0
mcs26
Asked:
mcs26
  • 9
  • 4
1 Solution
 
mbizupCommented:
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

0
 
mbizupCommented:
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.
0
 
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mbizupCommented:
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
0
 
mbizupCommented:
Are you able to upload a sample with just the relevant forms/tables?  (Mask or remove any sensitive data)
0
 
mbizupCommented:
Also try revising your code like this:

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

0
 
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.

Cheers,
forms---code.doc
0
 
mbizupCommented:
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


0
 
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
0
 
mbizupCommented:
Are you able to upload your .mdb file (with no sensitive data)?  That would make it easier to spot something we are missing here...
0
 
mbizupCommented:
<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?
0
 
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.
dbFX-1Min-db.mdb
0
 
mbizupCommented:
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).
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now