Select case 1

[ignacious]

I have the following code behind a command button of an option group that has 2 radio buttons. Case 1 or case 2 will work by themselves but as soon as both are in the code clicking the command button does nothing not even an error code..

Private Sub cmdAddTo_Click()
Dim RS As DAO.Recordset
Dim strSQL As String

Select Case Me![frameSelect]

Case 1

Dim ContractID As Long
ContractID = InputBox("Enter the Contract ID of the record you want to add to.")
strSQL = "Select ContractID From tblMaintContract Where ContractID = " & ContractID
Set RS = CurrentDb.OpenRecordset(strSQL)

If RS.EOF Then
'What to do when not found

MsgBox "Record not found. Please enter another number."

Else
'What to do when found
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddToMaintContract"
stLinkCriteria = "[ContractID]=" & [ContractID]
DoCmd.Close acForm, "frmMain"
DoCmd.OpenForm "frmBack"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
RS.Close
Set RS = Nothing


Case 2

Dim WebDesignID As Long

WebDesignID = InputBox("Enter the WebDesign ID of the record you want to add to.")
strSQL = "Select WebDesignID From tblWebDesign Where WebDesignID = " & WebDesignID
Set RS = CurrentDb.OpenRecordset(strSQL)

If RS.EOF Then
'What to do when not found

MsgBox "Record not found. Please enter another number."

Else
'What to do when found
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddToWebDesign"
stLinkCriteria = "[WebDesignID]=" & [WebDesignID]
DoCmd.Close acForm, "frmMain"
DoCmd.OpenForm "frmBack"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
RS.Close
Set RS = Nothing



End Select

End Sub

Thanks for any help..
ignacious

 

[traingamer]

Case 1 or case 2 will work by themselves but as soon as both are in the code clicking the command button does nothing not even an error code.

What do you mean by as soon as both are in the code?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[ignacious]

I can take the code in case 2 out (only the code, leaving the case 2) and it will work or I can take out the code in case 1 and it will work. Does not matter which code is with which case as long as it is only 1 piece of coding in the select case it will work.

Ignacious

 

[CaptainD]

Have you stepped through your code to see if the correct values are being passed and the correct SQL statements are being created?

 

[AccessGuruCarl]

Hello ignacious,

Are you sure you're using the 'Frame Name' and not the Frame's Label Name in your code.

Here is sample code with the Frame's Label Name set to frameSelect but the frame name is Frame0

Private Sub Command7_Click()
    
    Select Case Me.Frame0
    Case 1
        MsgBox "Case 1"
    Case 2
        MsgBox "Case 2"
    Case Else
        MsgBox "No Option Selected!" _
            & vbNewLine & "Please select an option first!"
    End Select

End Sub

Double click the rectangle box on the frame, to display the frame properties.

Hope this solves your problem.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[ogri]

Hi

Problem is (I think) the multiple declarations:-

    Dim stDocName As String
    Dim stLinkCriteria As String

The declarations are "executed" irrespective of their position within If statements, so you are trying to declare each variable twice.

All the best

Keith

 

[ignacious]

ogri....
Thanks and a big star. I took out the set of the declarations under case 2 and everything works properly now.
Thanks again

Ignacious

 

[AccessGuruCarl]

Great catch ogri....

I missed that totally!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[Eupher]

I'm guessing this is precisely the reason that all variables are usually declared at the top of the procedure...

Ken S.

 

[PHV]

FYI, the Option Explicit instruction is very useful ...