Go to a specific "page" in a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!
I have a large database of lakes with a network of tables.
I have made a form which shows one lake at a time, and all the data from all
tables which carry information on that lake.

In each table, the ID specific for each row is produced by a AutoNumber. In
the "main table", with one lake per row, many rows have been deleted, why the
row number no longer is the same as the ID.

In the form, I have used the bar at the bottom to go to a specific row, but
as that no longer corresponds to the Lake-ID, it doesn't take me to the right
page.

I guess the best was to solve the problem is to insert a List Box with the
Lake-ID, where I can type in and choose which page to go to based on Lake-ID.
The question is: how do I get the list box to work in this way? To go to a
new page instead of me failing to type anything in the box, since it is
connected to an AutoNumber which cannot be changed.

Thanks!!
 
If you know the lakeid you could just add an unbound textbox that will be
used as a lookup field. Some simple code in the AfterUpdate event of this
control will cause the form to navigate to the correct record.

Let's say your unbound control is named txtSearchLakeId then the code in the
AfterUpdate event of txtSearchLakeId would look like this:

with me.recordsetclone
.findfirst "Lakeid=" & me.txtSearchLakeId
if not .nomatch then
me.bookmark = bookmark
else
msgbox "No record exists with this LakeID"
endif
end with

You can use the same code with a listbox but a listbox really isn't
necessary if you (or your user) knows they want the record for a particular
LakeId.

FWIW, the "row number" of a record is only relevant to the current sort and
criteria of the dataset being displayed. There is no specific order in which
records are stored (Think of them being in a big bucket). The order is only
established through queries. Indexes are used to order records on demand.
 
I never managed to do as Sandra suggested, so some more help would be great.
When I try to enter a Lake-ID into the textbox, I get the following error
message:

"The expression BeforeUpdate you entered as the event property setting
produced the following error: Invalid outside procedure. "

The VB-code is now:
With Me.RecordsetClone
.FindFirst "Lake_ID=" & Me.txtSearchLakeId
If Not .NoMatch Then
Me.Bookmark = Bookmark
Else
MsgBox "No record exists with this LakeID"
End If
End With

Private Sub txtSearchLakeId_BeforeUpdate(Cancel As Integer)

End Sub

And the name of the textbox is txtSearchLakeId .
Is it the last part (which Access generated by itself) that causes the
problem?: "Private Sub txtSearchLakeId_BeforeUpdate(Cancel As Integer)" How
do I get rid of that in that case?

Thank you!
/Johanna
If you know the lakeid you could just add an unbound textbox that will be
used as a lookup field. Some simple code in the AfterUpdate event of this
control will cause the form to navigate to the correct record.

Let's say your unbound control is named txtSearchLakeId then the code in the
AfterUpdate event of txtSearchLakeId would look like this:

with me.recordsetclone
.findfirst "Lakeid=" & me.txtSearchLakeId
if not .nomatch then
me.bookmark = bookmark
else
msgbox "No record exists with this LakeID"
endif
end with

You can use the same code with a listbox but a listbox really isn't
necessary if you (or your user) knows they want the record for a particular
LakeId.

FWIW, the "row number" of a record is only relevant to the current sort and
criteria of the dataset being displayed. There is no specific order in which
records are stored (Think of them being in a big bucket). The order is only
established through queries. Indexes are used to order records on demand.
--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi!
I have a large database of lakes with a network of tables.
I have made a form which shows one lake at a time, and all the data
from all tables which carry information on that lake.

In each table, the ID specific for each row is produced by a
AutoNumber. In the "main table", with one lake per row, many rows
have been deleted, why the row number no longer is the same as the ID.

In the form, I have used the bar at the bottom to go to a specific
row, but as that no longer corresponds to the Lake-ID, it doesn't
take me to the right page.

I guess the best was to solve the problem is to insert a List Box
with the Lake-ID, where I can type in and choose which page to go to
based on Lake-ID. The question is: how do I get the list box to work
in this way? To go to a new page instead of me failing to type
anything in the box, since it is connected to an AutoNumber which
cannot be changed.

Thanks!!
Click to expand...
Click to expand...
 
Back
Top