Pick second value - get error message

[Tekhelpnet]

I have 2 combo boxes on a form.
Report can be run for only either - or value.
If empty - brings all data.

So if user is picking the value in one of the boxes and then trying to pick in another - message should appear like 'Report can be run for one value only'

BUT if they change their mind they should be able to erase the value from the first box and pick in second...

How do I accomplish that?
It gets co confusing on my level.
Thanks

 

[TheAceMan1]

How are ya Tekhelpnet . . .

Me!ComboboxName = Null to erase.

See Ya! . . . . . .

Be sure to see thread181-473997

 

[Tekhelpnet]

What I am using right now disabling a second one if value in one picked. I need not to erase but not to disable but get a message for user to not to touch another IF one value had been picked.

Private Sub Business_Unit_AfterUpdate()
If IsNull(Me.BUSINESS_UNIT) Then
Me.ID_Name.Enabled = True
Me.ID_Name.SetFocus
Else
Me.ID_Name.Enabled = False
End If
End Sub

 

[Tekhelpnet]

How do I code this in
If user erased value in one box - another becomes Enabled again?

 

[Tekhelpnet]

When value was deleted out of the combo box - is it different then if it was empty to begin with?

I am having problem to have second box become Enabled when values are deleted out of the first combo box...

Please, help.

 

[TheAceMan1]

OK Tekhelpnet . . .

Try this: ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):
[ol][li]Copy/paste the following into the code module for the form:

[blue]Private Sub CbxCtl(ctl As Control)
   
   Dim oppName As String [green]'oppName is the alternate ComboboxName[/green]
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   oppName = Switch(ctl.Name = "[purple][b]ComboName1[/b][/purple]", "[purple][b]Comboname2[/b][/purple]", _
                    ctl.Name = "[purple][b]Comboname2[/b][/purple]", "[purple][b]ComboName1[/b][/purple]")
   
   If Trim(Me(oppName) & "") <> "" Then
      ctl = "" 'Null the current combobox selection
      Msg = "Report can be run for one value only!" & DL & _
            "Click 'OK' to continue with current selection." & DL & _
            "Click 'Cancel' to start over . . ."
      Style = vbInformation + vbOKCancel
      Title = "User Response Required! . . ."
      
      If MsgBox(Msg, Style, Title) = vbCancel Then
         Me(oppName) = Null
         Me![purple][b]ComboName1[/b][/purple].SetFocus
      End If
   End If
   
End Sub[/blue]

[/li]
[li]Then in the [blue]AfterUpdate[/blue] event of the two combo's, copy/paste the following:

[blue]   Call CbxCtl(Me![purple][b]ComboName[/b][/purple])[/blue]

[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[Tekhelpnet]

wow! THANKS

I am getting an error 'Invalid use of NULL' here
oppName = Switch(ctl.Name = "ComboName1", "Comboname2", _
ctl.Name = "Comboname2", "ComboName1")

Yes I did substitute with names I have on my form.
I can select value in the first one and when I am clicking on the second one 'Invalid use of NULL' pops up.
OR if I am clicking on the second one without touching the first one and same error pops up anyway.

Thanks so much, I am trying to understand why it is happening...

 

[Tekhelpnet]

Wait! Should I substitute CbxCtl with something in :
Private Sub CbxCtl(ctl As Control)???????

Thanks

 

[Tekhelpnet]

Nevermind last question...it is NO right?

 

[Tekhelpnet]

Sorry what is trhe 'code module for the form'?
I am inserting it as General CbxCtl...

 

[Tekhelpnet]

I got it!
Please, comment on improvement!

Private Sub Recruiter_ID_Name2_AfterUpdate()

If IsNull(Me.BUSINESS_UNIT) Then
Me.Recruiter_ID_Name2.Enabled = True

Else
MsgBox "Only one parameter allowed for this Report!", vbExclamation
Me.Recruiter_ID_Name2 = Null


End If

End Sub



Private Sub Business_Unit_AfterUpdate()

If IsNull(Me.Recruiter_ID_Name2) Then
Me.BUSINESS_UNIT.Enabled = True


Else

MsgBox "Only one parameter allowed for this Report!", vbExclamation
Me.BUSINESS_UNIT = Null
End If

End Sub

 

[TheAceMan1]

Tekhelpnet . . .

Its fine except you [blue]don't need to disable[/blue] anything. If you check the code you'll see [blue]I wipe out the selection[/blue] (Me(oppName) = Null) if the alternate already exists! . . .

See Ya! . . . . . .

Be sure to see thread181-473997