ReQuery

  • Thread starter Thread starter Al Westerlund
  • Start date Start date
A

Al Westerlund

I have an application that probation agents use to see who
is waiting in the waiting room to report. The
receptionists enter the reporting probationers name using
a form called "report" entering the agents caseload number
and the name of the offender on the report. This goes
into a table named "report". On the agents desktop, they
open up their form called AgentReportScreen by entering
their agent number and then the form is filtered on that
number. The following code is on that form.


Private Sub Form_Load()
Me.TimerInterval = 60000

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim Message, Title As String
Message = "Enter your caseload number."
Title = "Set Filter for Agent Report Screen."

AgentNumber = InputBox(Message, Title)

Me.Filter = "[AgentNum] = '" & AgentNumber & "'"
Me.FilterOn = True

Me.OrderByOn = True
Me.OrderBy = "TimeReported DESC"

End Sub

Private Sub Form_Timer()
Me.Requery
Forms!AgentReportScreen![Name].SetFocus

End Sub

I changed the sort order so that the most recent person
reporting shows up on top of the screen so the agent does
not have to scroll down toward the end of the day to see
who has reported. I also needed to refresh the screen
every 60 seconds so that the filtered form picks up new
entries. The agent keeps the form open all day. I put the
SetFocus code in because some of the fields were going
blank (specifically the name field which is a concatenated
string with last + firs name) and that solved the problem.
The real problem I have is that at times, the requery does
not seem to pick up the latest entries the receptionists
put in the table and people are sitting in the waiting
room as the agent does not know they are there. Am I
using the wrong code to refresh the filtered form and
also, why was the name field going blank? Thanks in
advance.
Al Westerlund
 
The real problem I have is that at times, the requery does
not seem to pick up the latest entries the receptionists
put in the table and people are sitting in the waiting
room as the agent does not know they are there.
Click to expand...

I suspect the problem is at the other end. If the receptionist has
entered the information onto the form, but not moved off the record or
otherwise forced a save, the data will not yet be in the table.

You might want to set up the receptionist's form to encourage an
immediate save (for instance by setting the AutoTab property to True
in the last control in the tab order), or by putting a Timer on that
form to execute an explicit save every 60 seconds:

If Me.Dirty = True Then
Me.Dirty = False
End If

This would cause problems, however, if there are required fields, as
the save might be triggered in the middle of entering the data and
might cause an error message.
 
There are required fields but I know that they are saving
the entries. They then open up the form again and keep it
on the screen so that when the next person reports they
can enter their name, save and open it up again. Keeping
the form open should not be a problem (???) since I have
it set on No Locks. I recently put out a new version to
users with instructions to copy to the C drive so I will
have to see if all of them copied the newest version where
I changed the locking structure. Unfortunately, I do not
have the access to Novell on our network to push out the
application when I make a change and there are 50 + users
that I would have to change myself manually. I am going
to assume the requery code is right and I have some users
I am going to have to check into. If leaving the form
open for the next entry is a problem then I will have to
have them stop doing it that way.
Thanks, Al Westerlund
 
If leaving the form
open for the next entry is a problem then I will have to
have them stop doing it that way.
Click to expand...

It shouldn't be a problem, provided that you do *something* to save
the record. I'm not sure why your other form isn't showing the data,
if you're Refreshing it regularly and the data is in fact saved to the
table. Can you link to the backend from a third database and monitor
manually when the data a) gets to the table and b) to the officer's
screen?
 
I found the problem this morning by opening up the Report
screen for one of my agents who was having a report day.
I have the timer set up to requery the screen every 60
seconds to refresh the data. I also put in some code on
the timer event to setfocus on the name field after I did
the requery. I did this because for some reason, after the
requery, at times the name field and only the name field
(a concatenated field with last and first combined), would
go blank. The agent could get the names to return by
clicking on the form window but it was irritating. The
setfocus on the name field solved that. What is happening
now is that when the Setfocus code is triggered by the
timer and there is no name on the filtered form, it causes
an error message stating "cannot find specified record"
with the option for the agent of clicking on "End"
or "debug" or closing the window with the X button. When
they do this (other than debug of course), the window
stays blank but is frozen. The requery will not trigger
again. The agent has to go out of the form and reopen
it. Once there is one person reporting on the form there
is no problem. So I have to either figure out some other
way of preventing the name from disappearing or find a way
to check for any records before running the setfocus
command. At any rate, thanks for your help.
Al Westerlund
 
So I have to either figure out some other
way of preventing the name from disappearing or find a way
to check for any records before running the setfocus
command.
Click to expand...

Could you just check to see if there is a current record, or if the
textbox is empty?
 
I solved it by doing a Me.refresh after the Me.requery and
it keeps the name from disappearing. Still don't know why
the name would disappear and not other data in the record
but all is well.
Thanks,
Al Westerlund
 
Back
Top