Form won't requery

rede96 · May 14, 2015

I have a continuous form who's record source us a query I use for summarising data in the form detail.

In the form header there are some controls for the user to add new data. Usually this opens a pop up form, where the user will add the new data then I updates the underlying tables using code.

When the pop up form closes, I use Forms!Form.Requery to requery the main form so the new data shows in the main form detail.

The problem is doesn't! The user has to wait until he adds more data, when the previous data will then show or I put in a manual button for the user to reqery the form.

I've been trying to resolve this for a while bit not found a solution that works consistently. Also, I only really noticed the problem after I split the database. But it is a problem I have encountered before.

The database is saved as an Access 2000 version.

If anyone could help me solve this, it would be much appreciated!!!

pbaldy · May 15, 2015

It sounds like you may need to force the popup's data to save before requerying the form. Presuming you're doing it from the popup

If Me.Dirty Then Me.Dirty = False

rede96 · May 15, 2015

pbaldy said:
It sounds like you may need to force the popup's data to save before requerying the form. Presuming you're doing it from the popup

If Me.Dirty Then Me.Dirty = False

Thanks for the reply. As I am updating the tables directly using code, e.g. DAO.Recordset method, I assumed rst.update would update the tables immediately. The pop up form is just for organising the data input and is unbound.

I just think it is strange how I can put the requery code at the end of sub routines in the pop form, in the GotFocus event of the main form, even after each recordset update and nothing works.

But I manually click a command button on the main form with the simple line Me.Requery and hey presto, the form refreshes with the latest data added.

I have added Me.Requery in the OnTimerEvent of the main form, which does work. But not on the first cycle. (which is triggered when the pop up form closes) But it does work on the second cycle. (Timer is set to 10000, so every 10 seconds)

The only problem with this is the screen flicker is quite bad, so not ideal but at least it updates.

But I'd really like to figure out why it worked OK when I first started to design the database, but now doesn't. Strange!

spikepl · May 15, 2015

Show code. Entire subroutines, not snippets. If not event handlers then also show calling code.

I use Forms!Form.Requery

You haven't called your form Form, have you?

rede96 · May 15, 2015

spikepl said:
Show code. Entire subroutines, not snippets. If not event handlers then also show calling code.

You haven't called your form Form, have you?

No, the form is called 'FrmMain'.

It is a bit difficult to post all the code, as I have 4 different modules as well as various other code in some of the pop up forms.

But below is an example of some code I run directly from the main form. There are a couple of varibles passed to this code through 2 input boxes, but other wise it runs from the main form.

Code:

Public Sub AddNextReel()
'*** Set Variables
Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Dim strSQL As String
Dim MetersMade As String
Dim LastSets As Integer
Dim NewSets As Integer
Dim eShiftMRMeters As Long
Dim aShiftMRMeters As Long
Dim aCOType As String
Dim aCOTimeStd As Double
Dim aCOTimeActual As Double
Dim LastMeters As String
Dim NextMeters As String
Dim eFlags As Integer
Dim aFlags As Integer
Dim TempStartDate As Date
Dim TempEndDate As Date
Dim aEventCodeID As Integer
Dim eProductionDataID As Integer
Dim eProductionEventID As Integer
Dim NewMRMeters As String

'*** transfer the public variable to local in case used again on shift change ***
MetersMade = xFinishedMeters

'***Check how many meters on new mother reel
CheckMRMeters
If xCancelEvent = 1 Then
    xCancelEvent = 0
    Forms!FrmMain.TimerInterval = 10000
    OnTimerEvent
    SaveData
    Exit Sub
End If

'*** transfer the public variable to local in case used again on shift change ***
NewMRMeters = xMRMeters

'*** Set Edit Variables for Event and Reel ***
eProductionDataID = Forms!FrmMain!txtProductionDataID
eProductionEventID = Forms!FrmMain!txtProductionEventID
    
    
'*** Do Calcs ***
If xShiftChange = 0 Then


    '*** Get Standard time & sets from last reel ***
    If MetersMade > 0 Then 'Check to make sure meters have been made
        xStandardTimeLast = GetStandardTime(Forms!FrmMain!txtStdSpeed, Forms!FrmMain!txtSetSize, Forms!FrmMain!txtNumberAcross, _
        MetersMade, Forms!FrmMain!txtMetersOver, Forms!FrmMain!txtCustomerTape) + Forms!FrmMain!VBMotherReelChange + Forms!FrmMain!txtCOTimeStd
        LastSets = xNumberOfSets
        eShiftMRMeters = Forms!FrmMain!txtMotherReelSize 'Set the ShiftMR to mother reel size if metersmade > 0
    Else
        xStandardTimeLast = 0
        LastSets = 0
        eShiftMRMeters = 0
    End If
    
    
    '*** Get standard time & sets for new job
    xStandardTimeNew = GetStandardTime(Forms!FrmMain!txtStdSpeed, Forms!FrmMain!txtSetSize, Forms!FrmMain!txtNumberAcross, _
    NewMRMeters, Forms!FrmMain!txtMetersOver, Forms!FrmMain!txtCustomerTape) + Forms!FrmMain!VBMotherReelChange
    NewSets = xNumberOfSets
    
    '*** Edit the recordset here ***
    strSQL = "SELECT TblProductionData.*, TblProductionEvent.* FROM TblProductionData " & _
    "INNER JOIN TblProductionEvent ON TblProductionData.ProductionDataID=TblProductionEvent.[ProductionDataIDE] " & _
    "WHERE (((TblProductionData.ProductionDataID)=" & eProductionDataID & ") AND " & _
    "((TblProductionEvent.ProductionEventID)=" & eProductionEventID & "));"
    Set rst = db.OpenRecordset(strSQL)
    rst.Edit
    rst!MetersProduced = MetersMade
    rst!ShiftMRSize = eShiftMRMeters
    rst!NumberOfSets = LastSets
    rst!Flags = Forms!FrmMain!txtFlags
    rst!FlagTime = Forms!FrmMain!txtFlagTime
    If IsNull(Forms!FrmMain!txtComments) Then
    rst!Comments = ""
    Else
    rst!Comments = Forms!FrmMain!txtComments
    End If
    rst!StandardTime = xStandardTimeLast
    rst!EventEnd = TimeStamp
    rst!EventDuration = (TimeStamp - Forms!FrmMain!txtEventStart) * 1440
    rst!EventSpeed = Forms!FrmMain!txtEventSpeed
    rst!TimeStamp = TimeStamp
    rst.Update
    rst.Close
    
    '*** Add New Data to ProductionData
    Set rst = db.OpenRecordset("TblProductionData")
    rst.AddNew
    rst!OrderNumber = Forms!FrmMain!txtOrderNumber
    rst!MaterialNumber = Forms!FrmMain!txtMaterialNumber
    rst!MetersProduced = 0
    rst!ReelNumber = Forms!FrmMain!txtReelNumber + 1
    rst!MotherReelSize = NewMRMeters
    Forms!FrmMain!txtMetersLeft = NewMRMeters
    rst!ShiftMRSize = NewMRMeters
    rst!SetSize = Forms!FrmMain!txtSetSize
    rst!NumberOfSets = NewSets
    rst!NumberAcross = Forms!FrmMain!txtNumberAcross
    rst!StdSpeed = Forms!FrmMain!txtStdSpeed
    Forms!FrmMain!txtEventSpeed = Forms!FrmMain!txtStdSpeed
    'rst Flags will defauls to 0 on new reel but need to change form
    rst!MachineName = Forms!FrmMain!txtMachineName
    rst!Department = Forms!FrmMain!txtDepartment
    rst!Comments = ""
    rst!StandardTime = xStandardTimeNew
    rst!COType = 0
    rst!COTimeStd = 0
    rst!COTimeActual = 0
    rst!ShiftDate = xShiftDate ' New Shift
    rst!ShiftNumber = xShiftNumber 'New Shift
    '***Set Other Variables
    eProductionDataID = rst!ProductionDataID
    Forms!FrmMain!txtProductionDataID = eProductionDataID 'Always update the MainFrm with new ID's
    rst.Update
    rst.Close
    
    '***Add Next Event Details
    Set rst = db.OpenRecordset("TblProductionEvent")
    rst.AddNew
    rst!ProductionDataIDE = eProductionDataID
    rst!EventCodeID = 1
    Forms!FrmMain!txtEventCodeID = 1
    Forms!FrmMain!txtMachineStatus = 1
    rst!EventStart = TimeStamp
    Forms!FrmMain!txtEventStart = TimeStamp
    rst!EventSpeed = Forms!FrmMain!txtStdSpeed
    Forms!FrmMain!txtEventSpeed = Forms!FrmMain!txtStdSpeed
    rst!OperatorName = Forms!FrmMain!txtOperatorName
    rst!TimeStamp = TimeStamp
    'rst EventEnd and EventDuration will default
    '***Set Other Variables
    Forms!FrmMain!txtProductionEventID = rst!ProductionEventID 'Always update the MainFrm with new ID's
    rst.Update
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    
     '***Add Shift Meters to Shift Performance
    Forms!FrmMain!txtShiftMeters = Forms!FrmMain!txtShiftMeters + MetersMade


Else ' Else Shift Change

'*** Left this part out as it is very long and not really relevent to the problem

    
End If ' end of calcs

'*** Reset the time counters
Forms!FrmMain!txtReelDuration = 0
Forms!FrmMain!txtEventDuration = 0

'*** Restart the timer, save unbound data to temp table and then Requery form.
Forms!FrmMain.TimerInterval = 10000
SaveData
Me.Requery
End Sub

spikepl · May 15, 2015

and the rest of the story? We still do not read minds.

So:

1. That code updates the display OK or not? If OK, then what is the problem using that code (i.e. what do you want instead and why)?

2. How is that code related to your pop-up story outlined in #1?

Update.

For this type of stuff:

Code:

    '*** Add New Data to ProductionData
    Set rst = db.OpenRecordset("TblProductionData")
    rst.AddNew
    rst!OrderNumber = Forms!FrmMain!txtOrderNumber
...
    '***Set Other Variables
    eProductionDataID = rst!ProductionDataID
    Forms!FrmMain!txtProductionDataID = eProductionDataID 'Always update the MainFrm with new ID's
    rst.Update
    rst.Close

I normally do

Code:

    '
    Set rst = db.OpenRecordset("SELECT * FROM TblProductionData WHERE 1=0")
    rst.AddNew
    rst!OrderNumber = Forms!FrmMain!txtOrderNumber
...
    '***Set Other Variables
    eProductionDataID = rst!ProductionDataID
    Forms!FrmMain!txtProductionDataID = eProductionDataID 'Always update the MainFrm with new ID's
    rst.Update
    rst.Close

Think about what the difference is.

rede96 · May 15, 2015

spikepl said:
and the rest of the story? We still do not read minds.

We don't need to go through anything else, the code I have detailed causes the problem, so solve this and we solve the rest!

spikepl said:
1. That code updates the display OK or not? If OK, then what is the problem using that code (i.e. what do you want instead and why)?

No it does not display the changes. After I update, the new data does not show in the main form unless I manually requery using a Command button or I put a me.requery in the timer event, in which case the new data will show after the timerevent has triggered 2 or 3 times.

spikepl said:
2. How is that code related to your pop-up story outlined in #1?

Forget about the pop up for now. If we can fix the problem with the code I have given you then I would assume it will fix it for any update made.

spikepl said:
I normally do

Code:

Set rst = db.OpenRecordset("SELECT * FROM TblProductionData WHERE 1=0")

Ok, can try that, but all it does is stop any records from populating the recordset. I have no problem with the new data not saving. Even when the form does not display the new data, I can open the relvent tables and see the new data there. It just does not update in the form unless I do the things I mentioned.

The_Doc_Man · May 15, 2015

If the recordset you are using to update the data happens to open the same query as the one used by the form, then the flags you used to open the recordset could make a big difference. By any chance is the backend file NOT an Access database, but something else like SQL or ORACLE or MySQL? If so, there is a potential "handshake" latency associated with that situation that might affect how long you must wait before the BE is ready to accept the .Requery operation. I'm thinking about optimistic vs. pessimistic locking and also a couple of issues related to PassThrough and Consistent updates.

I'm also interested in your statement that an OnTimer of 10 seconds causes a bad screen flicker for a .Requery, which I don't think should be happening for simple refreshes. I've run OnTimer updates faster than that and saw no screen flicker. When you do that .Requery is there a lot of code involved in Before_Update events? Because a requery when the form is dirty will probably trigger those events. That could cause a big delay, too.

I'll also add a gentle reminder that we ask questions because things are not clear enough to us to solve your problem - probably because we haven't been living for several days or weeks with it like you have. Your response showing as #7 in this thread is just a bit snippy. We are trying to help but until we can wrap our minds around the problem, that might not be so easy for us. Forgive us if we ask the question that we think will help us figure out your problem and you don't immediately see it as being helpful.

spikepl · May 15, 2015

You are not making this easy, and I have a very short fuse, so this is my last attempt.

We don't need to go through anything else, the code I have detailed causes the problem, so solve this and we solve the rest!

We DO need to go through something. Please tell the FULL story about what is going on, like to a complete idiot.

I still have no clue what is happening here.

Which data is the data that is should be updated in the display and isn't, tblProduction Data?
What is the content of the subs called?
There is some temp table in play - does it have anything to do with this and if so, what?
If this code is called on frmMain, why are the controls on the form referred to by Forms!FrmMain!ControlName and not Me!ControlName?

As to the last query - I threw you a bone to think about, not to solve your requery problem. Simply forget it.

JHB · May 15, 2015

Is Forms!FrmMain and Me the same form?

Solo712 · May 15, 2015

Try:

Code:

Set rst = db.OpenRecordset("TblProductionData", [COLOR=darkred]dbOpenDynaset[/COLOR])

Best,
Jiri

rede96 · May 15, 2015

The_Doc_Man said:
If the recordset you are using to update the data happens to open the same query as the one used by the form, then the flags you used to open the recordset could make a big difference. By any chance is the backend file NOT an Access database, but something else like SQL or ORACLE or MySQL? If so, there is a potential "handshake" latency associated with that situation that might affect how long you must wait before the BE is ready to accept the .Requery operation. I'm thinking about optimistic vs. pessimistic locking and also a couple of issues related to PassThrough and Consistent updates.

Hi Doc_Man, thanks for the reply.

The backend is an access database, it is the same database I originally made, I just spilt the tables out as eventually the FE and BE will sit on servers.

The query in the main form's record source is based on the same tables that are updated in the recordset yes. The two tables in question (Which is all I am updating) are linked by a one to many relationship. The query in the record source for the main form is based on these two tables, it just groups by and sums some data to display for the user once he has updated the last event.

So I am not sure if that is a problem or not, it is a bit out of my depth.

The_Doc_Man said:
I'm also interested in your statement that an OnTimer of 10 seconds causes a bad screen flicker for a .Requery, which I don't think should be happening for simple refreshes. I've run OnTimer updates faster than that and saw no screen flicker. When you do that .Requery is there a lot of code involved in Before_Update events? Because a requery when the form is dirty will probably trigger those events. That could cause a big delay, too.

The ontimer code is quite small. It just calculates the duration in time for 3 different controls in the form, so it is just 12 or so lines long. I keep the procedure that is run from the OnTimer event in a different module, where it is called from there.

But all I can tell you is that if I requery as part of the timer event, the screen flickers. If I don't then there is no screen flicker.

The_Doc_Man said:
I'll also add a gentle reminder that we ask questions because things are not clear enough to us to solve your problem - probably because we haven't been living for several days or weeks with it like you have. Your response showing as #7 in this thread is just a bit snippy. We are trying to help but until we can wrap our minds around the problem, that might not be so easy for us. Forgive us if we ask the question that we think will help us figure out your problem and you don't immediately see it as being helpful.

lol, sorry! I can assure you I never mean any disrespect to anyone that takes the time to help. And I try not to read anything into responses either.

But I forget sometimes how things can be interpreted in these forums, so will try and word my responses better. But to all who have answered... please forgive me, no offence meant

My Mrs would have a giggle at that, she is always telling me off for being to 'Sharp' & 'factual'

rede96 · May 15, 2015

JHB said:
Is Forms!FrmMain and Me the same form?

Yes it is. I try and avoid using me in case I move the code at a later date into a module. So don't know which is best.

rede96 · May 15, 2015

spikepl said:
Please tell the FULL story about what is going on, like to a complete idiot.

I still have no clue what is happening here.

Which data is the data that is should be updated in the display and isn't, tblProduction Data?

What is the content of the subs called?

There is some temp table in play - does it have anything to do with this and if so, what?

If this code is called on frmMain, why are the controls on the form referred to by Forms!FrmMain!ControlName and not Me!ControlName?

Ok very simply, I have two tables which have a one-to-many relationship, tblProductionData and tblProductionEvent. They are linked on the ProductionDataID field which is the primary key in the tblProducitonData.

I summarise and display the data in these two tables in a query which is the record source for the main form (‘FrmMain’)

When a user wants to add new data or update current data he does so by pressing command buttons in the main form which open up either a pop up form or dialogue boxes for the user to enter data.

Any new/edited data is then updated to the two tables using the DAO.Recordset method, where I also do some other calculations. So the code is either in the pop up form or a module called by the main form.

None of the other forms are bound, they are just used to organising data easily for the user.

So when all code is run and updates the two tables, I want to see the new data displayed in the main form. But for some reason it won't display it unless I mess around with things.

I thought a simple requery would do, but it doesn’t seem to work.

As for your bullet points:

Which data is the data that is should be updated in the display and isn't, tblProduction Data?

- The specfic data are any feilds where the data has changed as part of the user updating. It isn't any specific field, just what ever ones change.
What is the content of the subs called?

- Sorry not sure what you mean here.
There is some temp table in play - does it have anything to do with this and if so, what?

- I am not aware of any temp table in play? Sorry if I miss-undstood this.
If this code is called on frmMain, why are the controls on the form referred to by Forms!FrmMain!ControlName and not Me!ControlName?

- I always try and refer to a COntroName by the full reference in case I have to move the code.

I really wasn't trying to be difficult, so sorry if it came across that way. Just not sure what else I can tell you.

The_Doc_Man · May 15, 2015

Let me offer advice "on the side." I won't use the word "never" here, but when you are dealing with data tailored to a form, the "Me" construct is appropriate and you would only very rarely want to move this code because it is probably too specific to apply other forms unless they were identical in all particulars to the original form. Which makes me wonder why you wouldn't get the first form working and then just copy/paste that form to a new name and edit the parts that need editing. I use a "prototype" form that contains all of my special code and form-specific routines. When I want a new form because I have a new table, I clone the form (and its class module).

But OK, let's say you HAD to move the code... make the function / sub call include the form as an Access.Form object in the formal parameter list - and then when you call the routine, make the ME argument the actual parameter for the form variable. There are speed advantages to the Me.Requery method vs. other ways of doing a requery.

Not that I think it is significantly slowing you down THAT much, but going through the Forms collection adds overhead. I would recommend you consider keeping things together that belong together. A form and its class module is a case in point where you really want to keep those things together. Because of the overhead of calling a common routine (which updates all sorts of things that you can VIEW later), keeping things close is easier.

rede96 · May 15, 2015

The_Doc_Man said:
Let me offer advice "on the side." I won't use the word "never" here, but when you are dealing with data tailored to a form, the "Me" construct is appropriate and you would only very rarely want to move this code because it is probably too specific to apply other forms unless they were identical in all particulars to the original form. Which makes me wonder why you wouldn't get the first form working and then just copy/paste that form to a new name and edit the parts that need editing. I use a "prototype" form that contains all of my special code and form-specific routines. When I want a new form because I have a new table, I clone the form (and its class module).

But OK, let's say you HAD to move the code... make the function / sub call include the form as an Access.Form object in the formal parameter list - and then when you call the routine, make the ME argument the actual parameter for the form variable. There are speed advantages to the Me.Requery method vs. other ways of doing a requery.

Not that I think it is significantly slowing you down THAT much, but going through the Forms collection adds overhead. I would recommend you consider keeping things together that belong together. A form and its class module is a case in point where you really want to keep those things together. Because of the overhead of calling a common routine (which updates all sorts of things that you can VIEW later), keeping things close is easier.

Ok, thanks for that, makes sense. As you can probably tell I am self-taught so there are a lot of gaps in my knowledge and I always appreciate advice! Thanks.

For now I have got around the problem by putting some code in the ontimer event that requires for the first 4 times the timer event is fired after an update. This seems to do the trick.

Although it is interesting to see on which iteration it actually updates as it isn’t always the same????

Anyway, thanks to all for your input.

rede96 · May 15, 2015

Solo712 said:
Try:

Code:

Set rst = db.OpenRecordset("TblProductionData", [COLOR=darkred]dbOpenDynaset[/COLOR])

Best,
Jiri

Hi and thanks for that. I did change my code, but it didn't sort it. But thanks anyway.

JHB · May 15, 2015

When do the below code come into play, I ask because you're leaving the procedure/ sub without a form requery?

Code:

  [FONT=&quot]If xCancelEvent = 1 Then
    xCancelEvent = 0
    Forms!FrmMain.TimerInterval = 10000
    OnTimerEvent
    SaveData
    [B][COLOR=Red]Exit Sub[/COLOR][/B]
End If
[/FONT]

The use of Me is always to be preferred compared to the full reference!

rede96 · May 15, 2015

JHB said:
When do the below code come into play, I ask because you're leaving the procedure/ sub without a form requery?

Code:

[FONT=&quot]If xCancelEvent = 1 Then xCancelEvent = 0 Forms!FrmMain.TimerInterval = 10000 OnTimerEvent SaveData [B][COLOR=Red]Exit Sub[/COLOR][/B] End If [/FONT]

The use of Me is always to be preferred compared to the full reference!

The procedure is to exit the sub if the user cancels any inputs. In which case there is no update so no requery needed.

I also changed from full references to Me, so thanks for that.

I have looked over my code a dozen times, even if I strip away everything and just do an update via code, the form never displays the new data. I have tried different record sources, e.g. SQL, saved query and even trying to set it at run time, but if doesn't work either.

So I am baffled!!

pbaldy · May 15, 2015

Shot in the dark, but try adding

DoEvents

between the save and the requery.

Form won't requery

Registered User.

Wino Moderator

Registered User.

Eledittingent Beliped

Registered User.

Eledittingent Beliped

Registered User.

Immoderate Moderator

Eledittingent Beliped

Have been here a while

Registered User.

Registered User.

Registered User.

Registered User.

Immoderate Moderator

Registered User.

Registered User.

Have been here a while

Registered User.

Wino Moderator

Similar threads

Users who are viewing this thread