"Go To ID" Button?

  • Thread starter Thread starter HWhite
  • Start date Start date
H

HWhite

This is probably amazingly simple, but I'm missing it.

"ID" is my primary key in the underlying table. I would like to add a
button to my form that will go to ID# (whatever the user types in). I don't
want to open the "find" screen and I don't want to filter the records. I
simply want to go to the record that belongs to that ID number.

Thanks for your help.
 
Hi H (what is your name?)

make a combobox (I usually put 'find combos' in the form header)

on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "SomeID = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the
RecordSource of the form -- assuming your primary key is a Long Integer
data type (autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can
pull from multiple tables or only use one ... just make sure that the
first column is the primary key ID of the table you want to search (and
that field is part of the RecordSource for the form you are searching).

I usually make one or more unbound (no ControlSource) combos on your
form (like in the header). Let the first column be invisible (unless
you want to search on the ID <smile>) and be the primary key ID of the
RecordSource of your form and then, on its AfterUpdate event...

=FindRecord()

you can use the same code for every 'find combo' -- and the RowSource
can be anything as long as the first field is the primary key ID of your
form

for information about setting properties for a combobox, read the
Combobox Example in:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

Warm Regards,
Crystal


*
(: have an awesome day :)
*
 
thank you... it worked very well but the instructions were a little hard to
follow since access 2003 doesn't use "Private Function", but it automatically
changed it. Also it wasn't clear what to name the combo box, but I figured
it out.

Thanks again for your help.
 
Hi H

you're welcome ;) I am glad you figured it out -- but you may have done
some things that you didn't need to ...

what do you mean by, "access 2003 doesn't use "Private Function"?

The reason to put this as a separate function is so that ANY control can
use it
=FindRecord()

"it wasn't clear what to name the combo box"

it doesn't matter what it is called -- that is why the code refers to
ActiveControl and not anything specific.

This code will work with a number of combos set up to find a record on
the form as long as the first column is the numeric primary key of the
table that the form is based on

Did you read the 'Access Basics' document? It is only 100 pages and has
lots of screen shots :) -- I guarantee it would be well worth your time

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
What I meant was when I copy and pasted your code, saved and closed, it did
not work. So I re-opened the code and it had changed the phrase "Private
Function" to "Private Sub"... so that caused some confusion for a while.
After realizing why the code wasn't working, Private Function/Private Sub was
really an irrelevant issue for me, but I thought I would mention it to you in
case it meant anything to you.

Also, I bookmarked your page and reviewed it briefly. I look forward to
spending more time with it.

Thanks again.
 
Hi H,

you're welcome

a procedure is either a Sub or a Function

Its visibility is either Public or Private. If a procedure in code
behind a form and is defined to be Private, then only that form can see it.

here is some text from Access Basics, p 5-17:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To see the code behind a form (anything said about code behind a form
also applies to code behind a report): from the menu in the design view
of a form, choose --> View, Code

This will take you to the module sheet that is stored 'behind' and with
the form. When the form is copied, the module sheet tags along without
you having to do anything special. This kind of code is a Class Module
as opposed to a Standard (General) Module. When you Click on the Modules
tab in the Database Window, you see a list of Standard Modules -- they
are able to be used by any form, any report, any query ... anything in
the database. Therefore, their scope is global.

A Procedure is a general term encompassing Subs and Functions. If you
look at a procedure declaration you might see something like this:

Private Sub eAddress_DblClick(Cancel As Integer)

Private means that only this form (and module stored behind the form)
can see and use this code -- its scope is limited to the form.

Sub means it is a Subroutine that does not return a value as opposed to
a Function which does ... or maybe I should say 'can' -- return a value.

A Function can do everything a Sub can -- a weird thing -- you cannot
assign a Sub directly to a property event, it must be a Function by
definition -- and there is no way to use a return value in these cases.
Why Microsoft chose to use Functions instead of Subs in Access is a
mystery. With this in mind, it seems odd that the [Event Procedure] code
is a Sub --not consistent! ... anyway, I digress...

eAddress_DblClick means that there is a control with the Name 'eAddress'
and this is the DoubleClick event procedure -- so it will be launched
(triggered) when the user double-clicks on the control.

Cancel As Integer -- Cancel is a parameter to the sub -- although most
people don't think of "Cancelling" a DoubleClick event because you can
simply exit it! Cancel is better understood on an event such as
BeforeUpdate where you Cancel the Update to a control or prevent Access
from saving a record if certain conditions are not met. 'As Integer'
means that Cancel is a whole number < 32K. Some event procedures have
Cancel as a parameter but most do not. For instance, out of the 50
events that can be defined at the form level, 13 of them have Cancel as
a parameter.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

anyway, thank you for your comments! I appreciate you mentioning
something you thought I needed to know ... actually, I did so I could
explain better <smile>


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Back
Top