findfirst then findnext

[johankotze]

Hello I know that this is a worn out topic but I do have some difficulty in
doing the following (as I'm new to Access and do not want to use Access built-
in search function)

On my Main (ClientInformation) I have a unbound field called [txtSearch] and
a cmdSearch button next the field called Search. My problem is that I have
multiple records that have the same Lastnames (don't want the search action
to be linked to Lastname but rather "any part of any field in my main form")

As I have multiple records I have to use the findnext option as well. Can
someone please help me with the code behind the cmdSearch button

 

[Tom van Stiphout]

Check out DoCmd.FindRecord in the Help file.

-Tom.
Microsoft Access MVP

 

[johankotze]

I did have look at the help file but it directed me to the online help and I
do get error "Contents cannot be found". If I post my code can you help me
with it.

Check out DoCmd.FindRecord in the Help file.

-Tom.
Microsoft Access MVP

Hello I know that this is a worn out topic but I do have some difficulty in
doing the following (as I'm new to Access and do not want to use Access built-

[quoted text clipped - 7 lines]

As I have multiple records I have to use the findnext option as well. Can
someone please help me with the code behind the cmdSearch button

 

[Access101]

I would use a combo box whose record source are the full names of everyone in
that table (or any field, like you said) for that matter.

Then, use an AfterUpdate property with code pointing to the Primary Key (in
column 0 of the combobox) of the table, and it will jump to that specific
record--because there is only one unique value in a primary key:

Private Sub cmbField_AfterUpdate()

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PrimaryKeyID] = " & str(Nz(Me![cmbField], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[johankotze via AccessMonster.com]

Th eproblem that I have is the fact that I have over 600 clients. The combo
box than will have 600 lastnames. Still I would know if it is the right
client because I would not see the initials

I would use a combo box whose record source are the full names of everyone in
that table (or any field, like you said) for that matter.

Then, use an AfterUpdate property with code pointing to the Primary Key (in
column 0 of the combobox) of the table, and it will jump to that specific
record--because there is only one unique value in a primary key:

Private Sub cmbField_AfterUpdate()

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PrimaryKeyID] = " & str(Nz(Me![cmbField], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Hello I know that this is a worn out topic but I do have some difficulty in
doing the following (as I'm new to Access and do not want to use Access built-

[quoted text clipped - 7 lines]

As I have multiple records I have to use the findnext option as well. Can
someone please help me with the code behind the cmdSearch button

 

[Access101]

I thought I had been clearer than I was ...

The combo box should be designed to contain MULTIPLE COLUMNS.

In the first column, column 0, put the ID, and hide that column by
indicating 0 in the Column Widths criteria. Also, make the Bound Column "1"

You can have 8 columns if you want, if that's what it takes to identify one
last name as being different from another: potential corresponding widths
listed below the columns that are in the row source of your under your Data
TAB

ID; Last; First; MI; Address; City; State; Zip
0;1";1";.3";1";.3",.5"

Th eproblem that I have is the fact that I have over 600 clients. The combo
box than will have 600 lastnames. Still I would know if it is the right
client because I would not see the initials

I would use a combo box whose record source are the full names of everyone in
that table (or any field, like you said) for that matter.

Then, use an AfterUpdate property with code pointing to the Primary Key (in
column 0 of the combobox) of the table, and it will jump to that specific
record--because there is only one unique value in a primary key:

Private Sub cmbField_AfterUpdate()

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PrimaryKeyID] = " & str(Nz(Me![cmbField], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Hello I know that this is a worn out topic but I do have some difficulty in
doing the following (as I'm new to Access and do not want to use Access built-

[quoted text clipped - 7 lines]

As I have multiple records I have to use the findnext option as well. Can
someone please help me with the code behind the cmdSearch button

 

[johankotze via AccessMonster.com]

Thankx Access101 just a few questions.
1. Can I insert a combo box on the main from where I want to find/search for
records.
2. How do I generate a Multiple Column Combo Box
3. When I find the right record will I be pointed to that record with a click
command or will it be automatically selected for me as I go done the list in
Multiple Columns Combo Box.
4. Must the combo box be bound to fields in main form or can it be unbound.

Sorry for all these question but certain things in Access I'm just stupid
with.

I thought I had been clearer than I was ...

The combo box should be designed to contain MULTIPLE COLUMNS.

In the first column, column 0, put the ID, and hide that column by
indicating 0 in the Column Widths criteria. Also, make the Bound Column "1"

You can have 8 columns if you want, if that's what it takes to identify one
last name as being different from another: potential corresponding widths
listed below the columns that are in the row source of your under your Data
TAB

ID; Last; First; MI; Address; City; State; Zip
0;1";1";.3";1";.3",.5"

Th eproblem that I have is the fact that I have over 600 clients. The combo
box than will have 600 lastnames. Still I would know if it is the right

[quoted text clipped - 21 lines]

 

[johankotze via AccessMonster.com]

Hi Access101
Thanx for the info it work, but how can sort the Multiple Columns Combo Box
= [LastName], [Initials] ASC. I want the data in the combo box to be
alfabetically in ascending form.

I thought I had been clearer than I was ...

The combo box should be designed to contain MULTIPLE COLUMNS.

In the first column, column 0, put the ID, and hide that column by
indicating 0 in the Column Widths criteria. Also, make the Bound Column "1"

You can have 8 columns if you want, if that's what it takes to identify one
last name as being different from another: potential corresponding widths
listed below the columns that are in the row source of your under your Data
TAB

ID; Last; First; MI; Address; City; State; Zip
0;1";1";.3";1";.3",.5"

Th eproblem that I have is the fact that I have over 600 clients. The combo
box than will have 600 lastnames. Still I would know if it is the right

[quoted text clipped - 21 lines]

 

[Access101]

If you search for this (phrase below) in the Access help file, it addresses
the solutions more eloquently than I could:

"Customize a list box, combo box, or drop-down list box"

Hi Access101
Thanx for the info it work, but how can sort the Multiple Columns Combo Box
= [LastName], [Initials] ASC. I want the data in the combo box to be
alfabetically in ascending form.

I thought I had been clearer than I was ...

The combo box should be designed to contain MULTIPLE COLUMNS.

In the first column, column 0, put the ID, and hide that column by
indicating 0 in the Column Widths criteria. Also, make the Bound Column "1"

You can have 8 columns if you want, if that's what it takes to identify one
last name as being different from another: potential corresponding widths
listed below the columns that are in the row source of your under your Data
TAB

ID; Last; First; MI; Address; City; State; Zip
0;1";1";.3";1";.3",.5"

Th eproblem that I have is the fact that I have over 600 clients. The combo
box than will have 600 lastnames. Still I would know if it is the right

[quoted text clipped - 21 lines]

As I have multiple records I have to use the findnext option as well. Can
someone please help me with the code behind the cmdSearch button