Trying to set textbox value in continuous form

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All

I have a form that opens in Continuous Forms view. In the detail section is
a textbox called [stmt_datee], and in the form header is a textbox called
[recs] with control source =Count([ID1]) (ID1 is the key field from the
underlying table, so [recs] is a reliable count of the number of records
being displayed - which the user will regularly alter by various filtering).

I want to create a button that the user can click to set the value of
[stmt_datee] for each of the currently displayed records. I have come up
with the code below, but this doesn't work: sometimes it just sets the
[stmt_datee] value in the first record, and sometimes I get a "Cannot go to
the specified record" message - even when there are, say, 4 records
displayed.

I can't for the life of me see what I've done wrong: any help would be very
much appreciated!!

Thanks
Leslie Isaacs


My code:

Private Sub Command37_click()

Dim sdate As Date
Dim inc As Integer

sdate = InputBox("Enter statement date")

DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate

For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc

End Sub
 
Hello All

I have a form that opens in Continuous Forms view. In the detail section is
a textbox called [stmt_datee], and in the form header is a textbox called
[recs] with control source =Count([ID1]) (ID1 is the key field from the
underlying table, so [recs] is a reliable count of the number of records
being displayed - which the user will regularly alter by various filtering).

I want to create a button that the user can click to set the value of
[stmt_datee] for each of the currently displayed records. I have come up
with the code below, but this doesn't work: sometimes it just sets the
[stmt_datee] value in the first record, and sometimes I get a "Cannot go to
the specified record" message - even when there are, say, 4 records
displayed.

I can't for the life of me see what I've done wrong: any help would be very
much appreciated!!

Thanks
Leslie Isaacs


My code:

Private Sub Command37_click()

Dim sdate As Date
Dim inc As Integer

sdate = InputBox("Enter statement date")

DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate

For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc

End Sub
Click to expand...
I think it is best to start back at the beginning and explain your table(s)
structure including table name(s) and all column names and data types. Then
explain what you are trying to accomplish.

It sounds like you have a table with a column which is null for every row. Now
you want to select a set of rows and change the null column in all the selected
rows to some value.

Possible create a query string to update all rows meeting a filter criteria.
Then execute the update query and requery the form.
 
Hello Michael

Thanks for your reply.

Where you say "It sounds like ....", you are exactly right!

I don't think that knowing the table name (it's [payments]) and all field
names and data types (there are lots of them!) will be necessary - or even
helpful - in determining what is wrong with my code. In fact, once I get the
code working, I would want to use it on various forms - obviously amending
the various field names as appropriate for each form.

If you can help me find out why my code isn't working I really would be very
grateful.

Thanks again

Les



Michael Gramelspacher said:
Hello All

I have a form that opens in Continuous Forms view. In the detail section
is
a textbox called [stmt_datee], and in the form header is a textbox called
[recs] with control source =Count([ID1]) (ID1 is the key field from the
underlying table, so [recs] is a reliable count of the number of records
being displayed - which the user will regularly alter by various
filtering).

I want to create a button that the user can click to set the value of
[stmt_datee] for each of the currently displayed records. I have come up
with the code below, but this doesn't work: sometimes it just sets the
[stmt_datee] value in the first record, and sometimes I get a "Cannot go
to
the specified record" message - even when there are, say, 4 records
displayed.

I can't for the life of me see what I've done wrong: any help would be
very
much appreciated!!

Thanks
Leslie Isaacs


My code:

Private Sub Command37_click()

Dim sdate As Date
Dim inc As Integer

sdate = InputBox("Enter statement date")

DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate

For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc

End Sub
Click to expand...
I think it is best to start back at the beginning and explain your
table(s)
structure including table name(s) and all column names and data types.
Then
explain what you are trying to accomplish.

It sounds like you have a table with a column which is null for every row.
Now
you want to select a set of rows and change the null column in all the
selected
rows to some value.

Possible create a query string to update all rows meeting a filter
criteria.
Then execute the update query and requery the form.
Click to expand...
 
Hello Michael

Thanks for your reply.

Where you say "It sounds like ....", you are exactly right!

I don't think that knowing the table name (it's [payments]) and all field
names and data types (there are lots of them!) will be necessary - or even
helpful - in determining what is wrong with my code. In fact, once I get the
code working, I would want to use it on various forms - obviously amending
the various field names as appropriate for each form.

If you can help me find out why my code isn't working I really would be very
grateful.

Thanks again

Les



Michael Gramelspacher said:
Hello All

I have a form that opens in Continuous Forms view. In the detail section
is
a textbox called [stmt_datee], and in the form header is a textbox called
[recs] with control source =Count([ID1]) (ID1 is the key field from the
underlying table, so [recs] is a reliable count of the number of records
being displayed - which the user will regularly alter by various
filtering).

I want to create a button that the user can click to set the value of
[stmt_datee] for each of the currently displayed records. I have come up
with the code below, but this doesn't work: sometimes it just sets the
[stmt_datee] value in the first record, and sometimes I get a "Cannot go
to
the specified record" message - even when there are, say, 4 records
displayed.

I can't for the life of me see what I've done wrong: any help would be
very
much appreciated!!

Thanks
Leslie Isaacs


My code:

Private Sub Command37_click()

Dim sdate As Date
Dim inc As Integer

sdate = InputBox("Enter statement date")

DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate

For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc

End Sub
Click to expand...
I think it is best to start back at the beginning and explain your
table(s)
structure including table name(s) and all column names and data types.
Then
explain what you are trying to accomplish.

It sounds like you have a table with a column which is null for every row.
Now
you want to select a set of rows and change the null column in all the
selected
rows to some value.

Possible create a query string to update all rows meeting a filter
criteria.
Then execute the update query and requery the form.
Click to expand...
Click to expand...

As I see it, the set of records displayed in your continuous form is represented
by an underlying query (recordset). Setting a field in all these records to a
certain value simply amounts to executing an update query and requerying the
form to show the updated records.

Perhaps, I just do not get what you want to do. Maybe you can start a new
thread and see if others respond.
 
Hello Michael

Thanks for your further reply.

The 'problem' is that the user will further amend the recordset by
implemeting various filters on top of the initial recordset (by doing some
filter-by-selections'), and it is only this final recordset that should be
affected by the data-update.

Hope that help explain it better!

Thanks again
Les




Michael Gramelspacher said:
Hello Michael

Thanks for your reply.

Where you say "It sounds like ....", you are exactly right!

I don't think that knowing the table name (it's [payments]) and all field
names and data types (there are lots of them!) will be necessary - or even
helpful - in determining what is wrong with my code. In fact, once I get
the
code working, I would want to use it on various forms - obviously amending
the various field names as appropriate for each form.

If you can help me find out why my code isn't working I really would be
very
grateful.

Thanks again

Les



Michael Gramelspacher said:
On Fri, 30 May 2008 14:17:35 +0100, "Leslie Isaacs"

Hello All

I have a form that opens in Continuous Forms view. In the detail section
is
a textbox called [stmt_datee], and in the form header is a textbox
called
[recs] with control source =Count([ID1]) (ID1 is the key field from the
underlying table, so [recs] is a reliable count of the number of records
being displayed - which the user will regularly alter by various
filtering).

I want to create a button that the user can click to set the value of
[stmt_datee] for each of the currently displayed records. I have come up
with the code below, but this doesn't work: sometimes it just sets the
[stmt_datee] value in the first record, and sometimes I get a "Cannot go
to
the specified record" message - even when there are, say, 4 records
displayed.

I can't for the life of me see what I've done wrong: any help would be
very
much appreciated!!

Thanks
Leslie Isaacs


My code:

Private Sub Command37_click()

Dim sdate As Date
Dim inc As Integer

sdate = InputBox("Enter statement date")

DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate

For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc

End Sub


I think it is best to start back at the beginning and explain your
table(s)
structure including table name(s) and all column names and data types.
Then
explain what you are trying to accomplish.

It sounds like you have a table with a column which is null for every
row.
Now
you want to select a set of rows and change the null column in all the
selected
rows to some value.

Possible create a query string to update all rows meeting a filter
criteria.
Then execute the update query and requery the form.
Click to expand...
Click to expand...

As I see it, the set of records displayed in your continuous form is
represented
by an underlying query (recordset). Setting a field in all these records
to a
certain value simply amounts to executing an update query and requerying
the
form to show the updated records.

Perhaps, I just do not get what you want to do. Maybe you can start a new
thread and see if others respond.
Click to expand...
 
Hello Michael

Thanks for your further reply.

The 'problem' is that the user will further amend the recordset by
implemeting various filters on top of the initial recordset (by doing some
filter-by-selections'), and it is only this final recordset that should be
affected by the data-update.

Hope that help explain it better!

Thanks again
Les




Michael Gramelspacher said:
Hello Michael

Thanks for your reply.

Where you say "It sounds like ....", you are exactly right!

I don't think that knowing the table name (it's [payments]) and all field
names and data types (there are lots of them!) will be necessary - or even
helpful - in determining what is wrong with my code. In fact, once I get
the
code working, I would want to use it on various forms - obviously amending
the various field names as appropriate for each form.

If you can help me find out why my code isn't working I really would be
very
grateful.

Thanks again

Les



On Fri, 30 May 2008 14:17:35 +0100, "Leslie Isaacs"

Hello All

I have a form that opens in Continuous Forms view. In the detail section
is
a textbox called [stmt_datee], and in the form header is a textbox
called
[recs] with control source =Count([ID1]) (ID1 is the key field from the
underlying table, so [recs] is a reliable count of the number of records
being displayed - which the user will regularly alter by various
filtering).

I want to create a button that the user can click to set the value of
[stmt_datee] for each of the currently displayed records. I have come up
with the code below, but this doesn't work: sometimes it just sets the
[stmt_datee] value in the first record, and sometimes I get a "Cannot go
to
the specified record" message - even when there are, say, 4 records
displayed.

I can't for the life of me see what I've done wrong: any help would be
very
much appreciated!!

Thanks
Leslie Isaacs


My code:

Private Sub Command37_click()

Dim sdate As Date
Dim inc As Integer

sdate = InputBox("Enter statement date")

DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate

For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc

End Sub


I think it is best to start back at the beginning and explain your
table(s)
structure including table name(s) and all column names and data types.
Then
explain what you are trying to accomplish.

It sounds like you have a table with a column which is null for every
row.
Now
you want to select a set of rows and change the null column in all the
selected
rows to some value.

Possible create a query string to update all rows meeting a filter
criteria.
Then execute the update query and requery the form.
Click to expand...

As I see it, the set of records displayed in your continuous form is
represented
by an underlying query (recordset). Setting a field in all these records
to a
certain value simply amounts to executing an update query and requerying
the
form to show the updated records.

Perhaps, I just do not get what you want to do. Maybe you can start a new
thread and see if others respond.
Click to expand...
Click to expand...

Les,

You seem to think that a recordset which has a filter applied is something more
than simply a query.

Initial recordset: SELECT * FROM SomeTable;

Filtered recordset: SELECT * FROM SomeTable WHERE some_code = 'B';

UPDATE SomeTable SET some_date = Now() WHERE some_code="B";
 
Hello Michael

Thanks again for your further reply.

I do understand that a filtered recordset is 'just another recordset' -
which could obviously be built with a query. My difficulty was - is -
building the query that would generate the same recordset that the user had
arrived at by doing a few filter-by-selections. If I knew how to build a
query that would somehow 'copy' the form's ultimate filter, my problem would
be solved!

Does that make sense?
Les


Michael Gramelspacher said:
Hello Michael

Thanks for your further reply.

The 'problem' is that the user will further amend the recordset by
implemeting various filters on top of the initial recordset (by doing some
filter-by-selections'), and it is only this final recordset that should be
affected by the data-update.

Hope that help explain it better!

Thanks again
Les




Michael Gramelspacher said:
On Mon, 2 Jun 2008 09:42:22 +0100, "Leslie Isaacs"
<[email protected]>
wrote:

Hello Michael

Thanks for your reply.

Where you say "It sounds like ....", you are exactly right!

I don't think that knowing the table name (it's [payments]) and all
field
names and data types (there are lots of them!) will be necessary - or
even
helpful - in determining what is wrong with my code. In fact, once I get
the
code working, I would want to use it on various forms - obviously
amending
the various field names as appropriate for each form.

If you can help me find out why my code isn't working I really would be
very
grateful.

Thanks again

Les



On Fri, 30 May 2008 14:17:35 +0100, "Leslie Isaacs"

Hello All

I have a form that opens in Continuous Forms view. In the detail
section
is
a textbox called [stmt_datee], and in the form header is a textbox
called
[recs] with control source =Count([ID1]) (ID1 is the key field from
the
underlying table, so [recs] is a reliable count of the number of
records
being displayed - which the user will regularly alter by various
filtering).

I want to create a button that the user can click to set the value of
[stmt_datee] for each of the currently displayed records. I have come
up
with the code below, but this doesn't work: sometimes it just sets the
[stmt_datee] value in the first record, and sometimes I get a "Cannot
go
to
the specified record" message - even when there are, say, 4 records
displayed.

I can't for the life of me see what I've done wrong: any help would be
very
much appreciated!!

Thanks
Leslie Isaacs


My code:

Private Sub Command37_click()

Dim sdate As Date
Dim inc As Integer

sdate = InputBox("Enter statement date")

DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate

For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc

End Sub


I think it is best to start back at the beginning and explain your
table(s)
structure including table name(s) and all column names and data types.
Then
explain what you are trying to accomplish.

It sounds like you have a table with a column which is null for every
row.
Now
you want to select a set of rows and change the null column in all the
selected
rows to some value.

Possible create a query string to update all rows meeting a filter
criteria.
Then execute the update query and requery the form.



As I see it, the set of records displayed in your continuous form is
represented
by an underlying query (recordset). Setting a field in all these
records
to a
certain value simply amounts to executing an update query and requerying
the
form to show the updated records.

Perhaps, I just do not get what you want to do. Maybe you can start a
new
thread and see if others respond.
Click to expand...
Click to expand...

Les,

You seem to think that a recordset which has a filter applied is something
more
than simply a query.

Initial recordset: SELECT * FROM SomeTable;

Filtered recordset: SELECT * FROM SomeTable WHERE some_code = 'B';

UPDATE SomeTable SET some_date = Now() WHERE some_code="B";
Click to expand...
 
Hello Michael

Thanks again for your further reply.

I do understand that a filtered recordset is 'just another recordset' -
which could obviously be built with a query. My difficulty was - is -
building the query that would generate the same recordset that the user had
arrived at by doing a few filter-by-selections. If I knew how to build a
query that would somehow 'copy' the form's ultimate filter, my problem would
be solved!

Does that make sense?
Les


Michael Gramelspacher said:
Hello Michael

Thanks for your further reply.

The 'problem' is that the user will further amend the recordset by
implemeting various filters on top of the initial recordset (by doing some
filter-by-selections'), and it is only this final recordset that should be
affected by the data-update.

Hope that help explain it better!

Thanks again
Les




On Mon, 2 Jun 2008 09:42:22 +0100, "Leslie Isaacs"
<[email protected]>
wrote:

Hello Michael

Thanks for your reply.

Where you say "It sounds like ....", you are exactly right!

I don't think that knowing the table name (it's [payments]) and all
field
names and data types (there are lots of them!) will be necessary - or
even
helpful - in determining what is wrong with my code. In fact, once I get
the
code working, I would want to use it on various forms - obviously
amending
the various field names as appropriate for each form.

If you can help me find out why my code isn't working I really would be
very
grateful.

Thanks again

Les



On Fri, 30 May 2008 14:17:35 +0100, "Leslie Isaacs"

Hello All

I have a form that opens in Continuous Forms view. In the detail
section
is
a textbox called [stmt_datee], and in the form header is a textbox
called
[recs] with control source =Count([ID1]) (ID1 is the key field from
the
underlying table, so [recs] is a reliable count of the number of
records
being displayed - which the user will regularly alter by various
filtering).

I want to create a button that the user can click to set the value of
[stmt_datee] for each of the currently displayed records. I have come
up
with the code below, but this doesn't work: sometimes it just sets the
[stmt_datee] value in the first record, and sometimes I get a "Cannot
go
to
the specified record" message - even when there are, say, 4 records
displayed.

I can't for the life of me see what I've done wrong: any help would be
very
much appreciated!!

Thanks
Leslie Isaacs


My code:

Private Sub Command37_click()

Dim sdate As Date
Dim inc As Integer

sdate = InputBox("Enter statement date")

DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate

For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc

End Sub


I think it is best to start back at the beginning and explain your
table(s)
structure including table name(s) and all column names and data types.
Then
explain what you are trying to accomplish.

It sounds like you have a table with a column which is null for every
row.
Now
you want to select a set of rows and change the null column in all the
selected
rows to some value.

Possible create a query string to update all rows meeting a filter
criteria.
Then execute the update query and requery the form.



As I see it, the set of records displayed in your continuous form is
represented
by an underlying query (recordset). Setting a field in all these
records
to a
certain value simply amounts to executing an update query and requerying
the
form to show the updated records.

Perhaps, I just do not get what you want to do. Maybe you can start a
new
thread and see if others respond.
Click to expand...

Les,

You seem to think that a recordset which has a filter applied is something
more
than simply a query.

Initial recordset: SELECT * FROM SomeTable;

Filtered recordset: SELECT * FROM SomeTable WHERE some_code = 'B';

UPDATE SomeTable SET some_date = Now() WHERE some_code="B";
Click to expand...
Click to expand...

maybe something like this for the command onclick event: (this is just an
example since I have no real table, column and control names.)

Private Sub cmdSomething_Click()

If (Me.FilterOn = True) Then

Dim s As String

s = "UPDATE " & Me.RecordSource & " Set some_date = Date() " & _
" WHERE " & Me.Filter & ";"

debug.print s ' just for testing

' remove single quote when satisfied SQL string is correct
' CurrentDb.Execute s, dbFailOnError

' same goes here
Me.Requery

End If
End Sub

also:

Private Sub Form_Current()

Me.cmdSomething.Visible = (Me.FilterOn = True)

End Sub
 
Back
Top