Help with Function

[PeterW]

Hi I am very new to programming in access and would appreciate any help with
the following.
I have inherited and enquiries database and we want to allocate the enquiry
to a showroom based on the county.
Would I use a function to do this and then somehow pass the value from the
county field into it for testing and then use the result in the Showroom
field.
I think I could write this as a sub with a select case statement but seem to
remember it was more efficient to write a function if you want to use
something like this on a number of events so if you need to update anything
you can just do it in one place.
Not well versed in writing functions though and would appreciate any help

 

[XPS350]

Hi I am very new to programming in access and would appreciate any help with
the following.
I have inherited and enquiries database and we want to allocate the enquiry
to a showroom based on the county.
Would I use a function to do this and then somehow pass the value from the
county field into it for testing and then use the result in the Showroom
field.
I think I could write this as a sub with a select case statement but seem to
remember it was more efficient to write a function if you want to use
something like this on a number of events so if you need to update anything
you can just do it in one place.
Not well versed in writing functions though and would appreciate any help

Sure you can make a function using 'select case'. What is your problem
with that?

But why putting data that tells you which showroom is connected to a
county in a function? We are talking DATAbases here, so why not put
this DATA in a (county)table?
In this way an enquiry will be automaticly connected to a showroom and
you can make a query to show it.
If something changes (a county is to be supported by an other
showroom), all you have tio do is change an entry in the (county)
table.


Groeten,

Peter
http://access.xps350.com

 

[PeterW]

Hi Sorry I think I may have not been very clear what I meant - this is for
when filling a form in - I am trying to get the showroom that should deal
with the enquiry name to automatically enter into the field when the user
exits the 'county' control on the form - I was thinking that I could set a
variable called showroom and then set it based on the value of the county and
use the result to enter data on the form does this make more sense?
As I said I am a bit new to programming and not sure how this would work if
data was in a table - do you mean that I could have a table listing the
counties and which showroom was relevant and look into this table for the
value?

 

[XPS350]

Hi Sorry I think I may have not been very clear what I meant - this is for
when filling a form in - I am trying to get the showroom that should deal
with the enquiry name to automatically enter into the field when the user
exits the 'county' control on the form -  I was thinking that I could set a
variable called showroom and then set it based on the value of the countyand
use the result to enter data on the form does this make more sense?
As I said I am a bit new to programming and not sure how this would work if
data was in a table - do you mean that I could have a table listing the
counties and which showroom was relevant and look into this table for the
value?

Yes, you should make a table listing the counties plus showroom.

On your form you create a combobox to select the county. Make sure
that in the RowSource of the combobox you also select the showroom
(SELECT County, Showroom FROM tblCounty), but only show the county
(ColumnWidths 2;0 (for example))

If you make a AfterUpdate event on the combobox you can fill the
showroom:
me.Showroom=me.County.Column(1)


Groeten,

Peter
http://access.xps350.com

 

[PeterW]

Yes, you should make a table listing the counties plus showroom.

On your form you create a combobox to select the county. Make sure
that in the RowSource of the combobox you also select the showroom
(SELECT County, Showroom FROM tblCounty), but only show the county
(ColumnWidths 2;0 (for example))

If you make a AfterUpdate event on the combobox you can fill the
showroom:
me.Showroom=me.County.Column(1)


Groeten,

Peter
http://access.xps350.com
.

 

[PeterW]

Thank You
I understand now and that is much simpler