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

Search And Return Record in Form, Access 2002

P: 19

I'm trying to complete a form that will search previous results entered in a table and return the row items based on a ID number. My current code is below:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd22_Click()
  2.     Dim strStudentRef As String
  3.     Dim strSearch As String
  5. DoCmd.ShowAllRecords
  6.     DoCmd.GoToControl ("ID")
  7.     DoCmd.FindRecord Me!text1
  9.     ID.SetFocus
  10.     strStudentRef = ID.Text
  11.     text1.SetFocus
  12.     strSearch = text1.Text
  14.     If strStudentRef = strSearch Then
  15.         MsgBox "Match Found For: " & strSearch & "Congratulations!"
  16.         ID.SetFocus
  17.         text1 = ""
  19.     'If value not found sets focus back to text1 and shows msgbox
  20.         Else
  21.           MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
  22.             , "Invalid Search Criterion!"
  23.             text1.SetFocus
  24.     End If
  25. End Sub
So I simply search the ID field column for the value entered in textbox1 and when found the program above returns the values in the adjacent fields in that row to the form.

My problem lies here.

In the ID field I use an Autonumber to generate a consecutive numbering sequence. In the Autonumber format I include a prefix of "HB1"-XXXXXXX.

If I enter the search number as HB1-XXXXXX, nothing is returned. If I enter the search number as -XXXXXX (without the prefix) the record is found however it returns the message that the number could not be found!!

Is there a way I can get the prefix included in the search text?

Nov 6 '07 #1
Share this Question
Share on Google+
1 Reply

P: 19
If it's any interest to anybody I fixed the problem.

As HB1 is always fixed, I simply put HB1 as a label outside the text box I'm typing the search value into (prefix)!! The user should hopefully then only enter the XXXXXX value, and it will appear the complete value is being considered....well so I'm guessing :)
Nov 7 '07 #2

Post your reply

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