Let me try something else; here is the code I use for an options group. Depending on which one is selected, it pulls up a form; in this case, a delete query. I am not really sure, after re-reading your questions, exactly what it is you want to do...but it is Friday of a hard week so do not blame yourself!
I think what I am doing is backwards of what you are trying to do. Anyway, here is the code to open forms and the code from just one form.
Private Sub FrameDeleteOptions_AfterUpdate()
'this is the delete options group
Dim strFormName As String
'form name dimensioned as string
Select Case Me.FrameDeleteOptions
'select case for what happens when a button is pushed
Case 1: strFormName = "frmDeleteLender"
Case 2: strFormName = "frmDeleteCity"
Case 3: strFormName = "frmDeleteCounty"
Case 4: strFormName = "frmDeleteZIPDiskOwners"
Case 5: strFormName = "frmDeleteRealEstateCompanies"
End Select
Option Compare Database
Option Explicit
'Delete a lender
'
' Notes:
' ---
' The code created was in response to a newsgroup posting
' The underscore character used in the code is a line-continuation character
'
Private Sub cmdCancel_Click()
On Error GoTo Proc_Err
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
Proc_Exit:
Exit Sub
Proc_Err:
MsgBox Prompt:=Err.Description, Buttons:=vbExclamation
Resume Proc_Exit
End Sub
Private Sub cmdOK_Click()
On Error GoTo Proc_Err
If IsNull(Me.cboLenders) Then
' The user clicked OK without selecting an entry
MsgBox _
Prompt:="A selection must be made before proceeding.", _
Buttons:=vbInformation + vbOKOnly, _
Title:="Choose a lender to PERMANENTLY delete!"
Me.cboLenders.SetFocus
Else
' The user made a selection. Display a "second chance"
' prompt to be sure the user wants to delete the entry
'
If vbYes = MsgBox _
( _
Prompt:="You have selected " & Me.cboLenders & _
" to be deleted." & vbCrLf & "Are you sure?", _
Buttons:=vbExclamation + vbYesNo + vbDefaultButton2, _
Title:="This lender will be PERMANENTLY deleted!" _
) Then
' The user wants to delete the entry
If DeleteEntry(Me.cboLenders) Then
MsgBox _
Prompt:="The selected lender was successfuly deleted.", _
Buttons:=vbInformation + vbOKOnly, _
Title:="This lender: " & Me.cboLenders & _
" was PERMANENTLY deleted!" _
' Now that the entry was deleted, requery the combo box so that
' the entry is removed from the list. Otherwise, Access gives a
' weird result in the place where the lender existed.
'
Me.cboLenders.Requery
' Clear the combo box so another entry can be selected.
Me.cboLenders = Null
Else
MsgBox _
Prompt:="The selected lender could not be deleted.", _
Buttons:=vbExclamation + vbOKOnly
End If
End If
End If
Proc_Exit:
Exit Sub
Proc_Err:
MsgBox Prompt:=Err.Description, Buttons:=vbExclamation
Resume Proc_Exit
End Sub
Private Function DeleteEntry( _
pstrEntry As String) As Boolean
'
' Comments: This will delete the lender entry that was selected.
'
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim fRetVal As Boolean
On Error GoTo Proc_Err
' This is how you execute a query programmatically and how
' you pass it a parameter.
'
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryDeleteLenderEntry"
qdf![prmLender] = pstrEntry
qdf.Execute
' Return a TRUE value to indicate the process completed successfully
'
fRetVal = True
Proc_Exit:
Set qdf = Nothing
Set dbs = Nothing
DeleteEntry = fRetVal
Exit Function
Proc_Err:
fRetVal = False
MsgBox Prompt:=Err.Description, Buttons:=vbExclamation
Resume Proc_Exit
End Function
Judge Hopkins
"...like hunting skunks underwater...."
John Steinbeck,
The Grapes of Wrath
