Referencing Option Groups 1

[integritycare]

Hi all,
Just a quick request please. How do you reference an option group on a mainform to a subform. I have enclosed the code of the option group on the mainform;

Option Compare Database
Option Explicit
Const SQL = "tblDocumentManagement.DocNo,tblDocumentManagement.RevNo,tblDocumentManagement.Group,tblDocumentManagement.Title,tblDocumentManagement.FileLoc,tblDocumentManagement.IssueReview,tblDocumentManagement.NextDate" 

Private Sub optFilterBy_AfterUpdate()
Dim FilterSQL As String
 
 Select Case Me!optFilterBy

Case 1
FilterSQL = SQL & " Where [Group] = 'Admin';"
Case 2
FilterSQL = SQL & " Where [Group] = 'Agenda';"
Case 3
FilterSQL = SQL & " Where [Group] = 'Contract';"
Case 4
FilterSQL = SQL & " Where [Group] = 'CLS';"
Case 5
FilterSQL = SQL & " Where [Group] = 'CV';"
Case 6
FilterSQL = SQL & " Where [Group] = 'Empl';"

Case Else
            FilterSQL = SQL & ";"
    End Select

    Me.RecordSource = FilterSQL
   Me.Requery

End Sub

The subform name is "DocManagement"


Many thanks

Integrity

 

[dhookom]

I'm not sure what you are attempting to do or what you mean by "an option group on a mainform to a subform".

There is no "SELECT" or "FROM" statements in your SQL. You need these in a Record Source. Once you set a Record Source you should not have to requery.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

An option group is a control
so to reference a control on a subform from the mainform

Me.SubformcontrolName.form.controlonsubformname

The name of the subform control is not always the name of the source object within the subform control

To reference a control on the main form from a subform

me.parent.controlOnMainformName

 

[TheAceMan1]

How are ya ntegritycare . . .

I'm working under the assumption that you want to filter the subform ... which is based on [blue]tblDocumentManagement[/blue].

To begin ... remove the [blue]SQL[/blue] Const (totally). Then copy/paste the new code below in the [blue]optFilterBy[/blue] AfterUpdate event:

[blue]   Dim SQL As String
   
   SQL = "[purple][b]SELECT[/b][/purple] DocNo,RevNo, Group, Title, " & _
         "FileLoc, IssueReview, NextDate " & _
         "[purple][b]FROM tblDocumentManagement[/b][/purple]"
   
   Select Case Me!optFilterBy
      Case 1
         SQL = SQL & " Where [Group] = 'Admin';"
      Case 2
         SQL = SQL & " Where [Group] = 'Agenda';"
      Case 3
         SQL = SQL & " Where [Group] = 'Contract';"
      Case 4
         SQL = SQL & " Where [Group] = 'CLS';"
      Case 5
         SQL = SQL & " Where [Group] = 'CV';"
      Case 6
         SQL = SQL & " Where [Group] = 'Empl';"
      Case Else
         SQL = SQL & ";"
   End Select

   [[purple][B][I]YourSubFormName[/I][/B][/purple]].RecordSource = SQL[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

integritycare . . .

Updated my prior code:

[blue]   Dim SQL As String, [purple][b]Cri[/b][/purple] As String
   
   SQL = "[purple][b]SELECT[/b][/purple] DocNo,RevNo, Group, Title, " & _
         "FileLoc, IssueReview, NextDate " & _
         "[purple][b]FROM tblDocumentManagement[/b][/purple]"
   [purple][b]Cri[/b][/purple] = Choose(Me!optFilterBy, "Admin", "Agenda", "Contract", _
                                "CLS", "CV", "Emp1")
   SQL = SQL & " Where [Group] = " & [purple][b]Cri[/b][/purple] & ";"
   [[purple][B][I]YourSubFormName[/I][/B][/purple]].RecordSource = SQL[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[integritycare]

Hi The Aceman1,

Many thanks for your reply.
Yes thats what I want to do is to filter the subform using an option group on the main form.
I have done as you have written. When I go to select a option button there is a complie error;

[DocManagement.RecordSource= SQL

The subform is based on a query from the tblDocumentManagement

"Method or data member not found"

Would you be able to explain this error to me.

Many Thanks

Integrity

 

[TheAceMan1]

integritycare . . .

Your missing a closing bracket:

[blue][DocManagement[red][b]][/b][/red].RecordSource= SQL[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[integritycare]

Hi the Aceman1,

Again thanks for your reply.

Missing that end bracket waqs just a typo in this post. The actual code as in the module is;

[DocManagement].RecordSource= SQL

But the error is still there

Many Thanks,

Integrity

 

[integritycare]

Hi The Aceman,
I think I am getting closer to a resolution!

Me.DocManagement.Form.Recordsource = SQL

I Had not referenced the subform

This does allow me to select the options...but
when I select an option I get a request for a parameter Value!

If I enter a value identical to the option it works ok.
How do I get rid of this Enter Parameter Value?

Regards,

Integrity

 

[TheAceMan1]

integritycare . . .

I see a syntax error (my fault).

[blue]Change: [YourSubFormName].RecordSource = SQL
To    : [YourSubFormName][purple][b].Form[/b][/purple].RecordSource = SQL[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

integritycare . . .

Put in the single quotes in [red]red[/red] where you see them.

[blue]SQL = SQL & " Where [Group] = [red][b]'[/b][/red]" & Cri & "[red][b]'[/b][/red];"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[integritycare]

The Aceman1..

Many thanks for that. Works Great. You're a champ!

Integrity

 

[dhookom]

IMO, it's wrong to have values hard-coded into your code when there should be a table with numbers and the associated values of:
Admin, Agenda, Contract, CLS, CV, and Emp1.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

dhookom . . .

I could'nt agree more! However I've learned that if data will be static (no additions/deletions/name changes) ... there's no harm in [blue]hard coding[/blue]. Infact ... coding can be made simpler ... as in this post.

If data can change (amount/names) then I make a table. If not ... I hardcode ... thru the years I've never had a problem with this.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

IMO, I would not consider hard-coding this. There are 6 options. I would always plan for 5 or 7 or whatever. I would probably use a combo box or list box rather than the option group.

I might use an option group with hard-coding for gender or Yes/No but never 6 options that seem to be data based on business groups or similar.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

dhookom . . .

IMO ... I don't see where the [blue]static[/blue] data presented in this thread poses any problem in design. I always seek the db that [blue]works well[/blue], not the one [blue]thats perfect![/blue]. Perfection in db design always leaves something to do!

IMO ... I can't expect posters to think of the necessary things you or I would. There just not learned enough. Nor (over the years) have they shown any real interest. There are exceptions ... but they are rare. They want what they want and thats basically it. I just don't fight it anymore.

Again ... I couldn't agree with you more. But, will [blue]integritycare[/blue] come back after reading your posts? Only time will tell ... and for this issue I say no. Its well meant but thats as far as it will go.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

TheAceMan1,
Then we agree to disagree. I would never accept this type of code from someone who works for me. I build applications that users can maintain.

These text values don't seem to be any type of standard like months of the year or days of the week. Clearly they are already stored in a field named [Group] in some table. I would anticipate the values would be modified or added to over time. As I stated, I would use a combo box or even better a multi-select list box based on the unique values from the [Group] field.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

dhookom . . .

I will not bicker with you ... I have too much respect for you for that. [blue]All things are as you wish them to be![/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[integritycare]

Hi Guys,
Yes I have read your posts.
There was a reason why I choose to use Option controls over a combo box in which I could have used a table with the respective criteria to select.
When we had our external audit (ISO9001:2008) the external auditor gave an observation that using a combo box (which was in use)would be difficult for the user....! I gave the staff the option.. what would they prefer to use. They opted for the option box.(wether this is right or not to give a choice..)
"IMO ... I can't expect posters to think of the necessary things you or I would. There just not learned enough. Nor (over the years) have they shown any real interest."

I dont know if I agree entirely with this statement. The first part is probably correct, but I do take an interest.. After all I'm not a programmer, but do hold a Masters degree in Avionics.
So learing to do things right is what I do best. But thanks for this forum. I will take on board your comments.
Sometimes its difficult when you read the forums with so many differing concepts. To a non programmer sometimes it does just not make sense!

Many thanks,
Happy programming to you all..I really enjoy these forums.Quite a teaching tool

Integrity