option group to display query

[Delindan]

I have a form that has an option group at the top called framemissing. With each selection from the option group, I would like my subform called qmissing to display records from the query created with each selection from the option group. In the properties for framemissing, I have an afterupdate routine as follows:

Private Sub FrameMissing_AfterUpdate()

Dim strsql As String

Select Case FrameMissing

Case 1
strsql = "SELECT * FROM [Head Count] WHERE 1=1 AND [Head Count].[Budgeted CC] In ("");"

Case 2
strsql = "SELECT * FROM [Head Count] WHERE 1=1 AND [Head Count].[Budgeted Annual Salary] In (0);"

Case 3
strsql = "SELECT * FROM [Head Count] WHERE (((1)=1) AND (([Head Count].[Budgeted Start]) Is Null));"

Case 4
strsql = "SELECT * FROM [Job Activity] WHERE 1=1 AND [Job Activity].[Act Cost Center] In ("");"

Case 5
strsql = "SELECT * FROM [Job Activity] WHERE 1=1 AND [Job Activity].[Act Annual Salary] In (0);"

Case 6
strsql = "SELECT * FROM [Job Activity] WHERE 1=1 AND (([Job Activity].[Act Start Date]) Is Null));"

Case 7
strsql = "SELECT * FROM [Job Activity] WHERE 1=1 AND (([Job Activity].[FC Start Date]) Is Null));"

Case 8
strsql = "SELECT * FROM [Job Activity] WHERE 1=1 AND [Job Activity].[FC Annual Salary] In (0);"

End Select

Me.QMissing.Form.RecordSource = strsql



End Sub

Obviously this isn't working or I wouldn't be posting however I do not have anything listed in the recordsource for the subform since I would like the option group to determine that. I'm a little confused about the properties for the subform. I know that there is the qmissing properties and the source object is listed as qmissing. Then you can click in the upper left corner and it selects a form that you can put a record source in the properties. this is blank right now. clearly I don't understand the difference between the two. Any ideas? Thanks!

 

[lameid]

After you change the recordsource you have to tell the subform to update it's data using requery...

Me.QMissing.Requery

 

[dhookom]

I don't think you really need to requery when you change the record source.
I'm not sure why you use "IN ()" with a single value. Just use "=".
You are using records from two different tables/queries. Do they have the same fields?

Private Sub FrameMissing_AfterUpdate()
  Dim strsql As String
  Select Case FrameMissing
    Case 1
      strsql = "SELECT * FROM [Head Count] WHERE [Budgeted CC] = '';"
    Case 2
      strsql = "SELECT * FROM [Head Count] WHERE [Budgeted Annual Salary] = 0;"
    Case 3
      strsql = "SELECT * FROM [Head Count] WHERE [Budgeted Start] Is Null;"
    Case 4
      strsql = "SELECT * FROM [Job Activity] WHERE [Act Cost Center] = '';"
    Case 5
      strsql = "SELECT * FROM [Job Activity] WHERE [Act Annual Salary] = 0;"
    Case 6
      strsql = "SELECT * FROM [Job Activity] WHERE [Act Start Date] Is Null;"
    Case 7
      strsql = "SELECT * FROM [Job Activity] WHERE [FC Start Date] Is Null;"
    Case 8
      strsql = "SELECT * FROM [Job Activity] WHERE [FC Annual Salary] = 0;"
  End Select
  Debug.Print strSQL
  Me.QMissing.Form.RecordSource = strsql
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

I'm using two different tables and the fields are different. I've been thinking about monkeying with the queries. Will try that next.

 

[Delindan]

So I did have a problem with a few of my queries. Here is what is happening though. The two tables I'm trying to view records for are related with one common field...position number. When the different options are chosen, the position number is the only field that will come up. The number of records is correct and the position number is correct as well. I'm guessing this has something to do with what I was talking about before that I'm having a bit of trouble understanding. (the properties of the subform vs. the properties of the form) How do I get it to display all fields that I've selected in my queries?

 

[Delindan]

I was rechecking the properties of the form and saw that I had put a record source of [Head Count] in there. I took that out and now the subform won't display anything. What do I put in the record source if I want the option group to determine that?

Thanks

 

[dhookom]

If your subform has bound controls, how do you expect to change their control sources to match two different sets of fields?

If you only want to display a datasheet view of your records in the subform, I would use your code to change the SQL property of a saved query and set the Source Object of the subform control to the query name.

Duane
Hook'D on Access
MS Access MVP