Adding a "find record" feature to a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom Gettys - 30 Mar 2007 18:16 GMT

I have a form that I use for data entry of music composers. On the form are
fields for FirstName and LastName. I have successfully added a ComboBox that
populates the form based on the LastName.

Because of families of composers that share the last name (Bach, for one), I
would like to be able to populate the ComboBox list with "LastName,
FirstName" to allow the user to see the full name, but I can't get it to work.

I tried making a query that has all the composer table fields and one more
that constructs the FullName, and using the query but that did not not seem
to do the trick (perhaps because the form is based on the table and not the
query the ComboBox is using??).

Reply to this Message

mscertified - 30 Mar 2007 19:04 GMT

Have your query concatenate the names together e.g. FirstName & ", " & Lastname
You also need the unique key of the composer as a hidden column in the combo
box in order to retrieve the correct record (which you probably already have).

-Dorian

> I have a form that I use for data entry of music composers. On the form are
> fields for FirstName and LastName. I have successfully added a ComboBox that
[quoted text clipped - 8 lines]
> to do the trick (perhaps because the form is based on the table and not the
> query the ComboBox is using??).

Reply to this Message

Tom Gettys - 30 Mar 2007 23:40 GMT

I guess I did not make myself clear Dorian, so let me try again. I have a
form which shows the fields from a table called Composers.

I created a query called qryComposers which returns all the fields in
Composers and adds one derived field (called FullName) that concatenates the
first and last names as you suggested, and the combobox has columns with the
ComposerID and FullName, again exactly as you suggested.

However, when using the combobox to lookup a name and I then press Enter,
access returns an error message that says

"The Microsoft Jet engine does not recognize 'FullName' as a valid field
name or expression"

When I click on Debug it takes me to my code, highlighting the line with
FullName in it:

Private Sub CboFullName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[FullName] = '" & Me![CboFullName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

> Have your query concatenate the names together e.g. FirstName & ", " & Lastname
> You also need the unique key of the composer as a hidden column in the combo
[quoted text clipped - 14 lines]
> > to do the trick (perhaps because the form is based on the table and not the
> > query the ComboBox is using??).

Reply to this Message

storrboy - 31 Mar 2007 03:05 GMT

On Mar 30, 5:40 pm, Tom Gettys <TomGet...@discussions.microsoft.com>
wrote:

> I guess I did not make myself clear Dorian, so let me try again. I have a
> form which shows the fields from a table called Composers.
[quoted text clipped - 40 lines]
> > > to do the trick (perhaps because the form is based on the table and not the
> > > query the ComboBox is using??).

I think I've missed the first part of this thread, but may I ask if
the query you mention is the recordsource of the form? If not then the
field FullName may not exist in the recordset. I ask because you say
the "...form which shows the fields from a table..." . I'm not sure
that the EOF property is changed when a record is not found. It may be
better to use NoMatch instead. For example:

Dim rst As Recordset
Set rst = Me.RecordsetClone

rst.FindFirst "[FullName] = '" & Me![CboFullName] & "'"
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "Not Found"
End If

Reply to this Message