By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Form with multiple queries

P: 14
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command10_Click()
  2. On Error GoTo Err_Command10_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "MainJobForm"
  8.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  9. Forms!mainjobform!.RowSourceType = "Table/Query"
  10. Forms!mainjobform!.RowSource = "qmainjobform"
  11.  
  12. Exit_Command10_Click:
  13.     Exit Sub
  14.  
  15. Err_Command10_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_Command10_Click
  18.  
  19. End Sub
Thanks in anticipation

Graeme
Jan 5 '07 #1
Share this Question
Share on Google+
14 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 6 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
Try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command10_Click()
  2.     Me.RowSourceType = "Table/Query"
  3.     Me.RowSource = "qmainjobform"
  4. End Sub
Jan 6 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command10_Click()
  2.     Me.RowSourceType = "Table/Query"
  3.     Me.RowSource = "qmainjobform"
  4. 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
Jan 6 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Me.RecordSource = "qmainjobform"
  3.  
  4.     'Optionally other unrelated code
  5. End Sub
Jan 6 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary is absolutely right - that was sloppy of me.
Do we have to go through the Mary is ALWAYS right speech again. ;)
Jan 6 '07 #6

missinglinq
Expert 2.5K+
P: 3,532
We don't HAVE to, but it's always fun!
Jan 6 '07 #7

missinglinq
Expert 2.5K+
P: 3,532
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!
Jan 6 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Jan 6 '07 #9

missinglinq
Expert 2.5K+
P: 3,532
As we say in Richmond (Virginia, that is) "Yes'em!"
Jan 6 '07 #10

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Me.RecordSource = "qmainjobform"
  3.  
  4.     'Optionally other unrelated code
  5. 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.
Jan 8 '07 #11

P: 14
Thanks very much everyone.

Mary was right :o)
Jan 8 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks very much everyone.

Mary was right :o)
Thanks leeg.

See I told you!
Jan 8 '07 #13

NeoPa
Expert Mod 15k+
P: 31,186
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 :(
Jan 8 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 8 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.