Using MS Access/VBA Code to get value from custom form option box
I am trying to have Access 2007 VBA open a custom form and wait for an option (1, 2 or 3) in an option group to be selected, then pass that value back in to a variable in my VBA code to continue processing. Once the value is retrieved and the processing completed, I need to then close the form.
I'm having two separate issues:
1. When I run the DoCmd.OpenForm, it only partially displays the form, and none of the buttons appear.
2. It appears that as soon as it attempts to open the form, it goes into a permanent loop where it is waiting on a response, but not allowing anything to be selected on the form.
Here are the relevant lines of the code I have so far (the first two lines are irrelevant to this example, as they pertain to other parts of the code but are needed to show the entire structure properly). I'm thinking of adding a form refresh in the VBA after it opens the form to resolve issue 1. Any help would be greatly appreciated, as this is a critical portion of the overall process I am doing.
' Code Listing begins here...
' The first two lines are irrelevant to the rest of the code,
' but are needed to show the whole context of the
' IF...THEN...ELSE block.
If intAnswer = vbYes Then
intFlag = 1
' The code to look at starts with the next line
intExcelType = 0
intExcelType = Value(Forms![zzForm-ExcelFormat]!_
' grpExcelType is the name of the option group on the form. The
' option group also has an AfterUpdate code to assign the value
' to variable grpValue, in case I need to go that route. In
' either case, the value should only be a 1, 2 or 3, with no
' default automatically selected.
Select Case intExcelType
intExcelFormat = 56 '56: 1995-2003 Excel format
strExcelExtension = "xls"
intExcelFormat = 51 '51: Office 2007 xlsx format
strExcelExtension = "xlsx"
MsgBox "Checklist creation cancelled by user.",_
vbOKOnly, "Checklist creation cancelled"
intFlag = 0
DoCmd.Close acForm, [zzForm-ExcelFormat], acSaveNo
First, you have the code in the wrong place. Second the popup form need to have the popup and modal properties set to Yes.
So you open the form, that's all, from the calling form. When you make a selection in the Option group, you populate a TempVar, global variable or a textbox on the calling form. Then you proceed with the next step.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA