# Access. Find the record number of a record (For forms and queries)



## firestormer (Sep 16, 2005)

I want to use the GoToRecord command to go to a specific record in a form. The problem is this uses an offset or record number. How can i find the record number of a specific record?

What i plan to do is use some code similar to this:

docmd.GoToRecord acDataForm, "FrmComputer", acGoTo,_ expression_

where the expression is the record number of the record 'Computer01' for example.


----------



## OBP (Mar 8, 2005)

firestormer, this is quite difficult to achieve because you have to find 'Compter01' first.
So the best way to learn how to do this is to create a Combo Box with the Wizard, but when it asks what you want to do click on the "Find a record on my form based on the value I selected in my Combo Box", this is the last of the 3 options.
If you use the field containing "Computer01" in the combo and then view the VBA code it shows how to find the requiered record and it is actually beter than using the Docmd version.


----------



## firestormer (Sep 16, 2005)

Unfortunatly i dont think this will work as the criteria field and VB will be on a different form.

I have 2 forms FrmFaultReport(Primary Key Fault Report ID) and FrmComputer (Primary Key Computer ID). The underlying tables of these forms are linked with a relationship using the field Computer ID. What i want is a command button in FrmFaultReport that when clicked opens FrmComputer and goes to the appropriote reocord.

Now this can be done using the Command Button wizrard but this method opens the form and applys a filter which i dont want.


----------



## OBP (Mar 8, 2005)

firestormer, I see your problem, you want the Form to open with all records, but go to the one record from the FrmFaultReport?
What you need is a record set based on the FrmComputer Table or Query, then use the Combo Box type "find" and then Goto the matching record in the Form using the Recordset Bookmark. The simplest way to do this is to have the code FrmComputer Form's "On Current" event Procedure and have a hidden field on the form that indicates that the form has been opened from the FrmFaultReport form. In this way the VB code only runs when you have come from the FrmFaultReport form.
If you can post the database I can set it up for you.


----------



## firestormer (Sep 16, 2005)

I think i see what your getting at.

Have the command button that opens FrmComputer set the value of a hidden text box to the right record, and then the form goes to that record. Ill give it ago a get back to you.


----------



## OBP (Mar 8, 2005)

firestormer, the hidden field only holds a key to the fact that you have been directed to the form from the Fault report form.
It will still need VB code to find the correct record.


----------



## firestormer (Sep 16, 2005)

OK what ive done is this:

Created a macro which minimizes FrmFaultReport, Opens FrmComputer and Sets the value of the unbound text box to the Computer ID in FrmFaultReport. The macro is run from a command button on FrmFaultReport. The macro works fine.

I then added the follwoing VB to the AfterUpdate event of the new unbound text box in FrmComputer:


```
Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Computer ID] = '" & Me![Text72] & "'"
    Me.Bookmark = rs.Bookmark
```
Now the code works fine. BUT running the code isn't.

I cant seem to get the code to run after the macro sets the value of the unbound text box (Text72). Ive also tried the OnChange event and that didnt work.


----------



## OBP (Mar 8, 2005)

firestormer the code needs to go in the Form's "On Current" event Procedure, the reason being is that Access doesn't recognise Macro/VBA "updating" as an "Update Event".


----------



## firestormer (Sep 16, 2005)

But if i put in the on current wont that prevent the user from changing the reocrd as the when the try they'll get sent back to the record it opened on?


----------



## firestormer (Sep 16, 2005)

Can i get the macro to run the code?
How do i turn the code above into a function?


----------



## OBP (Mar 8, 2005)

firestormer, the code in the On Current Event Procedure needs an If statement that says if the text box Text72 has a value then run the 
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Computer ID] = '" & Me![Text72] & "'"
Me.Bookmark = rs.Bookmark

The only time it has a value is when you run the Macro, if you move off the record it should loose it's value.


----------



## firestormer (Sep 16, 2005)

But as its unbound it wont lose its value, anyway ive found a work around, its bit messy but it works. Ive added an unbound check box and a macro to the end of the statement to set the tick box to true. If made the statement an IFstatement so that it only runs if the check box is false.
I moved the whole lot to the OnTimer event and set the time interval to 100.

This way the whole lot runs 100ms after the form opens and wont run again as the IF statement stops it after the first time.

Thanks for the help.


----------

