Change query with code

[mrbboy]

Hello.

I am running the following sql query as the record source for my form:

SELECT tbl_CARGeneral.CARID, tbl_Employee.Employee, tbl_CARGeneral.[Date Initiated], tbl_CARGeneral.Status
FROM tbl_CARGeneral INNER JOIN tbl_Employee ON tbl_CARGeneral.[Initiated By] = tbl_Employee.EmployeeID;

I have a Menu form with a command button that when clicked, I want to only show the records when Status = Open (or Close). How do I change the above code to achieve this?

I suppose that I can just copy the form and change Status in the query to either Open or Close but I am hoping that I could have just one form and somehow just change the query by code.

Thanks.

 

[Remou]

You can set the record source of a form programmatically. You may wish to use OpenArgs to indicate the status. In the Open Event of the form:

strSQL="SELECT tbl_CARGeneral.CARID, " _
& "tbl_Employee.Employee, tbl_CARGeneral.[Date Initiated], " _
& "tbl_CARGeneral.Status " _
& "FROM tbl_CARGeneral INNER JOIN tbl_Employee ON " _
& "tbl_CARGeneral.[Initiated By] = bl_Employee.EmployeeID"

If Me.OpenArgs="Open" Then
   strSQL=StrSQL & " WHERE Status='Open'"
Else
   strSQL=StrSQL & " WHERE Status='Closed'"
End If

 

[ProgramError]

strSELECT = "SELECT tbl_CARGeneral.CARID, tbl_Employee.Employee, tbl_CARGeneral.[Date Initiated], tbl_CARGeneral.Status"

StrFROM = " FROM tbl_CARGeneral INNER JOIN tbl_Employee ON tbl_CARGeneral.[Initiated By] = tbl_Employee.EmployeeID;"

if[commandbutton] then
strWHERE = " WHERE Status = 'Open'"
else
strWHERE = " WHERE Status = 'closed'"
end if

strSQL = strSELECT & strFROM & srtWHERE
me.recordsource = strSQL
me.requery

Untested but will probably work if you add dims

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[PHV]

Why not simply use the Filter and FilterOn properties of the form ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya mrbboy . . .

Agree with [blue]PHV[/blue]. You'd generate less lines of code this way using only the [blue]Filter[/blue] & [blue]Filter On[/blue] properties.

I also suggest a combobox instead of the button. The [blue]AfterUpdate[/blue] event would set the filter.

For starters the combobox would contain:

[tt][blue] All
Open
Close
'
'Others
'[/blue][/tt]

Your Thoughts? . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[mrbboy]

I am not familiar with the filter on/off properties. Can you please explain.

Thanks

 

[Remou]

If the users are allowed to filter using the standard methods, filters can be a bit of a problem.