Simplify function (Access 2000)

[svetljop]

Can i simplify my function

On my form FCustomers i am placing valies in the combo box afid depending on the choice in the list obx of the form FOrderInformation.


I have the following working function
Public Function CustomersPerOffice()

Select Case Forms![FOrderInformation]![Office] , Office is a list box enumerating the differtent cities, for example
' Forms![FOrderInformation]![Office]= 1 , is the office in Paris
' Forms![FOrderInformation]![Office]= 2 ' is the officed in Milano

Case 1
Forms![FCustomers].![afid].value = 1
Case 2
Forms![FCustomers].![afid].value = 2
Case 3
Forms![FCustomers].![afid].value = 3

etc
etc
I have 15 cases

End Function

The above function works perfeclty. However, I want to simplify my function in the following way


Public Function CustomersPerOffice()
Dim f As Form
Set f = Forms![FCustomers]
Dim city As Long
City = Forms![FOrderInformation]![office] - 1
f![afid] = " & city & "
End Function

However i get the answer that i cannot assign the value.
May i have some help as how to do it ?

[HansV]

Why do you subtract 1 from the value of Forms![FOrderInformation]![Office]? From the first bit of code, it would seem that afid should be equal to Office.

And why do you try to set afid to a constant string? The expression " & city & " is a literal string.

My guess would be that you can simply use

Public Function CustomersPerOffice()
Forms![FCustomers]![afid] = Forms![FOrderInformation]![office]
End Function