Array's and select case statements

[Ezekiël]

Hi everyone,

Does somebody has a good example how to use an array with select case
statement.

For example, i have a lookup table with values like A, B, C, etc.
If someone chooses a value in a form's combobox then the value should be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be >=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values in the select statement, but
rather use an arrayvalue to put next the case line. The array should use it
values from the lookup table.

How can i achieve this?

 

[Michel Walsh]

Hi,


We could do that through a table.


Validations ' table name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") >= rst("AndThat" )
Then
... failure... Qty is not in the range [BetweenThis] and
[AndThat]
assigned to its values of "CaseOf" in table
Validations.

End If



It may even be easier to visualize the correlation between the data just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP

 

[Ezekiël]

Is it better to do it in a table rather then in an array with select
statement?.

Hi,


We could do that through a table.


Validations ' table name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") >= rst("AndThat" )
Then
... failure... Qty is not in the range [BetweenThis] and
[AndThat]
assigned to its values of "CaseOf" in table
Validations.

End If



It may even be easier to visualize the correlation between the data just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP

Hi everyone,

Does somebody has a good example how to use an array with select case
statement.

For example, i have a lookup table with values like A, B, C, etc.
If someone chooses a value in a form's combobox then the value should be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be >=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values in the select statement, but
rather use an arrayvalue to put next the case line. The array should use it
values from the lookup table.

How can i achieve this?

 

[Michel Walsh]

Hi,


Modifying data in a table is standard process, while modifying any
constant in a SELECT CASE requires access to the code. Furthermore, data is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP

Is it better to do it in a table rather then in an array with select
statement?.

Hi,


We could do that through a table.


Validations ' table name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") >= rst("AndThat" )
Then
... failure... Qty is not in the range [BetweenThis] and
[AndThat]
assigned to its values of "CaseOf" in table
Validations.

End If



It may even be easier to visualize the correlation between the data just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP

Hi everyone,

Does somebody has a good example how to use an array with select case
statement.

For example, i have a lookup table with values like A, B, C, etc.
If someone chooses a value in a form's combobox then the value should be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be >=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values in the select

statement,
but

rather use an arrayvalue to put next the case line. The array should

use
it

values from the lookup table.

How can i achieve this?

 

[Ezekiël]

In your example is also appplyable when i want certain controls on a form
disabled.

E.g. in the beforementioned table someone may choose a value from a combobox
and when that value is choosen, the validation of a field may change and
other controls are enabled/disabled.
How can i solve a situation like this.

Hi,


Modifying data in a table is standard process, while modifying any
constant in a SELECT CASE requires access to the code. Furthermore, data is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP

Is it better to do it in a table rather then in an array with select
statement?.

Hi,


We could do that through a table.


Validations ' table name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") >= rst("AndThat" )
Then
... failure... Qty is not in the range [BetweenThis] and
[AndThat]
assigned to its values of "CaseOf" in table
Validations.

End If



It may even be easier to visualize the correlation between the data just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array with select case
statement.

For example, i have a lookup table with values like A, B, C, etc.
If someone chooses a value in a form's combobox then the value

should

be

 

[Michel Walsh]

Hi,


There is a point where building a universal good to every possible cases
machine is just too expensive. In most of the cases, some of the logic would
be hard-coded. As example, if your form can handle trucks and planes, then
selecting a truck or a plane may allow to further enter a GrossWeight
value, but if a truck is being "selected" , the WingSpan value makes no
sense. So, assuming someone start entering data for a truck, then the
WingSpan control may become disabled, and the logic may be hard coded with a
Select case statement in the AfterUpdate even of the combo box allowing to
choose a plane or a truck:

Select case Me.ComboBoxName.Value
Case "Plane"
Me.WingSpan.Enabled = True
Me. .. ' other controls, if applicable

Case "Truck"
Me.WingSpan.Enabled=False
...
End Select


You can also repeat the same code (or Call the same subroutine) in the
onCurrent event too, since you probably want the same behavior when the end
user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP

In your example is also appplyable when i want certain controls on a form
disabled.

E.g. in the beforementioned table someone may choose a value from a combobox
and when that value is choosen, the validation of a field may change and
other controls are enabled/disabled.
How can i solve a situation like this.

Hi,


Modifying data in a table is standard process, while modifying any
constant in a SELECT CASE requires access to the code. Furthermore, data is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP

Is it better to do it in a table rather then in an array with select
statement?.
Hi,


We could do that through a table.


Validations ' table name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") >= rst("AndThat" )
Then
... failure... Qty is not in the range [BetweenThis] and
[AndThat]
assigned to its values of "CaseOf" in
table
Validations.

End If



It may even be easier to visualize the correlation between the data just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array with select case
statement.

For example, i have a lookup table with values like A, B, C, etc.
If someone chooses a value in a form's combobox then the value

should

be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be >=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values in the select statement,
but
rather use an arrayvalue to put next the case line. The array

should
use

it
values from the lookup table.

How can i achieve this?

 

[Ezekiël]

Michel,

The example in your first message to test the vba code, is it a public
function?

Hi,


There is a point where building a universal good to every possible cases
machine is just too expensive. In most of the cases, some of the logic would
be hard-coded. As example, if your form can handle trucks and planes, then
selecting a truck or a plane may allow to further enter a GrossWeight
value, but if a truck is being "selected" , the WingSpan value makes no
sense. So, assuming someone start entering data for a truck, then the
WingSpan control may become disabled, and the logic may be hard coded with a
Select case statement in the AfterUpdate even of the combo box allowing to
choose a plane or a truck:

Select case Me.ComboBoxName.Value
Case "Plane"
Me.WingSpan.Enabled = True
Me. .. ' other controls, if applicable

Case "Truck"
Me.WingSpan.Enabled=False
...
End Select


You can also repeat the same code (or Call the same subroutine) in the
onCurrent event too, since you probably want the same behavior when the end
user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP

In your example is also appplyable when i want certain controls on a form
disabled.

E.g. in the beforementioned table someone may choose a value from a combobox
and when that value is choosen, the validation of a field may change and
other controls are enabled/disabled.
How can i solve a situation like this.

Hi,


Modifying data in a table is standard process, while modifying any
constant in a SELECT CASE requires access to the code. Furthermore,

data
is

better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP


Is it better to do it in a table rather then in an array with select
statement?.
Hi,


We could do that through a table.


Validations ' table name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") >=
rst("AndThat" )
Then
... failure... Qty is not in the range [BetweenThis] and
[AndThat]
assigned to its values of "CaseOf" in
table
Validations.

End If



It may even be easier to visualize the correlation between the

data
just

seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array with select case
statement.

For example, i have a lookup table with values like A, B, C, etc.
If someone chooses a value in a form's combobox then the value should
be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be >=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values in the select
statement,
but
rather use an arrayvalue to put next the case line. The array should
use
it
values from the lookup table.

How can i achieve this?

 

[Michel Walsh]

Hi,


The SELECT statement is the SQL text of a query that allows you to open
a recordset:



Dim rst As DAO.Recordset
Sert rst=CurrentDb.OpenRecordset("SELECT ... ")
If rst("fieldName") ...



where fieldName is any selected item in the SELECT clause.



Hoping it may help,
Vanderghast, Access MVP

Michel,

The example in your first message to test the vba code, is it a public
function?

Hi,


There is a point where building a universal good to every possible cases
machine is just too expensive. In most of the cases, some of the logic would
be hard-coded. As example, if your form can handle trucks and planes, then
selecting a truck or a plane may allow to further enter a GrossWeight
value, but if a truck is being "selected" , the WingSpan value makes no
sense. So, assuming someone start entering data for a truck, then the
WingSpan control may become disabled, and the logic may be hard coded

with

a
Select case statement in the AfterUpdate even of the combo box allowing to
choose a plane or a truck:

Select case Me.ComboBoxName.Value
Case "Plane"
Me.WingSpan.Enabled = True
Me. .. ' other controls, if applicable

Case "Truck"
Me.WingSpan.Enabled=False
...
End Select


You can also repeat the same code (or Call the same subroutine) in the
onCurrent event too, since you probably want the same behavior when the end
user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP

In your example is also appplyable when i want certain controls on a form
disabled.

E.g. in the beforementioned table someone may choose a value from a combobox
and when that value is choosen, the validation of a field may change and
other controls are enabled/disabled.
How can i solve a situation like this.

Hi,


Modifying data in a table is standard process, while modifying any
constant in a SELECT CASE requires access to the code. Furthermore, data
is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP


Is it better to do it in a table rather then in an array with select
statement?.
Hi,


We could do that through a table.


Validations ' table name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") >=
rst("AndThat" )
Then
... failure... Qty is not in the range

[BetweenThis]
and

[AndThat]
assigned to its values of

"CaseOf"
in

table
Validations.

End If



It may even be easier to visualize the correlation between the data
just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array with select
case
statement.

For example, i have a lookup table with values like A, B, C, etc.
If someone chooses a value in a form's combobox then the value
should
be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be >=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values in the select
statement,
but
rather use an arrayvalue to put next the case line. The array should
use
it
values from the lookup table.

How can i achieve this?

 

[Ezekiël]

Michel,

I think i got it almost right, but the afterupdate code does not quite work.
I have the following:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [1].*, [2].* FROM 1 INNER JOIN 2
ON [1].[CaseOf]=[2].[CaseOf]; ")

If rst("Length") >= rst("Between") And rst("Length") <= rst("AndThat")
Then
MsgBox "... failure... "
End If

End Sub
The value in length was 3300 and the range in the between/AndThat are 3200 -
6000. The value 3300 gave the failure message. How is it possiible that 3300
is not good, but it should because it is range of 3200-6000.

Hi,


The SELECT statement is the SQL text of a query that allows you to open
a recordset:



Dim rst As DAO.Recordset
Sert rst=CurrentDb.OpenRecordset("SELECT ... ")
If rst("fieldName") ...



where fieldName is any selected item in the SELECT clause.



Hoping it may help,
Vanderghast, Access MVP

Michel,

The example in your first message to test the vba code, is it a public
function?

with

allowing

to

choose a plane or a truck:

Select case Me.ComboBoxName.Value
Case "Plane"
Me.WingSpan.Enabled = True
Me. .. ' other controls, if applicable

Case "Truck"
Me.WingSpan.Enabled=False
...
End Select


You can also repeat the same code (or Call the same subroutine) in the
onCurrent event too, since you probably want the same behavior when

the
end

user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP


In your example is also appplyable when i want certain controls on a form
disabled.

E.g. in the beforementioned table someone may choose a value from a
combobox
and when that value is choosen, the validation of a field may change and
other controls are enabled/disabled.
How can i solve a situation like this.

Hi,


Modifying data in a table is standard process, while modifying any
constant in a SELECT CASE requires access to the code.

Furthermore,
data

is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP


Is it better to do it in a table rather then in an array with select
statement?.
Hi,


We could do that through a table.


Validations ' table name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") >=
rst("AndThat" )
Then
... failure... Qty is not in the range [BetweenThis]
and
[AndThat]
assigned to its values of "CaseOf"
in
table
Validations.

End If



It may even be easier to visualize the correlation between the data
just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array with select
case
statement.

For example, i have a lookup table with values like A, B, C, etc.
If someone chooses a value in a form's combobox then the value
should
be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be >=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values in the select
statement,
but
rather use an arrayvalue to put next the case line. The array
should
use
it
values from the lookup table.

How can i achieve this?

 

[Michel Walsh]

Hi,


Right! The failure, in that case, would be in the "Else" part:

If rst("Length") >= rst("Between") And rst("Length") <= rst("AndThat")
Then
Else
MsgBox "... failure... "
End If

since it is not a failure if the value is between the limits!

Hoping it may help,
Vanderghast, Access MVP

Michel,

I think i got it almost right, but the afterupdate code does not quite work.
I have the following:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [1].*, [2].* FROM 1 INNER JOIN 2
ON [1].[CaseOf]=[2].[CaseOf]; ")

If rst("Length") >= rst("Between") And rst("Length") <= rst("AndThat")
Then
MsgBox "... failure... "
End If

End Sub
The value in length was 3300 and the range in the between/AndThat are 3200 -
6000. The value 3300 gave the failure message. How is it possiible that 3300
is not good, but it should because it is range of 3200-6000.

Hi,


The SELECT statement is the SQL text of a query that allows you to open
a recordset:



Dim rst As DAO.Recordset
Sert rst=CurrentDb.OpenRecordset("SELECT ... ")
If rst("fieldName") ...



where fieldName is any selected item in the SELECT clause.



Hoping it may help,
Vanderghast, Access MVP

Michel,

The example in your first message to test the vba code, is it a public
function?

Hi,


There is a point where building a universal good to every possible
cases
machine is just too expensive. In most of the cases, some of the logic
would
be hard-coded. As example, if your form can handle trucks and

planes,
then

selecting a truck or a plane may allow to further enter a GrossWeight
value, but if a truck is being "selected" , the WingSpan value makes no
sense. So, assuming someone start entering data for a truck, then the
WingSpan control may become disabled, and the logic may be hard

coded
with

a
Select case statement in the AfterUpdate even of the combo box

allowing

to
choose a plane or a truck:

Select case Me.ComboBoxName.Value
Case "Plane"
Me.WingSpan.Enabled = True
Me. .. ' other controls, if applicable

Case "Truck"
Me.WingSpan.Enabled=False
...
End Select


You can also repeat the same code (or Call the same subroutine) in the
onCurrent event too, since you probably want the same behavior when the
end
user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP


In your example is also appplyable when i want certain controls on a
form
disabled.

E.g. in the beforementioned table someone may choose a value from a
combobox
and when that value is choosen, the validation of a field may

change
and

other controls are enabled/disabled.
How can i solve a situation like this.

Hi,


Modifying data in a table is standard process, while

modifying
any

constant in a SELECT CASE requires access to the code. Furthermore,
data
is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP


Is it better to do it in a table rather then in an array with select
statement?.
message
Hi,


We could do that through a table.


Validations ' table name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") >=
rst("AndThat" )
Then
... failure... Qty is not in the range [BetweenThis]
and
[AndThat]
assigned to its values of "CaseOf"
in
table
Validations.

End If



It may even be easier to visualize the correlation between the
data
just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array with select
case
statement.

For example, i have a lookup table with values like A, B, C,
etc.
If someone chooses a value in a form's combobox then the value
should
be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be >=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values in the select
statement,
but
rather use an arrayvalue to put next the case line. The array
should
use
it
values from the lookup table.

How can i achieve this?

 

[Ezekiël]

Hi Michel,

The code works now, but i'm getting a error message when i want to insert a
new record. The error says no current record.

Hi,


Right! The failure, in that case, would be in the "Else" part:

If rst("Length") >= rst("Between") And rst("Length") <= rst("AndThat")
Then
Else
MsgBox "... failure... "
End If

since it is not a failure if the value is between the limits!

Hoping it may help,
Vanderghast, Access MVP

Michel,

I think i got it almost right, but the afterupdate code does not quite work.
I have the following:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [1].*, [2].* FROM 1 INNER JOIN 2
ON [1].[CaseOf]=[2].[CaseOf]; ")

If rst("Length") >= rst("Between") And rst("Length") <= rst("AndThat")
Then
MsgBox "... failure... "
End If

End Sub
The value in length was 3300 and the range in the between/AndThat are 3200 -
6000. The value 3300 gave the failure message. How is it possiible that 3300
is not good, but it should because it is range of 3200-6000.

Hi,


The SELECT statement is the SQL text of a query that allows you to open
a recordset:



Dim rst As DAO.Recordset
Sert rst=CurrentDb.OpenRecordset("SELECT ... ")
If rst("fieldName") ...



where fieldName is any selected item in the SELECT clause.



Hoping it may help,
Vanderghast, Access MVP


Michel,

The example in your first message to test the vba code, is it a public
function?

Hi,


There is a point where building a universal good to every possible
cases
machine is just too expensive. In most of the cases, some of the logic
would
be hard-coded. As example, if your form can handle trucks and planes,
then
selecting a truck or a plane may allow to further enter a GrossWeight
value, but if a truck is being "selected" , the WingSpan value

makes
no

sense. So, assuming someone start entering data for a truck, then the
WingSpan control may become disabled, and the logic may be hard coded
with
a
Select case statement in the AfterUpdate even of the combo box allowing
to
choose a plane or a truck:

Select case Me.ComboBoxName.Value
Case "Plane"
Me.WingSpan.Enabled = True
Me. .. ' other controls, if applicable

Case "Truck"
Me.WingSpan.Enabled=False
...
End Select


You can also repeat the same code (or Call the same subroutine) in the
onCurrent event too, since you probably want the same behavior

when
the

end
user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP


In your example is also appplyable when i want certain controls

on

a

from

a

combobox
and when that value is choosen, the validation of a field may change
and
other controls are enabled/disabled.
How can i solve a situation like this.

Hi,


Modifying data in a table is standard process, while modifying
any
constant in a SELECT CASE requires access to the code. Furthermore,
data
is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP


Is it better to do it in a table rather then in an array with
select
statement?.
message
Hi,


We could do that through a table.


Validations '

table
name

CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") >=
rst("AndThat" )
Then
... failure... Qty is not in the range
[BetweenThis]
and
[AndThat]
assigned to its values of
"CaseOf"
in
table
Validations.

End If



It may even be easier to visualize the correlation between the
data
just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array with
select
case
statement.

For example, i have a lookup table with values like A,

B,

C,

 

[Michel Walsh]

Hi,


Can you append a new record, using the same query than the one you use
to open a recordset, right from the query designer? If the query is not
updateable, you may have to change the way you would add records. If the
designer allows you to append records, but not the form, are you using the
same query as "recordsource" for the form, or an older version of that
query/SQL text?



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

The code works now, but i'm getting a error message when i want to insert a
new record. The error says no current record.

Hi,


Right! The failure, in that case, would be in the "Else" part:

If rst("Length") >= rst("Between") And rst("Length") <= rst("AndThat")
Then
Else
MsgBox "... failure... "
End If

since it is not a failure if the value is between the limits!

Hoping it may help,
Vanderghast, Access MVP

Michel,

I think i got it almost right, but the afterupdate code does not quite work.
I have the following:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [1].*, [2].* FROM 1 INNER

JOIN

2

ON [1].[CaseOf]=[2].[CaseOf]; ")

If rst("Length") >= rst("Between") And rst("Length") <= rst("AndThat")
Then
MsgBox "... failure... "
End If

End Sub
The value in length was 3300 and the range in the between/AndThat are 3200 -
6000. The value 3300 gave the failure message. How is it possiible

that
3300

is not good, but it should because it is range of 3200-6000.

Hi,


The SELECT statement is the SQL text of a query that allows you to
open
a recordset:



Dim rst As DAO.Recordset
Sert rst=CurrentDb.OpenRecordset("SELECT ... ")
If rst("fieldName") ...



where fieldName is any selected item in the SELECT clause.



Hoping it may help,
Vanderghast, Access MVP


Michel,

The example in your first message to test the vba code, is it a public
function?

Hi,


There is a point where building a universal good to every possible
cases
machine is just too expensive. In most of the cases, some of the logic
would
be hard-coded. As example, if your form can handle trucks and planes,
then
selecting a truck or a plane may allow to further enter a GrossWeight
value, but if a truck is being "selected" , the WingSpan value makes
no
sense. So, assuming someone start entering data for a truck,

then
the

WingSpan control may become disabled, and the logic may be hard coded
with
a
Select case statement in the AfterUpdate even of the combo box
allowing
to
choose a plane or a truck:

Select case Me.ComboBoxName.Value
Case "Plane"
Me.WingSpan.Enabled = True
Me. .. ' other controls, if applicable

Case "Truck"
Me.WingSpan.Enabled=False
...
End Select


You can also repeat the same code (or Call the same subroutine)

in
the

onCurrent event too, since you probably want the same behavior when
the
end
user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP


In your example is also appplyable when i want certain

controls

on

a
form
disabled.

E.g. in the beforementioned table someone may choose a value

from

a
combobox
and when that value is choosen, the validation of a field may change
and
other controls are enabled/disabled.
How can i solve a situation like this.

message
Hi,


Modifying data in a table is standard process, while modifying
any
constant in a SELECT CASE requires access to the code.
Furthermore,
data
is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP


Is it better to do it in a table rather then in an array with
select
statement?.
message
Hi,


We could do that through a table.


Validations ' table
name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") =
rst("AndThat" )
Then
... failure... Qty is not in the range
[BetweenThis]
and
[AndThat]
assigned to its values of
"CaseOf"
in
table
Validations.

End If



It may even be easier to visualize the correlation

between
the

data
just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array with
select
case
statement.

For example, i have a lookup table with values like A,

B,

C,
etc.
If someone chooses a value in a form's combobox then the
value
should
be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be >=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values in the
select
statement,
but
rather use an arrayvalue to put next the case line. The
array
should
use
it
values from the lookup table.

How can i achieve this?

 

[Ezekiël]

Hi,

I'm not using the same query in the recordsource, only in the rstcode.

Hi,


Can you append a new record, using the same query than the one you use
to open a recordset, right from the query designer? If the query is not
updateable, you may have to change the way you would add records. If the
designer allows you to append records, but not the form, are you using the
same query as "recordsource" for the form, or an older version of that
query/SQL text?



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

The code works now, but i'm getting a error message when i want to

insert

a
new record. The error says no current record.

Hi,


Right! The failure, in that case, would be in the "Else" part:

If rst("Length") >= rst("Between") And rst("Length") <= rst("AndThat")
Then
Else
MsgBox "... failure... "
End If

since it is not a failure if the value is between the limits!

Hoping it may help,
Vanderghast, Access MVP


Michel,

I think i got it almost right, but the afterupdate code does not quite
work.
I have the following:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [1].*, [2].* FROM 1 INNER

JOIN

2
ON [1].[CaseOf]=[2].[CaseOf]; ")

If rst("Length") >= rst("Between") And rst("Length") <= rst("AndThat")
Then
MsgBox "... failure... "
End If

End Sub
The value in length was 3300 and the range in the between/AndThat are
3200 -
6000. The value 3300 gave the failure message. How is it possiible that
3300
is not good, but it should because it is range of 3200-6000.

Hi,


The SELECT statement is the SQL text of a query that allows

you

to

subroutine)

in

the
onCurrent event too, since you probably want the same behavior when
the
end
user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP


In your example is also appplyable when i want certain

controls

on
a
form
disabled.

E.g. in the beforementioned table someone may choose a value from
a
combobox
and when that value is choosen, the validation of a field may
change
and
other controls are enabled/disabled.
How can i solve a situation like this.

message
Hi,


Modifying data in a table is standard process, while
modifying
any
constant in a SELECT CASE requires access to the code.
Furthermore,
data
is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP


Is it better to do it in a table rather then in an array with
select
statement?.
"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>

wrote
in

message
Hi,


We could do that through a table.


Validations ' table
name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty") =
rst("AndThat" )
Then
... failure... Qty is not in the range
[BetweenThis]
and
[AndThat]
assigned to its values of
"CaseOf"
in
table
Validations.

End If



It may even be easier to visualize the correlation between
the
data
just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array with
select
case
statement.

For example, i have a lookup table with values like

A,
B,

C,
etc.
If someone chooses a value in a form's combobox then the
value
should
be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be =100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values in the
select
statement,
but
rather use an arrayvalue to put next the case line. The
array
should
use
it
values from the lookup table.

How can i achieve this?

 

[Michel Walsh]

Hi,


How do you start a new record? Do you have your


rs.AddNew


then, your
rs.Fields("fieldName") = ...some value here...



for each field to be specifeid with non-default values, then

rs.Upate


If so, at what step is there a problem?



Vanderghast, Access MVP

Hi,

I'm not using the same query in the recordsource, only in the rstcode.

Hi,


Can you append a new record, using the same query than the one you use
to open a recordset, right from the query designer? If the query is not
updateable, you may have to change the way you would add records. If the
designer allows you to append records, but not the form, are you using the
same query as "recordsource" for the form, or an older version of that
query/SQL text?



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

The code works now, but i'm getting a error message when i want to

insert

a
new record. The error says no current record.

Hi,


Right! The failure, in that case, would be in the "Else" part:

If rst("Length") >= rst("Between") And rst("Length") <= rst("AndThat")
Then
Else
MsgBox "... failure... "
End If

since it is not a failure if the value is between the limits!

Hoping it may help,
Vanderghast, Access MVP


Michel,

I think i got it almost right, but the afterupdate code does not quite
work.
I have the following:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [1].*, [2].* FROM 1

INNER
JOIN

2
ON [1].[CaseOf]=[2].[CaseOf]; ")

If rst("Length") >= rst("Between") And rst("Length") <=
rst("AndThat")
Then
MsgBox "... failure... "
End If

End Sub
The value in length was 3300 and the range in the between/AndThat are
3200 -
6000. The value 3300 gave the failure message. How is it possiible that
3300
is not good, but it should because it is range of 3200-6000.

Hi,


The SELECT statement is the SQL text of a query that allows

you

to
open
a recordset:



Dim rst As DAO.Recordset
Sert rst=CurrentDb.OpenRecordset("SELECT ... ")
If rst("fieldName") ...



where fieldName is any selected item in the SELECT clause.



Hoping it may help,
Vanderghast, Access MVP


Michel,

The example in your first message to test the vba code, is it a
public
function?

message
Hi,


There is a point where building a universal good to every
possible
cases
machine is just too expensive. In most of the cases, some of the
logic
would
be hard-coded. As example, if your form can handle trucks and
planes,
then
selecting a truck or a plane may allow to further enter a
GrossWeight
value, but if a truck is being "selected" , the WingSpan value
makes
no
sense. So, assuming someone start entering data for a truck, then
the
WingSpan control may become disabled, and the logic may be hard
coded
with
a
Select case statement in the AfterUpdate even of the combo box
allowing
to
choose a plane or a truck:

Select case Me.ComboBoxName.Value
Case "Plane"
Me.WingSpan.Enabled = True
Me. .. ' other controls, if applicable

Case "Truck"
Me.WingSpan.Enabled=False
...
End Select


You can also repeat the same code (or Call the same

subroutine)

in
the
onCurrent event too, since you probably want the same behavior
when
the
end
user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP


In your example is also appplyable when i want certain controls
on
a
form
disabled.

E.g. in the beforementioned table someone may choose a value
from
a
combobox
and when that value is choosen, the validation of a field may
change
and
other controls are enabled/disabled.
How can i solve a situation like this.

message
Hi,


Modifying data in a table is standard process, while
modifying
any
constant in a SELECT CASE requires access to the code.
Furthermore,
data
is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP


Is it better to do it in a table rather then in an array
with
select
statement?.
in
message
Hi,


We could do that through a table.


Validations '
table
name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty")
=
rst("AndThat" )
Then
... failure... Qty is not in the range
[BetweenThis]
and
[AndThat]
assigned to its

values
of

"CaseOf"
in
table
Validations.

End If



It may even be easier to visualize the correlation between
the
data
just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array
with
select
case
statement.

For example, i have a lookup table with values

like

A, then
the line.
The

 

[Ezekiël]

Hi,

I add a new record just by filling in the form.
But when i go to the next record, i get an error message.

Hi,


How do you start a new record? Do you have your


rs.AddNew


then, your
rs.Fields("fieldName") = ...some value here...



for each field to be specifeid with non-default values, then

rs.Upate


If so, at what step is there a problem?



Vanderghast, Access MVP

Hi,

I'm not using the same query in the recordsource, only in the rstcode.

Hi,


Can you append a new record, using the same query than the one you use
to open a recordset, right from the query designer? If the query is not
updateable, you may have to change the way you would add records. If the
designer allows you to append records, but not the form, are you using the
same query as "recordsource" for the form, or an older version of that
query/SQL text?



Hoping it may help,
Vanderghast, Access MVP



Hi Michel,

The code works now, but i'm getting a error message when i want to insert
a
new record. The error says no current record.

Hi,


Right! The failure, in that case, would be in the "Else" part:

If rst("Length") >= rst("Between") And rst("Length") <=
rst("AndThat")
Then
Else
MsgBox "... failure... "
End If

since it is not a failure if the value is between the limits!

Hoping it may help,
Vanderghast, Access MVP


Michel,

I think i got it almost right, but the afterupdate code does not quite
work.
I have the following:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [1].*, [2].* FROM 1 INNER
JOIN
2
ON [1].[CaseOf]=[2].[CaseOf]; ")

If rst("Length") >= rst("Between") And rst("Length") <=
rst("AndThat")
Then
MsgBox "... failure... "
End If

End Sub
The value in length was 3300 and the range in the

between/AndThat
are

3200 -
6000. The value 3300 gave the failure message. How is it possiible
that
3300
is not good, but it should because it is range of 3200-6000.

Hi,


The SELECT statement is the SQL text of a query that

allows
you

to
open
a recordset:



Dim rst As DAO.Recordset
Sert rst=CurrentDb.OpenRecordset("SELECT ... ")
If rst("fieldName") ...



where fieldName is any selected item in the SELECT clause.



Hoping it may help,
Vanderghast, Access MVP


Michel,

The example in your first message to test the vba code, is

it

a

public
function?

message
Hi,


There is a point where building a universal good to every
possible
cases
machine is just too expensive. In most of the cases, some

of
the

logic
would
be hard-coded. As example, if your form can handle trucks and
planes,
then
selecting a truck or a plane may allow to further enter a
GrossWeight
value, but if a truck is being "selected" , the WingSpan value
makes
no
sense. So, assuming someone start entering data for a truck,
then
the
WingSpan control may become disabled, and the logic may be hard
coded
with
a
Select case statement in the AfterUpdate even of the combo box
allowing
to
choose a plane or a truck:

Select case Me.ComboBoxName.Value
Case "Plane"
Me.WingSpan.Enabled = True
Me. .. ' other controls, if applicable

Case "Truck"
Me.WingSpan.Enabled=False
...
End Select


You can also repeat the same code (or Call the same subroutine)
in
the
onCurrent event too, since you probably want the same behavior
when
the
end
user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP


In your example is also appplyable when i want certain
controls
on
a
form
disabled.

E.g. in the beforementioned table someone may choose a value
from
a
combobox
and when that value is choosen, the validation of a

field
may

change
and
other controls are enabled/disabled.
How can i solve a situation like this.

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>

wrote
in

message
Hi,


Modifying data in a table is standard process, while
modifying
any
constant in a SELECT CASE requires access to the code.
Furthermore,
data
is
better stored in a data storage (table, file) than in code.


Hoping it may help,
Vanderghast, Access MVP


Is it better to do it in a table rather then in an array
with
select
statement?.
in
message
Hi,


We could do that through a table.


Validations '
table
name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty")
=
rst("AndThat" )
Then
... failure... Qty is not in the range
[BetweenThis]
and
[AndThat]
assigned to its values
of
"CaseOf"
in
table
Validations.

End If



It may even be easier to visualize the correlation
between
the
data
just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array
with
select
case
statement.

For example, i have a lookup table with values

like

A,
B,
C,
etc.
If someone chooses a value in a form's combobox then
the
value
should
be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be
=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the values

in
the

select
statement,
but
rather use an arrayvalue to put next the case line.
The
array
should
use
it
values from the lookup table.

How can i achieve this?

 

[Michel Walsh]

Hi,

I assume your form is bound to a table or to a query. It would be much
easier if the form was bound to the query we discussed so far, since then,
you would not need to use a recordset (implicitly) at all, since the data
would be available through the form (add invisible controls tied to the
various fields, if you do not want your user see them).

Right now, it seems there is a lack of synchronization between your
recordset and the form and data it stored in table: you recordset is not
aware that new data has been appended and cannot find the newly enter data,
since a recordset is "filled" at the moment of its creation, and after, only
if you requery it can it take into account records added by other means to
the table.



Hoping it may help,
Vanderghast, Access MVP

Hi,

I add a new record just by filling in the form.
But when i go to the next record, i get an error message.

Hi,


How do you start a new record? Do you have your


rs.AddNew


then, your
rs.Fields("fieldName") = ...some value here...



for each field to be specifeid with non-default values, then

rs.Upate


If so, at what step is there a problem?



Vanderghast, Access MVP

Hi,

I'm not using the same query in the recordsource, only in the rstcode.

Hi,


Can you append a new record, using the same query than the one

you
use

to open a recordset, right from the query designer? If the query is not
updateable, you may have to change the way you would add records.

If
the

designer allows you to append records, but not the form, are you

using
the

same query as "recordsource" for the form, or an older version of that
query/SQL text?



Hoping it may help,
Vanderghast, Access MVP



Hi Michel,

The code works now, but i'm getting a error message when i want to
insert
a
new record. The error says no current record.

Hi,


Right! The failure, in that case, would be in the "Else" part:

If rst("Length") >= rst("Between") And rst("Length") <=
rst("AndThat")
Then
Else
MsgBox "... failure... "
End If

since it is not a failure if the value is between the limits!

Hoping it may help,
Vanderghast, Access MVP


Michel,

I think i got it almost right, but the afterupdate code does not
quite
work.
I have the following:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [1].*, [2].* FROM 1 INNER
JOIN
2
ON [1].[CaseOf]=[2].[CaseOf]; ")

If rst("Length") >= rst("Between") And rst("Length") <=
rst("AndThat")
Then
MsgBox "... failure... "
End If

End Sub
The value in length was 3300 and the range in the between/AndThat
are
3200 -
6000. The value 3300 gave the failure message. How is it possiible
that
3300
is not good, but it should because it is range of 3200-6000.

message
Hi,


The SELECT statement is the SQL text of a query that allows
you
to
open
a recordset:



Dim rst As DAO.Recordset
Sert rst=CurrentDb.OpenRecordset("SELECT ... ")
If rst("fieldName") ...



where fieldName is any selected item in the SELECT clause.



Hoping it may help,
Vanderghast, Access MVP


Michel,

The example in your first message to test the vba code, is

it

a
public
function?

message
Hi,


There is a point where building a universal good to every
possible
cases
machine is just too expensive. In most of the cases,

some

of

the
logic
would
be hard-coded. As example, if your form can handle

trucks
and

planes,
then
selecting a truck or a plane may allow to further enter a
GrossWeight
value, but if a truck is being "selected" , the WingSpan value
makes
no
sense. So, assuming someone start entering data for a truck,
then
the
WingSpan control may become disabled, and the logic may be
hard
coded
with
a
Select case statement in the AfterUpdate even of the

combo
box

allowing
to
choose a plane or a truck:

Select case Me.ComboBoxName.Value
Case "Plane"
Me.WingSpan.Enabled = True
Me. .. ' other controls, if applicable

Case "Truck"
Me.WingSpan.Enabled=False
...
End Select


You can also repeat the same code (or Call the same
subroutine)
in
the
onCurrent event too, since you probably want the same behavior
when
the
end
user comes back to look at the data already entered.




Hoping it may help,
Vanderghast, Access MVP


In your example is also appplyable when i want certain
controls
on
a
form
disabled.

E.g. in the beforementioned table someone may choose a value
from
a
combobox
and when that value is choosen, the validation of a field
may
change
and
other controls are enabled/disabled.
How can i solve a situation like this.

in
message
Hi,


Modifying data in a table is standard process, while
modifying
any
constant in a SELECT CASE requires access to the code.
Furthermore,
data
is
better stored in a data storage (table, file) than in
code.


Hoping it may help,
Vanderghast, Access MVP


Is it better to do it in a table rather then in an array
with
select
statement?.
"Michel Walsh"

wrote
in
message
Hi,


We could do that through a table.


Validations '
table
name
CaseOf BetweenThis AndThat ' fields name
A 100 1000
B 0 100
... 'data


YourTable ' table name
CaseOf Qty ' fields name


Then, in a query,


SELECT yourtable.*, Validations.*
FROM yourTable INNER JOIN Validations
ON yourtable.CaseOf = Validations.CaseOf



and then, you could test

If rst("Qty") < rst("BetweenThis") OR rst("Qty")
=
rst("AndThat" )
Then
... failure... Qty is not in the range
[BetweenThis]
and
[AndThat]
assigned to its values
of
"CaseOf"
in
table
Validations.

End If



It may even be easier to visualize the correlation
between
the
data
just
seeing the query in its "data view".



Hoping it may help,
Vanderghast, Access MVP





Hi everyone,

Does somebody has a good example how to use an array
with
select
case
statement.

For example, i have a lookup table with values like
A,
B,
C,
etc.
If someone chooses a value in a form's

combobox
then

the
value
should
be
evaluated in a select statement.
For example:

Select case cmb1

case "A"
validation in some field's property must be
=100
case "B"
same as above but then <=100
etc.

What i would like is not to hardcode the

values

in