absolute position on subform or better way?

[newuser44]

Here's the issue:
I am working on a medical database that, let's say for the purposes of this,
has 2 tables (Abnormalities and Biopsy). Currently the two tables are not
linked because there is no common id between them that has been established,
other than medical record number (MRN). MRN is not sufficient as patients
can multiple abnormalities or multiple biopsies and I would like to be able
to link these tables so that a biopsy is linked to a specific abnormality.

What I thought to do was to generate a form that brings in the data from
both tables using a combobox that looks up all patients with a biopsy and
then brings in the corresponding list of abnormalities based on a matching
medical record number. I thought then that the user could view the
abnormalities and enter the corresponding biopsy id onto the subform. This
was working well enough, but I would like to make this less prone to user
entry error and so thought that I might use a button to run some code which
would automatically enter the biopsy id on the subform.

The problem I found with this however, is that the user needed to click on
the record on the subform to which they wanted to update the biopsy id field.
I would like to have a series of buttons that would work without having to
first click the subform record. I thought I could use .absoluteposition, but
this only seems to work after the user has clicked on the subform record.

Is there a way in the code to identify the 1st, 2nd, 3rd record in the
subform datasheet view, and then run code based on that position, i.e. I
would have a button that would update data for row1 into row1, a button that
would update data for row2 into row2, etc. The user would then click on
whatever series of buttons e.g. 1, 3, 4 to append the biopsy id to subform
rows 1, 3, and 4.

Any advice?

thanks!

 

[Arvin Meyer [MVP]]

There is no absolute position. You could number all the records in the table
with an autonumber and sort by the autonumber, and run your append code on
the click event of the form. But how do you plan getting the correct records
into the subform?

 

[newuser44]

My combobox brings in brings in records that have a matching medical record
number, so it appears that the correctly abnormalities are viewing on the
subform.

So, is it not possible to essentially code:

If subform record position = x then do ...

Alternatively, is there a code equivalent to clicking on the 2nd, 3rd, or x
row in the subform datasheet?

thanks!

 

[Arvin Meyer [MVP]]

Yes, look up SelStart, SelHeight, SelWidth, SelTop, SelLength, and SelText
properties in the help files.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com