Setting a string with option groups in Access

  • Thread starter Thread starter TrueType
  • Start date Start date
T

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
 
TrueType said:
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
Click to expand...

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
 
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
 
TrueType said:
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?
Click to expand...

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

Regards,
Keith.
www.keithwilby.com
 
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
 
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?
 
TrueType said:
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?
Click to expand...

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

Call Frame1_AfterUpdate

Regards,
Keith.
 
Back
Top