A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Adding new records via recordset



 
 
Thread Tools Display Modes
  #1  
Old August 20th, 2009, 05:35 PM posted to microsoft.public.access.gettingstarted
ssutton503
external usenet poster
 
Posts: 6
Default Adding new records via recordset

I want to create a form for the user to input some new jobs but I don't want
the form bound to a table (as I originally did). I don't like the fact that
just opening the form creates a new record in the table even if the user
never enters anything. What I'm thinking should happen is when the form opens
it creates a recordset, the new entries get added to the recordset, then the
user can choose to add the records from the recordset to the actual table
when they get done. Only problem is, I don't know how to do that. I know how
to create a recordset from an existing table but that seems a bit silly to
load the entire table when I just need the database structure for the
recordset. Can I just create a recordset, add the user's entries to the
recordset, then loop through the recordset and add the new records to the
table with and INSERT statement? Or am I just going at this the hard way? Any
suggestions would be greatly appreciated.
  #2  
Old August 20th, 2009, 06:47 PM posted to microsoft.public.access.gettingstarted
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Adding new records via recordset

I don't like the fact that
just opening the form creates a new record in the table even if the user
never enters anything.


Forms do not do that unless you have a code or a macro doing something very
strange.

Do a google search on Access Unbound Forms. They can do what you want, but
are a LOT of trouble. It would be easier to revisit how bogus records are
being added to your tables and fix that.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"ssutton503" wrote:

I want to create a form for the user to input some new jobs but I don't want
the form bound to a table (as I originally did). I don't like the fact that
just opening the form creates a new record in the table even if the user
never enters anything. What I'm thinking should happen is when the form opens
it creates a recordset, the new entries get added to the recordset, then the
user can choose to add the records from the recordset to the actual table
when they get done. Only problem is, I don't know how to do that. I know how
to create a recordset from an existing table but that seems a bit silly to
load the entire table when I just need the database structure for the
recordset. Can I just create a recordset, add the user's entries to the
recordset, then loop through the recordset and add the new records to the
table with and INSERT statement? Or am I just going at this the hard way? Any
suggestions would be greatly appreciated.

  #3  
Old August 20th, 2009, 08:22 PM posted to microsoft.public.access.gettingstarted
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Adding new records via recordset

am I just going at this the hard way?

Yes

just opening the form creates a new record in the table even if the user
never enters anything.

That's not correct! Something has to be entered before anything (a new
record) is saved.

Using a form to enter records is the correct way. If you want to check
before saving each new record, put code in the form's BeforeUpdate event
asking the user if he wants to save what he just entered and Cancel if he
says No.

Steve


"ssutton503" wrote in message
...
I want to create a form for the user to input some new jobs but I don't
want
the form bound to a table (as I originally did). I don't like the fact
that
just opening the form creates a new record in the table even if the user
never enters anything. What I'm thinking should happen is when the form
opens
it creates a recordset, the new entries get added to the recordset, then
the
user can choose to add the records from the recordset to the actual table
when they get done. Only problem is, I don't know how to do that. I know
how
to create a recordset from an existing table but that seems a bit silly to
load the entire table when I just need the database structure for the
recordset. Can I just create a recordset, add the user's entries to the
recordset, then loop through the recordset and add the new records to the
table with and INSERT statement? Or am I just going at this the hard way?
Any
suggestions would be greatly appreciated.



  #4  
Old August 21st, 2009, 07:37 PM posted to microsoft.public.access.gettingstarted
ssutton503
external usenet poster
 
Posts: 6
Default Adding new records via recordset

Thanks guys. I will rethink how I am approaching this situation. I'm sure you
are right when you say that "just opening a form" won't create a new record.
It seems that what happens is that as soon as the user starts entering data
on the form a record is created. I will use your suggestions and see what I
can come up with. Thanks again.

"Steve" wrote:

am I just going at this the hard way?

Yes

just opening the form creates a new record in the table even if the user
never enters anything.

That's not correct! Something has to be entered before anything (a new
record) is saved.

Using a form to enter records is the correct way. If you want to check
before saving each new record, put code in the form's BeforeUpdate event
asking the user if he wants to save what he just entered and Cancel if he
says No.

Steve


"ssutton503" wrote in message
...
I want to create a form for the user to input some new jobs but I don't
want
the form bound to a table (as I originally did). I don't like the fact
that
just opening the form creates a new record in the table even if the user
never enters anything. What I'm thinking should happen is when the form
opens
it creates a recordset, the new entries get added to the recordset, then
the
user can choose to add the records from the recordset to the actual table
when they get done. Only problem is, I don't know how to do that. I know
how
to create a recordset from an existing table but that seems a bit silly to
load the entire table when I just need the database structure for the
recordset. Can I just create a recordset, add the user's entries to the
recordset, then loop through the recordset and add the new records to the
table with and INSERT statement? Or am I just going at this the hard way?
Any
suggestions would be greatly appreciated.




  #5  
Old August 21st, 2009, 07:58 PM posted to microsoft.public.access.gettingstarted
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Adding new records via recordset

Post a brief description of what you are trying to record in your database
and we can help you create the tables and forms you need.

If you need extensive help, I can help you. I provide help with Access,
Excel and Word applications for a small fee. If you would like my help,
contact me.

Steve



"ssutton503" wrote in message
...
Thanks guys. I will rethink how I am approaching this situation. I'm sure
you
are right when you say that "just opening a form" won't create a new
record.
It seems that what happens is that as soon as the user starts entering
data
on the form a record is created. I will use your suggestions and see what
I
can come up with. Thanks again.

"Steve" wrote:

am I just going at this the hard way?

Yes

just opening the form creates a new record in the table even if the
user
never enters anything.

That's not correct! Something has to be entered before anything (a new
record) is saved.

Using a form to enter records is the correct way. If you want to check
before saving each new record, put code in the form's BeforeUpdate event
asking the user if he wants to save what he just entered and Cancel if he
says No.

Steve


"ssutton503" wrote in message
...
I want to create a form for the user to input some new jobs but I don't
want
the form bound to a table (as I originally did). I don't like the fact
that
just opening the form creates a new record in the table even if the
user
never enters anything. What I'm thinking should happen is when the form
opens
it creates a recordset, the new entries get added to the recordset,
then
the
user can choose to add the records from the recordset to the actual
table
when they get done. Only problem is, I don't know how to do that. I
know
how
to create a recordset from an existing table but that seems a bit silly
to
load the entire table when I just need the database structure for the
recordset. Can I just create a recordset, add the user's entries to the
recordset, then loop through the recordset and add the new records to
the
table with and INSERT statement? Or am I just going at this the hard
way?
Any
suggestions would be greatly appreciated.






  #6  
Old August 21st, 2009, 08:17 PM posted to microsoft.public.access.gettingstarted
John... Visio MVP
external usenet poster
 
Posts: 900
Default Adding new records via recordset - little stevie is desperate for work

"Steve" wrote in message
...

If you need extensive help, I can help you. I provide help with Access,
Excel and Word applications for a small fee. If you would like my help,
contact me.

Steve


If you want to know how much of an expert stevie is, check out his website,
www.pcdatasheet.com and see his tips page. That page has had zero content
for years.



These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Steve wrote:
Yes, you are right but a database is the correct tool to use not a
spreadsheet.



Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP

  #7  
Old August 22nd, 2009, 12:26 AM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Adding new records via recordset

Entering data in a bound form begins the process of inserting a new row
(record) into the underlying table, but the record is not actually saved
until the user navigates off the current record, closes the form or
explicitly saves the record in some other way. In the meantime the form's
Dirty property is True. The new record can be aborted with the Esc key
before it is saved.

If you are concerned about a new record being inadvertently saved then one
thing you can do is force the record to be saved only by the user clicking a
'Save Record' button on the form. The following is the code from the module
of a simple form which demonstrates this:

' updates can only be saved via command button
Option Compare Database
Option Explicit

Dim blnSaved As Boolean

Private Sub cmdSave_Click()

Const MESSAGETEXT = "Save record?"

If Me.Dirty Then
' if user confirms set variable to True and attempt to save record
If MsgBox(MESSAGETEXT, vbQuestion + vbYesNo, "Confirm") = vbYes Then
blnSaved = True
On Error Resume Next
RunCommand acCmdSaveRecord
' if record cannot be saved set variable to False
If Err 0 Then
blnSaved = False
End If
Else
blnSaved = False
End If
End If

End Sub


Private Sub cmdUndo_Click()

' undo edits
Me.Undo

End Sub

Private Sub Form_AfterUpdate()

' reset variable to False
blnSaved = False

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

' cancel update if variable is False,
' i.e. save button has not been clicked
If Not blnSaved Then
Cancel = True
End If

End Sub

Private Sub Form_Current()

' reset variable to False
blnSaved = False

End Sub


Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const IS_DIRTY = 2169

' suppress system error message if form
' is closed while record is unsaved,
' NB: changes to current record will be lost
If DataErr = IS_DIRTY Then
Response = acDataErrContinue
End If

End Sub

As well as the controls bound to the columns (fields) in the underlying table
the form includes two command buttons; cmdSave to save a new or edited
record; cmdUndo to abort a new record after data has begun to be entered, or
to discard any changes made to am existing record.

One thing to note is that with a form in which the above code is used, if a
user closes the form without first clicking the Save Record button any
changes to the current record will be lost without warning. If on the other
hand they try to navigate to another record without having first saved
changes to the current record via the button, they will be unable to do so
until they click either the save button or the undo button.

Ken Sheridan
Stafford, England

ssutton503 wrote:
Thanks guys. I will rethink how I am approaching this situation. I'm sure you
are right when you say that "just opening a form" won't create a new record.
It seems that what happens is that as soon as the user starts entering data
on the form a record is created. I will use your suggestions and see what I
can come up with. Thanks again.

am I just going at this the hard way?

[quoted text clipped - 33 lines]
Any
suggestions would be greatly appreciated.


--
Message posted via http://www.accessmonster.com

  #8  
Old August 26th, 2009, 01:24 PM posted to microsoft.public.access.gettingstarted
Keith Wilby
external usenet poster
 
Posts: 812
Default Adding new records via recordset

"Steve" wrote in message
...
we can help you


Please don't consider yourself to be part of this community until you start
to abide by its rules.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.