Another Search Form Question(s)

[Trojnfn]

I have a search form where I select the search from a combo box. I think click the search button and it runs a macro to retrieve the records based on the selected value. All works fine.



My first problem/question - as soon as I open the form, it is already populated with a list of values. Why is that and how can the form be blank when I first open it ?

Second problem/question - if no records are found based on my search value, how do I display a popup message to say somethink like 'No records found based on selection crieria.) ?

[JoeM]

My first problem/question - as soon as I open the form, it is already populated with a list of values. Why is that and how can the form be blank when I first open it ?

It sounds like your Form is Bound to something (the fields are coming from a table or query) when it should probably actually be an Unbound Form (fields not attached to any table or query).

Second problem/question - if no records are found based on my search value, how do I display a popup message to say somethink like 'No records found based on selection crieria.) ?

If you are running this by command button, use VBA to count the records of the query first before opening it (whether it is a query, form, or report), i.e.

If DCount("*","MyQuery",criteria)=0 Then
    MsgBox "There are no records to return!"
Else
    DoCmd.OpenQuery...
End If

[Trojnfn]

If the fields should not be attached to a table or query, where will the data come from ? The record source is tied to a table.

[JoeM]

Typically, search forms are unbound - they don't contain the data, they usually just provide a place for you to enter the criteria you want to apply to your data.

We may not be talking the same "apples to apples" here. Can you explain exactly how you have set this form up, and exactly how it works? You may want to include any code you have.

[Trojnfn]

This is the code I have for my search button (this code is copied and used in other forms too, thus the multiple options). I tried to paste the properties of the fields but it would not let me (how do you attach a screen shot ?). Perhaps that is the problem, what should some of the property columns be set to ?



Private Sub btnSearch_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'************************************************* **********************
'Look at each search box, and build up the criteria string from the non-blank ones.
'************************************************* **********************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.SearchFMContactCombo) Then
strWhere = strWhere & "([FMContactName] = """ & Me.SearchFMContactCombo & """) AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.SearchSiteCombo) Then
strWhere = strWhere & "([CarrierName] = """ & Me.SearchSiteCombo & """) AND "
End If

'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.SearchStartDate) Then
strWhere = strWhere & "([DateOrderPlaced] >= " & Format(Me.SearchStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this field has times as well as dates.
If Not IsNull(Me.SearchEndDate) Then 'Less than the next day.
strWhere = strWhere & "([DateOrderPlaced] < " & Format(Me.SearchEndDate + 1, conJetDate) & ") AND "
End If

'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "Please Select a Carrier Name.", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

[Trojnfn]

The search field combo box is unbount. In design mode, it has unbound and in my code, I call it SearchFMContactCombo, see other posts with my code used by my searchbutton.

[JoeM]

Yes, I definitely think we are talking about two different things here.

The Search Forms I created are totally unbound (not just a few unbound fields, but the whole form). I enter in text and/or combo boxes where users can enter/select the criteria they wish to use. I then use those selections to build the SQL code for the query they need, apply it to an exisiting query name, and then open that query (or form or report based on that query).

[Trojnfn]

The combobox that I use for my search is unbound and I calle it SearchFMContactCombo. The search button then creates a query (partial code) : If Not IsNull(Me.SearchFMContactCombo) Then
strWhere = strWhere & "([FMContactName] = """ & Me.SearchFMContactCombo & """) AND " ......................The search works and returns the values that I am looking for. The fields beneath the search are bound because they are from a table. If I have those unbound, it displays something like #?Name. Still dont understand why the form is not blank when it is opened. It is already populated.

[JoeM]

OK, I think I am beginning to understand your structure. You have the Search box (unbound) at the top of your Form, and the data (bound) underneath it.

What is the source of the data beneath it? That is where you data is coming from. It probably won't ever be blank, because that part is bound. As long as the data source still exists, it will still show the data, regardless of whether or not you have done a Search yet. Does it show your last search entry, or your first/last record?

If you really do not want to show any records until a search is made, here are some possible options to consider:
1. Have your Search Form and Data Form two separate forms.
- or -
2. Upon opening/loading your Form, have some VBA code to "hide" your bound data fields until a Search has been performed.

[Trojnfn]

OK, I think I am beginning to understand your structure. You have the Search box (unbound) at the top of your Form, and the data (bound) underneath it.

What is the source of the data beneath it? That is where you data is coming from. It probably won't ever be blank, because that part is bound. As long as the data source still exists, it will still show the data, regardless of whether or not you have done a Search yet. Does it show your last search entry, or your first/last record?

If you really do not want to show any records until a search is made, here are some possible options to consider:
1. Have your Search Form and Data Form two separate forms.
- or -
2. Upon opening/loading your Form, have some VBA code to "hide" your bound data fields until a Search has been performed.

I kept playing around with it and used the following code to hide/clear the form once loaded/opened.

Private Sub Form_Load()
'Remove the single quote from these lines if you want to initially show no records.
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

That seemed to do it. Now the form is completely blank when opened and the values only show up when I select a search value.

[JoeM]

Excellent! Glad you got it all sorted out!