Continuous Forms blank recored at top

[Simon]

i have a Continuous Forms in access that hold lots of notes, The Blank
recored is alwasy at bottlom of liust, how do i make it stay at the
top then sort by date order, So blank one first then newest then
oldest at bottle

Thanks

 

[Tom van Stiphout]

On Sun, 28 Jun 2009 02:59:33 -0700 (PDT), Simon <[email protected]>
wrote:

That is virtually impossible.

-Tom.
Microsoft Access MVP

 

[BRE]

You can't do it simply with a bound form, but there are a couple of ways
you
could engineer something similar:

1. Create two continuous forms based on the table or query, the first of
which returns no rows by making its RecordSource property something like:

SELECT *
FROM MyTable
WHERE MyID = 0;

where MyID is a column, e.g. an autonumber, with a value of zero in no
row.
The second form would return all rows ordered by date descending, so its
RecordSource property would be along these lines:

SELECT *
FROM MyTable
ORDER By MyDate DESC;

Set the second form's AllowAdditions property to False. Embed both forms
as
subforms in an otherwise empty form, with the first one above the second,
and
of a depth which accommodates only one row.

In the AfterInsert event procedure of the first subform requery both
subforms:


Me.Requery
Me.Parent.SecondSubformControl.Requery

2. Alternatively use a single form in continuous forms view and in its
header include unbound controls in which to enter data for a new row.
Include a 'Save New Record' button in the header, and in its Click event
procedure insert a new row into the table with code and the requery the
form
and set all the unbound controls in the header to Null. The code to do
this
might, in a simplified form which assumes only two columns, be along the
following lines:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "INSERT INTO MyTable" & _
"(MyDate, MyNotes) " & _
"VALUES(#" & _
Format(Me.txtMyDate, "yyyy-mm-dd") & _
"#,""" & Me.txtMyNotes & """)"

cmd.CommandText = strSQL
cmd.Execute

Me.Requery
Me.txtMyDate = Null
Me.txtMyNotes = Null

Where txtMyDate and txtMyNotes are the unbound controls in the header
for
entering data into the columns MyDate and MyNotes in the table MyTable.
Formatting the date value when building the SQL statement, using the ISO
standard of YYYY-MM-DD, ensures that the value will be interpreted
correctly
regardless of the regional date format in use on the system.

Ken Sheridan
Stafford, England

 

[BRE]

You can't do it simply with a bound form, but there are a couple of ways
you
could engineer something similar:

1. Create two continuous forms based on the table or query, the first of
which returns no rows by making its RecordSource property something like:

SELECT *
FROM MyTable
WHERE MyID = 0;

where MyID is a column, e.g. an autonumber, with a value of zero in no
row.
The second form would return all rows ordered by date descending, so its
RecordSource property would be along these lines:

SELECT *
FROM MyTable
ORDER By MyDate DESC;

Set the second form's AllowAdditions property to False. Embed both forms
as
subforms in an otherwise empty form, with the first one above the second,
and
of a depth which accommodates only one row.

In the AfterInsert event procedure of the first subform requery both
subforms:


Me.Requery
Me.Parent.SecondSubformControl.Requery

2. Alternatively use a single form in continuous forms view and in its
header include unbound controls in which to enter data for a new row.
Include a 'Save New Record' button in the header, and in its Click event
procedure insert a new row into the table with code and the requery the
form
and set all the unbound controls in the header to Null. The code to do
this
might, in a simplified form which assumes only two columns, be along the
following lines:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "INSERT INTO MyTable" & _
"(MyDate, MyNotes) " & _
"VALUES(#" & _
Format(Me.txtMyDate, "yyyy-mm-dd") & _
"#,""" & Me.txtMyNotes & """)"

cmd.CommandText = strSQL
cmd.Execute

Me.Requery
Me.txtMyDate = Null
Me.txtMyNotes = Null

Where txtMyDate and txtMyNotes are the unbound controls in the header
for
entering data into the columns MyDate and MyNotes in the table MyTable.
Formatting the date value when building the SQL statement, using the ISO
standard of YYYY-MM-DD, ensures that the value will be interpreted
correctly
regardless of the regional date format in use on the system.

Ken Sheridan
Stafford, England

 

[Douglas J. Steele]

Actually, if you set the first form's DataEntry property to True, it won't
show any existing records, so you can just use a normal recordset.

 

[o;;]

i have a Continuous Forms in access that hold lots of notes, The Blank
recored is alwasy at bottlom of liust, how do i make it stay at the
top then sort by date order, So blank one first then newest then
oldest at bottle

Thanks

 

[o;;]

i have a Continuous Forms in access that hold lots of notes, The Blank
recored is alwasy at bottlom of liust, how do i make it stay at the
top then sort by date order, So blank one first then newest then
oldest at bottle

Thanks