forms, Recordsets, and bookmarks

[Thomas Kroljic]

All,
I have a form that contains a subform. The subform list all the client
records for the current user. If the user double-clicks on the record
indicator for a particular client record, the vba logic will 1) store the
bookmark value in a public variable, 2) open another form with the following
logic in the form's OPEN event: copy the record set from the subform to the
new form (this recordset usually contains more than one record), set the
bookmark = to the public variable holding the bookmark, and then final
display the selected (double-clicked record) full client record. (second
form allows the user to still scroll through all the clients records
displaying all the client data)

This works well, but I noticed a problem. If the selected record from the
subform happens to be the first record in the record set, when the user goes
to edit the record after double-clicking on it in the subform to get to the
form displaying all the data, it will not allow the user to edit the record.
They get the following message "Update or CancelUpdate without AddNew or
Edit". If they move to the next record using the navigation arrows on the
bottom of the open form, and then back, to the first record, they can edit
the record. If they select any other record but the first record in the
subform, all works well.

Any thoughts? I'm at a lost.

Thomas J. Kroljic
Thank you.

 

[Dirk Goldgar]

All,
I have a form that contains a subform. The subform list all the client
records for the current user. If the user double-clicks on the record
indicator for a particular client record, the vba logic will 1) store
the bookmark value in a public variable, 2) open another form with
the following logic in the form's OPEN event: copy the record set
from the subform to the new form (this recordset usually contains
more than one record), set the bookmark = to the public variable
holding the bookmark, and then final display the selected
(double-clicked record) full client record. (second form allows the
user to still scroll through all the clients records displaying all
the client data)

This works well, but I noticed a problem. If the selected record from
the subform happens to be the first record in the record set, when
the user goes to edit the record after double-clicking on it in the
subform to get to the form displaying all the data, it will not allow
the user to edit the record. They get the following message "Update
or CancelUpdate without AddNew or Edit". If they move to the next
record using the navigation arrows on the bottom of the open form,
and then back, to the first record, they can edit the record. If they
select any other record but the first record in the subform, all
works well.

Any thoughts? I'm at a lost.

Are you saying that you're setting the detail form's Recordset property
to that of the subform? I have to say this seems like an unnecessarily
elaborate method to accomplish the job of displaying a detail form
corresponding to a specific record listed on a summary form. Your
problem *may* be due to quirks in the internal handling of the Recordset
property.

Did you ensure that the subform record was not dirty when you opened the
detail form?

 

[Thomas Kroljic]

Dirk,
Thanks for your quick reply.

First off, the second form that display the full detail data of the
selected record also has the full recordset of the subform. My client wanted
the ability to scroll between records in the second form without having to
go back to the subform on the first form to select the next client.

I forgot to add that the user has the ability to filter the recordset on
the subform. After the user double-clicks on the specific record, I copy the
subforms recordset to second form and I then apply the some filter before I
set the bookmark.

As for setting the bookmark for the second form, I actually pass the
client id from the subform to the second form via a public variable. When
the second form opens I:
1. assign the recordset from the subform to the second form
2. I apply a filter string from the subform if one exist
3. I create a variable (dim rst as dao.recordset) and set this
to me.recordset
4. I FIND the specific record using the public variable
5. I get the bookmark for this record (move to variable)
6. I assign this bookmark variable to me.bookmark (second form)

Could my problem have something to do with using the Me.Filter = mFilter
and
Me.FilterOn = true statement before I find the book mark?

Thanks for your help.

 

[Dirk Goldgar]

Dirk,
Thanks for your quick reply.

First off, the second form that display the full detail data of
the selected record also has the full recordset of the subform. My
client wanted the ability to scroll between records in the second
form without having to go back to the subform on the first form to
select the next client.

I forgot to add that the user has the ability to filter the
recordset on the subform. After the user double-clicks on the
specific record, I copy the subforms recordset to second form and I
then apply the some filter before I set the bookmark.

As for setting the bookmark for the second form, I actually pass
the client id from the subform to the second form via a public
variable. When the second form opens I:
1. assign the recordset from the subform to the second
form
2. I apply a filter string from the subform if one exist
3. I create a variable (dim rst as dao.recordset) and set
this to me.recordset
4. I FIND the specific record using the public variable
5. I get the bookmark for this record (move to variable)
6. I assign this bookmark variable to me.bookmark
(second form)

Could my problem have something to do with using the Me.Filter =
mFilter and
Me.FilterOn = true statement before I find the book mark?

Possibly, but without seeing all the code I couldn't say -- and even
then, it may be the kind of thing that only careful stepping through the
code can determine.

May I suggest an alternative approach that doesn't involve setting the
second form's recordset? This should work provided that the filter on
the subform doesn't involve lookups:

'---- start of example code for subform button ----
Private Sub cmdOpenDetails_Click()

Const conDetailsForm As String = "DetailsForm"
' Change the literal above to the name of your form.

Dim strFilter As String

' Save the current record, if it has been modified and not saved.
If Me.Dirty Then
Me.Dirty = False
End If

' Get this form's filter, if there is one.
if Me.FilterOn Then
strFilter = Me.Filter
End If

' Open the details form and filter it if need be.
DoCmd.OpenForm conDetailsForm,
WhereCondition:=strFilter

' Find the current record on the details form.
Forms(conDetailsForm).Recordset.FindFirst "ClientID=" & Me.ClientID

End Sub
'---- end of example code for subform button ----

Naturally, change "ClientID" in the above code to the name of the
primary key field in the forms' recordsource.

 

[Thomas Kroljic]

Dirk,
Thanks for the code example. I'll try it out immediately and post my
results back here.
Thanks again.
Thomas J. Kroljic

 

[Thomas Kroljic]

Dirk,
I added the code you suggest. It worked 99%. What I mean by that is
the last line of code - Forms(conDetailsForm).Recordset.FindFirst
"ContactID=" & Me.ContactID - would cause the following error message: "the
microsoft jet database engine coes not recognize the "contactid" as a valid
field name or expression". (the second form would open up and be on the
first record regardless of what record you double-clicked on)
I ensured that "contactid" was indeed included in the query behind the
form. I also placed a text field on the form with this data. ("contactid" is
the primary key to the contact table.)

What I tried that worked, thanks to you, was to use the following line
of code only to the second form in the LOAD event:

With Me
.Recordset.FindFirst "contactid = " &
Form_frmContactLst.ContactID
End With

Once I moved this line of code to the second form, everything worked great.
My original problem is now resolved. And yes, your approach (code) was a lot
easier than what I had coded. Thank you very much.

Follow-up questions:
1. What exactly does the ":=" do? I did notice that by using this
combination of symbols, I wasn't able to add anymore parameters to the docmd
statement. Is this normal?

Again, Thank you very much for showing me an easier way to accomplish my
goal.
Thomas J. Kroljic

 

[Dirk Goldgar]

Dirk,
I added the code you suggest. It worked 99%. What I mean by
that is the last line of code -
Forms(conDetailsForm).Recordset.FindFirst "ContactID=" & Me.ContactID
- would cause the following error message: "the microsoft jet
database engine coes not recognize the "contactid" as a valid field
name or expression". (the second form would open up and be on the
first record regardless of what record you double-clicked on) I
ensured that "contactid" was indeed included in the query behind the
form. I also placed a text field on the form with this data.
("contactid" is the primary key to the contact table.)

Hmm. That works fine for me. What version of Access are you running?
Mine's Access 2002.

You aren't by any chance setting the form's RecordSource in its Open
event, are you?

I suppose it could be a timing problem of some sort -- the form's
recordset not being loaded yet -- but I've never seen it before.

What I tried that worked, thanks to you, was to use the following
line of code only to the second form in the LOAD event:

With Me
.Recordset.FindFirst "contactid = " &
Form_frmContactLst.ContactID
End With

Once I moved this line of code to the second form, everything worked
great.

That's certainly suggestive of a timing problem of some sort. In the
Load event, the form's recordsource has been successfully queried and
the first record (if any) returned.

An alternative to hard-coding the search in the form's Load event would
be to pass the ContactID in the form's OpenArgs. That way, the form
could check for anything passed in OpenArgs. If there is something,
then assume it's a ContactID and find it; if not, then just open to the
first record.

Follow-up questions:
1. What exactly does the ":=" do? I did notice that by using this
combination of symbols, I wasn't able to add anymore parameters to
the docmd statement. Is this normal?

That's a parameter assignment by name. When you write ...

DoCmd.OpenForm conDetailsForm,
WhereCondition:=strFilter

.... you're saying, "pass strFilter to the WhereCondition argument of the
OpenForm method." See the help topic (in the VB Editor help)
"Understanding Named and Optional Arguments".

Again, Thank you very much for showing me an easier way to accomplish
my goal.

You're very welcome.