Free way of creating field value/formulas

[Guest]

Hi. I'm currently developing a software to make forecasts.

The way that formulas are calculated are different, it depends of the
department.

Imagine rubric Whater my be a fixed value or it can be calculated based in
other factors.

I would like to have the possibility that my users define how they want the
to calculate the rubric fileds.

Any one know a template based or for finance forecasts?

Regards,
Marco

 

[Guest]

How are you looking to do this? Allowing the user to select from a list of
pre-determined formulas, or enter their own?

 

[Guest]

I was think something lika having a list of fileds and choosing fields and
number like excel.

Imagine, I could choose (field A / 4) or (field A - field B)

what do you think?

Marco

 

[Guest]

That's doable, but Access doesn't have a built in interface for that kind of
thing. Depending on how complicated the formulas are getting it could be
rather interesting to make work.

You could display the fields, and have them key a formula into a text box..
and you could parse that string and put it into a SQL statement.

 

[Guest]

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their own formulas.

do you have any example like that?

Marco

 

[Guest]

I've never done this in "production" and what I'm including here was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of 10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text & ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as (field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would create a table
showing the results.

 

[Guest]

You know what was great? if there's way to have a list with all fields
avaiable in access and users could choose fields from that list.

Maco

I've never done this in "production" and what I'm including here was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of 10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text & ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as (field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would create a table
showing the results.

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their own formulas.

do you have any example like that?

Marco

 

[Guest]

You know what also could be great?

if we could put the formula into a textfiled in a form, then the query would
a field such as:

field1: [forms]![formabc]![txtformula1]

This could make probably make a column have diferent ways of calcule.

What do you think?

Marco

You know what was great? if there's way to have a list with all fields
avaiable in access and users could choose fields from that list.

Maco

I've never done this in "production" and what I'm including here was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of 10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text & ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as (field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would create a table
showing the results.

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their own formulas.

do you have any example like that?

Marco



:

That's doable, but Access doesn't have a built in interface for that kind of
thing. Depending on how complicated the formulas are getting it could be
rather interesting to make work.

You could display the fields, and have them key a formula into a text box..
and you could parse that string and put it into a SQL statement.

:

I was think something lika having a list of fileds and choosing fields and
number like excel.

Imagine, I could choose (field A / 4) or (field A - field B)

what do you think?

Marco



:

How are you looking to do this? Allowing the user to select from a list of
pre-determined formulas, or enter their own?

:

Hi. I'm currently developing a software to make forecasts.

The way that formulas are calculated are different, it depends of the
department.

Imagine rubric Whater my be a fixed value or it can be calculated based in
other factors.

I would like to have the possibility that my users define how they want the
to calculate the rubric fileds.

Any one know a template based or for finance forecasts?

Regards,
Marco

 

[Guest]

You can, this code will load all fields in all non-system tables into another
table.

Public Sub GetTableInfo()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TABLE_LIST")

For Each tbl In db.TableDefs
For Each fld In tbl.Fields
If Left$(tbl.Name, 4) <> "MSys" Then
rst.AddNew
rst!TABLE_NAME = tbl.Name
rst!FIELD_NAME = fld.Name
rst.UPDATE
End If
Next fld
Next tbl
End Sub

The major problem is the creation of the formula. Users are still going to
have to enter the formula by hand, either with a keyboard or a long series of
mouse clicks. Overall, I think the keyboard approach is easier.. although
being able to click on a table->field and have it appended to the end of the
text would be a nice touch.

You know what was great? if there's way to have a list with all fields
avaiable in access and users could choose fields from that list.

Maco

I've never done this in "production" and what I'm including here was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of 10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text & ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as (field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would create a table
showing the results.

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their own formulas.

do you have any example like that?

Marco



:

That's doable, but Access doesn't have a built in interface for that kind of
thing. Depending on how complicated the formulas are getting it could be
rather interesting to make work.

You could display the fields, and have them key a formula into a text box..
and you could parse that string and put it into a SQL statement.

:

I was think something lika having a list of fileds and choosing fields and
number like excel.

Imagine, I could choose (field A / 4) or (field A - field B)

what do you think?

Marco



:

How are you looking to do this? Allowing the user to select from a list of
pre-determined formulas, or enter their own?

:

Hi. I'm currently developing a software to make forecasts.

The way that formulas are calculated are different, it depends of the
department.

Imagine rubric Whater my be a fixed value or it can be calculated based in
other factors.

I would like to have the possibility that my users define how they want the
to calculate the rubric fileds.

Any one know a template based or for finance forecasts?

Regards,
Marco

 

[Guest]

Where do I put that?

Into a module? If so, how can I load the module in the form?


Or I put that in a form?

Thank,
Marco

You can, this code will load all fields in all non-system tables into another
table.

Public Sub GetTableInfo()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TABLE_LIST")

For Each tbl In db.TableDefs
For Each fld In tbl.Fields
If Left$(tbl.Name, 4) <> "MSys" Then
rst.AddNew
rst!TABLE_NAME = tbl.Name
rst!FIELD_NAME = fld.Name
rst.UPDATE
End If
Next fld
Next tbl
End Sub

The major problem is the creation of the formula. Users are still going to
have to enter the formula by hand, either with a keyboard or a long series of
mouse clicks. Overall, I think the keyboard approach is easier.. although
being able to click on a table->field and have it appended to the end of the
text would be a nice touch.

You know what was great? if there's way to have a list with all fields
avaiable in access and users could choose fields from that list.

Maco

I've never done this in "production" and what I'm including here was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of 10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text & ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as (field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would create a table
showing the results.

:

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their own formulas.

do you have any example like that?

Marco



:

That's doable, but Access doesn't have a built in interface for that kind of
thing. Depending on how complicated the formulas are getting it could be
rather interesting to make work.

You could display the fields, and have them key a formula into a text box..
and you could parse that string and put it into a SQL statement.

:

I was think something lika having a list of fileds and choosing fields and
number like excel.

Imagine, I could choose (field A / 4) or (field A - field B)

what do you think?

Marco



:

How are you looking to do this? Allowing the user to select from a list of
pre-determined formulas, or enter their own?

:

Hi. I'm currently developing a software to make forecasts.

The way that formulas are calculated are different, it depends of the
department.

Imagine rubric Whater my be a fixed value or it can be calculated based in
other factors.

I would like to have the possibility that my users define how they want the
to calculate the rubric fileds.

Any one know a template based or for finance forecasts?

Regards,
Marco

 

[Guest]

It's not really a form function.. so it probably belongs in a module. But it
would work either place. You would need to call it from something
regardless, either through a button on your form on through an autoexec macro
or whatever.

Where do I put that?

Into a module? If so, how can I load the module in the form?


Or I put that in a form?

Thank,
Marco

You can, this code will load all fields in all non-system tables into another
table.

Public Sub GetTableInfo()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TABLE_LIST")

For Each tbl In db.TableDefs
For Each fld In tbl.Fields
If Left$(tbl.Name, 4) <> "MSys" Then
rst.AddNew
rst!TABLE_NAME = tbl.Name
rst!FIELD_NAME = fld.Name
rst.UPDATE
End If
Next fld
Next tbl
End Sub

The major problem is the creation of the formula. Users are still going to
have to enter the formula by hand, either with a keyboard or a long series of
mouse clicks. Overall, I think the keyboard approach is easier.. although
being able to click on a table->field and have it appended to the end of the
text would be a nice touch.

You know what was great? if there's way to have a list with all fields
avaiable in access and users could choose fields from that list.

Maco



:

I've never done this in "production" and what I'm including here was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of 10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text & ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as (field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would create a table
showing the results.

:

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their own formulas.

do you have any example like that?

Marco



:

That's doable, but Access doesn't have a built in interface for that kind of
thing. Depending on how complicated the formulas are getting it could be
rather interesting to make work.

You could display the fields, and have them key a formula into a text box..
and you could parse that string and put it into a SQL statement.

:

I was think something lika having a list of fileds and choosing fields and
number like excel.

Imagine, I could choose (field A / 4) or (field A - field B)

what do you think?

Marco



:

How are you looking to do this? Allowing the user to select from a list of
pre-determined formulas, or enter their own?

:

Hi. I'm currently developing a software to make forecasts.

The way that formulas are calculated are different, it depends of the
department.

Imagine rubric Whater my be a fixed value or it can be calculated based in
other factors.

I would like to have the possibility that my users define how they want the
to calculate the rubric fileds.

Any one know a template based or for finance forecasts?

Regards,
Marco

 

[Guest]

Hello.


I tried and return me a message saying that "item not found on this
collection"

What is it?

Thanks,
Marco

It's not really a form function.. so it probably belongs in a module. But it
would work either place. You would need to call it from something
regardless, either through a button on your form on through an autoexec macro
or whatever.

Where do I put that?

Into a module? If so, how can I load the module in the form?


Or I put that in a form?

Thank,
Marco

You can, this code will load all fields in all non-system tables into another
table.

Public Sub GetTableInfo()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TABLE_LIST")

For Each tbl In db.TableDefs
For Each fld In tbl.Fields
If Left$(tbl.Name, 4) <> "MSys" Then
rst.AddNew
rst!TABLE_NAME = tbl.Name
rst!FIELD_NAME = fld.Name
rst.UPDATE
End If
Next fld
Next tbl
End Sub

The major problem is the creation of the formula. Users are still going to
have to enter the formula by hand, either with a keyboard or a long series of
mouse clicks. Overall, I think the keyboard approach is easier.. although
being able to click on a table->field and have it appended to the end of the
text would be a nice touch.

:

You know what was great? if there's way to have a list with all fields
avaiable in access and users could choose fields from that list.

Maco



:

I've never done this in "production" and what I'm including here was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of 10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text & ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as (field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would create a table
showing the results.

:

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their own formulas.

do you have any example like that?

Marco



:

That's doable, but Access doesn't have a built in interface for that kind of
thing. Depending on how complicated the formulas are getting it could be
rather interesting to make work.

You could display the fields, and have them key a formula into a text box..
and you could parse that string and put it into a SQL statement.

:

I was think something lika having a list of fileds and choosing fields and
number like excel.

Imagine, I could choose (field A / 4) or (field A - field B)

what do you think?

Marco



:

How are you looking to do this? Allowing the user to select from a list of
pre-determined formulas, or enter their own?

:

Hi. I'm currently developing a software to make forecasts.

The way that formulas are calculated are different, it depends of the
department.

Imagine rubric Whater my be a fixed value or it can be calculated based in
other factors.

I would like to have the possibility that my users define how they want the
to calculate the rubric fileds.

Any one know a template based or for finance forecasts?

Regards,
Marco

 

[Douglas J. Steele]

That's DAO code, so it's important that all the variables be defined
correctly.

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset

It's really only critical for the Field and Recordset objects (since objects
with that same name exist in other models), but no harm doing it for all
four.

Hopefully you realize that TABLE_LIST in Set rst =
db.OpenRecordset("TABLE_LIST") assumes that you've created a table named
TABLE_LIST (with fields TABLE_NAME and FIELD_NAME) in which to store the
information.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello.


I tried and return me a message saying that "item not found on this
collection"

What is it?

Thanks,
Marco

It's not really a form function.. so it probably belongs in a module.
But it
would work either place. You would need to call it from something
regardless, either through a button on your form on through an autoexec
macro
or whatever.

Where do I put that?

Into a module? If so, how can I load the module in the form?


Or I put that in a form?

Thank,
Marco





:

You can, this code will load all fields in all non-system tables into
another
table.

Public Sub GetTableInfo()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TABLE_LIST")

For Each tbl In db.TableDefs
For Each fld In tbl.Fields
If Left$(tbl.Name, 4) <> "MSys" Then
rst.AddNew
rst!TABLE_NAME = tbl.Name
rst!FIELD_NAME = fld.Name
rst.UPDATE
End If
Next fld
Next tbl
End Sub

The major problem is the creation of the formula. Users are still
going to
have to enter the formula by hand, either with a keyboard or a long
series of
mouse clicks. Overall, I think the keyboard approach is easier..
although
being able to click on a table->field and have it appended to the end
of the
text would be a nice touch.

:

You know what was great? if there's way to have a list with all
fields
avaiable in access and users could choose fields from that list.

Maco



:

I've never done this in "production" and what I'm including here
was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of
10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and
a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text
& ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as
(field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would
create a table
showing the results.

:

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their
own formulas.

do you have any example like that?

Marco



:

That's doable, but Access doesn't have a built in interface
for that kind of
thing. Depending on how complicated the formulas are getting
it could be
rather interesting to make work.

You could display the fields, and have them key a formula
into a text box..
and you could parse that string and put it into a SQL
statement.

:

I was think something lika having a list of fileds and
choosing fields and
number like excel.

Imagine, I could choose (field A / 4) or (field A - field
B)

what do you think?

Marco



:

How are you looking to do this? Allowing the user to
select from a list of
pre-determined formulas, or enter their own?

:

Hi. I'm currently developing a software to make
forecasts.

The way that formulas are calculated are different, it
depends of the
department.

Imagine rubric Whater my be a fixed value or it can be
calculated based in
other factors.

I would like to have the possibility that my users
define how they want the
to calculate the rubric fileds.

Any one know a template based or for finance forecasts?

Regards,
Marco

 

[Guest]

OK. I'M THERE. IT'S WORKING.

So, now how can make this working for me?

Imagine.

I have a "line" wich is Whater. This line going to be a cost for department
A and B

For Department A the whater a specific price and unique. for Department B is
diference. Is the average of A and the avarage of total number of worked days.

So my idea was to make a space where users could create their own formulas.

I think this is impossible.

regards,
Marco

That's DAO code, so it's important that all the variables be defined
correctly.

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset

It's really only critical for the Field and Recordset objects (since objects
with that same name exist in other models), but no harm doing it for all
four.

Hopefully you realize that TABLE_LIST in Set rst =
db.OpenRecordset("TABLE_LIST") assumes that you've created a table named
TABLE_LIST (with fields TABLE_NAME and FIELD_NAME) in which to store the
information.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello.


I tried and return me a message saying that "item not found on this
collection"

What is it?

Thanks,
Marco

It's not really a form function.. so it probably belongs in a module.
But it
would work either place. You would need to call it from something
regardless, either through a button on your form on through an autoexec
macro
or whatever.

:

Where do I put that?

Into a module? If so, how can I load the module in the form?


Or I put that in a form?

Thank,
Marco





:

You can, this code will load all fields in all non-system tables into
another
table.

Public Sub GetTableInfo()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TABLE_LIST")

For Each tbl In db.TableDefs
For Each fld In tbl.Fields
If Left$(tbl.Name, 4) <> "MSys" Then
rst.AddNew
rst!TABLE_NAME = tbl.Name
rst!FIELD_NAME = fld.Name
rst.UPDATE
End If
Next fld
Next tbl
End Sub

The major problem is the creation of the formula. Users are still
going to
have to enter the formula by hand, either with a keyboard or a long
series of
mouse clicks. Overall, I think the keyboard approach is easier..
although
being able to click on a table->field and have it appended to the end
of the
text would be a nice touch.

:

You know what was great? if there's way to have a list with all
fields
avaiable in access and users could choose fields from that list.

Maco



:

I've never done this in "production" and what I'm including here
was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of
10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and
a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text
& ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as
(field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would
create a table
showing the results.

:

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their
own formulas.

do you have any example like that?

Marco



:

That's doable, but Access doesn't have a built in interface
for that kind of
thing. Depending on how complicated the formulas are getting
it could be
rather interesting to make work.

You could display the fields, and have them key a formula
into a text box..
and you could parse that string and put it into a SQL
statement.

:

I was think something lika having a list of fileds and
choosing fields and
number like excel.

Imagine, I could choose (field A / 4) or (field A - field
B)

what do you think?

Marco



:

How are you looking to do this? Allowing the user to
select from a list of
pre-determined formulas, or enter their own?

:

Hi. I'm currently developing a software to make
forecasts.

The way that formulas are calculated are different, it
depends of the
department.

Imagine rubric Whater my be a fixed value or it can be
calculated based in
other factors.

I would like to have the possibility that my users
define how they want the
to calculate the rubric fileds.

Any one know a template based or for finance forecasts?

Regards,
Marco

 

[Guest]

All the list of tables & fields will give you is the option to display/choose
fields, it's just polish to the actual formula creation.

I provided an example of how to let users create their own formulas earlier.
It is going to have to involve keying it into a text box and then parsing
the text from that testbox into an SQL statement.

OK. I'M THERE. IT'S WORKING.

So, now how can make this working for me?

Imagine.

I have a "line" wich is Whater. This line going to be a cost for department
A and B

For Department A the whater a specific price and unique. for Department B is
diference. Is the average of A and the avarage of total number of worked days.

So my idea was to make a space where users could create their own formulas.

I think this is impossible.

regards,
Marco

That's DAO code, so it's important that all the variables be defined
correctly.

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset

It's really only critical for the Field and Recordset objects (since objects
with that same name exist in other models), but no harm doing it for all
four.

Hopefully you realize that TABLE_LIST in Set rst =
db.OpenRecordset("TABLE_LIST") assumes that you've created a table named
TABLE_LIST (with fields TABLE_NAME and FIELD_NAME) in which to store the
information.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello.


I tried and return me a message saying that "item not found on this
collection"

What is it?

Thanks,
Marco


:

It's not really a form function.. so it probably belongs in a module.
But it
would work either place. You would need to call it from something
regardless, either through a button on your form on through an autoexec
macro
or whatever.

:

Where do I put that?

Into a module? If so, how can I load the module in the form?


Or I put that in a form?

Thank,
Marco





:

You can, this code will load all fields in all non-system tables into
another
table.

Public Sub GetTableInfo()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TABLE_LIST")

For Each tbl In db.TableDefs
For Each fld In tbl.Fields
If Left$(tbl.Name, 4) <> "MSys" Then
rst.AddNew
rst!TABLE_NAME = tbl.Name
rst!FIELD_NAME = fld.Name
rst.UPDATE
End If
Next fld
Next tbl
End Sub

The major problem is the creation of the formula. Users are still
going to
have to enter the formula by hand, either with a keyboard or a long
series of
mouse clicks. Overall, I think the keyboard approach is easier..
although
being able to click on a table->field and have it appended to the end
of the
text would be a nice touch.

:

You know what was great? if there's way to have a list with all
fields
avaiable in access and users could choose fields from that list.

Maco



:

I've never done this in "production" and what I'm including here
was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of
10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and
a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text
& ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as
(field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would
create a table
showing the results.

:

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their
own formulas.

do you have any example like that?

Marco



:

That's doable, but Access doesn't have a built in interface
for that kind of
thing. Depending on how complicated the formulas are getting
it could be
rather interesting to make work.

You could display the fields, and have them key a formula
into a text box..
and you could parse that string and put it into a SQL
statement.

:

I was think something lika having a list of fileds and
choosing fields and
number like excel.

Imagine, I could choose (field A / 4) or (field A - field
B)

what do you think?

Marco



:

How are you looking to do this? Allowing the user to
select from a list of
pre-determined formulas, or enter their own?

:

Hi. I'm currently developing a software to make
forecasts.

The way that formulas are calculated are different, it
depends of the
department.

Imagine rubric Whater my be a fixed value or it can be
calculated based in
other factors.

I would like to have the possibility that my users
define how they want the
to calculate the rubric fileds.

Any one know a template based or for finance forecasts?

Regards,
Marco

 

[Guest]

Hi.

Can you send that example to me?

Please send to (e-mail address removed)


Thanks.
Marco

All the list of tables & fields will give you is the option to display/choose
fields, it's just polish to the actual formula creation.

I provided an example of how to let users create their own formulas earlier.
It is going to have to involve keying it into a text box and then parsing
the text from that testbox into an SQL statement.

OK. I'M THERE. IT'S WORKING.

So, now how can make this working for me?

Imagine.

I have a "line" wich is Whater. This line going to be a cost for department
A and B

For Department A the whater a specific price and unique. for Department B is
diference. Is the average of A and the avarage of total number of worked days.

So my idea was to make a space where users could create their own formulas.

I think this is impossible.

regards,
Marco

That's DAO code, so it's important that all the variables be defined
correctly.

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset

It's really only critical for the Field and Recordset objects (since objects
with that same name exist in other models), but no harm doing it for all
four.

Hopefully you realize that TABLE_LIST in Set rst =
db.OpenRecordset("TABLE_LIST") assumes that you've created a table named
TABLE_LIST (with fields TABLE_NAME and FIELD_NAME) in which to store the
information.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello.


I tried and return me a message saying that "item not found on this
collection"

What is it?

Thanks,
Marco


:

It's not really a form function.. so it probably belongs in a module.
But it
would work either place. You would need to call it from something
regardless, either through a button on your form on through an autoexec
macro
or whatever.

:

Where do I put that?

Into a module? If so, how can I load the module in the form?


Or I put that in a form?

Thank,
Marco





:

You can, this code will load all fields in all non-system tables into
another
table.

Public Sub GetTableInfo()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TABLE_LIST")

For Each tbl In db.TableDefs
For Each fld In tbl.Fields
If Left$(tbl.Name, 4) <> "MSys" Then
rst.AddNew
rst!TABLE_NAME = tbl.Name
rst!FIELD_NAME = fld.Name
rst.UPDATE
End If
Next fld
Next tbl
End Sub

The major problem is the creation of the formula. Users are still
going to
have to enter the formula by hand, either with a keyboard or a long
series of
mouse clicks. Overall, I think the keyboard approach is easier..
although
being able to click on a table->field and have it appended to the end
of the
text would be a nice touch.

:

You know what was great? if there's way to have a list with all
fields
avaiable in access and users could choose fields from that list.

Maco



:

I've never done this in "production" and what I'm including here
was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of
10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and
a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text
& ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as
(field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would
create a table
showing the results.

:

Well, it could be.

havind a range of formulas or something like that.

my idea was to give the possibility to my users to create their
own formulas.

do you have any example like that?

Marco



:

That's doable, but Access doesn't have a built in interface
for that kind of
thing. Depending on how complicated the formulas are getting
it could be
rather interesting to make work.

You could display the fields, and have them key a formula
into a text box..
and you could parse that string and put it into a SQL
statement.

:

I was think something lika having a list of fileds and
choosing fields and
number like excel.

Imagine, I could choose (field A / 4) or (field A - field
B)

what do you think?

Marco



:

How are you looking to do this? Allowing the user to
select from a list of
pre-determined formulas, or enter their own?

:

Hi. I'm currently developing a software to make
forecasts.

The way that formulas are calculated are different, it
depends of the
department.

Imagine rubric Whater my be a fixed value or it can be
calculated based in
other factors.

I would like to have the possibility that my users
define how they want the
to calculate the rubric fileds.

Any one know a template based or for finance forecasts?

Regards,
Marco

 

[Guest]

I've never done this in "production" and what I'm including here was just
thrown together in a few minutes..

Made a form which displayed all the fields from a test table of 10 random
number columns and an ID field.

Under them I had a single text box ( to key in the formula ) and a command
button with the following on click event script:

Private Sub Command4_Click()
Dim sql_string As String
On Error GoTo handler
Me.Text2.SetFocus
sql_string = "SELECT [TEST NUMBERS].[Name], (" & Me.Text2.Text & ") AS
Formula_Result INTO SAMPLE_TABLE FROM [TEST NUMBERS]"
Call DoCmd.RunSQL(sql_string)
Exit Sub
handler:
MsgBox Err.Description
End Sub

I could type in any formula I wanted, such as (field1-field2)/field3 +
(field4-field5)/field3, click the command button and it would create a table
showing the results.

http://www.microsoft.com/office/com...cess&mid=d93576fd-6301-403a-ad30-e5833e8d3627