Forms-Find Record

[cneeley]

I would like to type information into a field in a blank
form. This is the Primary Key field for the associated
table. Upon leaving that field the form should complete
with associated information, if there is a matching entry
in the table. How do I get this to occur?

 

[Gerard]

You can get this to occur by:
1. adding the fields for the associated info to the form and using an After
Update event procedure in
which you use the Dlookup function:
Me!txtBox1 = DLookup ("value1"; "table"; "ID = " &
Forms!frm_name!txtPrimaryKey)
Me!txtBox2 = DLookup ("value2"; "table"; "ID = " &
Forms!frm_name!txtPrimaryKey)

2. adding the fields for the associated info to the form and use in the
control source of the textbox of a field
the Dlookup function: =DLookup ("value1"; "table"; "ID = " &
Forms!frm_name!txtPrimaryKey)

 

[cneeley]

The DLookup function continually gives me errors. It
doesn't like the expressions. If I get past aht it gives
me a syntax error for ID string.

Is there no way to use the FindRecord macro and FindWhat?
It would seem that if that worked I would not have to
repeat the DLookup for each field.

 

[Gerard]

The answer depends on the number of fields you want to show on your form.
I was also under the impression that the table was not bound to the form
(table is the recordsource for the form). But I'm not so sure now...
If bound: you can use the following code:

Sub txtPrimaryKey_AfterUpdate()
Me.RecordsetClone.FindFirst "[ID] = " & Me![txtPrimaryKey]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub


If unbound: use the following AfterUpdate event procedure of the primary
field.

Public Sub txtPrimaryKey_AfterUpdate

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tabel", dbOpenDynaset)

rst.FindFirst "ID = " & Me!txtPrimaryKey

If rst.NoMatch = False Then
Me!txtValue1 = rst!value1
Me!txtValue2 = rst!value2
Else
MsgBox "There is no match!"
End If

End Sub

 

[Gerard]

The answer depends on the number of fields you want to show on your form.
I was also under the impression that the table was not bound to the form
(table is the recordsource for the form). But I'm not so sure now...
If bound: you can use the following code:

Sub txtPrimaryKey_AfterUpdate()
Me.RecordsetClone.FindFirst "[ID] = " & Me![txtPrimaryKey]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub


If unbound: use the following AfterUpdate event procedure of the primary
field.

Public Sub txtPrimaryKey_AfterUpdate

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tabel", dbOpenDynaset)

rst.FindFirst "ID = " & Me!txtPrimaryKey

If rst.NoMatch = False Then
Me!txtValue1 = rst!value1
Me!txtValue2 = rst!value2
Else
MsgBox "There is no match!"
End If

End Sub

 

[Gerard]

The answer depends on the number of fields you want to show on your form.
I was also under the impression that the table was not bound to the form
(table is the recordsource for the form). But I'm not so sure now...
If bound: you can use the following code:

Sub txtPrimaryKey_AfterUpdate()
Me.RecordsetClone.FindFirst "[ID] = " & Me![txtPrimaryKey]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub


If unbound: use the following AfterUpdate event procedure of the primary
field.

Public Sub txtPrimaryKey_AfterUpdate

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tabel", dbOpenDynaset)

rst.FindFirst "ID = " & Me!txtPrimaryKey

If rst.NoMatch = False Then
Me!txtValue1 = rst!value1
Me!txtValue2 = rst!value2
Else
MsgBox "There is no match!"
End If

End Sub