Help Please with Search form

[StephR]

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.

[puppydogbuddy]

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.

strFilter = ""
If Not IsNull(Me!Combo1) Then strFilter = strFilter & "[field1]=" & Chr(34) & Me!Combo1 & Chr(34) & " AND "
If Not IsNull(Me!Combo2) Then strFilter = strFilter & "[field2]=" & Chr(34) & Me!Combo2 & Chr(34) & " AND "
If (Me!Combo3.Column(1) = 1) And Not IsNull(Me!Combo4) Then strFilter = strFilter & "[field3]=" & Me!Combo4 & " AND "

If strFilter <> "" Then
    strFilter = Left$(strFilter, Len(strFilter) - 5)
    Me.Filter = strFilter
    Me.FilterOn = True
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

[StephR]

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

[StephR]

Ok. I'm not doing something right. I keep getting an error message saying the variable is not defined.

Private Sub cmdFilter_Click()

If Not IsNull(Me!City) Then strFilter = strFilterCity & "[City]=" & Chr(34) & 
    Me! City & Chr(34) & " AND "
If Not IsNull(Me!Carrier) Then strFilter = strFilter & "[Carrier]=" & Chr(34) & 
    Me!Carrier & Chr(34) & " AND "
If (Me!PO#.Column(1) = 1) And Not IsNull(Me!PO#) Then strFilter = strFilter
    & "[PO#]=" & Me!Combo4 & " AND "
 
If strFilter <> "" Then
    strFilter = Left$(strFilter, Len(strFilter) - 5)
    Me.Filter = strFilter
    Me.FilterOn = True
End If

End Sub

Do I have to define the table for the filter?

Thank You,
Stephanie

[puppydogbuddy]

Ok. I'm not doing something right. I keep getting an error message saying the varible is not defined.

Private Sub cmdFilter_Click()
  
If Not IsNull(Me!City) Then strFilter = strFilter & "[City]=" & Chr(34) & 
    Me! City & Chr(34) & " AND "
If Not IsNull(Me!Carrier) Then strFilter = strFilter & "[Carrier]=" & Chr(34) & 
    Me!Carrier & Chr(34) & " AND "
If (Me!PO#.Column(1) = 1) And Not IsNull(Me!PO#) Then strFilter = strFilter
    & "[PO#]=" & Me!Combo4 & " AND "
 
If strFilter <> "" Then
    strFilter = Left$(strFilter, Len(strFilter) - 5)
    Me.Filter = strFilter
    Me.FilterOn = True
End If

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

[jaxjagfan]

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.

[StephR]

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 ?

[puppydogbuddy]

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?

[StephR]

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.

[puppydogbuddy]

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.

[StephR]

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?

[puppydogbuddy]

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.

[StephR]

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:

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."

[puppydogbuddy]

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 cInvalidDateErr or 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_I nfo.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.V isible Then
Me.FormFooter.V isible = True
DoCmd.MoveSize Height:=Me.Wind owHeight + Me.FormFooter.H eight
End If
Me.All_Shipping _Information.Fo rm.Filter = strWhere
Me.All_Shipping _Information.Fo rm.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.

[NeoPa]

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.