Long Case Select statement - is there a better way?

[BarryMK]

This list of selections (working fine) is likely to grow to around 10 times the size it is shown below. Is this the most efficient way to add these variables or can anyone think of a better method please?


Private Sub ServiceName_GotFocus()

Select Case ServiceName.Value
'ES
Case "Dog wardens ES"
Me.ServicesID = 1
Me.DeptID = 1
Me.txtSectionID = 1
Me.DeptName = "Environmental Services"

Case "Noise nuisance ES"
Me.ServicesID = 2
Me.DeptID = 1
Me.txtSectionID = 1
Me.DeptName = "Environmental Services"

Case "Pest Control ES"
Me.ServicesID = 8
Me.DeptID = 1
Me.txtSectionID = 1
Me.DeptName = "Environmental Services"

Case "Food safety ES"
Me.ServicesID = 9
Me.DeptID = 1
Me.txtSectionID = 2
Me.DeptName = "Environmental Services"

Case "Health & Safety ES"
Me.ServicesID = 10
Me.DeptID = 1
Me.txtSectionID = 2
Me.DeptName = "Environmental Services"

Case "Licensing ES"
Me.ServicesID = 11
Me.DeptID = 1
Me.txtSectionID = 3
Me.DeptName = "Environmental Services"

Case "Dog wardens ES"
Me.ServicesID = 1
Me.DeptID = 1
Me.txtSectionID = 1
Me.DeptName = "Environmental Services"

'BC
Case "Administration BC"
Me.ServicesID = 12
Me.DeptID = 2
Me.txtSectionID = 5
Me.DeptName = "Building Consultancy"

Case "Site inspections BC"
Me.ServicesID = 13
Me.DeptID = 2
Me.txtSectionID = 5
Me.DeptName = "Building Consultancy"

Case "Conduct BC"
Me.ServicesID = 14
Me.DeptID = 2
Me.txtSectionID = 5
Me.DeptName = "Building Consultancy"

Case "Electronic applications BC"
Me.ServicesID = 4
Me.DeptID = 2
Me.txtSectionID = 5
Me.DeptName = "Building Consultancy"

'CS
Case "Dog fouling - pavement cleansing CS"
Me.ServicesID = 5
Me.DeptID = 3
Me.DeptName = "Client Services"

Case "Refuse collections - business CS"
Me.ServicesID = 6
Me.DeptID = 3
Me.DeptName = "Client Services"

Case "Refuse collections - domestic CS"
Me.ServicesID = 7
Me.DeptID = 3
Me.DeptName = "Client Services"


End Select
End Sub

 

[nateobot]

You could create a sub that sets those variables and then call that.

Public Sub SetFieldValues (sServices as Integer, sDeptID as Integer, sSectionID as Integer, sDeptName as String)
    Me.ServicesID = sServices
    Me.DeptID = sDeptID
    Me.txtSectionID = sSectionID
    Me.DeptName = sDeptName
End Sub

Then call it within your case statement like so:

Case "Noise nuisance ES"
    Call SetFieldValues(2, 1, 1,"Environmental Services")

 

[BarryMK]

That looks neater! I'll give it a go thanks.

 

[allan57]

Alternatively you could store the values in a table & retrieve the results by a recordset, and then assign the values to the text boxes.

ie



Set rst = CurrentDb.OpenRecordset("SELECT TableNameHere.ServiceName, TableNameHere.ServicesID, TableNameHere.DeptID, TableNameHere.txtSectionID, TableNameHere.DeptName " & _
"FROM TableNameHere " & _
"WHERE TableNameHere.ServiceName='" & ServiceName.Value & "';")

Me.ServicesID = rst!ServicesID
Me.DeptID = rst!DeptID
Me.txtSectionID = rst!txtSectionID
Me.DeptName = rst!DeptName