Go to record on open

[SueK]

Hi,
I know this has been asked before, but:
I have a form (form A) which uses a hyperlink feild to open one of three
other forms (form B, form C, or form D).
I need a way to open form B to the record I'm looking at on form A.

I assume it's a filter on the on open event, but for the life of me I can
get anything to work.

I'm particularly versed in visual basic or building expressions... so the
previous reply for the only simlar question I've found says use a Where or a
filter.... but I can't seem to get it to work - I suspect I need a slightly
more detailed answer.

I'm using Access 2000.
Thanks.

 

[Jeff Boyce]

Sue

You've described a "how", as in how you are trying to solve some
issue/business need... but we don't know about that business need.

It may be that there are other ways to solve the business need that wouldn't
require opening multiple sequential forms.

If you're game, consider describing more specifics about what having
multiple sequential forms opened will allow you (or your users) to do...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SueK]

Okay....
I have a form called SeqInfo. It holds a set of laboratory data for a sample
(each sample is a record - there are ~30,000 records).

Each sample also has datasets from other labs. The data from each lab is
text file data (For example: one text file will have the data for 96 samples
- so it's easy for me to turn them into forms with records for each sample).

Thus I have a form for the data from each lab, and would like to keep the
data separate, because there is a lot of it. (Forms: DLabAccession,
GLabAccession, and MLabAccession).

The nature of the data is such that a subform is cumbersome - there is too
much from each lab for it to work well in a subform; and I only need to
reference that data in certian situations, so I'd rather have a sequential
form to look at. My current form already has 3 subforms, containing the stuff
I need to see most often and takes up the screen. Adding more subforms makes
looking at the data hard. (But I'm sick of manually cross-referencing my
samples from each lab).

I would like to be able to click on a hyperlink in the SeqInfo form and open
the specific record in DLabAccession,GLabAccession, or MLabAccession.

I do not want to use a a button, since some of these samples have records
from all three labs, some have records from no labs, and some have records
from only one or two labs. Though a button would work; but I'd have to use a
button for each lab, and that seems like a waste - esp if I have to click and
open the form from each lab find out which samples were done by which lab.
I don't want to see the button/hyperlink if the lab hasn't done the work.

All SeqInfo has a field that can be used to look up each of the other forms.
For instance:
name_seqlong in MLabAccession is the same as the field Sequence_name in Seq
Info. Note: it's a different field which connects GLabAccession and SeqInfo.

I can make a list of hyperlinks to each form easily, and in fact already
have a listing of hyperlinks that connects the samples to the various
pictures, excel files, and analysis specific files I have for each sample on
the computer. (I learned early on that pictures & files are better as
hyperlinks.)

So a hyperlink seems to be the best fit, but only if, for example, I can
write a filter that occurs on the record sets when the form GLabAccession is
opened that automatically opens to the record currently open on the form
SeqInfo.

-Sue

 

[SueK]

Sorry about the wording of the reply I got interupted while writting it - so
it sounds more convoluted then it is.

 

[Jeff Boyce]

Sue

Yes, it did!<g>

If you are the only user and are comfortable with the "cascading forms"
approach, consider creating as many queries as you need to feed the forms
you want to have pop open, and in each query, use a reference to the
appropriate "parent" form that holds the ID you want used. In a query, in
the Selection Criterion "cell" under the ID field, you can use something
like:

Forms!YourFormName!YourIDFieldName

(substituting your actual form and field names).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SueK]

One more time and a bit more coherant perhaps.

I've got something half working...

Form A [SeqInfo] has a hyperlink that opens Form B [DLabAccessions]
Form B has a macro that during the on open event filters to show Form A:
Action Apply Filter
Where Condition
[Forms]![DLabAccessions]![name_seqlong]=[Forms]![SeqInfo]![Seq_name]

which is not Ideal... I'd rather show all the records and just have it open
to the correct one but can't figure out the code to get it to work.

However
The filter only works on the first record.... and not any of the other
records... they all come up as blank forms (even if I go back and cut and
paste the feild I need from one form to the other)

Help.

I'm willing to entertain solutions that don't involve a filter... like an On
Open Event in form B that allows me to goto the record that matches the where
codition above. I realize the Docmd.GoToRecord requires a record number, and
I can't figure out how to write code that will do that - so I've gone the
filter direction instead.

Thanks.
-SueK