filter on command

J

JRS

I asked this question and did not get a response so I am trying again. I
have a command button on a form. A parameter query is link to form so when
you open the form you are prompted for a group, subgroup and pkg number. The
command button was in case you wanted to request a different group from the
opened form. I created the command and once prompted for the same info...the
form goes blank and you have to manually go into records and hit the filter
sort to see the data. I wanted some code to automate that. I have read
other notes and tried to do what was reccomended however I must be putting it
in the wrong place as I have not got it to work. I am pasting the command
code....could you place the code where it belongs in the paste so I can see
exactly? thanks


Option Compare Database

Private Sub Command68_Click()
On Error GoTo Err_Command68_Click

Dim stDocName As String

stDocName = "FORM QUERY"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command68_Click:
Exit Sub

Err_Command68_Click:
MsgBox Err.Description
Resume Exit_Command68_Click

End Sub
Private Sub Command71_Click()
On Error GoTo Err_Command71_Click


DoCmd.Quit

Exit_Command71_Click:
Exit Sub

Err_Command71_Click:
MsgBox Err.Description
Resume Exit_Command71_Click

End Sub
Private Sub Command73_Click()
On Error GoTo Err_Command73_Click


DoCmd.PrintOut

Exit_Command73_Click:
Exit Sub

Err_Command73_Click:
MsgBox Err.Description
Resume Exit_Command73_Click

End Sub
Private Sub Command74_Click()
On Error GoTo Err_Command74_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command74_Click:
Exit Sub

Err_Command74_Click:
MsgBox Err.Description
Resume Exit_Command74_Click

End Sub
Private Sub Command75_Click()
On Error GoTo Err_Command75_Click


DoCmd.Quit

Exit_Command75_Click:
Exit Sub

Err_Command75_Click:
MsgBox Err.Description
Resume Exit_Command75_Click

End Sub
Private Sub GROUP_NUMBER_ONLY_Click()
On Error GoTo Err_GROUP_NUMBER_ONLY_Click

Dim stDocName As String

stDocName = "FORM QUERY GROUP NUMBER ONLY"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_GROUP_NUMBER_ONLY_Click:
Exit Sub

Err_GROUP_NUMBER_ONLY_Click:
MsgBox Err.Description
Resume Exit_GROUP_NUMBER_ONLY_Click

End Sub
Private Sub query_by_group_number_only_Click()
On Error GoTo Err_query_by_group_number_only_Click

Dim stDocName As String

stDocName = "QUERY GROUP NUMBER ONLY"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_query_by_group_number_only_Click:
Exit Sub

Err_query_by_group_number_only_Click:
MsgBox Err.Description
Resume Exit_query_by_group_number_only_Click

End Sub
Private Sub Query_by_Group_Name_Click()
On Error GoTo Err_Query_by_Group_Name_Click

Dim stDocName As String

stDocName = "QUERY BY GROUP NAME"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Query_by_Group_Name_Click:
Exit Sub

Err_Query_by_Group_Name_Click:
MsgBox Err.Description
Resume Exit_Query_by_Group_Name_Click

End Sub
Private Sub Command85_Click()
On Error GoTo Err_Command85_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PROGRAM INFORMATION"


stLinkCriteria = "[PKG_NBR]=" & "'" & Me![PKG_NBR] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command85_Click:
Exit Sub

Err_Command85_Click:
MsgBox Err.Description
Resume Exit_Command85_Click

End Sub
 
S

strive4peace

find record based on looking up value in related table
---

Hi Carol,

Rather than using the subform to find records in the mainform, why not
simply put a combo in the main form to find records based on listing
choices from a related table...

for example, make an unbound combo:

Name --> FindFamilyNumber
ColumnCount --> 2 'or whatever
ColumnWidths --> 0;2 'first column will be hidden
ListWidth --> 2 'sum of ColumnWidths
RowSource -->
SELECT FamilyNumber, Membername
FROM MembersTable
ORDER BY Membername

AfterUpdate --> =FindRecordFamilyNumber()

where
-FamilyNumber is a control on your main form with that name (make
changes according to your data) and is used to link the main and related
table -- am assuming Long Integer data type (Autonumbers are Long Integers)
-Membername is the field(s) containing the other information you want to
show
-MembersTable is the name of your related table


code behind form:
'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecordFamilyNumber()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "FamilyNumber = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function
'~~~~~~~~~~~~~~~~~~~~

if you want the search to be done from the subform, adjust Me. -->
Me.Parent.
and put the code behind the subform


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



I asked this question and did not get a response so I am trying again. I
have a command button on a form. A parameter query is link to form so when
you open the form you are prompted for a group, subgroup and pkg number. The
command button was in case you wanted to request a different group from the
opened form. I created the command and once prompted for the same info...the
form goes blank and you have to manually go into records and hit the filter
sort to see the data. I wanted some code to automate that. I have read
other notes and tried to do what was reccomended however I must be putting it
in the wrong place as I have not got it to work. I am pasting the command
code....could you place the code where it belongs in the paste so I can see
exactly? thanks


Option Compare Database

Private Sub Command68_Click()
On Error GoTo Err_Command68_Click

Dim stDocName As String

stDocName = "FORM QUERY"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command68_Click:
Exit Sub

Err_Command68_Click:
MsgBox Err.Description
Resume Exit_Command68_Click

End Sub
Private Sub Command71_Click()
On Error GoTo Err_Command71_Click


DoCmd.Quit

Exit_Command71_Click:
Exit Sub

Err_Command71_Click:
MsgBox Err.Description
Resume Exit_Command71_Click

End Sub
Private Sub Command73_Click()
On Error GoTo Err_Command73_Click


DoCmd.PrintOut

Exit_Command73_Click:
Exit Sub

Err_Command73_Click:
MsgBox Err.Description
Resume Exit_Command73_Click

End Sub
Private Sub Command74_Click()
On Error GoTo Err_Command74_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command74_Click:
Exit Sub

Err_Command74_Click:
MsgBox Err.Description
Resume Exit_Command74_Click

End Sub
Private Sub Command75_Click()
On Error GoTo Err_Command75_Click


DoCmd.Quit

Exit_Command75_Click:
Exit Sub

Err_Command75_Click:
MsgBox Err.Description
Resume Exit_Command75_Click

End Sub
Private Sub GROUP_NUMBER_ONLY_Click()
On Error GoTo Err_GROUP_NUMBER_ONLY_Click

Dim stDocName As String

stDocName = "FORM QUERY GROUP NUMBER ONLY"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_GROUP_NUMBER_ONLY_Click:
Exit Sub

Err_GROUP_NUMBER_ONLY_Click:
MsgBox Err.Description
Resume Exit_GROUP_NUMBER_ONLY_Click

End Sub
Private Sub query_by_group_number_only_Click()
On Error GoTo Err_query_by_group_number_only_Click

Dim stDocName As String

stDocName = "QUERY GROUP NUMBER ONLY"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_query_by_group_number_only_Click:
Exit Sub

Err_query_by_group_number_only_Click:
MsgBox Err.Description
Resume Exit_query_by_group_number_only_Click

End Sub
Private Sub Query_by_Group_Name_Click()
On Error GoTo Err_Query_by_Group_Name_Click

Dim stDocName As String

stDocName = "QUERY BY GROUP NAME"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Query_by_Group_Name_Click:
Exit Sub

Err_Query_by_Group_Name_Click:
MsgBox Err.Description
Resume Exit_Query_by_Group_Name_Click

End Sub
Private Sub Command85_Click()
On Error GoTo Err_Command85_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PROGRAM INFORMATION"


stLinkCriteria = "[PKG_NBR]=" & "'" & Me![PKG_NBR] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command85_Click:
Exit Sub

Err_Command85_Click:
MsgBox Err.Description
Resume Exit_Command85_Click

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

form filter and coomand button 0
How to make one button to update 3 queries 9
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Access 2002 MAR not opening in 2003 4
Error Handling not working properly 17
Command button plus message 2
Go to next record or close 1
Criteria when opening form 1

Top