form validation

[GSir]

i have made a form for searching and i took the code from the Thread702-563930 my code now its look like this:

Private Sub cmdSearch_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()


strSQL = "SELECT Åðþíõìï, ¼íïìá, Ðåñéï÷Þ,Äéåýèõíóç, Ôê " & _
"FROM tblCustomers"

strWhere = "WHERE"


' set where clause conditions

If Not IsNull(Me.txtFName) Then
strWhere = strWhere & " (tblCustomers.Åðþíõìï) Like '" & Me.txtFName & "*' AND "
End If


If Not IsNull(Me.txtLName) Then
strWhere = strWhere & " (tblCustomers.¼íïìá) Like '" & Me.txtLName & "*' AND"
End If


If Not IsNull(Me.txtCity) Then
strWhere = strWhere & " (tblCustomers.Ðåñéï÷Þ) Like '" & Me.txtCity & "*' AND"
End If

If Not IsNull(Me.txtAddress) Then
strWhere = strWhere & " (tblCustomers.Äéåýèõíóç) Like '" & Me.txtCity & "*' AND"
End If

If Not IsNull(Me.txtState) Then
strWhere = strWhere & " (tblCustomers.Ôê) Like '" & Me.txtState & "*' AND"
End If


strWhere = Mid(strWhere, 1, Len(strWhere) - 5) ' remove ' and'

Set qryDef = dbNm.QueryDefs("qrycustom&quot

qryDef.SQL = strSQL & " " & strWhere & ""

DoCmd.OpenReport "rptcustom", acViewPreview

End Sub



and i want if no one field is fill to have a msgbox to say "please fill at list one field"
and if there is no records after the search another msgbox to say no records found.


can anyone help me pls?

 

[RoyVidar]

Hi!

Ususally, I would have created a Boolean variable, and set it to True if any search string is added, but here, one could perform a check on the sql string. If the word "Where" is the last word of the where-string, then no fields have been selected. How:

If right$(strWhere, 5)="WHERE" then
Set qryDef = dbNm.QueryDefs("qrycustom&quot
qryDef.SQL = strSQL & " " & strWhere & ""
DoCmd.OpenReport "rptcustom", acViewPreview
else
msgbox "No fields selected"
endif

HTH Roy-Vidar

 

[GSir]

i try it but now the msgbox appears even the fields are fill

 

[RoyVidar]

Hi!

Sorry - didn't see the 'remove And part'. Your string is probably either 0 or 1 in length, and not containing "WHERE" at all, if no fields are selected, so checking for length > 1, or to be on the safe side, 5 should do the trick (sorry for the inconvenience, as my Office XP always tells me)

Replace the line starting with IF with:

if len(strWhere)>5 then
... perform the code

Roy-Vidar

 

[GSir]

now it works fine thank you but still i want to validate the form when there is no record
i write a code that its look like this:

Set qryDef = dbNm.QueryDefs("qrycustom&quot

qryDef.SQL = strSQL & " " & strWhere & ""

if qrycustom=0 then
msgbox"no records found"
exit sub
else

DoCmd.OpenReport "rptcustom", acViewPreview

but after thata the msgbox appears all the time can you help me to this pls?

 

[RoyVidar]

he he - sorry, I'm far from fluent in querydefs, so I don't know how to check that.

Whenever I want to check for no data, I use the "on no data" event in the report for this. Here's a sample of how to do that.

In the no data event of the report, place

msgbox "didn't find any matching results..."
cancel=true

Now, this will give an error (Report Open Cancel thingie), so before the open reportline

on error resume next
DoCmd.OpenReport "rptcustom", acViewPreview
if err.number=2501 then
err.clear
end if

You could of course just use the cancel=true line in the reports on no data, and do the actual messaging here. It's perhaps a messy approach, but it works;-)

If you want another solution, I'd recommend starting a new thread with "check querydefs for contence"

Roy-Vidar

 

[GSir]

they works very well all thank you very much my friend