Search form

J

Jam

I have a search form that displays its results on the
detail part of the form.
Code as follows:
Dim gstrsql As String


'check Organization and Staff Name who entered in the
phone log
If IsNull(Me.txtOrgName) Then
MsgBox "You must enter criteria to search."
Exit Sub
End If

'if by organization's name
If Not IsNull(Me.txtOrg_Name) Then
Set DB = CurrentDb()
Set qd = DB.QueryDefs("qrySurveyCrosstab")
gstrsql = " TRANSFORM Max
(dbo_TblSurveyHeader.LastModDate)AS MaxOfLastModDate " & _
" SELECT dbo_tblOrg_Demo.Track_No,
dbo_tblOrg_Demo.OrgName, dbo_tblOrg_Demo.Train_Date,
dbo_tblOrg_Demo.ImpDate, dbo_tblOrg_Demo.Org_SeqNo " & _
" FROM dbo_tblOrg_Demo LEFT JOIN
dbo_TblSurveyHeader ON dbo_tblOrg_Demo.Track_No =
dbo_TblSurveyHeader.TrackNum where dbo_tblOrg_Demo.orgname
LIKE '" & Me.txtOrgName & "*'" & _
" GROUP BY dbo_tblOrg_Demo.Track_No,
dbo_tblOrg_Demo.OrgName, dbo_tblOrg_Demo.Train_Date,
dbo_tblOrg_Demo.ImpDate, dbo_tblOrg_Demo.Org_SeqNo " & _
" ORDER BY dbo_tblOrg_Demo.OrgName " & _
" PIVOT dbo_TblSurveyHeader.WhichMonth IN
('3','6','9','12') "

'gstrsql = "Select
dbo_tblOrg_Demo.orgname,Org_SeqNo,train_date,track_no,impda
te from dbo_tblorg_demo where dbo_tblOrg_Demo.orgname
LIKE '" & Me.txtOrgName & "*'" & _
"ORDER BY dbo_tblOrg_Demo.orgname;"
End If

qd.SQL = gstrsql
Me.RecordSource = "qrySurveyCrosstab"
Me.Requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
End If

Set qd = Nothing
Set DB = Nothing

Problem is when I close the form - it doesn't show up all
the records but retains whatever it last searched.
I want the search form to open with all records before
user filters their search.

Please help.
Thanks.
 
J

Joe Black

Hi Jam

Jam said:
I have a search form that displays its results on the
detail part of the form.
Code as follows:
Dim gstrsql As String


'check Organization and Staff Name who entered in the
phone log
If IsNull(Me.txtOrgName) Then
MsgBox "You must enter criteria to search."
Exit Sub
End If

'if by organization's name
If Not IsNull(Me.txtOrg_Name) Then
Set DB = CurrentDb()
Set qd = DB.QueryDefs("qrySurveyCrosstab")
gstrsql = " TRANSFORM Max
(dbo_TblSurveyHeader.LastModDate)AS MaxOfLastModDate " & _
" SELECT dbo_tblOrg_Demo.Track_No,
dbo_tblOrg_Demo.OrgName, dbo_tblOrg_Demo.Train_Date,
dbo_tblOrg_Demo.ImpDate, dbo_tblOrg_Demo.Org_SeqNo " & _
" FROM dbo_tblOrg_Demo LEFT JOIN
dbo_TblSurveyHeader ON dbo_tblOrg_Demo.Track_No =
dbo_TblSurveyHeader.TrackNum where dbo_tblOrg_Demo.orgname
LIKE '" & Me.txtOrgName & "*'" & _
" GROUP BY dbo_tblOrg_Demo.Track_No,
dbo_tblOrg_Demo.OrgName, dbo_tblOrg_Demo.Train_Date,
dbo_tblOrg_Demo.ImpDate, dbo_tblOrg_Demo.Org_SeqNo " & _
" ORDER BY dbo_tblOrg_Demo.OrgName " & _
" PIVOT dbo_TblSurveyHeader.WhichMonth IN
('3','6','9','12') "

'gstrsql = "Select
dbo_tblOrg_Demo.orgname,Org_SeqNo,train_date,track_no,impda
te from dbo_tblorg_demo where dbo_tblOrg_Demo.orgname
LIKE '" & Me.txtOrgName & "*'" & _
"ORDER BY dbo_tblOrg_Demo.orgname;"
End If

qd.SQL = gstrsql
Me.RecordSource = "qrySurveyCrosstab"
Me.Requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
End If

Set qd = Nothing
Set DB = Nothing

Problem is when I close the form - it doesn't show up all
the records but retains whatever it last searched.
I want the search form to open with all records before
user filters their search.

Please help.
Thanks.
Click to expand...

Have qrySurveyCrosstab show all records, then set Me.RecordSource = gstrsql.

Regards - Joe
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Coding Search Form 3
multi-search criteria form 3
Programming for Search Form 1
search in a form 2
Search form creation - Hoping for help bringing some old code to life 0
Filter multiple fields with one search criteria on form 3
search form w/ no duplicates 7
search form 6

Top