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

"On Open" Event; applying a query to open a form

kcdoell
100+
P: 230
Hello:

I have a form that I want to open using a filter that I have created. I have done this usually by pointing the record source of the form to the query/filter that I created. In this new project, I want to open the form using the "On Open" event, via the properties of the form, and leave my record source pointing to the original table that was used to set it up.

My problem is that I am not too sure what the vba coding would be. This particular form has a subform that displays data. I later want to place a command button on the form that will change the sort of the displayed dataset if the user indicates what sort they want the table/dataset to be in.

Does anybody have any ideas they could give me?

Thanks,

Keith.
Feb 6 '08 #1
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Not sure I fully understand what you are looking for but ...

Me.filter = "[Fieldname]=" & someValue
Me.filterOn
Feb 6 '08 #2

kcdoell
100+
P: 230
Thanks for the reply!

I tried to apply the Me.filter idea by putting the following code in the "on Open" property of the form:

Private Sub Form_Load()
Me.Filter = "QryForecastform"
End Sub

Can I reference a query (like I am doing) because it currently does not work? I basically just want the form to initially load up using a filter that I created in Access called "QryForecastform".

Any ideas?

Thanks

Keith.
Feb 7 '08 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for the reply!

I tried to apply the Me.filter idea by putting the following code in the "on Open" property of the form:

Private Sub Form_Load()
Me.Filter = "QryForecastform"
End Sub

Can I reference a query (like I am doing) because it currently does not work? I basically just want the form to initially load up using a filter that I created in Access called "QryForecastform".

Any ideas?

Thanks

Keith.
Thats not a filter its a query so you would reset the record source of the form.

Private Sub Form_Load()
Me.RecordSource = "QryForecastform"
End Sub
Feb 7 '08 #4

kcdoell
100+
P: 230
Thanks, that worked but I discovered that it only worked if I put the code in both the Main form and the Sub form (when I created the form I have a subform embedded in it). Not too sure why....

I have another quick question that is related; on the form I want to change the way the subform is displaying the info (the sort order). That is to say that the subform is in a datasheet view and I want to give the user the ability to see the same information in different sort orders (By Name, Location, Product, etc).

I placed a combo box on my form called "cobSort" and in there is a drop down list to choose from (Name, Location, Product, etc). Then I put a command button called "cmdReSort_Tbl" and on the onclick property I put the following code:

Private Sub cmdReSort_Tbl_Click()
'When user makes a selection on the Sort Table dropdown menu the table will requery
' via that sort parameter

If cobSort = "Name" Then
Me.RecordSource = "UWQryFormSort"
End If

End Sub

_______________


You can see that I have another query called "UWQryFormSort" that is exactly the same as my first one except I put this one in ascending order under the field called "Name". When I clicked on the button it does not change the sort order of the table though I did confirm that my query does.

What am I doing wrong? I thought that the Me.RecordSource method would work in this new event.

Thanks again,

Keith.
Feb 7 '08 #5

jaxjagfan
Expert 100+
P: 254
Thanks, that worked but I discovered that it only worked if I put the code in both the Main form and the Sub form (when I created the form I have a subform embedded in it). Not too sure why....

I have another quick question that is related; on the form I want to change the way the subform is displaying the info (the sort order). That is to say that the subform is in a datasheet view and I want to give the user the ability to see the same information in different sort orders (By Name, Location, Product, etc).

I placed a combo box on my form called "cobSort" and in there is a drop down list to choose from (Name, Location, Product, etc). Then I put a command button called "cmdReSort_Tbl" and on the onclick property I put the following code:

Private Sub cmdReSort_Tbl_Click()
'When user makes a selection on the Sort Table dropdown menu the table will requery
' via that sort parameter

If cobSort = "Name" Then
Me.RecordSource = "UWQryFormSort"
End If

End Sub

_______________


You can see that I have another query called "UWQryFormSort" that is exactly the same as my first one except I put this one in ascending order under the field called "Name". When I clicked on the button it does not change the sort order of the table though I did confirm that my query does.

What am I doing wrong? I thought that the Me.RecordSource method would work in this new event.

Thanks again,

Keith.
If using the combo to define the column to sort - should be something like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReSort_Tbl_Click()
  2. Dim strCtl
  3. strCtl = Me.cobSort
  4. Docmd.GoToControl strCtl
  5. DoCmd.RunCommand acCmdSortAscending
  6. End Sub
  7.  
Feb 7 '08 #6

kcdoell
100+
P: 230
I receive an error message (runtime 2109) indicating that "There is no field name 'Name' in the current record"

Could that be happening because the code is tied to the form and not the subform that is displaying the table?

Keith.
Feb 7 '08 #7

kcdoell
100+
P: 230
You guys were great.... I played around with it and got it to work. All I needed to do was change my subform's default view from a datasheet to a continuous form and then I embedded my cobSort in the header of the subform. I also got rid of the command button and put the code in the after update event of my cobSort control to make it look cleaner and to save a click.

Thanks a lot!

:-)

Keith.
Feb 7 '08 #8

Post your reply

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