Multiple records from query not displaying on form

[texassynergy]

I have a form that a user will use to search records. Upon selecting a record, there are three buttons that allows the user to look at more detailed information. This information will be on the many side of the relationship. So I will have multiple values. The called form is just a popup form that I use for several other instances, like displaying memo fields from other tables.

The problem I am having is that only one record shows up at a time on the called form. I have read through the postings and have changed the properties for Default View, Filter on Load, Data Entry, etc. Nothing seems to change the outcome. I have run the query in SQL view and have no issues. It returns the correct records and multiple rows when expecting. Just cant figure out why they don't show up on the form. There are no errors, just the first record from the many side of the relationship shows up in the form. I would like all records to show up, just like when I run the query. Any help would be greatly appreciated. Let me know if there is anything else you need.

query code

SELECT Capability
FROM Process_Meetings_Capabilities, Process_Meetings
WHERE Process_Meetings_Capabilities.Meeting_ID = Process_Meetings.Meeting_ID
AND Process_Meetings_Capabilities.Meeting_ID = 2;

Button Code

Private Sub Capabilities_btn_Click()

strSQL = "SELECT Capability AS Results"
strSQL = strSQL & " FROM Process_Meetings_Capabilities, Process_Meetings"
strSQL = strSQL & " WHERE Process_Meetings_Capabilities.Meeting_ID = Process_Meetings.Meeting_ID"
strSQL = strSQL & " AND Process_Meetings_Capabilities.Meeting_ID = " & Me!Meeting_cmbo.Column(0)

ResultType = "C"
DoCmd.OpenForm "Results_frm"
Forms!Results_frm.RecordSource = strSQL
Forms!Results_frm.Form_Output.ControlSource = "Results"

End Sub

 

[jdraw]

Please explain why you have 2 tables in the query

SELECT Capability
FROM
Process_Meetings_Capabilities
, Process_Meetings
WHERE
Process_Meetings_Capabilities.Meeting_ID = Process_Meetings.Meeting_ID
AND Process_Meetings_Capabilities.Meeting_ID = 2;

I'm not sure where Capabilities is stored, but a query such as

SELECT Capability
FROM Process_Meetings_Capabilities
WHERE  Meeting_ID = 2;

would seem to be enough info to get the Capabilities. Perhaps I'm missing something....

 

[texassynergy]

Please explain why you have 2 tables in the query


I'm not sure where Capabilities is stored, but a query such as

SELECT Capability
FROM Process_Meetings_Capabilities
WHERE  Meeting_ID = 2;

would seem to be enough info to get the Capabilities. Perhaps I'm missing something....

It is an implied join. I did not call out the join with a Join clause, but am joining the two tables in order to match up the records. The Process_Meetings table provides me the One side of the relationship and the Process_Meetings_Capabilities provides the Many side of the relationship. My main form brings in the data from the first table, and the button (code) brings in the Many side of the relationship to my (subform) popup form. It is not a subform in the true instance. Just a call to another form for the purpose of displaying large amounts of data on one form with no other detail, i.e. Memo fields, records from queries with many result sets, etc.

The query works fine and I am not having an issue with it. I get the exact results I need when running it in SQL view. I just can't get those records to display on the form.

 

[shutzy]

have you selected continuous form?

 

[texassynergy]

Yes, I have tried continuous form, datasheet, etc, etc. Nothing changes. I am also trying to manipulate the form property in code but I am getting errors.

Forms!Results_frm.DefaultView = 1

Getting this straight from Microsoft - (tried to post the link, but was not able to). But I am getting errors. Trying to troubleshoot now. It seems fairly straightforward, but not working.

 

[texassynergy]

Still looking for help on this. I tried setting the DefaultView in code and read somewhere that it could only be set in the properties window for the form. If anybody knows different, please let me know. Also, still only getting 1 record on the form, no matter which option I switch it to.

Thanks

 

[JHB]

Post a stripped version of your database with some sample data, (zip it) + name of the form you have the problem.