combobox and if then else (1 Viewer)

[AndyCompanyZ]

Hi All

I am trying to find a solution to a piece of code I am building. What I have is a db that schedules delegates onto events. I am trying to work out some code that will check if the delegate is in the right area for the event. The events are held in 2 geographic areas (North and South) but have a number of venues (some in North and some in south). Delegates are also split into these 2 areas when they are entered itno the system.
I need to check that the event they have been scheduled for is in the right area and if not alert the user to this so that they can be scheduled onto an event in the right area.
This will be done from a form that the user will click a button to schedule and if everything is OK then it updates the delegate's table. I have a number of other checks currently running from this button to check if they have been already scheduled onto the same event or one running on the same day (this last one not quite working but that's on another thread).
The problem I am having is that the selection of an event is based on cascading comboboxes and the last box gives a selection of events corresponding to the course type that the delegate needs training on. This gives all the options of events but doesn't filter to correspond to North or South depending on the delegate's geographic location.
As I've been typing this I realised there might be a better way by filtering the options in the last combobox but not sure how to do this.
Originally I thought that I would write code that looked through the delegates details and flag up if the delegate was on the wrong event.
I wrote some pseudocode:
If DelegateLocation = 1 + VenueID = 2 or 5 + cmboEventID = 5,6,10,11,12,16,17 or 18 Then
msgbox OK
IF DelegateLocation = 2 + VenueID = 1,3 or 4 + cmboEventID = 1,2,3,4,7,8,9,13,14 or 15 Then
msgBox OK
Else
msgbox This is not in the right area for this delegate please reschedule
End if

But this seems a very longwinded way of doing it. After all this typing I realise that if I can finda way of filtering the combobox to only show the events that correspond to the delegates location it would be easier and cleaner. Funny how writing out what you need to do makes it clearer in your own head
So really I need a pointer on how to filter a combobox based on another criteria.
Sorry if this is very rambling and confusing.
Thanks to anyone who can read through my train of thought and point me where I need to go.

 

[DCrake]

If DelegateLocation = 1 + VenueID = 2 or 5 + cmboEventID = 5,6,10,11,12,16,17 or 18 Then
msgbox OK
IF DelegateLocation = 2 + VenueID = 1,3 or 4 + cmboEventID = 1,2,3,4,7,8,9,13,14 or 15 Then
msgBox OK
Else
msgbox This is not in the right area for this delegate please reschedule
End if

Try:

Select Case DelegateLocation
    Case 1
       Select Case VenueID
           Case 2 Or 5
               Select Case CmboEventID
                   Case 5,6,10,11,12,16,17,18
                         MsgBox "OK"
                   Case Else
                         MsgBox "Not Ok"
               End Select
           Case Else
               MsgBox "Not Ok"
        End Select
    Case 2
       Select Case VenueID
           Case 1,3,4
               Select Case CmboEventID
                   Case 1,2,3,4,7,8,9,13,14, 15
                         MsgBox "OK"
                   Case Else
                         MsgBox "Not Ok"
               End Select
           Case Else
               MsgBox "Not Ok"
        End Select
    Case Else
        MsgBox "Not Ok"
 End Select

 

[AndyCompanyZ]

Thanks yet again. I thought of a case select but wasn't sure how to code it. I will try this but I am thinking that maybe a better way would be to filter my combobox so that it only shows the events that correspond to the location of the delegate but I already have the combobox returning results from another combobox and so it might mess that up.

 

[DCrake]

The nested Select Case statement is cascading itself, first testing the delegate location, then the event Id then finally the combo box selection. It jumps out of the entire block once a condition has been met.

 

[AndyCompanyZ]

I put the code in and it says "not OK" with whatever I put in. I think I am wrong in the code because I think I need it to lookup the delegate details table and check the officelocation field to find where the delegate is located. But I can see that the case would work but not sure how it will check the delegate. I changed the code slightly :
Select Case DelegateLocation
Case 1
Select Case [Event.VenueID]
Case 2 Or 5
Select Case CmboEventID
Case 5, 6, 10, 11, 12, 16, 17, 18
MsgBox "OK"
Case Else
MsgBox "Not Ok"
End Select
Case Else
MsgBox "Not Ok"
End Select
Case 2
Select Case [Event.VenueID]
Case 1, 3, 4
Select Case CmboEventID
Case 1, 2, 3, 4, 7, 8, 9, 13, 14, 15
MsgBox "OK"
Case Else
MsgBox "Not Ok"
End Select
Case Else
MsgBox "Not Ok"
End Select
Case Else
MsgBox "Not Ok"
End Select

Because I think it needs the [Event.VenueID] to show where to look but I could be wrong as usual.

 

[DCrake]

Where are you getting [Event.VenueID] from?

 

[AndyCompanyZ]

That's a table "Event" which holds the VenueID

 

[DCrake]

Is the field bound to a control on the form though? You cannot refer directly to the table field from vba.

 

[AndyCompanyZ]

Ah that's where I'm going wrong.

 

[AndyCompanyZ]

Ok I have put on a the OfficeLocation onto the form but that only refers to the delegate. I guess i need the VenueID on the form too but that is on the Event table which will only be selected when the combobox is populated.

 

[DCrake]

Can you not do a DLookup or something to get the venueID. Not sure how your form works.

 

[AndyCompanyZ]

Yes I'm just trying that but now when I have looked at the form in form view I have no delegate's records showing at all. It happened when after I put the officelocation field on the page though it seemed to be working OK when I switched back to design view and then back to form view all the rcords disappeared