Using the most current data in VBA (1 Viewer)

GregoryWest · Jul 27, 2019

I have some VBA code that opens a form gets some user information from the user (this is all working great) and returns to VBA.

Here is where I am having problems! I need to make sure VBA is looking at the most current data available, NOT the data when VBA started. On the query on the calling form references the critical table, but I am always getting the data as it was when the VBA started not the current data. How can I force access to reread the tables and not use cached data?

Greg

CJ_London · Jul 27, 2019

your guess is as good as mine without any idea of what your code is doing at the moment.

Best guess is to repeat whatever you are doing when VBA 'starts'

If this is not sufficient, provide your code and use the code tags to preserve the indentation. Also if necessary explain what the code is supposed to be doing and show where you have the issue

theDBguy · Jul 27, 2019

Hi Greg. Welcome to the forum. Have you tried doing a Requery?

GregoryWest · Jul 27, 2019

Tries doing a requery. Did not seem to change much. Here is the code:
Private Sub Command14_Click()
Dim rs_in As dao.Recordset
Dim strSql_in As String
Dim rs_out As dao.Recordset
Dim strSql_out As String

DoCmd.OpenForm FormName:="GetClient", View:=acNormal, WindowMode:=acWindowNormal
strSql_in = "SELECT * FROM localvar;"
Set rs_in = DBEngine(0)(0).OpenRecordset(strSql_in)
rs_in.Requery
If rs_in!searchedclient <> 0 Then
strSql_out = "SELECT * FROM parcelowner;"
Set rs_out = DBEngine(0)(0).OpenRecordset(strSql_out)
rs_out.AddNew
rs_out!munino = Me.munino
rs_out!rollnotype = "R"
rs_out!rollno = Me.rollno
rs_out!recordtype = " "
rs_out!clientno = rs_in!searchedclient
rs_out!parcelclient = Mid(Me.RLID, 1, 10) & Mid(Me.RLID, 12, 3)
rs_out!created = Now()
rs_out!active = True
rs_out!lastupdate = Now()
rs_out!RollLinkID = Me.RLID

rs_out.Update
rs_out.Close
End If
rs_in.Close
End Sub

The form GetClient changes two fields in the table localvar. The field I am particularaly interested in is searchedclient. when the command rs_out!clientno = rs_in!searchedclient the value I get is the value of searchedclient before the for GetClient was called.

Greg

CJ_London · Jul 27, 2019

see you are using DBEngine - that might not be current, see this link https://sourcedaddy.com/ms-access/the-currentdb-function.html

better to use currentdb or set a db variable

either way, when you open the GetClient form, code in the open, load, current events will run then, unless that form is modal, the code will continue in your click event.

And please use code tags - if you can't be bothered to take a second to do so - why should we be bothered trying to understand it?

Pat Hartman · Jul 27, 2019

The code that follows the open form does not wait for the form to close so it would be using whatever data is in the table before the form updates it.

If you want the code to stop until the form is closed, open the form as a popup. The code will stop and restart when the form closes and control is returned to the calling procedure.

GregoryWest · Jul 27, 2019

CJ_London said:
see you are using DBEngine - that might not be current, see this link https://sourcedaddy.com/ms-access/the-currentdb-function.html

And please use code tags - if you can't be bothered to take a second to do so - why should we be bothered trying to understand it?

Sorry about the lack of code tags. It is not a case of being lazy, it is a case of not knowing how.....

How do I use code tags?

GregoryWest · Jul 27, 2019

Pat Hartman said:
The code that follows the open form does not wait for the form to close so it would be using whatever data is in the table before the form updates it.

If you want the code to stop until the form is closed, open the form as a popup. The code will stop and restart when the form closes and control is returned to the calling procedure.

That explains a lot of the issues I am having. How do I open the form as a popup instead of just a window like I have now.

arnelgp · Jul 27, 2019

before the Sub ending, requery the Form:

Code:

…
...
rs_out.Update
rs_out.Close
End If
rs_in.Close
[COLOR="Blue"]Forms(""GetClient").Requery[/COLOR]
End Sub

GregoryWest · Jul 27, 2019

Looks like opening the form as a Dialog works! Did not even know about this. Guess you learn something every day!

theDBguy · Jul 27, 2019

GregoryWest said:
Looks like opening the form as a Dialog works! Did not even know about this. Guess you learn something every day!

Hi Greg. Glad to hear you got it sorted out. Dialog mode pauses your VBA until the form is closed or hidden. Good luck with your project.

isladogs · Jul 27, 2019

GregoryWest said:
Sorry about the lack of code tags. It is not a case of being lazy, it is a case of not knowing how.....

How do I use code tags?

Click the # button on the toolbar and paste your code between the tags that appear

Pat Hartman · Jul 27, 2019

Sorry about the nomenclature. We often refer to the forms as popups but the argument is dialog. Glad you worked it out.

Using the most current data in VBA (1 Viewer)

GregoryWest

Registered User.

CJ_London

Super Moderator

theDBguy

I’m here to help

GregoryWest

Registered User.

CJ_London

Super Moderator

Pat Hartman

Super Moderator

GregoryWest

Registered User.

GregoryWest

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

GregoryWest

Registered User.

theDBguy

I’m here to help

isladogs

MVP / VIP

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread