Form for query

[sebastico]

Hi Access comm

I have been trying to have a form working as a query in Access 2003. I also
have been studying the Access Help, and revising the Access forums and the
Allen Brown web, etc, no success.

In such form, the idea is when a user filters in the "texTown" textbox
Access must show all the records.
I already have a form in design view. I add five (non linked) textbox and I
give the names from the Table 1ObID:
txtObrID
txtAutNmb
txtYy
txtTitle
txtRes
txtTown

I also added two command buttons, one with the text "Filter "and the other
"Show All".
In the "Show All" button I added in the event click:
DoCmd.ShowAllRecords “I hope this to take off any filter and will show all
records.
DoCmd.ApplyFilter sFilter

In the other button I add this code in the event click:
Dim sFilter as String
if len(Me.txtObrID.value)>0 then
sFiltro="ObrID='" & txtObrID.value & "' and " 'TEXT
sFiltro="ObrID='" # txtObrID.value & "' and " 'DATE
sFiltro="ObrID=" & txtObrID.value & "' and " 'NUMBER
end if

if len(Me.txtAutNmb.value)>0 then
sFiltro="AutNmb='" & txtAutNmb.value & "' and " 'TEXT
sFiltro=" AutNmb ='" # txtAutNmb.value & "' and " 'DATE
sFiltro=" AutNmb =" & txtAutNmb.value & "' and " 'NUMBER
end if

if len(Me. txtYy.value)>0 then
sFiltro="Yy='" & txtYy.value & "' and " 'TEXT
sFiltro=" Yy ='" # txtYy.value & "' and " 'DATE
sFiltro=" Yy =" & txtYy.value & "' and " 'NUMBER
end if

if len(Me. txtTitle.value)>0 then
sFiltro="Title='" & txtTitle.value & "' and " 'TEXT
sFiltro=" Title ='" # txtTitle.value & "' and " 'DATE
sFiltro=" Title =" & txtYy.value & "' and " 'NUMBER
end if

if len(Me. txtRes.value)>0 then
sFiltro="Res='" & txtRes.value & "' and " 'TEXT
sFiltro=" Res ='" # txtRes.value & "' and " 'DATE
sFiltro=" Res =" & txtRes.value & "' and " 'NUMBER
end if

if len(Me. txtTown.value)>0 then
sFiltro="Town='" & txt Town.value & "' and " 'TEXT
sFiltro=" Town ='" # txt Town.value & "' and " 'DATE
sFiltro=" Town =" & txt Town.value & "' and " 'NUMBER
end if

However, when I click the buttons nothing happens. Could you help to fix
what I’m doing wrong? I’m also interested in displaying records in a Report,
is that possible?

Many thanks

 

[Steve Sanford]

I don't know the data types of the text boxes; you might have to change the
delimiters.

Numbers - no delimiters
Text - quotes , single or double
Dates - the hash (#)

Try these: ------ untested code ---------
'-----------------------------------------------------------
'In the "Show All" button I added in the event click:
Public Sub ShowAll()

'remove filter and show all records.
DoCmd.ShowAllRecords

End Sub
'-----------------------------------------------------------

'In the other button I add this code in the event click:
Public Sub Filterme()

Dim sFiltro As String

If Len(Me.txtObrID) > 0 Then
sFiltro = "ObrID= " & txtObrID & " and " 'NUMBER
End If

If Len(Me.txtAutNmb) > 0 Then
sFiltro = " AutNmb = " & txtAutNmb & " and " 'NUMBER
End If

If Len(Me.txtYy) > 0 Then
sFiltro = " Yy = " & txtYy & " and " 'NUMBER
End If

If Len(Me.txtTitle) > 0 Then
sFiltro = "Title= '" & txtTitle & "' and " 'TEXT
End If

If Len(Me.txtRes) > 0 Then
sFiltro = "Res= '" & txtRes & "' and " 'TEXT
End If

If Len(Me.txtTown) > 0 Then
sFiltro = "Town= '" & txtTown & "' and " 'TEXT
End If

If Len(sFiltro) > 5 Then
'cut off the " and "
sFiltro = Left(sFiltro, Len(sFiltro) - 5)
'apply the filter
DoCmd.ApplyFilter , , sFiltro
End If

End Sub
'-----------------------------------------------------------



HTH