Form with multiple queries

[leeg]

Hi,

Really appreciate some help with the following: ps I am very poor at VBA!

I have a form and when clicking a cmdbutton want to open the same form but with a different underlying query. I have looked some threads which just said look at Rowsource help which I have and am still none the wiser!

At the moment I have 3 forms with 3 queries and it's cumbersome obviously

In the form properties, I have removed the RecordSource data as I assume it will be populated by the VBA code

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "MainJobForm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!mainjobform!.RowSourceType = "Table/Query"
Forms!mainjobform!.RowSource = "qmainjobform"
 
Exit_Command10_Click:
    Exit Sub
 
Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click
 
End Sub

Thanks in anticipation

Graeme

[MMcCarthy]

Hi,

Really appreciate some help with the following: ps I am very poor at VBA!

I have a form and when clicking a cmdbutton want to open the same form but with a different underlying query. I have looked some threads which just said look at Rowsource help which I have and am still none the wiser!

At the moment I have 3 forms with 3 queries and it's cumbersome obviously

In the form properties, I have removed the RecordSource data as I assume it will be populated by the VBA code

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MainJobForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!mainjobform!.RowSourceType = "Table/Query"
Forms!mainjobform!.RowSource = "qmainjobform"

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

Thanks in anticipation

Graeme

Hi Graeme,

You cannot open a form that is currently open with a change of record source.

Mary

[NeoPa]

Try :

Private Sub Command10_Click()
    Me.RowSourceType = "Table/Query"
    Me.RowSource = "qmainjobform"
End Sub

[MMcCarthy]

Try :

Private Sub Command10_Click()
    Me.RowSourceType = "Table/Query"
    Me.RowSource = "qmainjobform"
End Sub

Guys we're mixing up Record Source and Row Source here. Record Source is the forms source whereas Row Source is used for Combo boxes and List boxes (Yes Ade, I know you already know this).

Mary

[NeoPa]

Mary is absolutely right - that was sloppy of me.
My apologies.
I believe that the Record Source is changable from within the OnOpen event of a form (It certainly is in a report).
Try :

Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = "qmainjobform"
 
    'Optionally other unrelated code
End Sub

[MMcCarthy]

Mary is absolutely right - that was sloppy of me.

Do we have to go through the Mary is ALWAYS right speech again. ;)

[missinglinq]

We don't HAVE to, but it's always fun!

[missinglinq]

I think the simple answer here is to copy the form in question, paste it, rename it when the prompt comes up, then go into Design View on the new form and change the Record Source. Then place a command button on the original form to open the new form. Should take only a couple of minutes!

[MMcCarthy]

We don't HAVE to, but it's always fun!

OK, Repeat after me.

Mary is always right
Even when she's wrong Mary is right.


Got it?

[missinglinq]

As we say in Richmond (Virginia, that is) "Yes'em!"

[NeoPa]

Mary is absolutely right - that was sloppy of me.
My apologies.
I believe that the Record Source is changable from within the OnOpen event of a form (It certainly is in a report).
Try :

Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = "qmainjobform"
 
    'Optionally other unrelated code
End Sub

Mary was right about that point.
That doesn't mean the code won't work - just the way I had it earlier was wrong.

[leeg]

Thanks very much everyone.

Mary was right :o)

[MMcCarthy]

Thanks very much everyone.

Mary was right :o)

Thanks leeg.

See I told you!

[NeoPa]

Thanks very much everyone.

Mary was right :o)

Thanks leeg.

See I told you!

Graeme she sounds mollified and safe, but believe me - she noticed you restricted that to the past tense :(

[MMcCarthy]

Graeme she sounds mollified and safe, but believe me - she noticed you restricted that to the past tense :(

Stop trying to frighten the Newbies Ade. Let them get settled in first before you warn them about my dark side.