473,287 Members | 1,629 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Blank record in subform

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
19 6912
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
"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
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
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
"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
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
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
>
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
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
"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
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
"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
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
"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
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
"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
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Peter Kleiner | last post by:
Greetings all, I have a database with two tables: docs and edocs. For each record in docs there can be zero to unlimited records in edocs. Both tables have an integer primary key named index. ...
4
by: John Baker | last post by:
Hi: I have a query which supports a form. Te form is used to edit, update and change records in the table the query is based on. It all works fine EXCEPT that the "New" record (blank updatable...
1
by: Tim Graichen | last post by:
Hello, I have a main form with one subform. The main form displays a record from TableOne (PCID) This is a corporate software tracking utility that works like this: Main form...Choose PCID...
1
by: Tim Graichen | last post by:
I have FrmMain that has one subform (SbForm). FrmMain is filled with records from Tbl1. SbForm is filled with records from Tbl2 based on the current selected record in FrmMain from Tbl1. ...
0
by: Jean | last post by:
Hello everybody. Can someone point out please what is wrong with my work? What I have so far: People take part in experiments, and a record is made in tblExperimentInstances. There are a number...
5
by: ChadDiesel | last post by:
My basic question is why does my print report button on my subform print a blank report when my cursor is on a blank entry line? Here is a more detailed explanation of my problem. I have a...
12
by: swingingming | last post by:
Hi, in the NorthWind sample database, when clicking on the next navigation button on the new order record with nothing on the subform (order details), we got an order with nothing ordered. How can...
4
by: sparks | last post by:
I am trying to fix a database that someone did about 4 yrs ago in access97. The main table just contains demographics and is on the main form of the database. It has a subform on a tab that...
5
by: jpatchak | last post by:
Hello, I'm hoping someone can help me. I have a mainform based on a table (let's call it tblMain) with a Primary Key of . There is a subform on this main form based on another table (let's call...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.