Solved: Change Record Source

[jmentor]

How to dynamically change the record source of
a form using the OnLoad event when the call is
from a listbox as follows

Private Sub List0_DblClick(Cancel As Integer)
DoCmd.OpenForm Me.List0
End Sub

It would save having to use 8 identical forms.



Thanks

[xCav8r]

Put this in the OnOpen Event Procedure for the form and swap out the SQL statement with what you want.

[VBA]Me.RecordSource = "SELECT * FROM tblSample"[/VBA]

[jmentor]

xCav8r

Your solution is hard coded into the form.
What I want is to have the form change
it record source depend on what the user
selected in the listbox

[xCav8r]

Okay, then change the recordsource from the event procedure that fires after the listbox is updated. Or, if it would be easier, use a form and subform and use the listbox in the parent/child relationship.

[jmentor]

Hi xCav8r

I don't think you understand what I am trying
to achieve.
Perhaps I'am not explaining it clearly.

I have a listbox with 5 items displayed. Lets say
the 5 items are Notebooks, Monitors, Scanners,
Printers DigitalCameras.
If the user clicks on Scanners then I want the form
to open with a record source of, lets say, qryScanners
on the OnLoad event of the form.
If the user clicks on Printers then the OnLoad event
of the form will have a record source of qryPrinters
and so on.

The OnClick event of the list box will start with
DoCmd.OpenForm "myForm" etc etc etc

Its the etc etc bits that I need to sort out

[Norie]

jmentor

What you need to do is refer to the listbox when setting the record source for the form you are opening.

Me.RecordSource = Forms!OtherForm!Listbox0.Value 

[jmentor]

No, this doesn't work
It simply returns the value of the form
being opened as in

DoCmd.OpenForm Me.List0

[Norie]

Well I think we need some more information.

xCav8r had exactly the right idea and he also pointed out that you would need to change the hardcoded record source.

[jmentor]

Hi Norie

Let me explain in a different way

I have a form called "select". On the form is a ListBox with
5 items listed.

Cameras
Scanners
Printers
Monitors
Notebooks

If I click on Scanners a form will open called MyForm based
on the tblScanners. So the record source for the form is
tblScanners. If I click on Printers the same form will open
but its record source will be tblPrinters.
So depending on the item clicked the record source for the
form "MyForm" will change

On the double click event of the list box there is
DoCmd.OpenForm "MyForm"

Now I need to extend that line to include the record source
the form will open under.

Please let me know if you require further information

Thanks

[xCav8r]

Pass the record source as a string in the opening arguments (see help on openform) then me.recordsource = me.openargs.

Edit: or look at the code for the buttons you discussed and copy what that's doing.

[jmentor]

xCav8r

In order not to prolong the agony let me ask you this.
Have you tried the suggestions you have made and
do they work ?. If yes please post the line (can't imagine
that its more than a line) and I'll copy and paste into the
listbox.

Thanks

[xCav8r]

I found a database with a sample. Here you go:

This is an event procedure from a textbox.
[VBA]
Private Sub txtMenuCaption_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMenuWizard_All_Start", acNormal, , , acFormEdit, acWindowNormal, _
"SELECT * FROM tblMenuItems WHERE lngMenuItemID=" & Me.cboMenuItemID & ";"
End Sub
[/VBA]

This is the open event procedure for the form I'm opening.

[VBA]
Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.OpenArgs) Then
Cancel = True
Else
Me.RecordSource = Me.OpenArgs
End If
End Sub
[/VBA]

You'll need to add to this obviously to account for the listbox and what the user selected.

[VBA]Private Sub lstElectronics_DblClick(Cancel As Integer)
Select Case Me.lstElectronics.Value
Case "Cameras"
DoCmd.OpenForm "MyForm", , , , , , "qryCameras"
Case "Scanners"
DoCmd.OpenForm "MyForm", , , , , , "qryScanners"
Case "Printers"
DoCmd.OpenForm "MyForm", , , , , , "qryPrinters"
Case "Monitors"
DoCmd.OpenForm "MyForm", , , , , , "qryMonitors"
Case "Notebooks"
DoCmd.OpenForm "MyForm", , , , , , "qryNotebooks"
End Select
End Sub[/VBA]

Hope that helps!

[jmentor]

xCav8r and Norie

After a lot of trial and error this is what is needed.

[VBA] Private Sub List0_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMain", , , , , , Me.ListBox0
End Sub
[/VBA]

On the form that has the listbox this goes into
the double click event. The table that supplies the
listbox has a field that contains an Sql statement.
This is the bound column on the listbox but is hidden.
The Me.ListBox0 is infact the OpenArgs and is the
Sql statment that is passed to the form frmMain

This is what goes into the OnLoad (Not OnOpen)
event of the form "frmMain"

[VBA] Sub Form_OnLoad()
Me.RecordSource = Me.OpenArgs
End sub
[/VBA]

Thats it. However, after playing around I also
discovered something very interesting with the
OpenArgs variable. You could include many parameters
seperated by a "," and parse the whole lot out in the
form that is being opened. eg.

The parameters are held in a field in a table thus
SELECT * FROM MyTable, SELECT * FROM YourTable WHERE
etc etc.
You can supply the parsed results to any control on the form.
A good way to handle an unbound combo box that has a
record source different from the main form's record source.

[xCav8r]

I'm glad you got it working. A few comments.

Private Sub List0_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMain", , , , , , Me.ListBox0
End Sub

You can make your code easier for others to read on the forum by highlighting it and clicking on the VBA button. This formats it as the VBE does. I edited your previous post to include the VBA tags.

On the form that has the listbox this goes into
the double click event. The table that supplies the
listbox has a field that contains an Sql statement.
This is the bound column on the listbox but is hidden.
The Me.ListBox0 is infact the OpenArgs and is the
Sql statment that is passed to the form frmMain

There are many ways to skin this cat, but I'm glad you found something that works for you.

This is what goes into the OnLoad (Not OnOpen)
event of the form "frmMain"

Although you can put it into the OnLoad Event Procedure, it's better as a rule of thumb to put it into the OnOpen Event Procedure instead. The reason for that is the order in which they occur and what happens at each stage. OnOpen happens when the form is opened, but before any records are returned. OnLoad happens after the form is opened and the displayed records are returned. So, it's faster to put it in the OnOpen since it sets the record source before returning any records.

Thats it. However, after playing around I also
discovered something very interesting with the
OpenArgs variable. You could include many parameters
seperated by a "," and parse the whole lot out in the
form that is being opened. eg.

Probably better to get into the habit of using a less used character like a pipe: |. That way you don't run into issues when you need to pass something containing a comma--especially considering that commas separate field names in SQL statements.

Anyway, thank you very much for sharing your solution with us. It improves the content of the forum for future readers looking for solutions. Would you mind marking this thread solved? (At the top of the window, click Thread Tools and mark solved).

[jmentor]

Hi xCav8r

Yes, you are right. I did some timings on large data sets
and the OnOpen is faster (marginally).
Have to say, didn't know about the VBA button.
I only used the , since I'am sure how to parse a pipe. I
can do it in dos.

Thanks for your help everyone

[xCav8r]

I only used the , since I'am sure how to parse a pipe. I
can do it in dos.

Here's an example that works with 2000 and up. Make sure your immediate window is showing in the VBE: CTRL + G.

[VBA]Sub TestParseByPipes()
Dim astrMyParams() As String
Dim intCounter As Integer
astrMyParams = ParseByPipes("param1|param2|param3|param4|param5")
For intCounter = LBound(astrMyParams) To UBound(astrMyParams)
Debug.Print astrMyParams(intCounter)
Next intCounter
End Sub

Function ParseByPipes(StringToParse As String) As Variant
ParseByPipes = Split(StringToParse, "|", , vbTextCompare)
End Function[/VBA]

Thanks for your help everyone

Just happy to be part of the solution. Glad you up and running!