How to Find / Query / Refresh form

[Guest]

Hello,

I know that I should know this but....

1. When I open a bound form, the form opens to the first record.
2. I use an InputBox to prompt the user for a value used to query the DB.
3. I perform a DCount to veryify what I want is in the database.

4. Assuming the record exists, I want to display the record that I'm after.
How do I make the form display the record that I want using the key value
entered by the user?

Here is my brief piece of code:


Dim strS3ID As String

strS3ID = UCase(InputBox("Please enter the S3ID of the employee whose
role you wish to change."))
If DCount("S3ID", "tbl_Empl_Master", "S3ID = '" & strS3ID & "'") <> 1 Then
MsgBox ("The S3ID number you entered was not found in the database.")
Else
'make the record display on the form. strS3ID is the PK.
End If



End Sub

 

[Jeff Boyce]

Rich

Consider using a different approach...

Imagine the following:

You open a form which displays no records. The form contains a combobox
(the Header is a good spot).

The user selects an actual employee from the combobox (names are often
easier than ID#s).

The form "loads up" that employee's record.

Here's how this works:

The form is based on a query that uses the combobox (on the form) as
providing a selection criterion for the ID field.

The combobox is based on query that gets ID and EmployeeName, but only shows
the name.

The combobox has code in an AfterUpdate event that tells the form to rerun
the (form's) query ... i.e.:
Me.Requery

Does that sound like it would do the job?

(Note that there's NO way to select or enter an invalid ID# -- you could use
the LimitToList property of the combobox to ensure that users couldn't enter
someone who isn't an employee.)

Regards

Jeff Boyce
Microsoft Office/Access MVP