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,
Fliss RundleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
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 & "'"

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fliss RundleAuthor Commented:
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.
John TsioumprisSoftware & Systems EngineerCommented:
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...
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Download eBook
Gustav BrockCIOCommented:
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
PatHartmanCommented:
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.
Fliss RundleAuthor Commented:
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

Open in new window

Fliss RundleAuthor Commented:
Thanks again.
PatHartmanCommented:
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.
Fliss RundleAuthor Commented:
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,
PatHartmanCommented:
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.
Fliss RundleAuthor Commented:
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.
PatHartmanCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.