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
19  6763 
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
"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.
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.
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?
"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.
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
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.
> 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?
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.
"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.
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.
"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.
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.
"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?
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?
"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.
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.
"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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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. ...
|
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...
|
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...
|
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.
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Kemmylinns12 |
last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
|
by: Oralloy |
last post by:
Hello Folks,
I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA.
My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
|
by: Carina712 |
last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
|
by: Ricardo de Mila |
last post by:
Dear people, good afternoon...
I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control.
Than I need to discover what...
|
by: Johno34 |
last post by:
I have this click event on my form. It speaks to a Datasheet Subform
Private Sub Command260_Click()
Dim r As DAO.Recordset
Set r = Form_frmABCD.Form.RecordsetClone
r.MoveFirst
Do
If...
|
by: DizelArs |
last post by:
Hi all)
Faced with a problem, element.click() event doesn't work in Safari browser.
Tried various tricks like emulating touch event through a function:
let clickEvent = new Event('click', {...
|
by: F22F35 |
last post by:
I am a newbie to Access (most programming for that matter). I need help in creating an Access database that keeps the history of each user in a database. For example, a user might have lesson 1 sent...
| |
