Changing the record source of a form

[jl280328]

I am trying to change the recordsource of the form, but I can't seem to get it to work. It tried form.recordsource="SQL statement in here" then did a form.refresh and form.requery but nothing happened. How do I dynamically change the Query that is populating the form?

 

[lars7]

Hi there,
When do you want the code to be fired i.e.; on open of the form or after update of something on the form and you say you want to change the recordsource, from what to what?

 

[jl280328]

I want it to change when a combo box changes and when a list box changes and I want it to change from:
Select * From Table Where FormID = 2 and CarletonName <> Null
OR:
Select * From Table Where FormID = 4 and CarletonName = Null
OR:
Select * From Table Where FormID = 3
OR:
Select * From Table Where CarletonName = Null
OR:
Select * From Table

SOmething along these lines, it's giving me fits because I want the option to get every FORMID or every type of CarletonName so I can't just say Where FormID = combo15.value and CarletonName = listbox40.value

 

[lars7]

Have you tried something like this:

Private Sub "Yourcombobox"_AfterUpdate()

Me.RecordSource = "yourSQL"

End Sub

 

[PHV]

Why not simply play with the Filter and FilterOn properties of the Form object ?

FYI: CarletonName = Null is NEVER True !
Use this instead:
CarletonName Is Null


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jl280328]

How would I do the Filter with two options?

Form.Filter = CarletonName IS Null AND FormID = 4

Something like this?

 

[PHV]

Me.Filter = "CarletonName IS Null AND FormID = 4"
Me.FilterOn = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jl280328]

The filter is not working I tried it, I even copied and pasted it from here after I couldn't get it to work, but had no luck here is what I put:

Me.Filter = "CarletonName IS Null AND FormID = 4"
Me.FilterOn = True

 

[jl280328]

Nevermind I got it working thanks to everyone for your help!