Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

LinkCritera using an option group and a cbo box

Status
Not open for further replies.
oxicottin

oxicottin

Programmer
Jun 20, 2008
353
US
Ok, I have a form frmSwitchboard with an option group optClassicficationGroup
with eight option values that I want to use as part of a search and a Combo
box named cboFindByEmployeeName with employees names would be used also. Now I
have a button that would be used called cmdFindClassicfication and in the
buttons OnClick event I have: (Only case 1 is shown) My question is how
would I also add the the option value as part of the stLinkCritera and if an
option isnt selected how could I bring up a msgbox like the one used for the
employee cbo box and set focus to that option? Thanks!

Code: 
Private Sub cmdFindClassicfication_Click()
   Dim stDocName As String
   Dim stLinkCriteria As String
   
Select Case Me.optClassicficationGroup

        Case 1
    
    If Me.cboFindByEmployeeName & "" <> "" Then
    stDocName = "frmSearchEntries"
    stLinkCriteria = "[EmployeeName]=" & "'" & Me![cboFindByEmployeeName] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Me.Visible = False

    Else
       MsgBox "You Must Enter a Date.", vbOKOnly, "Date Not Entered"
       Me.cboFindByEmployeeName.SetFocus
    End If
            
        Case 2
            
        Case 3
            
        Case 4
            
    End Select

End Sub


Thanks,
SoggyCashew.....
 
Sort by date Sort by votes
How are ya oxicottin . . .

Have a look at the [blue]Choose[/blue] function!

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
TheAceMan1, I have looked around on the net and im not sure how to add the choose function to the code? Can you give an example of how it would be used in my situation?

Thanks,
Chad

Thanks,
SoggyCashew.....
 
Upvote 0 Downvote
oxicottin . . .

Need a little more explanation on your option values!

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
TheAceMan1, if you mean how they are saved it is by a number 1,2,3,4,,,,8 in a table. Im not sure what you are wanting? Thanks!

Thanks,
SoggyCashew.....
 
Upvote 0 Downvote
TheAceMan1, I got it working this way but is it the proper way? Thanks!

Code: 
Private Sub cmdFindClassicfication_Click()
   Dim stDocName As String
   Dim stLinkCriteria As String
   Dim radioValue As String
    
    If Me.cboFindByEmployeeName & "" <> "" Then
    
        Select Case Me.optClassicficationGroup
    
            Case 1
                radioValue = "1"
            Case 2
                radioValue = "2"
            Case 3
                radioValue = "3"
            Case 4
                radioValue = "4"
            Case 5
                radioValue = "5"
            Case 6
                radioValue = "6"
            Case 7
                radioValue = "7"
            Case 8
                radioValue = "8"
                
        End Select
        
        stDocName = "frmSearchEntries"
        stLinkCriteria = "Employee=" & Me![cboFindByEmployeeName] & " AND AccidentTypeName=" & 'radioValue' & "'"
        'MsgBox (stLinkCriteria)
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        Me.Visible = False
    
    Else
    
       MsgBox "You Must Enter a Employee.", vbOKOnly, "Employee Not Entered"
       Me.cboFindByEmployeeName.SetFocus
       
    End If
End Sub

Thanks,
SoggyCashew.....
 
Upvote 0 Downvote
oxicottin . . .

Sorry to get back so late! AceMan is not retired and hard to get in a post at work.

You got this working? [surprise] ... There are definite errors in [blue]stLinkCriteria![/blue] [surprise]

In any case, condensing your code reveals:
Code: 
[blue]
   Dim Cri As String, RadioVal As String
    
   If Trim(Me.cboFindByEmployeeName & "") <> "" Then
      RadioVal = Me.optClassicficationGroup
    
      Cri = "[Employee] = '" & Me![cboFindByEmployeeName] & "' AND " & _
            "[AccidentTypeName] = '" & RadioVal & "'"
      DoCmd.OpenForm "frmSearchEntries", , , Cri
      Me.Visible = False
    Else
      MsgBox "You Must Enter a Employee.", vbOKOnly, "Employee Not Entered"
      Me.cboFindByEmployeeName.SetFocus
   End If[/BLUE]

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
It worked but if there was no data I would get a 2501 error? I will try yours and see it it works... Im at work as well so Im running back and forth to the PC to se if I can get this running. Thanks!

Thanks,
SoggyCashew.....
 
Upvote 0 Downvote
Sorry Ace I posted the wrong code in my 3rd post! the option value is text. Yours gives the number. What do I change in you for it to work? Here is what works for me right now. BUT, if I have no records it gives me the 2501 error and I have tried error trap but that dont work either? Weird... Thanks!


Code: 
Private Sub cmdFindClassicfication_Click()
Dim stDocName As String
   Dim stLinkCriteria As String
   Dim nameChecker As String
   Dim radioValue As String
    
   If Me.cboFindByEmployeeName & "" <> "" Then
    
        Select Case Me.optClassicficationGroup
    
            Case 1
                radioValue = "ANI"
            Case 2
                radioValue = "First Aid"
            Case 3
                radioValue = "3rd Party"
            Case 4
                radioValue = "Confirmation 1st Aid"
            Case 5
                radioValue = "Recordable"
            Case 6
                radioValue = "Restricted Duty"
            Case 7
                radioValue = "Lost Time"
            Case 8
                radioValue = "Fatality"
                
        End Select
        
        stDocName = "frmSearchEntries"
        stLinkCriteria = "Employee=" & Me![cboFindByEmployeeName] & " AND                 AccidentTypeName=" & "'" & radioValue & "'"
        ‘MsgBox (stLinkCriteria)
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        Me.Visible = False
    
    Else
    
       MsgBox "You Must Enter a Employee.", vbOKOnly, "Employee Not Entered"
       Me.cboFindByEmployeeName.SetFocus
       
   End If
End Sub

Thanks,
SoggyCashew.....
 
Upvote 0 Downvote
oxicottin . . .

So were back to the [blue]Choose[/blue] function and what I was asking for! The following should do:
Code: 
[blue]   Dim Cri As String, [purple][b]idx[/b][/purple] As Integer, RadioVal As String
    
   If Me.Recordset.RecordCount > 0 Then
      [purple][b]idx[/b][/purple] = Me.optClassicficationGroup
       
      If Trim(Me.cboFindByEmployeeName & "") <> "" Then
         RadioVal = Choose([purple][b]idx[/b][/purple], "ANI", "First Aid", "3rd Party", "Confirmation 1st Aid", _
                                "Recordable", "Restricted Duty", "Lost Time", "Fatality")
         
         Cri = "[Employee] = " & Me![cboFindByEmployeeName] & " AND " & _
               "[AccidentTypeName] = '" & RadioVal & "'"
         DoCmd.OpenForm "frmSearchEntries", , , Cri
         Me.Visible = False
       Else
         MsgBox "You Must Enter a Employee.", vbOKOnly, "Employee Not Entered"
         Me.cboFindByEmployeeName.SetFocus
      End If
   Else
      MsgBox "No Records!"
   End If[/blue]
BTW, to view the [blue]Choose[/blue] function, in VBA put the cursor on the word and hit [blue]F1[/blue].

BTW: Welcome to [blue]Tek-Tips![/blue] [thumbsup2] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [thumbsup2] [blue]Its Worthy Reading![/blue]

[blue]Your Thoughts? . . .[/blue]

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
If the label for each of the option values represents the values you are searching for, then the following should work. For example, if the label (caption) for the 1st option value is "ANI" and the label (caption) for the 2nd option value is "First Aid", etc.

Private Sub cmdFindClassicfication_Click()
Dim stDocName As String
Dim stLinkCriteria As String

If (Not IsNull(cboFindByEmployeeName.value)) then
stDocName = "frmSearchEntries"
stLinkCriteria = "Employee='" & Me![cboFindByEmployeeName] & "' AND AccidentTypeName='" & optClassicficationGroup.controls(optClassicficationGroup.value*2-1).controls(0).caption & "'"
'MsgBox (stLinkCriteria)
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me.Visible = False
Else

MsgBox "You Must Enter a Employee.", vbOKOnly, "Employee Not Entered"
Me.cboFindByEmployeeName.SetFocus

End If
End Sub
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BJZeak
  • Locked
  • Question
Struggling with finding an answer to a simple question
Replies
5
Views
530
BJZeak
BJZeak
monkeysee
  • Locked
  • Question
Combo Box - Limit results
Replies
1
Views
391
dhookom
dhookom
breezett93
  • Locked
  • Question
Function that find all controls on a form and puts their tab index in numerical order
Replies
19
Views
732
dhookom
dhookom
visitnag
  • Locked
  • Question
combo box entries problem
Replies
8
Views
351
visitnag
visitnag
Lamar Washington
  • Locked
  • Question
Programmatically send email using only OWA
Replies
2
Views
308
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top