Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Access: Opening a Form to show records based on a Query
Hi,
I'm trying to open a form in Access to show the records as filtered by a query.
I've got the following code:
Private Sub CMD_SearchMeetings_Click()
On Error GoTo myError
DoCmd.OpenForm "FRM_meetinglog", , "QRY_SearchMeetings"
Exit Sub
myError:
MsgBox Error$
Resume Next
End Sub
But it doesn't seem to want to work - it keeps asking me for parameters when I click the button even though the query runs fine on its own. I'm a novice at Access and I'm sure this is a really basic thing, but I can't seem to find anything useful on the net to help.
The query runs from the entries on the form where the button sits and when I run the query manually, it filters to the correct results. The query output is a key field in one table, which is linked to others, the form has a combination of data from various tables, but all linked back to the one key field. The reason for the multiple tables is because I have meetings which can be attended by multiple people from my organisation and others. My query checks dates, attendees from my organisation and attendees from other organisations, to link back to the individual meeting reference.
I want to be able to see all of the information for the specific meetings searched for.
Thanks,
I'm trying to open a form in Access to show the records as filtered by a query.
I've got the following code:
Private Sub CMD_SearchMeetings_Click()
On Error GoTo myError
DoCmd.OpenForm "FRM_meetinglog", , "QRY_SearchMeetings"
Exit Sub
myError:
MsgBox Error$
Resume Next
End Sub
But it doesn't seem to want to work - it keeps asking me for parameters when I click the button even though the query runs fine on its own. I'm a novice at Access and I'm sure this is a really basic thing, but I can't seem to find anything useful on the net to help.
The query runs from the entries on the form where the button sits and when I run the query manually, it filters to the correct results. The query output is a key field in one table, which is linked to others, the form has a combination of data from various tables, but all linked back to the one key field. The reason for the multiple tables is because I have meetings which can be attended by multiple people from my organisation and others. My query checks dates, attendees from my organisation and attendees from other organisations, to link back to the individual meeting reference.
I want to be able to see all of the information for the specific meetings searched for.
Thanks,
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I am guessing the query has criteria bound to the form's controls..in this case it would be better to go like this :
DoCmd.OpenForm "FRM_meetinglog", , , "NumericCriteria= " & NumericValue & " AND StringCriteria ='" & StringValue & "'"
Thanks John,
I'm not sure that helps. My query uses input from a form, has multiple criteria that I need to be able to change and generates multiple records as the output.
Criteria include dates, meeting attendees and attendee organisations. The query I have built works. It generates a list of individual meeting references which I then want to be able to use to filter the full list of data by and display the results (along with linked data from other tables) in a form. I have the form built already, and this, with sub forms already embedded and working, can be used to scroll through all of the records. I want to be able to view a sub-set of those records, ie. meetings 1, 3, 5 and 7 were held between dates x and y with q attending, so I want to be able to scroll through those meetings seeing the full information on them.
I'm not sure that helps. My query uses input from a form, has multiple criteria that I need to be able to change and generates multiple records as the output.
Criteria include dates, meeting attendees and attendee organisations. The query I have built works. It generates a list of individual meeting references which I then want to be able to use to filter the full list of data by and display the results (along with linked data from other tables) in a form. I have the form built already, and this, with sub forms already embedded and working, can be used to scroll through all of the records. I want to be able to view a sub-set of those records, ie. meetings 1, 3, 5 and 7 were held between dates x and y with q attending, so I want to be able to scroll through those meetings seeing the full information on them.
Just run the above code using some static sample data...if it runs Ok then you have to modify the above code to work as you want....not to forget for date the criteria goes like this
"DateCriteria = #" & DateCriteriaValue &"#"
It would be most helpful to show some sample data...
"DateCriteria = #" & DateCriteriaValue &"#"
It would be most helpful to show some sample data...
You can't use a query this way. A filter query can only use those fields already present in the recordsource of the report.
So rebuild the query that you use a source for the report.
/gustav
So rebuild the query that you use a source for the report.
/gustav
Substitute the query name for whatever you are currently using as the popup Form's RecordSource. If the query references form controls from a different form, that form MUST be open for this to work.
Thanks for all the help, I sorted it this way in the end as I was struggling to apply the filter from the sub-forms in John's solution above.
Dim MeetRefs As String
Dim RS As DAO.Recordset
DoCmd.OpenForm "FRM_SearchMeetingRefs", acFormDS
Set RS = Forms!FRM_SearchMeetingRefs.RecordsetClone
If RS.RecordCount = 0 Then
MsgBox ("No Meetings")
DoCmd.Close acForm, "FRM_SearchMeetingRefs", acSaveNo
Else
With RS
Do While Not RS.EOF
MeetRefs = MeetRefs & "meet_id = " & RS!meet_id & " Or "
RS.MoveNext
Loop
End With
MeetRefs = Left(MeetRefs, Len(MeetRefs) - 4)
DoCmd.Close acForm, "FRM_SearchMeetingRefs", acSaveNo
DoCmd.OpenForm "FRM_meetinglog", , , _
MeetRefs
End If
Just FYI - a subform is a form that is embedded on another form. What you have, most people call a pop up form since formA opens a new mainformB in a separate form window. If FormB were a subform, it would be in a subform control on FormA and would not open in a new window. Having questions answered correctly and quickly can depend on accurate use of terminology.
I'm not sure why you awarded me points since you used John's suggestion to switch to the WHERE argument of the OpenForm method. I don't mean to sound ungrateful but most experts here are adults and don't expect to be awarded points for participation. Glad you got your answer.
I'm not sure why you awarded me points since you used John's suggestion to switch to the WHERE argument of the OpenForm method. I don't mean to sound ungrateful but most experts here are adults and don't expect to be awarded points for participation. Glad you got your answer.
Hi Pat,
My 'pop-up form' has sub forms in it which is what I was referring to hence the use of that term.
I awarded you a point because of your comment around the other form needing to be open to be able to use it.. That helped me find elements of the ultimate solution I used. I wasn't just being nice, it was a reflection of the value I believed had been added.
Thanks,
My 'pop-up form' has sub forms in it which is what I was referring to hence the use of that term.
I awarded you a point because of your comment around the other form needing to be open to be able to use it.. That helped me find elements of the ultimate solution I used. I wasn't just being nice, it was a reflection of the value I believed had been added.
Thanks,
I see. But filtering a subform on a popup form is probably best done as I suggested - by using a query that is replaced when the form loads. When a form with subform opens, the subform events run before the main form events which can make it hard to control the subforms.
OK, that makes sense. I did try to do it that way and for the dates it worked a charm, I just couldn't get the code working around the sub-form elements. What I was trying to achieve was:
MainFormA date criteria between From and To dates from SearchForm and
SubformA (on MainFormA) keyID1 equal to keyID1 from SearchForm and
SubformB (on MainFormA) keyID2 equal to keyID2 from SearchForm and
SubformB (on MainFormA) keyID3 equal to keyID3 from SearchForm
With all having the flexibility for each element to be omitted.
If there is a cleaner way of doing this, and I'm sure there is, then I'd love to see it broken down to a basic level. As I said, I am extremely new at this and it's a pretty steep hill to climb from scratch so any help is much appreciated, apologies if it seems like I need spoon feeding.
MainFormA date criteria between From and To dates from SearchForm and
SubformA (on MainFormA) keyID1 equal to keyID1 from SearchForm and
SubformB (on MainFormA) keyID2 equal to keyID2 from SearchForm and
SubformB (on MainFormA) keyID3 equal to keyID3 from SearchForm
With all having the flexibility for each element to be omitted.
If there is a cleaner way of doing this, and I'm sure there is, then I'd love to see it broken down to a basic level. As I said, I am extremely new at this and it's a pretty steep hill to climb from scratch so any help is much appreciated, apologies if it seems like I need spoon feeding.
To make optional arguments, use this pattern:
Select ..
From ..
Where (fldA = Forms!yourform!fldA OR Forms!yourform!fldA is Null)
AND (fldB = Forms!yourform!fldB OR Forms!yourform!fldB is Null)
AND (fldC = Forms!yourform!fldC OR Forms!yourform!fldC is Null)
...
The parentheses are critical to controlling how the expressions are evaluated and of course any form referenced MUST be open.
Select ..
From ..
Where (fldA = Forms!yourform!fldA OR Forms!yourform!fldA is Null)
AND (fldB = Forms!yourform!fldB OR Forms!yourform!fldB is Null)
AND (fldC = Forms!yourform!fldC OR Forms!yourform!fldC is Null)
...
The parentheses are critical to controlling how the expressions are evaluated and of course any form referenced MUST be open.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial