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

Search Form using Option Buttons

P: 7
Hello all,

I have created a database for my work that stores information for keys/locks, doors and employees.

Specifically the database contains all the information of our lock system, which doors certain locks are attached to and what employees have keys to certain locks.

The form has 3 subforms, 1 is the Key List which is basically the master subform as the other 2 subforms are a Door List and Staff List both of which are linked to the Key List using the [KeyNumber] field.

I am trying to make a search form to this that will allow me to search certain fields in all 3 of the subforms, the fields will be chosen by option buttons. With the search box being just a simple AfterUpdate command.

This is the code I currently have ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub SearchFor_AfterUpdate()
  2. On Error GoTo Err_SearchFor_AfterUpdate
  3.     Dim MySQL As String
  4.  
  5.     MySQL = "SELECT KeyNumber, KeyCode, KeyName "
  6.     MySQL = MySQL & "FROM fKeyList "
  7.  
  8.     If InStr(1, Me.SearchFor, Chr(34)) Then
  9.         MsgBox "Do not use quotes"
  10.     Else
  11.  
  12.         Select Case Me.SearchField
  13.  
  14.         Case 1  'keynumber
  15.             MySQL = MySQL & "WHERE [KeyNumber] like " & Chr(34) & Me![SearchFor] & "" & Chr(34)
  16.         Case 2  'keycode
  17.             MySQL = MySQL & "WHERE [KeyCode] like " & Chr(34) & Me![SearchFor] & "" & Chr(34)
  18.         Case 3  'keyname
  19.             If Me.SearchFor.Value Like "*[!',!A-Z,!a-z]*" Then
  20.                 MsgBox "Must use letters or single apostrophe. No spaces or special characters"
  21.             Else
  22.                 MySQL = MySQL & "WHERE [KeyName] like " & Chr(34) & Me![SearchFor] & "" & Chr(34)
  23.  
  24.             End If
  25.  
  26.         Case 4  'doornumber
  27.             MySQL = MySQL & "WHERE (KeyNumber In (SELECT DISTINCT KeyNumber FROM sfDoorList WHERE DoorNumber LIKE " & Chr(34) & "" & Me![SearchFor] & "" & Chr(34) & "))"
  28.  
  29.         Case 5  'lastname
  30.             If Me.SearchFor.Value Like "*[!',!A-Z,!a-z]*" Then
  31.                 MsgBox "Must use letters or single apostrophe. No spaces or special characters"
  32.             Else
  33.                 MySQL = MySQL & "WHERE (KeyNumber In (SELECT DISTINCT KeyNumber FROM sfStaffList WHERE StaffLastName LIKE " & Chr(34) & "" & Me![SearchFor] & "" & Chr(34) & "))"
  34.  
  35.             End If
  36.         End Select
  37.  
  38.     'MsgBox MySQL
  39.     Me.sfKeyList.Form.RecordSource = MySQL
  40.     End If
  41.  
  42. Exit_SearchFor_AfterUpdate:
  43.     Exit Sub
  44.  
  45. Err_SearchFor_AfterUpdate:
  46.     MsgBox Err.Description
  47.     Resume Exit_SearchFor_AfterUpdate
  48.  
  49. End Sub
The problem is it's not working. I keep getting this error when searching for anything using any of the options ...

The record source 'SELECT KeyNumber, KeyCode, KeyName FROM fKeyList WHERE [KeyNumber] like "****" specified on
this form or report does not exist.

The **** indicates the item i'm searching for.

Any help would be appreciated.

Thanks
Mar 2 '08 #1
Share this Question
Share on Google+
8 Replies


Expert Mod 2.5K+
P: 2,545
Expand|Select|Wrap|Line Numbers
  1. ...MySQL = MySQL & "WHERE [KeyNumber] like " & Chr(34) & Me![SearchFor] & "" & Chr(34)
Hi Nmarks. At a glance the SQL looks fine - but then I notice the use of Chr(34) before and after all search phrases as above. ASCII 34 is the double-quote character ("). I guess it should instead be the single quote. Why not just refer directly to the character involved, like this:
Expand|Select|Wrap|Line Numbers
  1. MySQL = MySQL & "WHERE [KeyNumber] like '" & Me![SearchFor] & "'"
For clarity I would suggest not using Chr at all in your search strings unless you want to refer to a non-printable character.

-Stewart
ps Access string literals within a string built as above are referred to within single quotes, although strings in general are referred to within double quotes.
Mar 3 '08 #2

Expert Mod 2.5K+
P: 2,545
Hi again. Notwithstanding my concerns about the quote marks, it isn't that. I have tried a skeleton version of your code in a new form using one of my own tables, but without the subforms. The SQL works without error on the recordsource of a main form.

After writing this I tried it successfully on a subform within a main form. Since everything seems to work, is sfKeyList a valid subform name? I too used
Expand|Select|Wrap|Line Numbers
  1. me.[subformname].form.recordsource = ... 
and it worked fine for me.

-Stewart
Mar 3 '08 #3

P: 7
In order to get all the forms to display in a tabular format like I wanted, and linked together, I had to create the form, then the "main" subform labeled sfKeyList.

The 2 additional subforms are sfDoorList and sfStaffList which are both subforms of sfKeyList.
Mar 4 '08 #4

P: 7
Expand|Select|Wrap|Line Numbers
  1. ...MySQL = MySQL & "WHERE [KeyNumber] like " & Chr(34) & Me![SearchFor] & "" & Chr(34)
Hi Nmarks. At a glance the SQL looks fine - but then I notice the use of Chr(34) before and after all search phrases as above. ASCII 34 is the double-quote character ("). I guess it should instead be the single quote. Why not just refer directly to the character involved, like this:
Expand|Select|Wrap|Line Numbers
  1. MySQL = MySQL & "WHERE [KeyNumber] like '" & Me![SearchFor] & "'"
For clarity I would suggest not using Chr at all in your search strings unless you want to refer to a non-printable character.

-Stewart
ps Access string literals within a string built as above are referred to within single quotes, although strings in general are referred to within double quotes.
I will have to edit this then.

The majority of my Access experience has been via Google and a guy I work with. He uses the Chr(34) a lot and I picked it up along the way.

Thank you for the advice.
Mar 4 '08 #5

Expert Mod 2.5K+
P: 2,545
I will have to edit this then.

The majority of my Access experience has been via Google and a guy I work with. He uses the Chr(34) a lot and I picked it up along the way.

Thank you for the advice.
Hi again. Just one more thought, since the SQL works fine for strings - it is a string you are comparing, isn't it? If the unique key number is a numeric value, not a string, the SQL 'where' condition will fail (there are no quote marks at all used when comparing numeric values, nor is the string comparison 'like' used). Long shot, though, given that your where clause seems well thought-out.

-Stewart
Mar 4 '08 #6

P: 7
Hi again. Just one more thought, since the SQL works fine for strings - it is a string you are comparing, isn't it? If the unique key number is a numeric value, not a string, the SQL 'where' condition will fail (there are no quote marks at all used when comparing numeric values, nor is the string comparison 'like' used). Long shot, though, given that your where clause seems well thought-out.

-Stewart
The unique Key Number field is a letter/numeric value. It contains 2 letters followed by 6 numbers manually entered by us, the user.

An example is "HB123456".

The KeyCode and DoorNumber fields are also setup the same way. Just less letters and numbers.
Mar 4 '08 #7

P: 7
Alright I finally got it.

This is the end result of my code ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub SearchFor_AfterUpdate()
  2. On Error GoTo Err_SearchFor_AfterUpdate
  3.     Dim MySQL As String
  4.  
  5.     MySQL = "SELECT KeyNumber, KeyCode, KeyName "
  6.     MySQL = MySQL & "FROM tKey "
  7.  
  8.     If InStr(1, Me.SearchFor) Then
  9.         MsgBox "Do not use quotes"
  10.     Else
  11.  
  12.         Select Case Me.SearchField
  13.  
  14.         Case 1  'keynumber
  15.             MySQL = MySQL & "WHERE [KeyNumber] like '" & Me![SearchFor] & "'"
  16.         Case 2  'keycode
  17.             MySQL = MySQL & "WHERE [KeyCode] like '" & Me![SearchFor] & "'"
  18.         Case 3  'keyname
  19.             If Me.SearchFor.Value Like "*[!',!A-Z,!a-z]*" Then
  20.                 MsgBox "Must use letters or single apostrophe. No spaces or special characters"
  21.             Else
  22.                 MySQL = MySQL & "WHERE [KeyName] like '" & Me![SearchFor] & "'"
  23.  
  24.             End If
  25.  
  26.         Case 4  'doornumber
  27.             MySQL = MySQL & "WHERE (KeyNumber In (SELECT DISTINCT KeyNumber FROM tDoor WHERE DoorNumber LIKE " & Chr(34) & "" & Me![SearchFor] & "" & Chr(34) & "))"
  28.  
  29.         Case 5  'lastname
  30.             If Me.SearchFor.Value Like "*[!',!A-Z,!a-z]*" Then
  31.                 MsgBox "Must use letters or single apostrophe. No spaces or special characters"
  32.             Else
  33.                 MySQL = MySQL & "WHERE (KeyNumber In (SELECT DISTINCT KeyNumber FROM tStaff WHERE StaffLastName LIKE " & Chr(34) & "" & Me![SearchFor] & "" & Chr(34) & "))"
  34.         End If
  35.         End Select
  36.  
  37.     'MsgBox MySQL
  38.     Me.sfKeyList.Form.RecordSource = MySQL
  39.     End If
  40.  
  41. Exit_SearchFor_AfterUpdate:
  42.     Exit Sub
  43.  
  44. Err_SearchFor_AfterUpdate:
  45.     MsgBox Err.Description
  46.     Resume Exit_SearchFor_AfterUpdate
  47.  
  48. End Sub
I replaced all the FROM fields to reference the tables directly as opposed the subforms on the form.

This worked like a charm. I did also clean up the code a bit as you advised, more so in the first section I still have to go through the bottom 2 sections that reference the other subforms, I tried once already but keep getting a syntax error so I just need to work it out a bit.

Thank you for all your help though it was greatly appreciated.
Mar 4 '08 #8

Expert Mod 2.5K+
P: 2,545
Hi again. Well done for solving what was a very puzzling problem. You did all the work, and should take all the credit!

Cheers

Stewart
Mar 4 '08 #9

Post your reply

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