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

[kcdoell]

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.

[MMcCarthy]

Not sure I fully understand what you are looking for but ...

Me.filter = "[Fieldname]=" & someValue
Me.filterOn

[kcdoell]

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.

[MMcCarthy]

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

[kcdoell]

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.

[jaxjagfan]

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:

Private Sub cmdReSort_Tbl_Click()
Dim strCtl
strCtl = Me.cobSort
Docmd.GoToControl strCtl
DoCmd.RunCommand acCmdSortAscending
End Sub
 

[kcdoell]

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.

[kcdoell]

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.