By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,377 Members | 3,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,377 IT Pros & Developers. It's quick & easy.

Blank record in subform

P: n/a
Hi all,

I am having trouble with access adding a blank record to a subform
everytime I finish entering data and closing the form. (The form and
subform are based on the one table)

When entering data into the subform I get the pencil icon to show data
addition, but underneath the record I am inputting data the record is
blank with a star(*)icon and this is the record(blank)that always gets
saved with all the records that actually have data.

I know that there must be something simple that I am overlooking, but
for the life of me cannot work it out.

Thanking all you geniuses out there in advance.

Joe
Nov 12 '05 #1
Share this Question
Share on Google+
19 Replies


P: n/a
Your form will always have the new record row (star icon) if new records can
be added. The presence of this line alone will not generate blank records.
There must be something that is dirtying the new record before it will write
another record to your table. Is there code in the Current event of the
form, for example?

Open your table in design view, and choose a field that you could mark
Required (lower pane). This will at least stop Access entering a completely
blank row, and may help you identify where the problem is occurring.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joe Scully" <ve***@whoever.com> wrote in message
news:11**************************@posting.google.c om...

I am having trouble with access adding a blank record to a subform
everytime I finish entering data and closing the form. (The form and
subform are based on the one table)

When entering data into the subform I get the pencil icon to show data
addition, but underneath the record I am inputting data the record is
blank with a star(*)icon and this is the record(blank)that always gets
saved with all the records that actually have data.

I know that there must be something simple that I am overlooking, but
for the life of me cannot work it out.

Thanking all you geniuses out there in advance.

Joe

Nov 12 '05 #2

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>...
Your form will always have the new record row (star icon) if new records can
be added. The presence of this line alone will not generate blank records.
There must be something that is dirtying the new record before it will write
another record to your table. Is there code in the Current event of the
form, for example?

Open your table in design view, and choose a field that you could mark
Required (lower pane). This will at least stop Access entering a completely
blank row, and may help you identify where the problem is occurring.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


No, there is no code in the Current event of the form, I just built a
straightforward Form/Subform based on a single table.

I tried what you suggested and one by one changed each field to
required and retried the form but to no avail, the form kept on saving
blank records, until I came across one field the gives me a message
box "The field cannot contain a null value because the required
property of this field is set to True. Enter a value in this field.

When I try to enter a value the message box just keeps on popping up.
I'm at a complete loss.
Nov 12 '05 #3

P: n/a
What is in the RecordSource property of your form?
Is it the name of a table, or a query?

If it's a query, how many tables are in the query? Could you change it to a
table (temporarily)?

Access should not be able to save the records while the Required fields are
null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joe Scully" <ve***@whoever.com> wrote in message
news:11**************************@posting.google.c om...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...
Your form will always have the new record row (star icon) if new records can be added. The presence of this line alone will not generate blank records. There must be something that is dirtying the new record before it will write another record to your table. Is there code in the Current event of the
form, for example?

Open your table in design view, and choose a field that you could mark
Required (lower pane). This will at least stop Access entering a completely blank row, and may help you identify where the problem is occurring.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


No, there is no code in the Current event of the form, I just built a
straightforward Form/Subform based on a single table.

I tried what you suggested and one by one changed each field to
required and retried the form but to no avail, the form kept on saving
blank records, until I came across one field the gives me a message
box "The field cannot contain a null value because the required
property of this field is set to True. Enter a value in this field.

When I try to enter a value the message box just keeps on popping up.
I'm at a complete loss.

Nov 12 '05 #4

P: n/a
Joe Scully wrote:
No, there is no code in the Current event of the form, I just built a
straightforward Form/Subform based on a single table.


How about the Before/After Update events in either main or sub forms?

Are any functions or subroutines called?

Is there data in the main form or do you simply hit the subform record first?
Nov 12 '05 #5

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>...
What is in the RecordSource property of your form?
Is it the name of a table, or a query?

If it's a query, how many tables are in the query? Could you change it to a
table (temporarily)?

Access should not be able to save the records while the Required fields are
null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


The record source of the form is a table, I tried everything I can
think of but access is still saving blank records.

Heres the strange part, I've rebuild the database from scratch, just
incase there was something else going on that I was not aware of, but
Access still keeps on saving blank records.
Nov 12 '05 #6

P: n/a
Salad <oi*@vinegar.com> wrote in message news:<3F***************@vinegar.com>...
Joe Scully wrote:
No, there is no code in the Current event of the form, I just built a
straightforward Form/Subform based on a single table.


How about the Before/After Update events in either main or sub forms?

Are any functions or subroutines called?

Is there data in the main form or do you simply hit the subform record first?

There are no events at all in either of the forms and no functions or
subroutines are called.

There data in the Main form, I'll give you the rundown:

Main form:
Date
Shift
Crew

Subform:
Truck
Excavator
Source
RL
Material
Destination
Loads
Nov 12 '05 #7

P: n/a
Joe Scully wrote:
Salad <oi*@vinegar.com> wrote in message news:<3F***************@vinegar.com>...
Joe Scully wrote:
No, there is no code in the Current event of the form, I just built a
straightforward Form/Subform based on a single table.


How about the Before/After Update events in either main or sub forms?

Are any functions or subroutines called?

Is there data in the main form or do you simply hit the subform record first?


There are no events at all in either of the forms and no functions or
subroutines are called.

There data in the Main form, I'll give you the rundown:

Main form:
Date
Shift
Crew

Subform:
Truck
Excavator
Source
RL
Material
Destination
Loads


I would kill off your forms and recreate them. It sounds like it'd take no time at
all to recreate them.

I would also look at the tables and see where the fields are required.
Nov 12 '05 #8

P: n/a
>
I would kill off your forms and recreate them. It sounds like it'd take no time at
all to recreate them.

I would also look at the tables and see where the fields are required.

Okay, I've done that and now after data entry the subform is still
saving blank records but the mainform is now saving the date shift and
crew.

Any ideas?
Nov 12 '05 #9

P: n/a
I take it your main form is now working correctly, but the subform is still
saving blank records?

Open the table that the *subform* is based on, and mark some of its fields
Required. You probably have a foreign key field - a number field that links
to the primary key of the main form's table (like the OrderID field in
Northwind's OrderDetail table). Take special note of this field: mark it
Required, and also delete anything in its Default Value. (That property
defaults to zero for numeric fields, and you don't want a zero in the
foreign key.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joe Scully" <ve***@whoever.com> wrote in message
news:11*************************@posting.google.co m...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...
What is in the RecordSource property of your form?
Is it the name of a table, or a query?

If it's a query, how many tables are in the query? Could you change it to a table (temporarily)?

Access should not be able to save the records while the Required fields are null.


The record source of the form is a table, I tried everything I can
think of but access is still saving blank records.

Heres the strange part, I've rebuild the database from scratch, just
incase there was something else going on that I was not aware of, but
Access still keeps on saving blank records.

Nov 12 '05 #10

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>...
I take it your main form is now working correctly, but the subform is still
saving blank records?

Open the table that the *subform* is based on, and mark some of its fields
Required. You probably have a foreign key field - a number field that links
to the primary key of the main form's table (like the OrderID field in
Northwind's OrderDetail table). Take special note of this field: mark it
Required, and also delete anything in its Default Value. (That property
defaults to zero for numeric fields, and you don't want a zero in the
foreign key.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I checked the foreign key and marked it as required (there was nothing
in its default value) still no difference.

The main form is saving the Date, Shift and Crew (when it shouldn't as
the record indicator is an arrow in the last record) and the Subform
is still saving blank records.

I went and rebuilt the database again but this time without comboboxes
and it works a treat.
(Maybe I should have mentioned it before because they seem to be
causing the problem).

I then went and added combo boxes one at a time for shift, crew and
machine (the machine one on the subform is the only one that doesn't
cause any problems) it seems to be that the two comboboxes in the
mainform that are causing the problems. Ie adding data when there no
data in the subform.
Nov 12 '05 #11

P: n/a
Good. You have narrowed this down to the combo boxes.
Are you doing anything that is setting the value of the combos?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joe Scully" <ve***@whoever.com> wrote in message
news:11*************************@posting.google.co m...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...
I take it your main form is now working correctly, but the subform is still saving blank records?

Open the table that the *subform* is based on, and mark some of its fields Required. You probably have a foreign key field - a number field that links to the primary key of the main form's table (like the OrderID field in
Northwind's OrderDetail table). Take special note of this field: mark it
Required, and also delete anything in its Default Value. (That property
defaults to zero for numeric fields, and you don't want a zero in the
foreign key.)

I checked the foreign key and marked it as required (there was nothing
in its default value). still no difference.

The main form is saving the Date, Shift and Crew (when it shouldn't as
the record indicator is an arrow in the last record) and the Subform
is still saving blank records.

I went and rebuilt the database again but this time without comboboxes
and it works a treat.
(Maybe I should have mentioned it before because they seem to be
causing the problem).

I then went and added combo boxes one at a time for shift, crew and
machine (the machine one on the subform is the only one that doesn't
cause any problems) it seems to be that the two comboboxes in the
mainform that are causing the problems. Ie adding data when there no
data in the subform.

Nov 12 '05 #12

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>...
Good. You have narrowed this down to the combo boxes.
Are you doing anything that is setting the value of the combos?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

The only thing that I am doing to the comboboxes is setting the value
of the Row source Type and Row source

ie. Row Source Type = Value list
Row Source = (The data I want in the combobox) ie Day;Night

If I do not enter any data into the Row Source everything works as it
should (no blank records)so the problem appears to be the Row source.
Nov 12 '05 #13

P: n/a
Joe, I've never tried dynamically reassigning the RowSourceType of a combo
on the fly. Is this really necessary?

Assigning items to the RowSource should not cause a record to insert, but
you will need to use the correct delimiters within and around the string.

There must be something setting the value of the combo (or of something)
somewhere.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joe Scully" <ve***@whoever.com> wrote in message
news:11*************************@posting.google.co m...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...
Good. You have narrowed this down to the combo boxes.
Are you doing anything that is setting the value of the combos?


The only thing that I am doing to the comboboxes is setting the value
of the Row source Type and Row source

ie. Row Source Type = Value list
Row Source = (The data I want in the combobox) ie Day;Night

If I do not enter any data into the Row Source everything works as it
should (no blank records)so the problem appears to be the Row source.

Nov 12 '05 #14

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>...
Joe, I've never tried dynamically reassigning the RowSourceType of a combo
on the fly. Is this really necessary?

Assigning items to the RowSource should not cause a record to insert, but
you will need to use the correct delimiters within and around the string.

There must be something setting the value of the combo (or of something)
somewhere.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


Its not necessary to reassign on the fly but it was the only way to
narrow down the problem as the lookup wizard in table design view was
causing the same problem.

I used the correct delimiters within and around the string but didn't
type them in the thread - sorry.

I've had a good lood around for anything that could possibly set the
value of the combobox, but cannot find a thing, so I have quickly
built up a couple of new databases from scratch using combos on the
main and sub forms and still have the same problem.

I'm starting to wonder, is it possible to use comboboxes for data
entry in forms without having blank records saved?

Or could there be a setting inside of Access that is causing this type
of behaviour?
Nov 12 '05 #15

P: n/a
Combo boxes do not cause blank records.
Required fields prevent blank records.
The Form_BeforeUpdate event should let you programmatically trap blank
records.

Not sure what else to suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Joe Scully" <ve***@whoever.com> wrote in message
news:11*************************@posting.google.co m...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...
Joe, I've never tried dynamically reassigning the RowSourceType of a combo on the fly. Is this really necessary?

Assigning items to the RowSource should not cause a record to insert, but you will need to use the correct delimiters within and around the string.
There must be something setting the value of the combo (or of something)
somewhere.

Its not necessary to reassign on the fly but it was the only way to
narrow down the problem as the lookup wizard in table design view was
causing the same problem.

I used the correct delimiters within and around the string but didn't
type them in the thread - sorry.

I've had a good lood around for anything that could possibly set the
value of the combobox, but cannot find a thing, so I have quickly
built up a couple of new databases from scratch using combos on the
main and sub forms and still have the same problem.

I'm starting to wonder, is it possible to use comboboxes for data
entry in forms without having blank records saved?

Or could there be a setting inside of Access that is causing this type
of behaviour?

Nov 12 '05 #16

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>...
Combo boxes do not cause blank records.
Required fields prevent blank records.
The Form_BeforeUpdate event should let you programmatically trap blank
records.

Not sure what else to suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Joe Scully" <ve***@whoever.com> wrote in message
news:11*************************@posting.google.co m...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...

Thanks for sticking with me on this I appreciate it.

Could I ask of you just two last things:

How do I programmatically trap blank errors?

And secondly do you know of any good tutorials on VBA programming?

Ive just started learning VB, but find VBA a completely different
animal with properties and methods unique to Access.
Nov 12 '05 #17

P: n/a
If the record is being generated in a form, use the BeforeUpdate event of
that form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SomeField) Then
If MsgBox("Save a record with SomeField blank?",
vbYesNo+vbDefaultButton2) = vbNo Then
Cancel = True
Me.Undo
End If
End If
End Sub

As for learning how to use VBA in Access, the best would be Getz et at "MS
Access xx Developers Handbook" published by Sybex, where xx is the version
number. Big. Expensive. Worth it. Volume 1 will do.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joe Scully" <ve***@whoever.com> wrote in message
news:11**************************@posting.google.c om...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...
Combo boxes do not cause blank records.
Required fields prevent blank records.
The Form_BeforeUpdate event should let you programmatically trap blank
records.

Not sure what else to suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Joe Scully" <ve***@whoever.com> wrote in message
news:11*************************@posting.google.co m...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...

Thanks for sticking with me on this I appreciate it.

Could I ask of you just two last things:

How do I programmatically trap blank errors?

And secondly do you know of any good tutorials on VBA programming?

Ive just started learning VB, but find VBA a completely different
animal with properties and methods unique to Access.

Nov 12 '05 #18

P: n/a
"Joe Scully" wrote
And secondly do you know of any good
tutorials on VBA programming?

Ive just started learning VB, but find VBA
a completely different animal with properties
and methods unique to Access.


VB and VBA are almost identical. It is the object model that is so different
between VB and Access, and their object models are, indeed, quite different.
It is difficult, when starting out, to differentiate between the object
model being manipulated and the code that is manipulating it.

Allen has recommended the Developer's Handbook, by Getz. I do _not_ agree
that this is a good tutorial for learning VBA; it is a great reference and
resource for the developer, however. I have a copy of every edition of this
book, and wouldn't part with any of them, from Access 2.0 through Access
2002 (I don't believe the Access 2003 edition is available, yet).

A book that I find good for "power users moving to developer level" is Rick
Dobson's "Programming Access 2000" or "Programming Access 2002" by Microsoft
Press. I haven't reviewed the Access 2003 edition. There are several other
books that I have seen which are aimed at the beginning Access VBA
programmer that would likely be suitable, too. And, Getz is also co-author
of books called the "VBA Developer's Handbook" which is another good
reference/resource for developers.

Larry Linson
Microsoft Access MVP
Nov 12 '05 #19

P: n/a
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Larry Linson" <bo*****@localhost.not> wrote in message news:<mF*****************@nwrddc02.gnilink.net>...


Thanks Allen and Larry.
Nov 12 '05 #20

This discussion thread is closed

Replies have been disabled for this discussion.