Setting a string with option groups in Access

[TrueType]

Dear Access Experts,

I am trying to create an Access form which contains an option group
(Frame1) with several entries. After selecting one of these entries the
user should be able to execute the corresponding query by clicking on
the action button (Command1). Unfortunately the action button
"Command1" does not accept the defined string in "Frame1" as the error
message "Method or data member not found" pops up. Can anybody help me
out? Here's my Access code:

____________________________________________________________
Private Sub Frame1_AfterUpdate()
Dim strSelection As String
Select Case Frame1.Value
Case 1
strSelection = "NameofQuery1"
Case 2
strSelection = "NameofQuery2"
End Select

End Sub

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

DoCmd.OpenQuery Frame1.strSelection, acNormal, acEdit

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub
____________________________________________________________


Thanks for your help in advance,
TrueType

 

[Keith Wilby]

Dear Access Experts,

I am trying to create an Access form which contains an option group
(Frame1) with several entries. After selecting one of these entries the
user should be able to execute the corresponding query by clicking on
the action button (Command1). Unfortunately the action button
"Command1" does not accept the defined string in "Frame1" as the error
message "Method or data member not found" pops up. Can anybody help me
out? Here's my Access code:

____________________________________________________________
Private Sub Frame1_AfterUpdate()
Dim strSelection As String
Select Case Frame1.Value
Case 1
strSelection = "NameofQuery1"
Case 2
strSelection = "NameofQuery2"
End Select

End Sub

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

DoCmd.OpenQuery Frame1.strSelection, acNormal, acEdit

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

Try declaring strSelection in the module header and changing

DoCmd.OpenQuery Frame1.strSelection, acNormal, acEdit

to

DoCmd.OpenQuery strSelection, acNormal, acEdit

Regards,
Keith.
www.keithwilby.com

 

[TrueType]

That made it! Thanks for your fast help Keith.

One more question: Is it possible to add a second action button (e.g.
Command2) which automatically exports the chosen query into MS Excel?

Kind regards,
TrueType

 

[Keith Wilby]

That made it! Thanks for your fast help Keith.

One more question: Is it possible to add a second action button (e.g.
Command2) which automatically exports the chosen query into MS Excel?

Yes, search the help for the "transfer spreadsheet" method.

Regards,
Keith.
www.keithwilby.com

 

[TrueType]

Found it. Unfortunately there seems to be a problem with "strSelection"
again as the following message pops up: "The action or method requires
a table name argument."

______________________________________________________________
Private Sub Command14_Click()

Dim strXLFile As String

strXLFile = InputBox("Enter path of file", "Path of File")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
strSelection, strXLFile

End Sub
______________________________________________________________

When entering the Query directly everything works just fine.

Any idea?

Thanks,
TrueType

 

[TrueType]

Please ignore my previous message. Nearly everything is working fine
now!

There's only one small issue: Every time I call up the form I first
have to select/deselect/select one of the opportunities even though
Option "1" is selected by default. Otherwise the string seems to be
empty. Any idea how to solve this?

 

[Keith Wilby]

Please ignore my previous message. Nearly everything is working fine
now!

There's only one small issue: Every time I call up the form I first
have to select/deselect/select one of the opportunities even though
Option "1" is selected by default. Otherwise the string seems to be
empty. Any idea how to solve this?

Try calling the option group's module in the form's load event:

Call Frame1_AfterUpdate

Regards,
Keith.

 

[TrueType]

Thanks! All problems solved...

Have a nice time,
TrueType

 

[Keith Wilby]

Thanks! All problems solved...

Have a nice time,
TrueType

Glad to help. Good luck with your project.

Keith.