Data Entry Form-need to show list of records entered

  • Thread starter Thread starter Lisa Jones
  • Start date Start date
L

Lisa Jones

Hello,
I am trying to create a form which will update a table as
the user fills in the fields.

In the footer of the form, I want the user to see the
datasheet, showing all the records they have entered
during a session. I've tried using a subform with the
original table as the datasource. But, when a record is
entered, the subform flickers the data for a second and
then goes blank, showing only the "autonumber" prompt.

What am I doing wrong?

Thanks,
Lisa
 
The following technique will work on Access 2000 or higher.

Create the form and subform as you have done already - both based on the
same table.

If you want the subform (datasheet) to be for information only - ie you
don't want the users to enter data via the datasheet then set the enabled
property of the datasheet control to "no" and the locked property to "yes".

In the open event of the main form put the following code:

Set Me.subDatasheet.Form.Recordset = Me.Recordset

(where subDatasheet is the name of the control containing the datasheet
subform).

In your current setup the main form and subform have probably been linked
via the primary key of the table. This means that the subform will only
every show one record - the same one as the main form. If the main form is
at a new record then the subform will also show a new record.
 
OK...how about Access 97? (Sorry I didn't specify.)

Instead of Recordset, it only gives me the option of
RecordsetTYPE or RecordsetCLONE. And I cannot make either
of those work, so I'm missing a statement somewhere.

Thanks,
Lisa
 
Right - this is a bit more difficult in Access 97 as it does not have a form
recordset object. I've tried to come up with a way of getting this to work
by a different method, but I can't do it! Perhaps someone else can help.

Sorry.
 
OK, I've thought about it a bit more. This is a method that might work, but
is not very elegant!

1. Remove the entries from "link child fields" and "link master fields" in
the subform control property sheet. If you do nothing else this will mean
that the subform will just show all the records in the table.

2. Create a function in a standard module to open the form:

Public Function OpenMyForm()

Dim strSQL As String
strSQL = "SELECT * FROM tblName WHERE 0 = 1"

DoCmd.OpenForm "MyForm", acNormal, , , , acHidden
With Forms!MyForm
.RecordSource = strSQL
.subDatasheet.Form.RecordSource = strSQL
.Visible = True
End With

End Function

(Change the names to match your form, subform control and table names).

3. Put this code in the form After Update event:

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
Dim strSQL As String

strSQL = "SELECT * FROM tblName WHERE "

With rst
.MoveFirst
Do Until .EOF
strSQL = strSQL & "PrimaryKeyName = " & !PrimaryKeyName & " OR "
.MoveNext
Loop
.Close
End With

strSQL = Left(strSQL, Len(strSQL) - 4)
Me.subDatasheet.Form.RecordSource = strSQL
'End of code

Use the function to open the form for a new record.

This probably needs some error checking.
 
Back
Top