You are here:

Using MS Access/VBA Code to get value from custom form option box


Hi Scott:
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
  DoCmd.OpenForm [zzForm-ExcelFormat]
     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
     Case 1
        intExcelFormat = 56 '56: 1995-2003 Excel format
        strExcelExtension = "xls"
     Case 2
        intExcelFormat = 51 '51: Office 2007 xlsx format
        strExcelExtension = "xlsx"
     Case 3
        MsgBox "Checklist creation cancelled by user.",_
         vbOKOnly, "Checklist creation cancelled"
        intFlag = 0
  End Select
End If
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

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.