Search combo box

[David Stanton]

I am developing a club membership database (in Access 2003) which
includes, (not surpisingly) a table called "person", holding the names
of members, and so on.

I have included a combo box to search for "surname". This is as done for
me by Access when putting in the box, and is unbound, working from a
select query on "surname", and with the standard little bit of coding:-

Quote:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[pkPers_ID] = " & Str(Nz(Me![Combo93], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Unquote

in the "afterupdate" event.

"pkPers_ID" is the primary key, populated by autonumber

How can I make the combo box follow the "surname" field of the current
record at all times when not being used to search?

Failing that, could it show a fixed message such as "search".

Currently it seems to stay at the last name searched for which some
users might find confusing.

The frustrating thing is that I have done this before and forgotten how,
as I haven't played with Access for several years!

Thanks in advance


David

 

[DubboPete]

I am developing a club membership database (in Access 2003) which
includes, (not surpisingly) a table called "person", holding the names
of members, and so on.

I have included a combo box to search for "surname". This is as done for
me by Access when putting in the box, and is unbound, working from a
select query on "surname", and with the standard little bit of coding:-

Quote:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[pkPers_ID] = " & Str(Nz(Me![Combo93], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Unquote

in the "afterupdate" event.

"pkPers_ID" is the primary key, populated by autonumber

How can I make the combo box follow the "surname" field of the current
record at all times when not being used to search?

Failing that, could it show a fixed message such as "search".

Currently it seems to stay at the last name searched for which some
users might find confusing.

The frustrating thing is that I have done this before and forgotten how,
as I haven't played with Access for several years!

Thanks in advance


David

Hi David,

Seems the easiest way is to return to a blank combo box aafter update, in which case the last line of code (to be added) should be

Quote:
Dim rs As Object
Set rs = Me.Recordset.Clone

rs.FindFirst "[pkPers_ID] = " & Str(Nz(Me![Combo93], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo93 = ""

Unquote


with no spaces between the rabbits ears. Then, just set the focus on your next area of concern, next field perhaps,

......
Me.NextField.SetFocus

Unquote

and that should also now be the last line of code....

HTH
cheers
Pete

MS Access's equivalent of the 17th Century Goat Herder in a 21st century landscape

 

[David]

I am developing a club membership database (in Access 2003) which
includes, (not surpisingly) a table called "person", holding the names
of members, and so on.

I have included a combo box to search for "surname". This is as done for
me by Access when putting in the box, and is unbound, working from a
select query on "surname", and with the standard little bit of coding:-

Quote:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[pkPers_ID] = "& Str(Nz(Me![Combo93], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Unquote

in the "afterupdate" event.

"pkPers_ID" is the primary key, populated by autonumber

How can I make the combo box follow the "surname" field of the current
record at all times when not being used to search?

Failing that, could it show a fixed message such as "search".

Currently it seems to stay at the last name searched for which some
users might find confusing.

The frustrating thing is that I have done this before and forgotten how,
as I haven't played with Access for several years!

Thanks in advance


David

Hi David,

Seems the easiest way is to return to a blank combo box aafter update, in which case the last line of code (to be added) should be

Quote:
Dim rs As Object
Set rs = Me.Recordset.Clone

rs.FindFirst "[pkPers_ID] = "& Str(Nz(Me![Combo93], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo93 = ""

Unquote


with no spaces between the rabbits ears. Then, just set the focus on your next area of concern, next field perhaps,

......
Me.NextField.SetFocus

Unquote

and that should also now be the last line of code....

HTH
cheers
Pete

MS Access's equivalent of the 17th Century Goat Herder in a 21st century landscape

Thanks, Pete!