Access, GoToRecord, offset action argument

[Darrell]

I am using Access 2000 and in the GoToRecord function it allows you to put in
an expression for the offset in the action arguments. I want the GoToRecord
to find the record number in a specific cell in one of my tables. What would
be the format of that expression to insert into the offset action argument
box?

Thanks

 

[Steve Schapel]

Darrell,

If I understand you correctly, you are trying to move to a record based
on the value in one of the fields. If so, the Offset argument of a
GoToRecord action is not really applicable, as this relates to the
number of records and not to the data. The FindRecord action may be
more useful for your purposes.

If you are trying to use macros or VBA procedures to navigate around the
actual table datasheet, maybe you could consider using a form instead
for your data management... you will find you have more options.

 

[Darrell]

Steve:

I appreciate your quick response but I am using the GoToRecord in a Macro
and not from a Command Button using the wizard. In the Macro design view it
gives you the option to select Go To in the Record Action Arguments and when
I put in the number 3 in the Offset argument and attach the Macro to a
Command Button, when clicked, the Command Button returns Record #3 in my form
and NOT three (3) records.

What I am actually trying to accomplish is to automate a Find Record by
using the Primary Key ID# in the table. I am doing this because I have
another software program that can download a value or a string to a cell in
my Access table which I want to use (automate) to find the specific record
associated with either the ID# or text in that cell.

 

[Steve Schapel]

Darrell,

Your understanding of the functioning of the GoToRecord macro action is
correct. Accordingly, as I mentioned before, the Offset argument is not
applicable to your situation, as it does not relate to the vallue of the
data, which is what you want to use (i.e. the Primary Key ID#) as the
basis of the record location.

So, have I got this right?... Your form is based on one table. There
is another table into which data is imported from an external
application. This imported data contains a Primary Key ID# field. You
want to identify the value of this field in one of the records in the
imported data, and then locate the record via your form with the same
Primary Key ID#. Correct? Ok, so what is the name of the table with
the imported data, and what is the name of the field containing the
Primary Key ID# data? Is there only one record in this imported data?
If more than one, how are you supposed to identify which one is to be
used as the basis of the finding the record on the form?

Maybe a specific example would help.

 

[Darrell]

Steve:

Again I appreciate your timely response.

My form is based on one table called LinkTrac I/O (actually there are more
tables that have relations to this table but we do not need to address these
at this time). The LinkTrac I/O table has all of the records in it that I am
interested in. This table has a Primary Key TagID AutoNumber in the first
field. I assume that this is the record number also because there can be no
duplicates and is indexed.

The second table is called FindDevice and that table only has a Primary Key
ID# field and a Find Device field. Obviously I can change the Data Type to
either Text or Number for the Find Device Field.

The other software I have is a graphics based control software that can
connect to my Access database and either download a value or a string by
using ODBC and data mining directly to a specific cell in my ccess database
(FindDevice table). I can actually pass information back and forth if I
wanted to.

I alread have a FindRecord Command Button on my form for finding specific
records but that uses a dialog box that someone has to input information in
to find. I want to automate a find by having the control software put a value
in the one field, Find Device, in my FindDevice table then I would like
something in Access to look at that Find Device field and go to that specific
record. I would prefer to use an integer (number) verses a text string in
that Find Device field because it would be easier in my graphical control
software to do it that way.

So, if the graphical control software writes a number to the Find Device
field then Access would automatically find and display the record in my form
using the number in the Find Device field matching it to the coresponding
number in the Primary Key TagID column in the LinkTrac I/O table to display
the record.

Is this clearer?

Thanks again.

 

[Steve Schapel]

Darrell,

Just to clarify a few matters, side issues but relevant:
- databases don't have cells
- data in Access tables can not be a record number
- records in Access tables is unordered
.... and while I'm on a tangent...
- it is not a good idea to use characters liks # or / as part of the
name of a table or field or database object.

Ok, back to your question. Your macro will be running from an event on
the form, right? You can do it like this...

Action: GoToControl
Control Name: [TagID]
Action: FindRecord
Find What: =DLookUp("[Find Device]","FindDevice")

As I mentioned in my previous reply, this assumes there will only ever
be one record in the FindDevice table.

 

[Darrell]

Steve:

Thank you for your clarifications and your solution. It works!

At first I did not have the TagID showing on my form and I got an error in
the macro. Once I put it on my form all went well.

Good job.

Regards,
Darrell

Darrell,

Just to clarify a few matters, side issues but relevant:
- databases don't have cells
- data in Access tables can not be a record number
- records in Access tables is unordered
.... and while I'm on a tangent...
- it is not a good idea to use characters liks # or / as part of the
name of a table or field or database object.

Ok, back to your question. Your macro will be running from an event on
the form, right? You can do it like this...

Action: GoToControl
Control Name: [TagID]
Action: FindRecord
Find What: =DLookUp("[Find Device]","FindDevice")

As I mentioned in my previous reply, this assumes there will only ever
be one record in the FindDevice table.

--
Steve Schapel, Microsoft Access MVP

Steve:

Again I appreciate your timely response.

My form is based on one table called LinkTrac I/O (actually there are more
tables that have relations to this table but we do not need to address these
at this time). The LinkTrac I/O table has all of the records in it that I am
interested in. This table has a Primary Key TagID AutoNumber in the first
field. I assume that this is the record number also because there can be no
duplicates and is indexed.

The second table is called FindDevice and that table only has a Primary Key
ID# field and a Find Device field. Obviously I can change the Data Type to
either Text or Number for the Find Device Field.

The other software I have is a graphics based control software that can
connect to my Access database and either download a value or a string by
using ODBC and data mining directly to a specific cell in my ccess database
(FindDevice table). I can actually pass information back and forth if I
wanted to.

I alread have a FindRecord Command Button on my form for finding specific
records but that uses a dialog box that someone has to input information in
to find. I want to automate a find by having the control software put a value
in the one field, Find Device, in my FindDevice table then I would like
something in Access to look at that Find Device field and go to that specific
record. I would prefer to use an integer (number) verses a text string in
that Find Device field because it would be easier in my graphical control
software to do it that way.

So, if the graphical control software writes a number to the Find Device
field then Access would automatically find and display the record in my form
using the number in the Find Device field matching it to the coresponding
number in the Primary Key TagID column in the LinkTrac I/O table to display
the record.

Is this clearer?

Thanks again.