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

Help Please with Search form

P: 18
Hello, the users on this site seem to be very helpful. I hope you guys can help me. I have been searching for the past 2 weeks on how to create a search form for my database at work. I'm not really experienced with working with code, although I have been able to pull a couple of them off. This is what I did:

I have two databases my co workers use - one for the trackers and one for the shipping guy. (Since he has absolutly no computer experience) Then I took their info and combined them in a query, then made the query into a table. The update happens once a week.

Next I created a new form (unbound). Then I put the sub form of the query make table in the footer. Then tried changing the field names to conform to my own database. But I am having problems. The good news is, it's doing something. The bad news is, it's not doing what I want it to. If someone can walk me through the steps of writing the If is NOT isNull(ME. blah....

1. I get the is not is Null.
2. I do not understand the Me. or Me!

I want the user to be able to enter maybe 3 of the fields in order to narrow the search. (or i would have just done a form filter.)

Please help...

Any suggestion, pointer or tip is apprieated.
Jan 27 '08 #1
Share this Question
Share on Google+
16 Replies


puppydogbuddy
Expert 100+
P: 1,923
Hello, the users on this site seem to be very helpful. I hope you guys can help me. I have been searching for the past 2 weeks on how to create a search form for my database at work. I'm not really experienced with working with code, although I have been able to pull a couple of them off. This is what I did:

I have two databases my co workers use - one for the trackers and one for the shipping guy. (Since he has absolutly no computer experience) Then I took their info and combined them in a query, then made the query into a table. The update happens once a week.

Next I created a new form (unbound). Then I put the sub form of the query make table in the footer. Then tried changing the field names to conform to my own database. But I am having problems. The good news is, it's doing something. The bad news is, it's not doing what I want it to. If someone can walk me through the steps of writing the If is NOT isNull(ME. blah....

1. I get the is not is Null.
2. I do not understand the Me. or Me!

I want the user to be able to enter maybe 3 of the fields in order to narrow the search. (or i would have just done a form filter.)

Please help...

Any suggestion, pointer or tip is apprieated.

Here is an example of building a filter string from the selections made from comboboxes.
Expand|Select|Wrap|Line Numbers
  1. strFilter = ""
  2. If Not IsNull(Me!Combo1) Then strFilter = strFilter & "[field1]=" & Chr(34) & Me!Combo1 & Chr(34) & " AND "
  3. If Not IsNull(Me!Combo2) Then strFilter = strFilter & "[field2]=" & Chr(34) & Me!Combo2 & Chr(34) & " AND "
  4. If (Me!Combo3.Column(1) = 1) And Not IsNull(Me!Combo4) Then strFilter = strFilter & "[field3]=" & Me!Combo4 & " AND "
  5.  
  6. If strFilter <> "" Then
  7.     strFilter = Left$(strFilter, Len(strFilter) - 5)
  8.     Me.Filter = strFilter
  9.     Me.FilterOn = True
  10. End If
__________________________________________________ ______________
As far as Me. vs Me!:
the ! is known as the bang operator and it is used to indicate a field name or member of a collection. Me![Field] or Me![YourCombo]
The . is known as the dot operator and is used to indicate a property or method
Me![Field].Recalc or Me![YourCombo].Requery
Jan 27 '08 #2

P: 18
Oh I see!!! I have just enrolled back into school to learn more about code. I am very excited to work more with it. Hopefully I will get to the point to give out tips and tricks like you.

I am going to try this and let you know how I do. Thank you so much for your help!

Stephanie
Jan 28 '08 #3

P: 18
Ok. I'm not doing something right. I keep getting an error message saying the variable is not defined.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2.  
  3. If Not IsNull(Me!City) Then strFilter = strFilterCity & "[City]=" & Chr(34) & 
  4.     Me! City & Chr(34) & " AND "
  5. If Not IsNull(Me!Carrier) Then strFilter = strFilter & "[Carrier]=" & Chr(34) & 
  6.     Me!Carrier & Chr(34) & " AND "
  7. If (Me!PO#.Column(1) = 1) And Not IsNull(Me!PO#) Then strFilter = strFilter
  8.     & "[PO#]=" & Me!Combo4 & " AND "
  9.  
  10. If strFilter <> "" Then
  11.     strFilter = Left$(strFilter, Len(strFilter) - 5)
  12.     Me.Filter = strFilter
  13.     Me.FilterOn = True
  14. End If
  15.  
  16. End Sub
Do I have to define the table for the filter?

Thank You,
Stephanie
Jan 28 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
Ok. I'm not doing something right. I keep getting an error message saying the varible is not defined.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2.  
  3. If Not IsNull(Me!City) Then strFilter = strFilter & "[City]=" & Chr(34) & 
  4.     Me! City & Chr(34) & " AND "
  5. If Not IsNull(Me!Carrier) Then strFilter = strFilter & "[Carrier]=" & Chr(34) & 
  6.     Me!Carrier & Chr(34) & " AND "
  7. If (Me!PO#.Column(1) = 1) And Not IsNull(Me!PO#) Then strFilter = strFilter
  8.     & "[PO#]=" & Me!Combo4 & " AND "
  9.  
  10. If strFilter <> "" Then
  11.     strFilter = Left$(strFilter, Len(strFilter) - 5)
  12.     Me.Filter = strFilter
  13.     Me.FilterOn = True
  14. End If
  15.  
  16. End Sub
Do I have to define the table for the filter?

Thank You,
Stephanie
Hi Stephanie,
You have to declare the variable strFilter in the declaration section for your procedure with all of your other declarations. Also, I changed strFilterCity to strFilter in line 2:

Dim strFilter As String
Jan 28 '08 #5

jaxjagfan
Expert 100+
P: 254
FYI - When you see "Me" in the code. It normally is the current form that the code is running from. If you have a Form named frmMain which you are running code from, you can reference it using Forms!frmMain. or just Me. A textbox named txtFirstName would be referenced as Me.txtFirstName. VBA is much less structured than some other programming languages but you can accomplish a lot with it.

I have found the Bible series (IE Access 2003 Bible) to be extremely useful references when I was learning and still use them occassionally now.
Jan 28 '08 #6

P: 18
Thank you for the replies! Your descriptions were very helpful for me to understand this more. I loved the idea for the book too. I went to the library and found one book for SQL and one for VBA. Does anyone else have any good recommendations?
Jan 30 '08 #7

puppydogbuddy
Expert 100+
P: 1,923
Hi Stephanie,
You have to declare the variable strFilter in the declaration section for your procedure with all of your other declarations. Also, I changed strFilterCity to strFilter in line 2:

Dim strFilter As String
Stephanie,
Did you implement the code changes I suggested? What happened?
Jan 30 '08 #8

P: 18
Stephanie,
Did you implement the code changes I suggested? What happened?

I changed the code and it still isn't working. I don't think it was becuase of your suggestion, but becuase I am not doing the record sorce right. So I deleted the form and I'm starting over.

I am sorry if I wasited your time.
Jan 31 '08 #9

puppydogbuddy
Expert 100+
P: 1,923
I changed the code and it still isn't working. I don't think it was becuase of your suggestion, but becuase I am not doing the record sorce right. So I deleted the form and I'm starting over.

I am sorry if I wasited your time.
Stephanie,
You did not waste my time, but it is a shame you started over because I think you were pretty close to making it work. Here is a simplistic overview of how the Search form would work:

Your record source should have been a generalized query that would include all the values from the tables ........ something like Select * From YourTable

Then your comboboxes would act together like a multi-constraint where clause to limit the values returned from the record source to those that matched the selections in the comboboxes and/or textboxes.
Jan 31 '08 #10

P: 18
Stephanie,
You did not waste my time, but it is a shame you started over because I think you were pretty close to making it work. Here is a simplistic overview of how the Search form would work:

Your record source should have been a generalized query that would include all the values from the tables ........ something like Select * From YourTable

Then your comboboxes would act together like a multi-constraint where clause to limit the values returned from the record source to those that matched the selections in the comboboxes and/or textboxes.
Puppydogbuddy,

I think I am very close too. I really need to get this working so I can move on. I am so obessed with it, I'm dreaming about it. It's really bothering me I cannot complete the form.

So if I made a make-table query and put that in the record source, that wouldn't work? Because that's what I did. This guy that is using the form knows pretty close to nothing about computers. He knows how to type, use e-mail and the internet, so i made him his own database and his form pops up when he opens it. Then he enters his 5 fields, and he's done. His info goes back to the "master" database. but if he wants to go back and find a piece of info that he has entered, he needs values from the "master", so I made all the tables link, then made a query from them, and then made it a table. Do you think that's where the mistake is?
Jan 31 '08 #11

puppydogbuddy
Expert 100+
P: 1,923
Puppydogbuddy,

I think I am very close too. I really need to get this working so I can move on. I am so obessed with it, I'm dreaming about it. It's really bothering me I cannot complete the form.

So if I made a make-table query and put that in the record source, that wouldn't work? Because that's what I did. This guy that is using the form knows pretty close to nothing about computers. He knows how to type, use e-mail and the internet, so i made him his own database and his form pops up when he opens it. Then he enters his 5 fields, and he's done. His info goes back to the "master" database. but if he wants to go back and find a piece of info that he has entered, he needs values from the "master", so I made all the tables link, then made a query from them, and then made it a table. Do you think that's where the mistake is?
Yes, if it is filtered. No, as long as the table represents a unfiltered view of the database and is used as the record source of the form....Select * From YourMakeTable.
Try placing a code break on the line Me.Filter = strFilter, then go to the immediate window, type ?strFilter;hit the Enter key and post it back here.
Jan 31 '08 #12

P: 18
Yes, if it is filtered. No, as long as the table represents a unfiltered view of the database and is used as the record source of the form....Select * From YourMakeTable.
Try placing a code break on the line Me.Filter = strFilter, then go to the immediate window, type ?strFilter;hit the Enter key and post it back here.
Puppydogbuddy,

I put the form of the table in the footer of the Search Form and made it the record source.

Then I wrote this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Search_Click()
  2.     Const cInvalidDateError As String = "You have entered an invalid date."
  3.     Dim strWhere As String
  4.     Dim strError As String
  5.  
  6.     strWhere = "1=1"
  7.  
  8.    ' If PO #
  9.     If Nz(Me!PO#) <> "" Then
  10.         ' Add it to the predicate - match on leading characters
  11.         strWhere = strWhere & " AND " & "All_Shipping_Info.PO# Like '*" & Me!PO# & "*'"
  12.     End If
  13.  
  14.  
  15.     If strError <> "" Then
  16.         MsgBox strError
  17.     Else
  18.         'DoCmd.OpenForm "All Shipping Info", acFormDS, , strWhere, acFormEdit, acWindowNormal
  19.         If Not Me.FormFooter.Visible Then
  20.             Me.FormFooter.Visible = True
  21.             DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
  22.         End If
  23.         Me.All_Shipping_Information.Form.Filter = strWhere
  24.         Me.All_Shipping_Information.Form.FilterOn = True
  25.     End If
  26. End Sub
Then when I tried testing it, it came back with " Shipping Information can't find the field 'PO' referred to in your expression."
Jan 31 '08 #13

puppydogbuddy
Expert 100+
P: 1,923
Puppydogbuddy,

I put the form of the table in the footer of the Search Form and made it the record source.

Then I wrote this:

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If PO #
If Nz(Me!PO#) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "All_Shipping_Info.PO# Like '*" & Me!PO# & "*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "All Shipping Info", acFormDS, , strWhere, acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.All_Shipping_Information.Form.Filter = strWhere
Me.All_Shipping_Information.Form.FilterOn = True
End If
End Sub

Then when I tried testing it, it came back with " Shipping Information can't find the field 'PO' referred to in your expression."
Stephanie,
The # sign is used as a delimiter for date strings in Access. Advise you not to use for field names, but if you do, you need to encapsulate the field name in brackets. Change references for PO# to [PO#] and tell me what happens.
Jan 31 '08 #14

NeoPa
Expert Mod 15k+
P: 31,707
Steph,

As a little extra something to look at on this, have a browse of Example Filtering on a Form.

PS. This is no reason to interrupt the conversation with pDog of course, just some extra info that might help you understand things a little better. A little tutorial on filtering a form.
Feb 2 '08 #15

P: 18
Ok... I got side tracked on this project and I revisited my strategy and what I did is really working out for the users... It's not really doing what I wanted, but it's working. I simply just put a FIND RECORD button on the main form, and added a sub form to show the rest of the data they are looking for... Again, I'm still a newbie, but I hope to play with this a little more and perfect it.

Then, I made another command button that prints the report for the current record. They LOVE it! They think I'm some kind of genius or something... if they only knew...

Thank you for all your help.


Stephanie
Feb 27 '08 #16

NeoPa
Expert Mod 15k+
P: 31,707
Very pleased to hear it Steph :)
Why shouldn't they think you're a genius anyway? You're the one who found the solution and implemented it.
Feb 27 '08 #17

Post your reply

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