i can retrieve, add, edit data to a table when it is closed but i get an error, "The command or action 'GoToRecord' isnt available now." if i try to move to a record when the table is not physically opened. seems odd to me. If i open the table this sub works fine.
any insight.. thanks. im using the DoCmd
Code:
Sub movetoRecord()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblresponsetracker", 2)
DoCmd.GoToRecord acDataTable, "tblresponsetracker", acGoTo, 3
End Sub
In your subroutine, "rs" is a locally declared recordset variable. You open the recordset OK, and the odds are that the .GoToRecord actually works. BUT.... when you hit that End Sub, the locally declared recordset is dissolved by the "return from subroutine" code related to End Sub / Exit Sub processing.
Recordsets exist as object variables (obviously, objects of type Recordset) just like any other variables. You can have literally a dozen recordsets open at the same time to the same table in the same application on the same machine - and each one of them has its own independent location, because one element of a recordset is its bookmark. It is that bookmark that is affected by the .GoToRecord, and in your subroutine, it seems to me that the recordset, bookmark, and all else are reclaimed from the stack on return from that sub.
What I find surprising is your claim that if you open the sub it works fine. I am confused by what you mean here. To my understanding, your subroutine's "rs" is a local object that will cease to exist when the subroutine de-instantiates. Any other recordset has its own bookmarks and won't be affected by the "rs" recordset inside your subroutine.
What I find surprising is your claim that if you open the sub it works fine. I am confused by what you mean here. To my understanding, your subroutine's "rs" is a local object that will cease to exist when the subroutine de-instantiates. Any other recordset has its own bookmarks and won't be affected by the "rs" recordset inside your subroutine.
You can try this for yourself in the attached database where I tested the OP's assertion. If the table is not open (open by double clicking on it in the nav pane) you get the error, ""The command or action 'GoToRecord' isn't available now." Actually this part of the code
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblresponsetracker", 2)
is a red herring as it works as the OP describes with just.
Duh - I skimmed just a bit and didn't catch that the .GoToRecord wasn't referencing the recordset - it was referencing the table.
However, my comment still applies. The cursor doesn't exist unless the object is explicitly or implicitly open, and of course there is an implied recordset when you open the table in table view (datasheet view) because that is actually just a continuous form. No open table = no (implied) recordset. No recordset = no cursor. No cursor = no .GoToRecord.
That this ever actually works is a bit surprising. I guess Access detects that the table is open and thus by selecting acDataTable option, you connect to that open implied continuous form. Wouldn't have guessed that up front.
"No open table = no (implied) recordset. No recordset = no cursor. No cursor = no .GoToRecord"
makes complete sense. I am new to Access VBA so was experimenting and could not understand the logic and your line sums it up.
And the 'red herring' was the 'Set rs =' (not on purpose) But assume i want to use the GoToRecord with my RecordSet variable. I have tried the different ObjectTypes and they dont seem to allow me to reference the recordset variable. It would make sense to have a data type acRecordSet
how is this done?
Code:
Sub movetoRecord()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblresponsetracker", 2)
DoCmd.GoToRecord acActiveDataObject, rs, acGoTo, 3
End Sub
If you just want to go to record 3 in the recordset (once it is open...)
Code:
rs.movefirst
rs.movenext
rs.movenext
You are now at record 3.
However... let's point out something. If this actually opens a table, you can get to ANY RECORD in that table - but doing it by counting records will ALWAYS disappoint you. The problem is that Access is based on SET theory, so a recordset is an amorphous set of records all in a bunch. Which one is third? Damned if I know. Reach in and grab one, then another, and then one more. That last one is the third record. However, the next time you edit a record in that table, or when you do a compact & repair on the database, or when you import the table to a new database, any previous order you might have known about is now gone.
Note that this comment applies SPECIFICALLY to any recordset opened in a way that does not explicitly include an "ORDER BY" clause. Despite having a prime key (PK), there is no guarantee that you will visit the table in PK order. This might be hard to wrap your head around, but in a query, the rules of Access (as it relates to set theory) say that without special elements in the query to assure a particular order, you cannot predict - and Access does not care - the order in which records are processed. They are processed as a set, not as individual records.
OK, you and I know that the computer really has to do them one at a time. But the point is, Access BEHAVES as though you didn't know that. To you from the outside, ALL action queries are monolithic.
Therefore, when looking for a specific record, do not say "go to the third record." If you REALLY wanted to find a record with ID number 3, use
