Populating datasheet using search criteria....

[Anand Vaidya]

hi all,
I want to populate datasheet based on some search criteria in the mainform
using an sql statement.I wrote a sample code for this using dummy table and
it worked fine . Now when I replaced the dummy table fields with original
table fields,it is not reflecting in the datasheet but in the immediate
window , I could see the sql statement built correctly.
Code that I wrote for search button is (which when clicked should display
records in datasheet) -

Quote:
Dim strsql As String
strsql = "select PK_Query_Id,Received_Date,Target_Date,FK_Request_F
rom_Id,FK_Request_Type_Id,FK_Status_Id from tblQueryDetails"
Dim flag1, flag2, flag3, flag4, flag5, flag6, flag7, flag8 As Integer
If Len(Me.cboQuery_Id & vbNullString) Then '------------1
flag1 = 1
If flag2 = 1 Or flag3 = 1 Or flag4 = 1 Or flag5 = 1 Or flag6 = 1 Or flag7 =
1 Or flag8 = 1 Then
strsql = strsql & " and PK_Query_Id=" & Me.cboQuery_Id
Else
strsql = strsql & " where PK_Query_Id=" & Me.cboQuery_Id
End If
Debug.Print strsql
End If

If Len(Me.txtReceived_Date & vbNullString) Then '-------2
flag2 = 1
If flag1 = 1 Or flag3 = 1 Or flag4 = 1 Or flag5 = 1 Or flag6 = 1 Or flag7 =
1 Or flag8 = 1 Then
strsql = strsql & " and Received_Date=#" & Me.txtReceived_Date & "#"
Else
strsql = strsql & " where Received_Date=#" & Me.txtReceived_Date & "#"
End If
Debug.Print strsql
End If

If Len(Me.txtEnd_Date & vbNullString) Then '--------3
flag3 = 1
If flag1 = 1 Or flag2 = 1 Or flag4 = 1 Or flag5 = 1 Or flag6 = 1 Or flag7 =
1 Or flag8 = 1 Then
strsql = strsql & " and End_Date=#" & Me.txtEnd_Date & "#"
Else
strsql = strsql & " where End_Date=#" & Me.txtEnd_Date & "#"
End If
Debug.Print strsql
End If

If Len(Me.txtTarget_Date & vbNullString) Then '---------4
flag4 = 1
If flag2 = 1 Or flag3 = 1 Or flag1 = 1 Or flag5 = 1 Or flag6 = 1 Or flag7 =
1 Or flag8 = 1 Then
strsql = strsql & " and Target_Date=#" & Me.txtTarget_Date & "#"
Else
strsql = strsql & " where Target_Date=#" & Me.txtTarget_Date & "#"
End If
Debug.Print strsql
End If

If Len(Me.cboRequest_From & vbNullString) Then '-------5
flag5 = 1
If flag2 = 1 Or flag3 = 1 Or flag4 = 1 Or flag1 = 1 Or flag6 = 1 Or flag7 =
1 Or flag8 = 1 Then
strsql = strsql & " and FK_Request_From_Id=" & Me.cboRequest_From
Else
strsql = strsql & " where FK_Request_From_Id=" & Me.cboRequest_From
End If
Debug.Print strsql
End If

If Len(Me.cboDivision & vbNullString) Then '------------6
flag6 = 1
If flag2 = 1 Or flag3 = 1 Or flag4 = 1 Or flag5 = 1 Or flag1 = 1 Or flag7 =
1 Or flag8 = 1 Then
strsql = strsql & " and FK_Division_Id=" & Me.cboDivision
Else
strsql = strsql & " where FK_Division_Id=" & Me.cboDivision
End If
Debug.Print strsql
End If

If Len(Me.cboRequest_Type & vbNullString) Then '---------7
flag7 = 1
If flag2 = 1 Or flag3 = 1 Or flag4 = 1 Or flag5 = 1 Or flag6 = 1 Or flag1 =
1 Or flag8 = 1 Then
strsql = strsql & " and FK_Request_Type_Id=" & Me.cboRequest_Type
Else
strsql = strsql & " where FK_Request_Type_Id=" & Me.cboRequest_Type
End If
Debug.Print strsql
End If

If Len(Me.cboStatus & vbNullString) Then '---------------8
flag8 = 1
If flag2 = 1 Or flag3 = 1 Or flag4 = 1 Or flag5 = 1 Or flag6 = 1 Or flag7 =
1 Or flag1 = 1 Then
strsql = strsql & " and FK_Status_Id=" & Me.cboStatus
Else
strsql = strsql & " where FK_Status_Id=" & Me.cboStatus
End If
Debug.Print strsql
End If
Me.SearchQueryDetails2.Form.RecordSource = strsql 'SearchQueryDetails2 is
the sub form


Debug.Print strsql gave the following result in Immediate window when I
selected "Target Date" and "Status" in search criteria but I could not see
this reflection in datasheet.
select PK_Query_Id,Received_Date,Target_Date,FK_Request_F
rom_Id,FK_Request_Type_Id,FK_Status_Id from tblQueryDetails where
Target_Date=#2/3/2006 4:52:00 PM# and FK_Status_Id=10

Is Me.SearchQueryDetails2.Form.RecordSource = strsql a correct statement?