recordset navigation "off by one" problem

  • Thread starter Thread starter tariss via AccessMonster.com
  • Start date Start date
T

tariss via AccessMonster.com

I have a main form with a tab control on it. The first tab has a record navigation control at the bottom. When I use the control to move to the next record I see the form fields change accordingly. On the second tab I have a subform which displays records in datasheet view based on an ID from the record in the first tab.

The problem is that the subform is also used as a stand-alone form so I'm using a procedure to force the subform requery rather than having it linked directly to the main-form. I make a call to the procedure in the subform from the mainform OnCurrent event. This all works fine. In the procedure I have the following line to get the unique key for the record displayed on the mainform

nPatientID = Me.Parent.Recordset("PatientID")

unfortunately the PatientID that is returned is not the one that corresponds to the record displayed on the mainform, but rather the previous record that was displayed. I can navigate forward and backward with the nav buttons on the mainform and the value returned with the above call is ALWAYS that of the previous record.

I'm sure I'm just not understanding something about how the recordset navigation works.

Any help would be much appreciated.

thanks
tariss
 
Drop the "Recordset" bit:
nPatienID = Me!PatentID
What is nPatientID? Field? Control? Variable?

Presumably there is nothing in the LinkMasterFields/LinkChildFields of the
subform?

An alternative might be to set the RecordSource of the subform in the
Current event of the main form:

Private Sub Form_Current()
Dim strSql As String
If IsNull(Me.PatientID) Then
strSQL = "SELECT * FROM Table2 WHERE (False);"
Else
strSQL = "SELECT * FROM Table2 WHERE (PatientID = " &
Me.PatientID & ");"
End If
With Me.[sub1]
.Form.RecordSource = strSQL
.LinkMasteFields = vbNullString
.LinkChildFields = vbNullString
End With
End Sub
 
tariss via AccessMonster.com said:
I have a main form with a tab control on it. The first tab has a
record navigation control at the bottom. When I use the control to
move to the next record I see the form fields change accordingly. On
the second tab I have a subform which displays records in datasheet
view based on an ID from the record in the first tab.

The problem is that the subform is also used as a stand-alone form so
I'm using a procedure to force the subform requery rather than having
it linked directly to the main-form. I make a call to the procedure
in the subform from the mainform OnCurrent event. This all works
fine. In the procedure I have the following line to get the unique
key for the record displayed on the mainform

nPatientID = Me.Parent.Recordset("PatientID")

unfortunately the PatientID that is returned is not the one that
corresponds to the record displayed on the mainform, but rather the
previous record that was displayed. I can navigate forward and
backward with the nav buttons on the mainform and the value returned
with the above call is ALWAYS that of the previous record.

I'm sure I'm just not understanding something about how the recordset
navigation works.

Any help would be much appreciated.

thanks
tariss
Click to expand...

I haven't investigated this, but my first guess would be that in the
form's Current event, its Recordset hasn't yet been fully advanced to
the new record (though that seems counterintuituve to me). But why use
the form's Recordset property at all; why not get the value from the
form itself, just writing this:

nPatientID = Me.Parent!PatientID

?
 
Allen,

I am essentially do this. I call a public procedure from the main form Current event. The procedure I call is in the sub form and in that procedure I make the following calls (nPatientID is a variable which is then parsed into an SQL statement ):


nPatientID = Me.Parent.Recordset("PatientID")
Me!AppointmentsDetail.Form.RecordSource = mstrSQL

And you are right, I'm not using LinkMasterFields/LinkChildFields.

it's weird.
 
Yes, you're right! I just put a message box to display the recordset values in the main forms Current event and it does show that the recordset hasn't advanced, even though the fields in the form are clearly changed. So the problem has nothing to do with the subform issue, but rather that the main form's Current event is firing but the recordset hasn't actually advanced yet.

So now the question is what to do about it?

thannks in advance for the help guys

tariss
 
I don't get the value from the form directly because the PatientID isn't actually displayed on the form. It's only a unique key for the table. I suppose I could make it an invisible textbox if I have to.

tariss
 
tariss via AccessMonster.com said:
I don't get the value from the form directly because the PatientID
isn't actually displayed on the form. It's only a unique key for the
table. I suppose I could make it an invisible textbox if I have to.

tariss
Click to expand...

You shouldn't have to display it on the form; the value that *would* be
displayed on the form if you had a control bound to it is still
available as a property of the form, and present as an AccessField
object in the form's Controls collection.

Why don't you try what Allen and I have both suggested you do, and write

nPatientID = Me.Parent!PatientID

in your subform procedure? It's odd, though, because I am not finding
the same "lagging recordset" phenomenon on a simple test form I'm using
(Access 2002). So I can't claim to know what's really going on in your
form.

If necessary, as you say you could put an invisible text box on the
parent form and refer to that.
 
Ok I think I understand now ... well sort of.

I tried writing it like you both suggested,

nPatientID = Me.Parent!PatientID

and it works. Thanks.

What I don't understand is why these two versions give different answers?

This one gives me the current record
nPatientID = Me.Parent!PatientID

while this one gives me the previous record, even though they're both called from the Current event of the main form.
nPatientID = Me.Parent.Recordset("PatientID")

Thank you both for the help. This has solved my problem. If anyone can help me understand the reason these are different I'd very much appreciate it. I clearly have some conceptual misunderstanding of how recordsets work.

thanks again.
tariss
 
It's a timing issue. The Recordset has not updated yet, but reading the
value from the form (or its actual Recordsource) does give the correct
answer.

The Recordset property was added to Access about the same time that *many*
unfixed bugs were introduced. Going directly always gives better results.
 
Back
Top