If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Search Feature
txtSearch is the text box on the same form as the button with this VB
control. "Klatuu" wrote in message ... Is txtSearch the name of a control on the form the code is in or it is on a subform or different form? -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Thanks for the explaination of the two fields. I took out the first field since we are searching for Alphanumeric data (like Z004 or Y142K). The new error is a Compile Error: Invalid or Unqualified Reference on: ..txtSearch in this peice of the code. 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" Thanks for sticking with this issue! "Klatuu" wrote in message ... You are using both versions of what should be one line. 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" As the comments say, if Unit_ID ia a numeric field in the form's record set, use the first version and delete the second version. If it is a text field, delete the first version and use the second version. Note the text version encloses the search value in quotes which is correct syntax when seaching for text. You do not use any delimiters when seaching for a numeric value. And, FYI, if you are searching a date field, the delimiters are the # sign: .FindFirst "[Unit_ID] = #" & Me.txtSearch & "#" -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: great, I added the revised wording. No errors but the search button is not finding existing values! Here is the revised code. 'Performs the search using value entered into txtSearch 'and evaluates this against values in UNIT_ID field Private Sub cmdSearch_Click() 'Check txtSearch for Null value or Nill Entry first. If Nz(Me.txtSearch, vbNullString) = vbNullString Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me.txtSearch.SetFocus Else With Me.RecordsetClone 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" If .NoMatch Then MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus Else MsgBox "Match Found For: " & strSearch, , "Congratulations!" Me.Unit_ID.SetFocus Me.txtSearch = vbNullString End If End With End If End Sub "Klatuu" wrote in message news Another whoopsy, sorry, but it is hard to get it right without VBE correcting me as I go. This line: .FindFirst "[Unit_ID] = " & .txtSearch Should be .FindFirst "[Unit_ID] = " & Me.txtSearch -- Dave Hargis, Microsoft Access MVP |
#22
|
|||
|
|||
Search Feature
Just found that I had a space between the Me and the period. So the error
went away but it still does not see the current records? It is trying to work The correct message box comes up when the txtSearch field is blank but existing data is not being found? thanks, Rohn "Rohn" wrote in message ... txtSearch is the text box on the same form as the button with this VB control. "Klatuu" wrote in message ... Is txtSearch the name of a control on the form the code is in or it is on a subform or different form? -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Thanks for the explaination of the two fields. I took out the first field since we are searching for Alphanumeric data (like Z004 or Y142K). The new error is a Compile Error: Invalid or Unqualified Reference on: ..txtSearch in this peice of the code. 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" Thanks for sticking with this issue! "Klatuu" wrote in message ... You are using both versions of what should be one line. |
#23
|
|||
|
|||
Search Feature
That doesn't make any sense, then.
Try going into the VB Editor and commenting out that line. Then type it in again and when you type Me. The intellisense dropdown should have txtSearch in it. If it doesn't then there is a naming problem. If it does, then select it and see what happens. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Thanks for the explaination of the two fields. I took out the first field since we are searching for Alphanumeric data (like Z004 or Y142K). The new error is a Compile Error: Invalid or Unqualified Reference on: ..txtSearch in this peice of the code. 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" Thanks for sticking with this issue! "Klatuu" wrote in message ... You are using both versions of what should be one line. 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" As the comments say, if Unit_ID ia a numeric field in the form's record set, use the first version and delete the second version. If it is a text field, delete the first version and use the second version. Note the text version encloses the search value in quotes which is correct syntax when seaching for text. You do not use any delimiters when seaching for a numeric value. And, FYI, if you are searching a date field, the delimiters are the # sign: .FindFirst "[Unit_ID] = #" & Me.txtSearch & "#" -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: great, I added the revised wording. No errors but the search button is not finding existing values! Here is the revised code. 'Performs the search using value entered into txtSearch 'and evaluates this against values in UNIT_ID field Private Sub cmdSearch_Click() 'Check txtSearch for Null value or Nill Entry first. If Nz(Me.txtSearch, vbNullString) = vbNullString Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me.txtSearch.SetFocus Else With Me.RecordsetClone 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" If .NoMatch Then MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus Else MsgBox "Match Found For: " & strSearch, , "Congratulations!" Me.Unit_ID.SetFocus Me.txtSearch = vbNullString End If End With End If End Sub "Klatuu" wrote in message news Another whoopsy, sorry, but it is hard to get it right without VBE correcting me as I go. This line: .FindFirst "[Unit_ID] = " & .txtSearch Should be .FindFirst "[Unit_ID] = " & Me.txtSearch -- Dave Hargis, Microsoft Access MVP |
#24
|
|||
|
|||
Search Feature
Intellisense has txtSearch in the list! I selected it and it doesn't work
as it is or with the added & """" at the end? Any other ideas? "Klatuu" wrote in message ... That doesn't make any sense, then. Try going into the VB Editor and commenting out that line. Then type it in again and when you type Me. The intellisense dropdown should have txtSearch in it. If it doesn't then there is a naming problem. If it does, then select it and see what happens. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Thanks for the explaination of the two fields. I took out the first field since we are searching for Alphanumeric data (like Z004 or Y142K). The new error is a Compile Error: Invalid or Unqualified Reference on: ..txtSearch in this peice of the code. 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" Thanks for sticking with this issue! "Klatuu" wrote in message ... You are using both versions of what should be one line. 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" As the comments say, if Unit_ID ia a numeric field in the form's record set, use the first version and delete the second version. If it is a text field, delete the first version and use the second version. Note the text version encloses the search value in quotes which is correct syntax when seaching for text. You do not use any delimiters when seaching for a numeric value. And, FYI, if you are searching a date field, the delimiters are the # sign: .FindFirst "[Unit_ID] = #" & Me.txtSearch & "#" -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: great, I added the revised wording. No errors but the search button is not finding existing values! Here is the revised code. 'Performs the search using value entered into txtSearch 'and evaluates this against values in UNIT_ID field Private Sub cmdSearch_Click() 'Check txtSearch for Null value or Nill Entry first. If Nz(Me.txtSearch, vbNullString) = vbNullString Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me.txtSearch.SetFocus Else With Me.RecordsetClone 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" If .NoMatch Then MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus Else MsgBox "Match Found For: " & strSearch, , "Congratulations!" Me.Unit_ID.SetFocus Me.txtSearch = vbNullString End If End With End If End Sub "Klatuu" wrote in message news Another whoopsy, sorry, but it is hard to get it right without VBE correcting me as I go. This line: .FindFirst "[Unit_ID] = " & .txtSearch Should be .FindFirst "[Unit_ID] = " & Me.txtSearch -- Dave Hargis, Microsoft Access MVP |
#25
|
|||
|
|||
Search Feature
Save the mdb and do a compact and repair.
There is something wrong with the form. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Intellisense has txtSearch in the list! I selected it and it doesn't work as it is or with the added & """" at the end? Any other ideas? "Klatuu" wrote in message ... That doesn't make any sense, then. Try going into the VB Editor and commenting out that line. Then type it in again and when you type Me. The intellisense dropdown should have txtSearch in it. If it doesn't then there is a naming problem. If it does, then select it and see what happens. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Thanks for the explaination of the two fields. I took out the first field since we are searching for Alphanumeric data (like Z004 or Y142K). The new error is a Compile Error: Invalid or Unqualified Reference on: ..txtSearch in this peice of the code. 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" Thanks for sticking with this issue! "Klatuu" wrote in message ... You are using both versions of what should be one line. 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" As the comments say, if Unit_ID ia a numeric field in the form's record set, use the first version and delete the second version. If it is a text field, delete the first version and use the second version. Note the text version encloses the search value in quotes which is correct syntax when seaching for text. You do not use any delimiters when seaching for a numeric value. And, FYI, if you are searching a date field, the delimiters are the # sign: .FindFirst "[Unit_ID] = #" & Me.txtSearch & "#" -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: great, I added the revised wording. No errors but the search button is not finding existing values! Here is the revised code. 'Performs the search using value entered into txtSearch 'and evaluates this against values in UNIT_ID field Private Sub cmdSearch_Click() 'Check txtSearch for Null value or Nill Entry first. If Nz(Me.txtSearch, vbNullString) = vbNullString Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me.txtSearch.SetFocus Else With Me.RecordsetClone 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" If .NoMatch Then MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus Else MsgBox "Match Found For: " & strSearch, , "Congratulations!" Me.Unit_ID.SetFocus Me.txtSearch = vbNullString End If End With End If End Sub "Klatuu" wrote in message news Another whoopsy, sorry, but it is hard to get it right without VBE correcting me as I go. This line: .FindFirst "[Unit_ID] = " & .txtSearch Should be .FindFirst "[Unit_ID] = " & Me.txtSearch -- Dave Hargis, Microsoft Access MVP |
#26
|
|||
|
|||
Search Feature
I am hosed!!!!!! Compact and Repair did nothing!
An I stream line the Search to just do the search and eliminate the popup boxes if that is adding some complications to the functionality! Thanks for your help so far! Rohn "Klatuu" wrote in message ... Save the mdb and do a compact and repair. There is something wrong with the form. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Intellisense has txtSearch in the list! I selected it and it doesn't work as it is or with the added & """" at the end? Any other ideas? "Klatuu" wrote in message ... That doesn't make any sense, then. Try going into the VB Editor and commenting out that line. Then type it in again and when you type Me. The intellisense dropdown should have txtSearch in it. If it doesn't then there is a naming problem. If it does, then select it and see what happens. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Thanks for the explaination of the two fields. I took out the first field since we are searching for Alphanumeric data (like Z004 or Y142K). The new error is a Compile Error: Invalid or Unqualified Reference on: ..txtSearch in this peice of the code. 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" Thanks for sticking with this issue! "Klatuu" wrote in message ... You are using both versions of what should be one line. 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" As the comments say, if Unit_ID ia a numeric field in the form's record set, use the first version and delete the second version. If it is a text field, delete the first version and use the second version. Note the text version encloses the search value in quotes which is correct syntax when seaching for text. You do not use any delimiters when seaching for a numeric value. And, FYI, if you are searching a date field, the delimiters are the # sign: .FindFirst "[Unit_ID] = #" & Me.txtSearch & "#" -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: great, I added the revised wording. No errors but the search button is not finding existing values! Here is the revised code. 'Performs the search using value entered into txtSearch 'and evaluates this against values in UNIT_ID field Private Sub cmdSearch_Click() 'Check txtSearch for Null value or Nill Entry first. If Nz(Me.txtSearch, vbNullString) = vbNullString Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me.txtSearch.SetFocus Else With Me.RecordsetClone 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" If .NoMatch Then MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus Else MsgBox "Match Found For: " & strSearch, , "Congratulations!" Me.Unit_ID.SetFocus Me.txtSearch = vbNullString End If End With End If End Sub "Klatuu" wrote in message news Another whoopsy, sorry, but it is hard to get it right without VBE correcting me as I go. This line: .FindFirst "[Unit_ID] = " & .txtSearch Should be .FindFirst "[Unit_ID] = " & Me.txtSearch -- Dave Hargis, Microsoft Access MVP |
#27
|
|||
|
|||
Search Feature
I Think I got the number of quotes correct, but just as a text, change this
line: .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" To: .FindFirst "[Unit_ID] = '" & Me.txtSearch & "'" Expanded for clarity: .FindFirst "[Unit_ID] = ' " & Me.txtSearch & " ' " It shouldn't make a difference, but just in case I got the quotes wrong. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: I am hosed!!!!!! Compact and Repair did nothing! An I stream line the Search to just do the search and eliminate the popup boxes if that is adding some complications to the functionality! Thanks for your help so far! Rohn "Klatuu" wrote in message ... Save the mdb and do a compact and repair. There is something wrong with the form. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Intellisense has txtSearch in the list! I selected it and it doesn't work as it is or with the added & """" at the end? Any other ideas? "Klatuu" wrote in message ... That doesn't make any sense, then. Try going into the VB Editor and commenting out that line. Then type it in again and when you type Me. The intellisense dropdown should have txtSearch in it. If it doesn't then there is a naming problem. If it does, then select it and see what happens. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Thanks for the explaination of the two fields. I took out the first field since we are searching for Alphanumeric data (like Z004 or Y142K). The new error is a Compile Error: Invalid or Unqualified Reference on: ..txtSearch in this peice of the code. 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" Thanks for sticking with this issue! "Klatuu" wrote in message ... You are using both versions of what should be one line. 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" As the comments say, if Unit_ID ia a numeric field in the form's record set, use the first version and delete the second version. If it is a text field, delete the first version and use the second version. Note the text version encloses the search value in quotes which is correct syntax when seaching for text. You do not use any delimiters when seaching for a numeric value. And, FYI, if you are searching a date field, the delimiters are the # sign: .FindFirst "[Unit_ID] = #" & Me.txtSearch & "#" -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: great, I added the revised wording. No errors but the search button is not finding existing values! Here is the revised code. 'Performs the search using value entered into txtSearch 'and evaluates this against values in UNIT_ID field Private Sub cmdSearch_Click() 'Check txtSearch for Null value or Nill Entry first. If Nz(Me.txtSearch, vbNullString) = vbNullString Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me.txtSearch.SetFocus Else With Me.RecordsetClone 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" If .NoMatch Then MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus Else MsgBox "Match Found For: " & strSearch, , "Congratulations!" Me.Unit_ID.SetFocus Me.txtSearch = vbNullString End If End With End If End Sub "Klatuu" wrote in message news Another whoopsy, sorry, but it is hard to get it right without VBE correcting me as I go. This line: .FindFirst "[Unit_ID] = " & .txtSearch Should be .FindFirst "[Unit_ID] = " & Me.txtSearch -- Dave Hargis, Microsoft Access MVP |
#28
|
|||
|
|||
Search Feature
Dave,
I removed the entire line and put in a stripped down version: .FindFirst [Unit_ID] = Me.txtSearch with no luck but my error is: Run-time error 3421: Data type conversion error. I think it is trying to work! I even tried the form.control wording: .FindFirst "[(F)FRDF_CONTACT.Unit_ID] = ' " & Me.txtSearch & " ' " but didn't have any luck. "Klatuu" wrote in message ... I Think I got the number of quotes correct, but just as a text, change this line: .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" To: .FindFirst "[Unit_ID] = '" & Me.txtSearch & "'" Expanded for clarity: .FindFirst "[Unit_ID] = ' " & Me.txtSearch & " ' " It shouldn't make a difference, but just in case I got the quotes wrong. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: I am hosed!!!!!! Compact and Repair did nothing! An I stream line the Search to just do the search and eliminate the popup boxes if that is adding some complications to the functionality! Thanks for your help so far! Rohn "Klatuu" wrote in message ... Save the mdb and do a compact and repair. There is something wrong with the form. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Intellisense has txtSearch in the list! I selected it and it doesn't work as it is or with the added & """" at the end? Any other ideas? "Klatuu" wrote in message ... That doesn't make any sense, then. Try going into the VB Editor and commenting out that line. Then type it in again and when you type Me. The intellisense dropdown should have txtSearch in it. If it doesn't then there is a naming problem. If it does, then select it and see what happens. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Thanks for the explaination of the two fields. I took out the first field since we are searching for Alphanumeric data (like Z004 or Y142K). The new error is a Compile Error: Invalid or Unqualified Reference on: ..txtSearch in this peice of the code. 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" Thanks for sticking with this issue! "Klatuu" wrote in message ... You are using both versions of what should be one line. 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" As the comments say, if Unit_ID ia a numeric field in the form's record set, use the first version and delete the second version. If it is a text field, delete the first version and use the second version. Note the text version encloses the search value in quotes which is correct syntax when seaching for text. You do not use any delimiters when seaching for a numeric value. And, FYI, if you are searching a date field, the delimiters are the # sign: .FindFirst "[Unit_ID] = #" & Me.txtSearch & "#" -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: great, I added the revised wording. No errors but the search button is not finding existing values! Here is the revised code. 'Performs the search using value entered into txtSearch 'and evaluates this against values in UNIT_ID field Private Sub cmdSearch_Click() 'Check txtSearch for Null value or Nill Entry first. If Nz(Me.txtSearch, vbNullString) = vbNullString Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me.txtSearch.SetFocus Else With Me.RecordsetClone 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" If .NoMatch Then MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus Else MsgBox "Match Found For: " & strSearch, , "Congratulations!" Me.Unit_ID.SetFocus Me.txtSearch = vbNullString End If End With End If End Sub "Klatuu" wrote in message news Another whoopsy, sorry, but it is hard to get it right without VBE correcting me as I go. This line: .FindFirst "[Unit_ID] = " & .txtSearch Should be .FindFirst "[Unit_ID] = " & Me.txtSearch -- Dave Hargis, Microsoft Access MVP |
#29
|
|||
|
|||
Search Feature
Here is a question! The form is set to Data Entry on initial open will
that affect this type of code? "Klatuu" wrote in message ... I Think I got the number of quotes correct, but just as a text, change this line: .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" To: .FindFirst "[Unit_ID] = '" & Me.txtSearch & "'" Expanded for clarity: .FindFirst "[Unit_ID] = ' " & Me.txtSearch & " ' " It shouldn't make a difference, but just in case I got the quotes wrong. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: I am hosed!!!!!! Compact and Repair did nothing! An I stream line the Search to just do the search and eliminate the popup boxes if that is adding some complications to the functionality! Thanks for your help so far! Rohn "Klatuu" wrote in message ... Save the mdb and do a compact and repair. There is something wrong with the form. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Intellisense has txtSearch in the list! I selected it and it doesn't work as it is or with the added & """" at the end? Any other ideas? "Klatuu" wrote in message ... That doesn't make any sense, then. Try going into the VB Editor and commenting out that line. Then type it in again and when you type Me. The intellisense dropdown should have txtSearch in it. If it doesn't then there is a naming problem. If it does, then select it and see what happens. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Thanks for the explaination of the two fields. I took out the first field since we are searching for Alphanumeric data (like Z004 or Y142K). The new error is a Compile Error: Invalid or Unqualified Reference on: ..txtSearch in this peice of the code. 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" Thanks for sticking with this issue! "Klatuu" wrote in message ... You are using both versions of what should be one line. 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" As the comments say, if Unit_ID ia a numeric field in the form's record set, use the first version and delete the second version. If it is a text field, delete the first version and use the second version. Note the text version encloses the search value in quotes which is correct syntax when seaching for text. You do not use any delimiters when seaching for a numeric value. And, FYI, if you are searching a date field, the delimiters are the # sign: .FindFirst "[Unit_ID] = #" & Me.txtSearch & "#" -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: great, I added the revised wording. No errors but the search button is not finding existing values! Here is the revised code. 'Performs the search using value entered into txtSearch 'and evaluates this against values in UNIT_ID field Private Sub cmdSearch_Click() 'Check txtSearch for Null value or Nill Entry first. If Nz(Me.txtSearch, vbNullString) = vbNullString Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me.txtSearch.SetFocus Else With Me.RecordsetClone 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" If .NoMatch Then MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus Else MsgBox "Match Found For: " & strSearch, , "Congratulations!" Me.Unit_ID.SetFocus Me.txtSearch = vbNullString End If End With End If End Sub "Klatuu" wrote in message news Another whoopsy, sorry, but it is hard to get it right without VBE correcting me as I go. This line: .FindFirst "[Unit_ID] = " & .txtSearch Should be .FindFirst "[Unit_ID] = " & Me.txtSearch -- Dave Hargis, Microsoft Access MVP |
#30
|
|||
|
|||
Search Feature
I set both the Main form and Sub Form to No DataEntry to allow all records
to be visible via the form navigation and ran the new code, the popup actually says "record found" but it does not change the current record set to that found record? This is very close, I just can't tell what the problem is. I really appreicate all the help you have given on this one. I have learned alot from you about how these code actually work. Thanks again for the assistance. Rohn "Rohn" wrote in message ... Here is a question! The form is set to Data Entry on initial open will that affect this type of code? "Klatuu" wrote in message ... I Think I got the number of quotes correct, but just as a text, change this line: .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" To: .FindFirst "[Unit_ID] = '" & Me.txtSearch & "'" Expanded for clarity: .FindFirst "[Unit_ID] = ' " & Me.txtSearch & " ' " It shouldn't make a difference, but just in case I got the quotes wrong. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: I am hosed!!!!!! Compact and Repair did nothing! An I stream line the Search to just do the search and eliminate the popup boxes if that is adding some complications to the functionality! Thanks for your help so far! Rohn "Klatuu" wrote in message ... Save the mdb and do a compact and repair. There is something wrong with the form. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Intellisense has txtSearch in the list! I selected it and it doesn't work as it is or with the added & """" at the end? Any other ideas? "Klatuu" wrote in message ... That doesn't make any sense, then. Try going into the VB Editor and commenting out that line. Then type it in again and when you type Me. The intellisense dropdown should have txtSearch in it. If it doesn't then there is a naming problem. If it does, then select it and see what happens. -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: Thanks for the explaination of the two fields. I took out the first field since we are searching for Alphanumeric data (like Z004 or Y142K). The new error is a Compile Error: Invalid or Unqualified Reference on: ..txtSearch in this peice of the code. 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" Thanks for sticking with this issue! "Klatuu" wrote in message ... You are using both versions of what should be one line. 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" As the comments say, if Unit_ID ia a numeric field in the form's record set, use the first version and delete the second version. If it is a text field, delete the first version and use the second version. Note the text version encloses the search value in quotes which is correct syntax when seaching for text. You do not use any delimiters when seaching for a numeric value. And, FYI, if you are searching a date field, the delimiters are the # sign: .FindFirst "[Unit_ID] = #" & Me.txtSearch & "#" -- Dave Hargis, Microsoft Access MVP "Rohn" wrote: great, I added the revised wording. No errors but the search button is not finding existing values! Here is the revised code. 'Performs the search using value entered into txtSearch 'and evaluates this against values in UNIT_ID field Private Sub cmdSearch_Click() 'Check txtSearch for Null value or Nill Entry first. If Nz(Me.txtSearch, vbNullString) = vbNullString Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me.txtSearch.SetFocus Else With Me.RecordsetClone 'Use this if the table field Unit_ID is numberic .FindFirst "[Unit_ID] = " & Me.txtSearch 'Use this if the table field Unit_ID is text .FindFirst "[Unit_ID] = """ & Me.txtSearch & """" If .NoMatch Then MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus Else MsgBox "Match Found For: " & strSearch, , "Congratulations!" Me.Unit_ID.SetFocus Me.txtSearch = vbNullString End If End With End If End Sub "Klatuu" wrote in message news Another whoopsy, sorry, but it is hard to get it right without VBE correcting me as I go. This line: .FindFirst "[Unit_ID] = " & .txtSearch Should be .FindFirst "[Unit_ID] = " & Me.txtSearch -- Dave Hargis, Microsoft Access MVP |
Thread Tools | |
Display Modes | |
|
|