DIM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a question regarding the DIM statement.
I know that you can dim a variable as a datatype for example "Dim X as string"
But I now have a table e.g. "Names" with lets say 1 field. This field is
"Name" and its datatype is "Text". This table has 5 rows with the following
values "John", "Mike", "Andrea", "Bob", "Anna".
This table is dynamic and can get different values from time to time. That
is that it can get more names, so more records.
So, this is what I want to do. I want to DIM each value as a string. This
must happen in code. The following has to be done automatically:
Dim John as String
Dim Mike as String
Dim Andrea as String
Dim Bob as String
Dim Anna as String
And if the table gets more values, also those values have to be declared
with the Dim statement automatically.
Can anyone out there help me with this problem.
I am in the middle of a project and I am stuck!

Thanks
 
Rachiano,

Rather than creating a variable for each record in the table, could you give
us a better idea of what you're trying to accomplish? There may be a better
way of doing it.
 
Hi Wayne,
I want to make computations with fields that do not exist in the form the
code will run on. In another table I have a formula for the computation. Say
e.g. "X = Y * Z"
This formula I want to use to make the computations. This formula is also
dynamic.
X,Y and Z are in this case the Names I mentioned before. You can say that I
want to make computations with row values, but not with field values. This
formula will be dynamicly assigned by my client and since they are not so
technical I want to make it easy as possible for them. But as you see I am
making it much difficult for myself.

Here an example of what I want to do:
"TableX" has 2 fields "Fieldname" and "Value"
This table has 3 rows:
row1: Fieldname = "Z" and Value = "" >>The value is empty (null)
row2: Fieldname = "X" and Value = "7"
row3: Fieldname = "Y" and Value = "9"
I have another table "TableA" with the formulas. One of them being "X = Y *
Z".

I have a form "Form1" with a subform "Sub1".
"Sub1" has a recordsource with 2 fields:
"FieldName" and "Value"
"Sub1" has 3 rows:
row1: Fieldname = "Z" and Value = "" >>The value is empty (null)
row2: Fieldname = "X" and Value = "7">>The value is empty (null)
row3: Fieldname = "Y" and Value = "9">>The value is empty (null)

Now having my formula I want to populate the rows of "Sub1" ("Value") with
values generated by the formule. (I still have to look how I will make these
computations. Maybe you can also help me out) So my result has to be for
"Sub1":
row1: Fieldname = "Z" and Value = "63" >>This is the formula's result.
row2: Fieldname = "X" and Value = "7" >>Kept its default value
row3: Fieldname = "Y" and Value = "9" >>Kept its default value

I hope this is a little clear, becuase I know it's difficult.
I hope you can help me out.
Thanks!
 
It sounds as if what you need are the Domain Aggregate Functions, such as
DLookup. You would be able to use this to pull data from another table or
query and apply criteria to filter what you get.

Example:
X = DLookup("[FieldName]", "[TableOrQueryName]", "Criteria")

The criteria is a where clause without the word WHERE.
Example:
"[FieldName]='" & Me.txtPersonName & "'"

The help file only shows one criteria in the expression, but it will accept
And and Or to add more criteria options.
 
Wayne,
The Dlookup solution will help me partially, but I use the ADODB recordsets.
I still need to run the code in the module for a form (The OnClick event for
a button). And to do that I need the declarations that have to be dynamically
assigned. Do you think that there is a solution to dynamically declare a
couple of field values? These values will come from a table.

Wayne Morgan said:
It sounds as if what you need are the Domain Aggregate Functions, such as
DLookup. You would be able to use this to pull data from another table or
query and apply criteria to filter what you get.

Example:
X = DLookup("[FieldName]", "[TableOrQueryName]", "Criteria")

The criteria is a where clause without the word WHERE.
Example:
"[FieldName]='" & Me.txtPersonName & "'"

The help file only shows one criteria in the expression, but it will accept
And and Or to add more criteria options.

--
Wayne Morgan
MS Access MVP


Rachiano said:
Hi Wayne,
I want to make computations with fields that do not exist in the form the
code will run on. In another table I have a formula for the computation.
Say
e.g. "X = Y * Z"
This formula I want to use to make the computations. This formula is also
dynamic.
X,Y and Z are in this case the Names I mentioned before. You can say that
I
want to make computations with row values, but not with field values. This
formula will be dynamicly assigned by my client and since they are not so
technical I want to make it easy as possible for them. But as you see I am
making it much difficult for myself.

Here an example of what I want to do:
"TableX" has 2 fields "Fieldname" and "Value"
This table has 3 rows:
row1: Fieldname = "Z" and Value = "" >>The value is empty (null)
row2: Fieldname = "X" and Value = "7"
row3: Fieldname = "Y" and Value = "9"
I have another table "TableA" with the formulas. One of them being "X = Y
*
Z".

I have a form "Form1" with a subform "Sub1".
"Sub1" has a recordsource with 2 fields:
"FieldName" and "Value"
"Sub1" has 3 rows:
row1: Fieldname = "Z" and Value = "" >>The value is empty (null)
row2: Fieldname = "X" and Value = "7">>The value is empty (null)
row3: Fieldname = "Y" and Value = "9">>The value is empty (null)

Now having my formula I want to populate the rows of "Sub1" ("Value") with
values generated by the formule. (I still have to look how I will make
these
computations. Maybe you can also help me out) So my result has to be for
"Sub1":
row1: Fieldname = "Z" and Value = "63" >>This is the formula's result.
row2: Fieldname = "X" and Value = "7" >>Kept its default value
row3: Fieldname = "Y" and Value = "9" >>Kept its default value

I hope this is a little clear, becuase I know it's difficult.
I hope you can help me out.
Thanks!
Click to expand...
Click to expand...
 
To make a variable dynamic you dim it as a variant.
This allows it to be set as a string, integer, double, and so on when you
assign it a value.

Dim x as Variant
x = 0
x = "Tom"
x = 1.5
Set x = Access.Application

Rachiano said:
Wayne,
The Dlookup solution will help me partially, but I use the ADODB recordsets.
I still need to run the code in the module for a form (The OnClick event for
a button). And to do that I need the declarations that have to be dynamically
assigned. Do you think that there is a solution to dynamically declare a
couple of field values? These values will come from a table.

Wayne Morgan said:
It sounds as if what you need are the Domain Aggregate Functions, such as
DLookup. You would be able to use this to pull data from another table or
query and apply criteria to filter what you get.

Example:
X = DLookup("[FieldName]", "[TableOrQueryName]", "Criteria")

The criteria is a where clause without the word WHERE.
Example:
"[FieldName]='" & Me.txtPersonName & "'"

The help file only shows one criteria in the expression, but it will accept
And and Or to add more criteria options.

--
Wayne Morgan
MS Access MVP


Rachiano said:
Hi Wayne,
I want to make computations with fields that do not exist in the form the
code will run on. In another table I have a formula for the computation.
Say
e.g. "X = Y * Z"
This formula I want to use to make the computations. This formula is also
dynamic.
X,Y and Z are in this case the Names I mentioned before. You can say that
I
want to make computations with row values, but not with field values. This
formula will be dynamicly assigned by my client and since they are not so
technical I want to make it easy as possible for them. But as you see I am
making it much difficult for myself.

Here an example of what I want to do:
"TableX" has 2 fields "Fieldname" and "Value"
This table has 3 rows:
row1: Fieldname = "Z" and Value = "" >>The value is empty (null)
row2: Fieldname = "X" and Value = "7"
row3: Fieldname = "Y" and Value = "9"
I have another table "TableA" with the formulas. One of them being "X = Y
*
Z".

I have a form "Form1" with a subform "Sub1".
"Sub1" has a recordsource with 2 fields:
"FieldName" and "Value"
"Sub1" has 3 rows:
row1: Fieldname = "Z" and Value = "" >>The value is empty (null)
row2: Fieldname = "X" and Value = "7">>The value is empty (null)
row3: Fieldname = "Y" and Value = "9">>The value is empty (null)

Now having my formula I want to populate the rows of "Sub1" ("Value") with
values generated by the formule. (I still have to look how I will make
these
computations. Maybe you can also help me out) So my result has to be for
"Sub1":
row1: Fieldname = "Z" and Value = "63" >>This is the formula's result.
row2: Fieldname = "X" and Value = "7" >>Kept its default value
row3: Fieldname = "Y" and Value = "9" >>Kept its default value

I hope this is a little clear, becuase I know it's difficult.
I hope you can help me out.
Thanks!



:

Rachiano,

Rather than creating a variable for each record in the table, could you
give
us a better idea of what you're trying to accomplish? There may be a
better
way of doing it.

--
Wayne Morgan
MS Access MVP


Hi,
I have a question regarding the DIM statement.
I know that you can dim a variable as a datatype for example "Dim X as
string"
But I now have a table e.g. "Names" with lets say 1 field. This field
is
"Name" and its datatype is "Text". This table has 5 rows with the
following
values "John", "Mike", "Andrea", "Bob", "Anna".
This table is dynamic and can get different values from time to time.
That
is that it can get more names, so more records.
So, this is what I want to do. I want to DIM each value as a string.
This
must happen in code. The following has to be done automatically:
Dim John as String
Dim Mike as String
Dim Andrea as String
Dim Bob as String
Dim Anna as String
And if the table gets more values, also those values have to be
declared
with the Dim statement automatically.
Can anyone out there help me with this problem.
I am in the middle of a project and I am stuck!

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi,
The OP wants dynamically declare variables at runtime, which is not possible.
Rachiano, I haven't looked too closely at your code, but will a dynamic array not
fit the bill?

--
HTH
Dan Artuso, Access MVP


visdev1 said:
To make a variable dynamic you dim it as a variant.
This allows it to be set as a string, integer, double, and so on when you
assign it a value.

Dim x as Variant
x = 0
x = "Tom"
x = 1.5
Set x = Access.Application

Rachiano said:
Wayne,
The Dlookup solution will help me partially, but I use the ADODB recordsets.
I still need to run the code in the module for a form (The OnClick event for
a button). And to do that I need the declarations that have to be dynamically
assigned. Do you think that there is a solution to dynamically declare a
couple of field values? These values will come from a table.

Wayne Morgan said:
It sounds as if what you need are the Domain Aggregate Functions, such as
DLookup. You would be able to use this to pull data from another table or
query and apply criteria to filter what you get.

Example:
X = DLookup("[FieldName]", "[TableOrQueryName]", "Criteria")

The criteria is a where clause without the word WHERE.
Example:
"[FieldName]='" & Me.txtPersonName & "'"

The help file only shows one criteria in the expression, but it will accept
And and Or to add more criteria options.

--
Wayne Morgan
MS Access MVP


Hi Wayne,
I want to make computations with fields that do not exist in the form the
code will run on. In another table I have a formula for the computation.
Say
e.g. "X = Y * Z"
This formula I want to use to make the computations. This formula is also
dynamic.
X,Y and Z are in this case the Names I mentioned before. You can say that
I
want to make computations with row values, but not with field values. This
formula will be dynamicly assigned by my client and since they are not so
technical I want to make it easy as possible for them. But as you see I am
making it much difficult for myself.

Here an example of what I want to do:
"TableX" has 2 fields "Fieldname" and "Value"
This table has 3 rows:
row1: Fieldname = "Z" and Value = "" >>The value is empty (null)
row2: Fieldname = "X" and Value = "7"
row3: Fieldname = "Y" and Value = "9"
I have another table "TableA" with the formulas. One of them being "X = Y
*
Z".

I have a form "Form1" with a subform "Sub1".
"Sub1" has a recordsource with 2 fields:
"FieldName" and "Value"
"Sub1" has 3 rows:
row1: Fieldname = "Z" and Value = "" >>The value is empty (null)
row2: Fieldname = "X" and Value = "7">>The value is empty (null)
row3: Fieldname = "Y" and Value = "9">>The value is empty (null)

Now having my formula I want to populate the rows of "Sub1" ("Value") with
values generated by the formule. (I still have to look how I will make
these
computations. Maybe you can also help me out) So my result has to be for
"Sub1":
row1: Fieldname = "Z" and Value = "63" >>This is the formula's result.
row2: Fieldname = "X" and Value = "7" >>Kept its default value
row3: Fieldname = "Y" and Value = "9" >>Kept its default value

I hope this is a little clear, becuase I know it's difficult.
I hope you can help me out.
Thanks!



:

Rachiano,

Rather than creating a variable for each record in the table, could you
give
us a better idea of what you're trying to accomplish? There may be a
better
way of doing it.

--
Wayne Morgan
MS Access MVP


Hi,
I have a question regarding the DIM statement.
I know that you can dim a variable as a datatype for example "Dim X as
string"
But I now have a table e.g. "Names" with lets say 1 field. This field
is
"Name" and its datatype is "Text". This table has 5 rows with the
following
values "John", "Mike", "Andrea", "Bob", "Anna".
This table is dynamic and can get different values from time to time.
That
is that it can get more names, so more records.
So, this is what I want to do. I want to DIM each value as a string.
This
must happen in code. The following has to be done automatically:
Dim John as String
Dim Mike as String
Dim Andrea as String
Dim Bob as String
Dim Anna as String
And if the table gets more values, also those values have to be
declared
with the Dim statement automatically.
Can anyone out there help me with this problem.
I am in the middle of a project and I am stuck!

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi,
The OP wants dynamically declare variables at runtime, which is not possible.
Rachiano, I haven't looked too closely at your code, but will a dynamic array not
fit the bill?
Click to expand...

Failing that, a collection:

Dim colC As New Collection
colC.Add "ValueOfJohn", "John"
colC.Add "ValueOfAnna", "Anna"
Debug.Print colC("John") & colC("Anna")
 
Hi,
Thanks all you guys.
I have tried using a crosstab query as the source for my computation and it
works o.k. The crosstab query converts al those rows into columns and on that
I use a filter.
Thanks for all the help. I have more questions, but I will put that in new
post.
 
Back
Top