Data availability when a from loads

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello. I am working with a .adp project using SQL Server and Access 2003.
What I am trying to do sounds so simple, but I am having a problem and I hope
someone will be able to assist. What I want to do is have a form load and,
upon doing so, have a particular record displayed. I open the form using
DoCmd.Openform strFormName, but don't want to set a filter - I want all
records available. So I pass a key (LocationID) as an OpenArgs, and use code
similar to this in the Form_Load() event for the form.

If Len(Me.OpenArgs) = 0 the Exit Sub
Dim lngLocationID As Long
lngLocationID = CLng(Me.OpenArgs)
Me.txtLocationID.SetFocus
DoCmd.FindRecord intLocationID

This code works, sometimes it does not. It will ALWAYS work if I set a
breakpoint on any line in the code. Because this code is in the Form_Load()
event, it appears that without a breakpoint, the data in the form (which
comes from a SQL Server stored procedure) is not all available when the find
occurs, so it only works if that particular record happens to be available.
I have tried putting a DoEvents before the find, but this does not work. I
can find no reliable way to insure all records are available before the find
happens. I have tried moving this to the Form_Activate() event and even the
Form_Current() event and get the same result. Is there any way I can prevent
the DoCmd.findrecord from happening untiil the recordsource is completely
available to the form? I greatly appreciate any help. Regards.
 
Virtualjay,

Just a stab in the dark, as I haven't heard of this problem before, but
you could try this, as it might force it to load all records before
going to the FindRecord...

If Len(Me.OpenArgs) > 0 Then
DoCmd.GoToRecord , , acLast
Me.txtLocationID.SetFocus
DoCmd.FindRecord CLng(Me.OpenArgs)
End If
 
Steve,

Thanks so much, that worked! Never have I felt both so stupid and so
relieved at the same time. I would be embarassed to tell you how many things
I had tried and how much time I had spent with no satisfaction. So just let
me say a very big thank you. I hope Santa treats you well! Regards, jay
 
Back
Top