how can I use unique ID no. not record no to automatically open a

[ColinR]

I know that there is no connection between the unique ID no and the record
number. I have set up a membership data base and use the unique ID No as the
membership number, which is great.
Because there is a family membership which meand two different uniqu ID Nos,
no problem. I need to be able to switch from one form to the other related
form and back etc by using a command button. I had set this up using Macro
Goto and the record No whcih works OK but when a memebrship is deleted it
throws out all the record numbers correlation to the Unique ID No and it
fails.
How can I do this by using the unique ID NO (and not the find option)

 

[John W. Vinson]

I know that there is no connection between the unique ID no and the record
number. I have set up a membership data base and use the unique ID No as the
membership number, which is great.
Because there is a family membership which meand two different uniqu ID Nos,
no problem. I need to be able to switch from one form to the other related
form and back etc by using a command button. I had set this up using Macro
Goto and the record No whcih works OK but when a memebrship is deleted it
throws out all the record numbers correlation to the Unique ID No and it
fails.
How can I do this by using the unique ID NO (and not the find option)

What "correlation"? I hope you're not assuming that you can use Autonumbers in
two tables and have them stay in synch, because they won't!! Autonumbers are
meaningless and arbitrary, and CANNOT be used to link two tables.

What is in fact the structure of your tables? Why are there two unique ID's
and where are they located?

Generally autonumber ID's should be hidden from the user; they should never
even *see* them, much less enter them for searching. They really aren't
suitable for member numbers - they will develop gaps, and can become random
(you really don't want to have to deal with member numbers like 1423865221 or
-838992164). You can use a Combo Box on a form (using the toolbox combo box
wizard) to find a record, allowing the user to select a member name or a
family name and have Access jump to the record for that person. Would this
meet your need?

John W. Vinson [MVP]

 

[ColinR]

Thanks,
I do not have two tables, simply one. I sue the unique id no as the
membership number and now have some worries following your comment thta the
ID number allocated by acess is unstable. I had noted that there were gaps
thanks for that.
Bedcause I could not do it any other way I have used the report number at
the bottom of the form generated by access to utilise the goto function in a
macro, so I have created a relationship between the id number and the report
no which works fine until I remove a member then the report numbers all
change.
I use [Forms]![Member List1]![Member ListID]=128 (The ID NO)
GoToRecord and insert the record number in the goto box at the bottom

I hope that this helps yuo understand a bit better.

Colin