By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,606 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Findnext and FindRecord problem

P: 26
Well, in need to find a record using a command button so I set up a FindRecord in a macro. Now the person wants to be able to find all the records that meet the last name criterian so I thought I would use FindNext but as I found out you cant do that because the focus shifts so it starts over. The help menu said to use a custom Toolbar button. But I don't know what that is and I'm not all that good at VBA programing could yous help.
thanx
Steve
Sep 13 '07 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,599
Well, in need to find a record using a command button so I set up a FindRecord in a macro. Now the person wants to be able to find all the records that meet the last name criterian so I thought I would use FindNext but as I found out you cant do that because the focus shifts so it starts over. The help menu said to use a custom Toolbar button. But I don't know what that is and I'm not all that good at VBA programing could yous help.
thanx
Steve
You could simply set a Filter for your Form as in:
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[LastName]='" & "Mitchell" & "'"
  2. Me.FilterOn = True
To turn off the Filter:
Expand|Select|Wrap|Line Numbers
  1. Me.FilterOn = False
Sep 14 '07 #2

P: 26
But this search is at the end of a form, so if some one is looking to find this persons form they would type in the last name and hit the search button. The way I have it now it can only find the first occurrence of the last name. Would this code work in this way (with a button). Thanx for your help
Sep 14 '07 #3

P: 26
I have been looking and it looks like it can be done with DoCmd.FindRecord but I don't know how to set this up. Any suggestions
Steve
Sep 14 '07 #4

ADezii
Expert 5K+
P: 8,599
But this search is at the end of a form, so if some one is looking to find this persons form they would type in the last name and hit the search button. The way I have it now it can only find the first occurrence of the last name. Would this code work in this way (with a button). Thanx for your help
You can define the Filter within the Click() Event of a Command Button. Assuming the Text Box containing the Last Name to serarch for is called txtLastName, and the Field is named [Last Name], the following code will set the Filter of the Form to return all Records meeting the criteria:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command60_Click()
  2. If Not IsNull(Me![txtLastName]) Then
  3.   Me.Filter = "[Last Name]= '" & Me![txtLastName] & "'"
  4.   Me.FilterOn = True
  5. End If
  6. End Sub
Sep 14 '07 #5

P: 26
That is the best, you are so kind. Not to push my luck but is there a way to add a wild card to the last name. So, if I type in Kim it will give me Kimbull or Kimbell. Also you mentioned before that you can turn the filter off, do I make another button to do that or can you just click it again and turn it off.
Thanks again
Sep 14 '07 #6

ADezii
Expert 5K+
P: 8,599
That is the best, you are so kind. Not to push my luck but is there a way to add a wild card to the last name. So, if I type in Kim it will give me Kimbull or Kimbell. Also you mentioned before that you can turn the filter off, do I make another button to do that or can you just click it again and turn it off.
Thanks again
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command60_Click()
  2. If Not IsNull(Me![txtLastName]) Then
  3.   Me.Filter = "[Last Name] Like '*" & Me![txtLastName] & "*'"
  4.   Me.FilterOn = True
  5. End If
  6. End Sub
  7.  
  8. 'If you type Kim in [txtLastName], will return Kimbull, Kimbell, McKimberly, Jakim, etc. Will match any pattern of criteria in [txtLastName] whether it exists in the beginning, middle, or end of the Last Name.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command60_Click()
  2. If Not IsNull(Me![txtLastName]) Then
  3.   Me.Filter = "[Last Name] Like '" & Me![txtLastName] & "*'"
  4.   Me.FilterOn = True
  5. End If
  6. End Sub
  7.  
  8. 'If you type Kim in [txtLastName], will return Kimbull, Kimbell, but not McKimberly, Jakim, etc. Will match any pattern of criteria in [txtLastName] that exists in the beginning of the String
Also you mentioned before that you can turn the filter off, do I make another button to do that or can you just click it again and turn it off.
You can place the following code in the Click() Event of a Command Button if you like, with a Caption similar to 'Show ALL Records':
Expand|Select|Wrap|Line Numbers
  1. Me.FilterOn = False
Sep 14 '07 #7

P: 26
Thanx so much. This worked like a charm.
Steve
Sep 17 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.