auto-fill based on unique field

[_Bigred]

I have a inmate database and want to be able to auto-fill a form when I type
in the inmates number.

Table: OpenInfo
Fields: InmateNumber, OffenderName,Cell,SpecialNeeds,ArrivalDate etc...

Is there a method to type in say Inmate John Doe's inmate number and
auto-populate the rest of the form based on his number (his number will stay
the same as long as he is a inmate).

TIA,
_Bigred

 

[Graham Mandeno]

Lets assume that you have a table (or a query based on more than one table)
which contains all the fields you want to display and which has the
InmateNumber as a unique index (probably primary key).

Create the form with this table/query as its RecordSource and add all the
fields you want displayed, including the InmateNumber.

Now, delete the ControlSource property for the InmateNumber textbox (it
should now say "Unbound" on the form in design view) and change its Name
property to txtLookupInmate

Next, add an AfterUpdate event procedure for the txtLookupInmate textbox as
follows:

Private Sub txtLookupInmate_AfterUpdate()
With Me.RecordsetClone
.FindFirst "InmateNumber=" & txtLookupInmate
If .NoMatch then
MsgBox "Unknown inmate number"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub

Finally, add a Form_Current event procedure with the following line of code:

If txtLookupInmate <> Me.InmateNumber then _
txtLookupInmate = Me.InmateNumber