Running record for how many times a form is used

G

Guest

I have a Continous SubForm on a Tab Page where I want to keep a running
record of how many times the form has been used. The Form is used as a quiz
answer form with the answers provided by another SubForm on the same Tab
Page. The idea is that I can show the user how many times they have attempted
the quiz. How would I build a control mechanism for this, I assume I would
need a seperate table to hold the incrementing number, I have this code on a
test button at the moment:

Forms.Runs.[WayPoint_Test_Register subform].Form.WayPoint_Test_Reg_No =
WayPoint_Test_Reg_No + 1

but I can’t seem to build a solution that increments, It never goes past
‘1’. I think I’m overlooking a fundemental point here, but can’t figure what
it is?...
 
G

Guest

Ofer,

Thanks for replying. with reference to that link; it say's

"This uses the DMAX function to find the highest EmployeeID value in table
[tblPracticeEmployeeData] and adds 1 to the value."

does this mean it will find the highest EmployeeID value across all
records?, if so, then I have a problem.

Because my form represents a form based quiz, based on multiple vehicle runs
(think bus routes) which are represented by a mainform Run_No linked to a
subform route. There are over 50 different runs, each one is essentially a
seperate quiz (route). I have everything working fine, as the user advances
through the main (Run) records; the Route Quiz appears on a Subform/Tab Page.
Except that I want to record the number of times the user has tried any given
quiz. So If i'm reading my opening comment right, I can only record 1
Quiz/Run_No?. Is that how you would see things?




Ofer Cohen said:
Look at this link on "Automatically Increment a Fields Value in a database"

http://www.databasedev.co.uk/automatically_increment_value.html

What you need is the example of the function that they use to get the new
number

--
Good Luck
BS"D


efandango said:
I have a Continous SubForm on a Tab Page where I want to keep a running
record of how many times the form has been used. The Form is used as a quiz
answer form with the answers provided by another SubForm on the same Tab
Page. The idea is that I can show the user how many times they have attempted
the quiz. How would I build a control mechanism for this, I assume I would
need a seperate table to hold the incrementing number, I have this code on a
test button at the moment:

Forms.Runs.[WayPoint_Test_Register subform].Form.WayPoint_Test_Reg_No =
WayPoint_Test_Reg_No + 1

but I can’t seem to build a solution that increments, It never goes past
‘1’. I think I’m overlooking a fundemental point here, but can’t figure what
it is?...
Click to expand...
Click to expand...
 
G

Guest

To the DMax you can add a criteria, so you can get the max number by any
group you would like

DMax("[FieldName","[TableName]","Criteria")

Something like:
Criteria for number
DMax("FieldName","TableNAme","NumberFieldName = " & Variable)

Criteria for text
DMax("FieldName","TableNAme","TextFieldName = '" & Variable & "'")

Criteria for both
DMax("FieldName","TableNAme","NumberFieldName = " & Variable & "
TextFieldName = '" & Variable & "'")

The variable can be a text box in the form
Me.[TextBoxName]

Also: Incase no match is found it will be better using the Mz function to
replace null with 1

Nz(DMax("FieldName","TableNAme","TextFieldName = '" & Variable & "'"),1)

--
Good Luck
BS"D


efandango said:
Ofer,

Thanks for replying. with reference to that link; it say's

"This uses the DMAX function to find the highest EmployeeID value in table
[tblPracticeEmployeeData] and adds 1 to the value."

does this mean it will find the highest EmployeeID value across all
records?, if so, then I have a problem.

Because my form represents a form based quiz, based on multiple vehicle runs
(think bus routes) which are represented by a mainform Run_No linked to a
subform route. There are over 50 different runs, each one is essentially a
seperate quiz (route). I have everything working fine, as the user advances
through the main (Run) records; the Route Quiz appears on a Subform/Tab Page.
Except that I want to record the number of times the user has tried any given
quiz. So If i'm reading my opening comment right, I can only record 1
Quiz/Run_No?. Is that how you would see things?




Ofer Cohen said:
Look at this link on "Automatically Increment a Fields Value in a database"

http://www.databasedev.co.uk/automatically_increment_value.html

What you need is the example of the function that they use to get the new
number

--
Good Luck
BS"D


efandango said:
I have a Continous SubForm on a Tab Page where I want to keep a running
record of how many times the form has been used. The Form is used as a quiz
answer form with the answers provided by another SubForm on the same Tab
Page. The idea is that I can show the user how many times they have attempted
the quiz. How would I build a control mechanism for this, I assume I would
need a seperate table to hold the incrementing number, I have this code on a
test button at the moment:

Forms.Runs.[WayPoint_Test_Register subform].Form.WayPoint_Test_Reg_No =
WayPoint_Test_Reg_No + 1

but I can’t seem to build a solution that increments, It never goes past
‘1’. I think I’m overlooking a fundemental point here, but can’t figure what
it is?...
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ofer,

Thanks for the feedback. For me, it's quite a bit to take in..., so before I
try and get my head round this. Will I be able to end up with a table or
query like this:


Run_No WayPoint_Test_Reg_No
1 6
2 8
3 13
19 5
24 1
25 9
26 101
27 32

Where [WayPoint_Test_Reg_No] is the number of times this particular
Form/Record has been opened/used.


Ofer Cohen said:
To the DMax you can add a criteria, so you can get the max number by any
group you would like

DMax("[FieldName","[TableName]","Criteria")

Something like:
Criteria for number
DMax("FieldName","TableNAme","NumberFieldName = " & Variable)

Criteria for text
DMax("FieldName","TableNAme","TextFieldName = '" & Variable & "'")

Criteria for both
DMax("FieldName","TableNAme","NumberFieldName = " & Variable & "
TextFieldName = '" & Variable & "'")

The variable can be a text box in the form
Me.[TextBoxName]

Also: Incase no match is found it will be better using the Mz function to
replace null with 1

Nz(DMax("FieldName","TableNAme","TextFieldName = '" & Variable & "'"),1)

--
Good Luck
BS"D


efandango said:
Ofer,

Thanks for replying. with reference to that link; it say's

"This uses the DMAX function to find the highest EmployeeID value in table
[tblPracticeEmployeeData] and adds 1 to the value."

does this mean it will find the highest EmployeeID value across all
records?, if so, then I have a problem.

Because my form represents a form based quiz, based on multiple vehicle runs
(think bus routes) which are represented by a mainform Run_No linked to a
subform route. There are over 50 different runs, each one is essentially a
seperate quiz (route). I have everything working fine, as the user advances
through the main (Run) records; the Route Quiz appears on a Subform/Tab Page.
Except that I want to record the number of times the user has tried any given
quiz. So If i'm reading my opening comment right, I can only record 1
Quiz/Run_No?. Is that how you would see things?




Ofer Cohen said:
Look at this link on "Automatically Increment a Fields Value in a database"

http://www.databasedev.co.uk/automatically_increment_value.html

What you need is the example of the function that they use to get the new
number

--
Good Luck
BS"D


:

I have a Continous SubForm on a Tab Page where I want to keep a running
record of how many times the form has been used. The Form is used as a quiz
answer form with the answers provided by another SubForm on the same Tab
Page. The idea is that I can show the user how many times they have attempted
the quiz. How would I build a control mechanism for this, I assume I would
need a seperate table to hold the incrementing number, I have this code on a
test button at the moment:

Forms.Runs.[WayPoint_Test_Register subform].Form.WayPoint_Test_Reg_No =
WayPoint_Test_Reg_No + 1

but I can’t seem to build a solution that increments, It never goes past
‘1’. I think I’m overlooking a fundemental point here, but can’t figure what
it is?...
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You can either edit the existing field in the table with the next max count
value, and then you can get this resault viewing the table

Or, add a new record for each entry to the form with the new count number
(incase you want to save the date and time of each entry), and then you get
the resault using a group by query

SELECT TableName.Run_No, Max(TableName.WayPoint_Test_Reg_No) AS MaxWayPoint
FROM TableName
GROUP BY TableName.Run_No

--
Good Luck
BS"D


efandango said:
Ofer,

Thanks for the feedback. For me, it's quite a bit to take in..., so before I
try and get my head round this. Will I be able to end up with a table or
query like this:


Run_No WayPoint_Test_Reg_No
1 6
2 8
3 13
19 5
24 1
25 9
26 101
27 32

Where [WayPoint_Test_Reg_No] is the number of times this particular
Form/Record has been opened/used.


Ofer Cohen said:
To the DMax you can add a criteria, so you can get the max number by any
group you would like

DMax("[FieldName","[TableName]","Criteria")

Something like:
Criteria for number
DMax("FieldName","TableNAme","NumberFieldName = " & Variable)

Criteria for text
DMax("FieldName","TableNAme","TextFieldName = '" & Variable & "'")

Criteria for both
DMax("FieldName","TableNAme","NumberFieldName = " & Variable & "
TextFieldName = '" & Variable & "'")

The variable can be a text box in the form
Me.[TextBoxName]

Also: Incase no match is found it will be better using the Mz function to
replace null with 1

Nz(DMax("FieldName","TableNAme","TextFieldName = '" & Variable & "'"),1)

--
Good Luck
BS"D


efandango said:
Ofer,

Thanks for replying. with reference to that link; it say's

"This uses the DMAX function to find the highest EmployeeID value in table
[tblPracticeEmployeeData] and adds 1 to the value."

does this mean it will find the highest EmployeeID value across all
records?, if so, then I have a problem.

Because my form represents a form based quiz, based on multiple vehicle runs
(think bus routes) which are represented by a mainform Run_No linked to a
subform route. There are over 50 different runs, each one is essentially a
seperate quiz (route). I have everything working fine, as the user advances
through the main (Run) records; the Route Quiz appears on a Subform/Tab Page.
Except that I want to record the number of times the user has tried any given
quiz. So If i'm reading my opening comment right, I can only record 1
Quiz/Run_No?. Is that how you would see things?




:

Look at this link on "Automatically Increment a Fields Value in a database"

http://www.databasedev.co.uk/automatically_increment_value.html

What you need is the example of the function that they use to get the new
number

--
Good Luck
BS"D


:

I have a Continous SubForm on a Tab Page where I want to keep a running
record of how many times the form has been used. The Form is used as a quiz
answer form with the answers provided by another SubForm on the same Tab
Page. The idea is that I can show the user how many times they have attempted
the quiz. How would I build a control mechanism for this, I assume I would
need a seperate table to hold the incrementing number, I have this code on a
test button at the moment:

Forms.Runs.[WayPoint_Test_Register subform].Form.WayPoint_Test_Reg_No =
WayPoint_Test_Reg_No + 1

but I can’t seem to build a solution that increments, It never goes past
‘1’. I think I’m overlooking a fundemental point here, but can’t figure what
it is?...
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ofer,

To be really honest, your suggestion(s) has left me somewhat baffled, a
complete mental block on this problem.

So that I can understand the order of things, what do I have to do in order
of precedence?.



Place

Ofer Cohen said:
You can either edit the existing field in the table with the next max count
value, and then you can get this resault viewing the table

Or, add a new record for each entry to the form with the new count number
(incase you want to save the date and time of each entry), and then you get
the resault using a group by query

SELECT TableName.Run_No, Max(TableName.WayPoint_Test_Reg_No) AS MaxWayPoint
FROM TableName
GROUP BY TableName.Run_No

--
Good Luck
BS"D


efandango said:
Ofer,

Thanks for the feedback. For me, it's quite a bit to take in..., so before I
try and get my head round this. Will I be able to end up with a table or
query like this:


Run_No WayPoint_Test_Reg_No
1 6
2 8
3 13
19 5
24 1
25 9
26 101
27 32

Where [WayPoint_Test_Reg_No] is the number of times this particular
Form/Record has been opened/used.


Ofer Cohen said:
To the DMax you can add a criteria, so you can get the max number by any
group you would like

DMax("[FieldName","[TableName]","Criteria")

Something like:
Criteria for number
DMax("FieldName","TableNAme","NumberFieldName = " & Variable)

Criteria for text
DMax("FieldName","TableNAme","TextFieldName = '" & Variable & "'")

Criteria for both
DMax("FieldName","TableNAme","NumberFieldName = " & Variable & "
TextFieldName = '" & Variable & "'")

The variable can be a text box in the form
Me.[TextBoxName]

Also: Incase no match is found it will be better using the Mz function to
replace null with 1

Nz(DMax("FieldName","TableNAme","TextFieldName = '" & Variable & "'"),1)

--
Good Luck
BS"D


:

Ofer,

Thanks for replying. with reference to that link; it say's

"This uses the DMAX function to find the highest EmployeeID value in table
[tblPracticeEmployeeData] and adds 1 to the value."

does this mean it will find the highest EmployeeID value across all
records?, if so, then I have a problem.

Because my form represents a form based quiz, based on multiple vehicle runs
(think bus routes) which are represented by a mainform Run_No linked to a
subform route. There are over 50 different runs, each one is essentially a
seperate quiz (route). I have everything working fine, as the user advances
through the main (Run) records; the Route Quiz appears on a Subform/Tab Page.
Except that I want to record the number of times the user has tried any given
quiz. So If i'm reading my opening comment right, I can only record 1
Quiz/Run_No?. Is that how you would see things?




:

Look at this link on "Automatically Increment a Fields Value in a database"

http://www.databasedev.co.uk/automatically_increment_value.html

What you need is the example of the function that they use to get the new
number

--
Good Luck
BS"D


:

I have a Continous SubForm on a Tab Page where I want to keep a running
record of how many times the form has been used. The Form is used as a quiz
answer form with the answers provided by another SubForm on the same Tab
Page. The idea is that I can show the user how many times they have attempted
the quiz. How would I build a control mechanism for this, I assume I would
need a seperate table to hold the incrementing number, I have this code on a
test button at the moment:

Forms.Runs.[WayPoint_Test_Register subform].Form.WayPoint_Test_Reg_No =
WayPoint_Test_Reg_No + 1

but I can’t seem to build a solution that increments, It never goes past
‘1’. I think I’m overlooking a fundemental point here, but can’t figure what
it is?...
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pointing a specific line in a continuous form 1
Locking in a new record in a subform 1
Help with prefilling a form 2
Add Record to Subform using a Listbox 4
Problem creating form that selects tables and fields 2
Changing RecordSource of a subform 2
How to link form and subform w/ Combo 7
Add record then lock field 5

Top