DATABASE RECORD HELP

[Georoxx (George)]

I have a database with about 400 records. I want to place a text box (on my
form that references that database) wherein I can type in the "record number"
(say) and then click on an adjacent button to execute the request... at which
time I want the specific record to be summoned for viewing or edit on that
form. I am just learning, so I'd appreciate some "laymen terms". (Don't kick
me too hard.) Thanks.

 

[Klatuu]

The first problem is there is no such thing in Access as a record number that
relates to a specific record. The number you see in the navigation control
on a form is the relative record number of the current recordset. If you add
a record, delete a record, filter the recordset, or resort the recordset, the
numbers will change.

You can use the GoToRecord method to navigate to a specific record using the
GoToRecord method, but how will you know what the number of the record is?

It is more common for each record to have a primary key field that uniquely
identifies the record.

 

[Jeff Boyce]

One approach (after you've digested Dave's response) would be to use a combo
box to list user-friendly choices (who can remember a record number,
anyway?!).

The combo box control has properties, including an AfterUpdate event. In
that AfterUpdate event, you could requery the form (e.g., Me.Requery) to get
the selected record's fields.

Note that this also requires that you base your form on a query, not
directly on the table. And you add a parameter (selection criterion) to the
record number/ID field that looks something like:
Forms!YourFormName!YourComboBoxName

This has the effect of telling the form to use the selected record in the
combo box and return (only) the record selected for display.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Georoxx (George)]

I understand, Dave. I thank you for your response. (I didn't want to say
"type in the BARCODE" ...which is my primary key... as that could have been
misunderstood. My bad choice of words using the term RECORD NUMBER.)
Anyway, I do not want to use the typical cntl-F "find" function. I want to
make it user friendly so that a person can type in the BARCODE number into
the text box and simply push the adjacent "GO" button to execute the request
to have the desired record called up and dispayed for reference or editing.
Do you think this can be done in Access? I appreciate you taking the time to
help. Have a great day. -George

 

[Klatuu]

Yes, it can be done. It is usually done with an unbound combo rather than a
text box. The combo is much better suited for this sort of thing.

You have to set your combo box up so it has a list of all the bar codes in
your table.
Then the user can use the drop down portion of the combo and select the
barcode or they can start typing and after each keystroke, the closest match
will display. When the user has selected the barcode, you use the After
Update event of the combo to make the record for the selected barcode the
current record. Here is an example of how that is done:

With Me.RecordsetClone
.FindFirst "[BAR_CODE] = '" & Me.cboBarCode & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

[BAR_CODE] should be the name of the field in the form's recordset that
stores the barcodes.

cboBarCode is the name I used for the combo box for example purposes.

In this line:
.FindFirst "[BAR_CODE] = '" & Me.cboBarCode & "'"
It is assumed that [BAR_CODE] is a text field. If it is a numeric field, it
should be like this:
.FindFirst "[BAR_CODE] = " & Me.cboBarCode