Is there a way to know where the bookmark in a DAO.recordset is?

[Niklas Östergren]

Hi!

Is there a way to know where a bookmark in a DAO.Recordset is?

I have opened a form (frmMemberRegistration) used a where clause (created by
an array) to open just the records I´d like to show. This where clause is
created from an array so I don´t know how many records there will be in the
recordset of the form from time to time. The underlying table´s name is
tblMember.

This form is one of 5 form´s in a wizard and I create records in several
tables on the fly before I open form´s showing what have been created. For
each tables where records are created I store the primary key´s in arrays so
I can filter form´s showing only the newly created records. This is what´s
been done here when I open this form frmMemberRegistration.

On this form I have an unbound textcontrol where I lookup the members name
so the user know which member the records displayed on the form belongs to.

Before I open this form I have created one or several records in tblPerson.
Also here I have stored the primary keys in a global array
glngarrFamilyPersonID ().

The first record in frmMemberRegistration belongs to person with primary key
glngarrFamilyPersonID (0), the second record belongs to person with primary
key glngarrFamilyPersonID (1) and so on.

Now to my Q:
Since the relationship isn´t created yet between tblPerson and tblMember I
can´´t use DLookUp or any other way to get the correct FirstName and
LastName to be displayed in the textcontrol on form frmMemberRegistration.
The only thing I have is the array holding the primary key´s and the sort
order the underlying recordset of the form is corresponding to each other.

I know, like I said, that the first and last records in the forms
recordsource belongs to the first and last index of the array
glngarrFamilyPersonID. But how do I get the rest of the records in the
underlying forms recordsource? I don´t know how many records there may be
accept if I use UBound(glngarrFamilyPersonID). But I still don´t know if I´m
in record No 2,3 or 4 in the underlying recordset of form
frmMemberRegistration.

Any help would be highly appreciated!

TIA!
// Niklas

 

[Guest]

Just a thought not fully fleshed out, but what if you had another variable
array that is set equal to the recordset index nbr. and reference that
variable the same way you reference the name variables.

 

[Niklas Östergren]

Hmm, I don´t quit follow you here!

Please try to explane a little bit more how you are thinking!

// Niklas

 

[Niklas Östergren]

What I could do is to create a 2 dimensional array when I´m creating the
records in tblMember where I store not only the primary key, which I´m
storing no, but also storing MemberNo.

But I don´t know how to populate a 2 dimensional array and neither how to
get the stored values when I need them again.

If I just can figure out how to do this then I can compare MemberNo with the
one showed on control txtMemberNo on the form. And since the array holding
MemberNo and primary key is created in the same sort order as the array
holding primary key for tblPerson (Names, DoB etc.) then I can compare the
two indexes and get the correct name. Or am I totally out of line here?

TIA!
// Niklas

 

[Dirk Goldgar]

Hi!

Is there a way to know where a bookmark in a DAO.Recordset is?

I have opened a form (frmMemberRegistration) used a where clause
(created by an array) to open just the records I´d like to show. This
where clause is created from an array so I don´t know how many
records there will be in the recordset of the form from time to time.
The underlying table´s name is tblMember.

This form is one of 5 form´s in a wizard and I create records in
several tables on the fly before I open form´s showing what have been
created. For each tables where records are created I store the
primary key´s in arrays so I can filter form´s showing only the newly
created records. This is what´s been done here when I open this form
frmMemberRegistration.

On this form I have an unbound textcontrol where I lookup the members
name so the user know which member the records displayed on the form
belongs to.

Before I open this form I have created one or several records in
tblPerson. Also here I have stored the primary keys in a global array
glngarrFamilyPersonID ().

The first record in frmMemberRegistration belongs to person with
primary key glngarrFamilyPersonID (0), the second record belongs to
person with primary key glngarrFamilyPersonID (1) and so on.

Now to my Q:
Since the relationship isn´t created yet between tblPerson and
tblMember I can´´t use DLookUp or any other way to get the correct
FirstName and LastName to be displayed in the textcontrol on form
frmMemberRegistration. The only thing I have is the array holding the
primary key´s and the sort order the underlying recordset of the form
is corresponding to each other.

I know, like I said, that the first and last records in the forms
recordsource belongs to the first and last index of the array
glngarrFamilyPersonID. But how do I get the rest of the records in the
underlying forms recordsource? I don´t know how many records there
may be accept if I use UBound(glngarrFamilyPersonID). But I still
don´t know if I´m in record No 2,3 or 4 in the underlying recordset
of form frmMemberRegistration.

Any help would be highly appreciated!

TIA!
// Niklas

Let me see if I understand you, Niklas. Your problem is that you need
to look up, from tblPerson, the FirstName and LastName of the record
that corresponds to the tblMember record currently being displayed on
frmMemberRegistration, but the *only* way of relating the records is by
the ordinal position of the record in the form's recordset,
corresponding to the element in array glngarrFamilyPersonID? That seems
very strange. Why is there no field in tblMember that is a foreign key
into tblPerson, such that the records are naturally related? You're
certainly going to need such a field sooner or later.

In the mean time, to answer your specific question, there are two
properties that can give you the information I think you are looking
for. One of these is the CurrentRecord property of the form itself;
e.g.,

Forms!frmMemberRegistration.CurrentRecord

or

Me.CurrentRecord ' for code on the form itself

That's the record number as it would be shown in the built-in navigation
buttons. For the form's first record, its value is 1, for the second,
its value is 2, and so on. The "new record" has a number that is one
more than the total number of records in the form's recordset.

The other property is the AbsolutePosition property of the form's
recordset; e.g.,

Forms!frmMemberRegistration.Recordset.AbsolutePosition

or

Me.Recordset.AbsolutePosition ' for code on the form itself

This property is zero-based, so the first record in the recordset has an
AbsolutePosition of 0.

Presumably, you can -- if you must -- use one of these two properties to
give you what you want. But beware of the user imposing a filter or
sort order on the form! That will bollix up the supposed ordinal
relationship between the form and the array.

 

[Niklas Östergren]

Thank´s for an exelent answer! I will try this out tomorrow. menawhile I´ll
answere the Q I knew was comming up after my Q.

Well, infact I DO have a relation between these to tables. It look´s like
this:

tblPerson.................tblMemberValidation................tblMember
PersonID (1------M) fkPersonID
..................................fkMemberID (M------------1) MemberID

Where PersonID and MemberID are autonumber primary key.

I have alot of other tables related to tblmember and tblMember needs to have
a record to be able to add a related record in tblMemberValidation. And the
working order is to first add a record in tblPerson and then a record in
tblmember and last (in these example anyway) a record in
tblMemberValidation.

And the user can finish the wizard at any time and each table has it´s own
form. So at this time, for which my Q is valid, I don´t have any record
created in tblmember yet and definately a record in tblMemberValidation. I
hope this asnwer your Q!?

After a lot of work done with this application I realise that I should have
implemented the fields which I now have in tblmember in tblPerson. But not
all records in tblPerson has a related record in tblMemberValidation since
not all persons are a member of the assosiation.

I have to think of this a little bit more because maby, if this isn´t going
to work or if it´s going to give me some major problem later on then I HAVE
to redisigne the table design.

Right now it does work though but if you can se any other problem than what
I have tryed to explane in my Q (first msg) I´d be happy (I think ;-) ) to
here about it.

Thank´s a lot for helping out! I have, during the afternoon read about
absolute position of recordset and I think that could be used as well!?

Best reguards!
// Niklas

 

[Niklas Östergren]

Sorry Dirk!

I was in a stressed situation when I read and answered your msg.
AbsolutePosition was one of the solutions that you helped with be. The funny
was that I actually, just an hour before have read about this so that´s why
I answered the way I did.

It´s late here so I havn´t had time to try it out though but I´m sure this
is one solution to my problem. So thank you very much for helping out and
merry christmas!

// Niklas

 

[Niklas Östergren]

Good morning Dirk!

I have tested it and it works just fine.

Regarding "But beware of the user imposing a filter or sort order on the
form!": I will not let the user apply any filter or sorting option on this
form. This form is ONLY going to be used in the wizard. But thank´s for the
warning!

Thanks a lot again for your help Dirk!

// Niklas