criteria in unbound textbox of form needs to be entered

[Guest]

I have a form with 2 fields [MagDate] and [Comments]. frmSearch has 2
unbound fields with these 2 fieldnames. Search button at top of frmSearch
opens sfrmSearch which shows the results of what was searched for in below in
a subform. sfrmSearch uses qrySearch with the following criteria set for
each field: [Forms]![frmSearch]![controlname] or
[Forms]![frmSearch]![controlname] is null..... When I open the form
initially, all of my records show up in the subform. How do I get that to
NOT happen - and no records to initally appear? I also need help with the
code to make no records show up and a msgbox appear when nothing is typed
into either of my Fields.

Thank you in advance for any help...

 

[Guest]

Simply remove the OR [Forms]![frmSearch]![controlname] IS NULL criteria from
the query's WHERE clause.

To Test for the controls being empty test for Nulls in the search button's
Click event procedure:

If IsNull(FirstControlName) OR IsNull(SecondControlName) Then
MsgBox "Your message goes here", vbExclamation, "Warning"
Else
<code to open sfrmSearch>
End If

Ken Sheridan
Stafford, England

 

[Guest]

Thank you Ken --- so far VERY good! I removed the "IS NULL" criteria and
that part works. I changed your next statement from "OR" to "AND" between my
2 fields. I wanted the msgbox to appear only if a user didn't enter anything
into the form at all - I didn't want info to have to be entered into both
fields. The last part --- in this same code, I want a different msgbox to
appear if I do a search and the record count comes back as zero. I want to
tell the user that no records were found.

Thank you again...

Simply remove the OR [Forms]![frmSearch]![controlname] IS NULL criteria from
the query's WHERE clause.

To Test for the controls being empty test for Nulls in the search button's
Click event procedure:

If IsNull(FirstControlName) OR IsNull(SecondControlName) Then
MsgBox "Your message goes here", vbExclamation, "Warning"
Else
<code to open sfrmSearch>
End If

Ken Sheridan
Stafford, England

I have a form with 2 fields [MagDate] and [Comments]. frmSearch has 2
unbound fields with these 2 fieldnames. Search button at top of frmSearch
opens sfrmSearch which shows the results of what was searched for in below in
a subform. sfrmSearch uses qrySearch with the following criteria set for
each field: [Forms]![frmSearch]![controlname] or
[Forms]![frmSearch]![controlname] is null..... When I open the form
initially, all of my records show up in the subform. How do I get that to
NOT happen - and no records to initally appear? I also need help with the
code to make no records show up and a msgbox appear when nothing is typed
into either of my Fields.

Thank you in advance for any help...

 

[Guest]

If the user is required to complete only one of the two controls this could
conflict with removal of the OR…IS NULL criteria from the query if the query
uses a Boolean AND operation such as:

WHERE MagDate = [Forms]![frmSearch]![firstcontrolname]
AND Comments = [Forms]![frmSearch]![secondcontrolname]

If it uses a Boolean OR, however:

WHERE MagDate = [Forms]![frmSearch]![firstcontrolname]
OR Comments = [Forms]![frmSearch]![secondcontrolname

then there will be no problem. I assume below that the query uses an OR.

To determine if there are any matches you can first check to see if there
are any matching rows by calling the DLookup function to find the value of
any 'required' field (usually its primary key) in the underlying table. If
there are no matches this would return Null, so the code would be like this.
I'm assuming that MagDate is a date/time data type and Comments is text:

Dim strCriteria As String

strCriteria = "Magdate = #" & _
Format(FirstControlName,"mm/dd/yyyy") & _
"# Or Comments = """ & _
SecondControlName & """"

If IsNull(FirstControlName) And IsNull(SecondControlName) Then
MsgBox "Your message goes here", vbExclamation, "Warning"
Else
If Not IsNull("SomeRequiredField","YourTable", strCriteria) Then
<code to open sfrmSearch>
Else
MsgBox "Your other message goes here", vbExclamation, "Warning"
End If
End If

Signing off for the night now.

Ken Sheridan
Stafford, England

 

[joseph]

Ken,

I was hoping to ask you a follow up question to this post?

Please let me know

Thanks,

Joseph

If the user is required to complete only one of the two controls this could
conflict with removal of the OR…IS NULL criteria from the query if the query
uses a Boolean AND operation such as:

WHERE MagDate = [Forms]![frmSearch]![firstcontrolname]
AND Comments = [Forms]![frmSearch]![secondcontrolname]

If it uses a Boolean OR, however:

WHERE MagDate = [Forms]![frmSearch]![firstcontrolname]
OR Comments = [Forms]![frmSearch]![secondcontrolname

then there will be no problem. I assume below that the query uses an OR.

To determine if there are any matches you can first check to see if there
are any matching rows by calling the DLookup function to find the value of
any 'required' field (usually its primary key) in the underlying table. If
there are no matches this would return Null, so the code would be like this.
I'm assuming that MagDate is a date/time data type and Comments is text:

Dim strCriteria As String

strCriteria = "Magdate = #" & _
Format(FirstControlName,"mm/dd/yyyy") & _
"# Or Comments = """ & _
SecondControlName & """"

If IsNull(FirstControlName) And IsNull(SecondControlName) Then
MsgBox "Your message goes here", vbExclamation, "Warning"
Else
If Not IsNull("SomeRequiredField","YourTable", strCriteria) Then
<code to open sfrmSearch>
Else
MsgBox "Your other message goes here", vbExclamation, "Warning"
End If
End If

Signing off for the night now.

Ken Sheridan
Stafford, England

Thank you Ken --- so far VERY good! I removed the "IS NULL" criteria and
that part works. I changed your next statement from "OR" to "AND" between my
2 fields. I wanted the msgbox to appear only if a user didn't enter anything
into the form at all - I didn't want info to have to be entered into both
fields. The last part --- in this same code, I want a different msgbox to
appear if I do a search and the record count comes back as zero. I want to
tell the user that no records were found.

Thank you again...