473,320 Members | 1,580 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,320 software developers and data experts.

Populat a NewRecord from Text boxes Revisited



Please bear in mind that I am a newbie.
I am posting this question a second time because the responses to my earlier
post were a little wide of the mark.
So, here goes:

I am using Access 2000 and I would like to make a data entry form
(frmDataEntry) with a two text boxes.

The table (tblPeople) has fields Name and Age. Both are character fields.
The Form has two text boxes (txtName and txtAge)

On the form I created a command button (cmdAddPerson)
I used a command button (the command button wizard, record operations,Add
New Record) and it generates the code snippet below.
My understanding is that this creates a new unpopulated record in the table.

Now I want to populate the new record from the txtName and txtAge text
boxes.

I think I have to add some code in the area I marked with question marks in
the snippet, something like:
XXXXXXX = Me.txtName
YYYYYYYY = Me.txtAge.

Can I replace the XXXXXXX and YYYYYYY with a reference to the newly created
record?

Help would be greatly appreciated.

******************** snippet ***********
Private Sub commandAddRecord_Click()

On Error GoTo Err_commandAddRecord_Click
DoCmd.GoToRecord , , acNewRec
'
' Move information from textboxes to the new record
'
' ????????????????????????????????????
' ????????????????????????????????????
'
Exit_commandAddRecord_Click:
Exit Sub

Err_commandAddRecord_Click:
MsgBox Err.Description
Resume Exit_commandAddRecord_Click

End Sub

******************** snippet ***********

Nov 17 '08 #1
9 2063
"RICHARD BROMBERG" <di******@worldnet.att.netwrote in message
news:Xc******************@bgtnsc05-news.ops.worldnet.att.net...
>

Please bear in mind that I am a newbie.
I am posting this question a second time because the responses to my
earlier post were a little wide of the mark.
People can only give as good a respose as the the original information
allows. If you consider them "wide of the mark" then I would suggest that
the OP was unclear.
So, here goes:

I am using Access 2000 and I would like to make a data entry form
(frmDataEntry) with a two text boxes.

The table (tblPeople) has fields Name and Age. Both are character fields.
The Form has two text boxes (txtName and txtAge)

On the form I created a command button (cmdAddPerson)
I used a command button (the command button wizard, record operations,Add
New Record) and it generates the code snippet below.
My understanding is that this creates a new unpopulated record in the
table.
Your understanding is correct but it isn't a sh!te of use to you.
>
Now I want to populate the new record from the txtName and txtAge text
boxes.

I think I have to add some code in the area I marked with question marks
in
the snippet, something like:
XXXXXXX = Me.txtName
YYYYYYYY = Me.txtAge.

Can I replace the XXXXXXX and YYYYYYY with a reference to the newly
created record?
Don't use the DoCmd method, you will need some code. Discard all of the
code and replace it with this:

Private Sub commandAddRecord_Click()

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb

strSQL = Select * from tblPeople"
Set rs = db.OpenRecordset(strSQL)

With rs
.AddNew
![fldName] = Me.txtName
![Age] = Me.txtAge
.Update
End With

rs.Close
Set rs = Nothing
Set db = Nothing

Me.Requery

End Sub

The requery line will take you to the beginning of the recordset so you'll
probably need a method of going to the new record (GoTo Last might work). I
have assumed that you have heeded previous advice to rename the Name field
(fldName in my example).

Keith.
www.keithwilby.co.uk

Nov 17 '08 #2
Thank you Keith.

I did not in fact use a field name of "NAME" in the table.
I only used "NAME" in my posting, the table actually uses "FirstName" .

When I used the field name
"Keith Wilby" <he**@there.comwrote in message
news:49**********@glkas0286.greenlnk.net...
"RICHARD BROMBERG" <di******@worldnet.att.netwrote in message
news:Xc******************@bgtnsc05-news.ops.worldnet.att.net...
>>

Please bear in mind that I am a newbie.
I am posting this question a second time because the responses to my
earlier post were a little wide of the mark.

People can only give as good a respose as the the original information
allows. If you consider them "wide of the mark" then I would suggest that
the OP was unclear.
>So, here goes:

I am using Access 2000 and I would like to make a data entry form
(frmDataEntry) with a two text boxes.

The table (tblPeople) has fields Name and Age. Both are character fields.
The Form has two text boxes (txtName and txtAge)

On the form I created a command button (cmdAddPerson)
I used a command button (the command button wizard, record operations,Add
New Record) and it generates the code snippet below.
My understanding is that this creates a new unpopulated record in the
table.

Your understanding is correct but it isn't a sh!te of use to you.
>>
Now I want to populate the new record from the txtName and txtAge text
boxes.

I think I have to add some code in the area I marked with question marks
in
the snippet, something like:
XXXXXXX = Me.txtName
YYYYYYYY = Me.txtAge.

Can I replace the XXXXXXX and YYYYYYY with a reference to the newly
created record?

Don't use the DoCmd method, you will need some code. Discard all of the
code and replace it with this:

Private Sub commandAddRecord_Click()

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb

strSQL = Select * from tblPeople"
Set rs = db.OpenRecordset(strSQL)

With rs
.AddNew
![fldName] = Me.txtName
![Age] = Me.txtAge
.Update
End With

rs.Close
Set rs = Nothing
Set db = Nothing

Me.Requery

End Sub

The requery line will take you to the beginning of the recordset so you'll
probably need a method of going to the new record (GoTo Last might work).
I have assumed that you have heeded previous advice to rename the Name
field (fldName in my example).

Keith.
www.keithwilby.co.uk

Nov 17 '08 #3
Keith

Getting closer.

The Dim statement Dim db As DAO.Database, rs As DAO.Recordset, strSQL As
String does not compile so I split the dim statement up into three seperate
Dim statements as follows:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

The first two dim statements cause a compiler error error User type not
defined .
I looked through the add in manager but found nothing there at all.

Where do I get the definitions?

Again, thanks
"Keith Wilby" <he**@there.comwrote in message
news:49**********@glkas0286.greenlnk.net...
"RICHARD BROMBERG" <di******@worldnet.att.netwrote in message
news:Xc******************@bgtnsc05-news.ops.worldnet.att.net...
>>

Please bear in mind that I am a newbie.
I am posting this question a second time because the responses to my
earlier post were a little wide of the mark.

People can only give as good a respose as the the original information
allows. If you consider them "wide of the mark" then I would suggest that
the OP was unclear.
>So, here goes:

I am using Access 2000 and I would like to make a data entry form
(frmDataEntry) with a two text boxes.

The table (tblPeople) has fields Name and Age. Both are character fields.
The Form has two text boxes (txtName and txtAge)

On the form I created a command button (cmdAddPerson)
I used a command button (the command button wizard, record operations,Add
New Record) and it generates the code snippet below.
My understanding is that this creates a new unpopulated record in the
table.

Your understanding is correct but it isn't a sh!te of use to you.
>>
Now I want to populate the new record from the txtName and txtAge text
boxes.

I think I have to add some code in the area I marked with question marks
in
the snippet, something like:
XXXXXXX = Me.txtName
YYYYYYYY = Me.txtAge.

Can I replace the XXXXXXX and YYYYYYY with a reference to the newly
created record?

Don't use the DoCmd method, you will need some code. Discard all of the
code and replace it with this:

Private Sub commandAddRecord_Click()

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb

strSQL = Select * from tblPeople"
Set rs = db.OpenRecordset(strSQL)

With rs
.AddNew
![fldName] = Me.txtName
![Age] = Me.txtAge
.Update
End With

rs.Close
Set rs = Nothing
Set db = Nothing

Me.Requery

End Sub

The requery line will take you to the beginning of the recordset so you'll
probably need a method of going to the new record (GoTo Last might work).
I have assumed that you have heeded previous advice to rename the Name
field (fldName in my example).

Keith.
www.keithwilby.co.uk

Nov 17 '08 #4
In some versions of Access, the Data Access Objects DLLs were not, by
default, included in the References. Go to a module window, and on the menu
Tools | References, and check the entry for the DAO library... I usually
move it up above the ADO library, just in case I forget.

However, I am wondering why you do not just bind your form to the Table or a
Query on the Table, and place First Name and Age controls on the Form with
Control Source of the corresponding Fields. If you only want the form used
to add new records, you can set the "Data Entry" property of the Forms Data
properties; but my users have never seemed to have any problem using the
same form for editing existing records and using the navigation buttons to
go to a new Record to add.

If the target users of an application were really raw novice casual users, I
might add Command Buttons to the Form to switch the properties, so that
normally they could just view, but had to click a button to Edit an existing
Record, or click a different button to add a new Record.

Access makes data entry and editing so trivially simple with bound forms
that I continue to be amazed, sixteen years after Access was first released,
how many people think they have to use unbound forms and code to do these
simple tasks -- when they are re-implementing built-in features of the
product.

Larry Linson
Microsoft Office Access MVP
"RICHARD BROMBERG" <di******@worldnet.att.netwrote in message
news:xp******************@bgtnsc05-news.ops.worldnet.att.net...
Keith

Getting closer.

The Dim statement Dim db As DAO.Database, rs As DAO.Recordset, strSQL As
String does not compile so I split the dim statement up into three
seperate Dim statements as follows:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

The first two dim statements cause a compiler error error User type not
defined .
I looked through the add in manager but found nothing there at all.

Where do I get the definitions?

Again, thanks
"Keith Wilby" <he**@there.comwrote in message
news:49**********@glkas0286.greenlnk.net...
>"RICHARD BROMBERG" <di******@worldnet.att.netwrote in message
news:Xc******************@bgtnsc05-news.ops.worldnet.att.net...
>>>

Please bear in mind that I am a newbie.
I am posting this question a second time because the responses to my
earlier post were a little wide of the mark.

People can only give as good a respose as the the original information
allows. If you consider them "wide of the mark" then I would suggest
that the OP was unclear.
>>So, here goes:

I am using Access 2000 and I would like to make a data entry form
(frmDataEntry) with a two text boxes.

The table (tblPeople) has fields Name and Age. Both are character
fields.
The Form has two text boxes (txtName and txtAge)

On the form I created a command button (cmdAddPerson)
I used a command button (the command button wizard, record
operations,Add
New Record) and it generates the code snippet below.
My understanding is that this creates a new unpopulated record in the
table.

Your understanding is correct but it isn't a sh!te of use to you.
>>>
Now I want to populate the new record from the txtName and txtAge text
boxes.

I think I have to add some code in the area I marked with question marks
in
the snippet, something like:
XXXXXXX = Me.txtName
YYYYYYYY = Me.txtAge.

Can I replace the XXXXXXX and YYYYYYY with a reference to the newly
created record?

Don't use the DoCmd method, you will need some code. Discard all of the
code and replace it with this:

Private Sub commandAddRecord_Click()

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb

strSQL = Select * from tblPeople"
Set rs = db.OpenRecordset(strSQL)

With rs
.AddNew
![fldName] = Me.txtName
![Age] = Me.txtAge
.Update
End With

rs.Close
Set rs = Nothing
Set db = Nothing

Me.Requery

End Sub

The requery line will take you to the beginning of the recordset so
you'll probably need a method of going to the new record (GoTo Last might
work). I have assumed that you have heeded previous advice to rename the
Name field (fldName in my example).

Keith.
www.keithwilby.co.uk


Nov 17 '08 #5
rkc
On Nov 17, 10:37*am, "RICHARD BROMBERG" <dickb...@worldnet.att.net>
wrote:
Please bear in mind that I am a newbie.
I am posting this question a second time because the responses to my earlier
post were a little wide of the mark.
So, here goes:

I am using Access 2000 and I would like to make a data entry form
(frmDataEntry) with a two text boxes.

The table (tblPeople) has fields Name and Age. Both are character fields.
The Form has two text boxes *(txtName and txtAge)

On the form I created a command button (cmdAddPerson)
I used a command button (the command button wizard, record operations,Add
New Record) *and it generates the code snippet below.
My understanding is that this creates a new unpopulated record in the table.

Now I want to populate the new record from the txtName and txtAge text
boxes.

I think I have to add some code in the area I marked with question marks in
the snippet, something like:
XXXXXXX = Me.txtName
YYYYYYYY = Me.txtAge.

Can I replace the XXXXXXX and YYYYYYY with a reference to the newly created
record?

Help would be greatly appreciated.

******************** snippet ***********
Private Sub commandAddRecord_Click()

On Error GoTo Err_commandAddRecord_Click
* * DoCmd.GoToRecord , , acNewRec
'
' * * *Move information from textboxes to the new record
'
' * *????????????????????????????????????
' * *????????????????????????????????????
'
Exit_commandAddRecord_Click:
* * Exit Sub

Err_commandAddRecord_Click:
* * MsgBox Err.Description
* * Resume Exit_commandAddRecord_Click

End Sub

******************** snippet ***********
I think everyone is assuming you know how to simply build a form that
is bound to the table and that what you're doing is for educational
purposes.

Is that the case? Do you know how to use the form wizard?
Nov 17 '08 #6
Larry

Thanks

I am just learning and don't really have enough experience of insight to
determine the easiest or simplest way of doing things.
After I get the present program to work, I am going back and try the way
you suggest.

Once again,
many thanks
I guess that comes with a little more experience.
"Larry Linson" <bo*****@localhost.notwrote in message
news:ER****************@nwrddc01.gnilink.net...
In some versions of Access, the Data Access Objects DLLs were not, by
default, included in the References. Go to a module window, and on the
menu Tools | References, and check the entry for the DAO library... I
usually move it up above the ADO library, just in case I forget.

However, I am wondering why you do not just bind your form to the Table or
a Query on the Table, and place First Name and Age controls on the Form
with Control Source of the corresponding Fields. If you only want the
form used to add new records, you can set the "Data Entry" property of the
Forms Data properties; but my users have never seemed to have any problem
using the same form for editing existing records and using the navigation
buttons to go to a new Record to add.

If the target users of an application were really raw novice casual users,
I might add Command Buttons to the Form to switch the properties, so that
normally they could just view, but had to click a button to Edit an
existing Record, or click a different button to add a new Record.

Access makes data entry and editing so trivially simple with bound forms
that I continue to be amazed, sixteen years after Access was first
released, how many people think they have to use unbound forms and code to
do these simple tasks -- when they are re-implementing built-in features
of the product.

Larry Linson
Microsoft Office Access MVP
"RICHARD BROMBERG" <di******@worldnet.att.netwrote in message
news:xp******************@bgtnsc05-news.ops.worldnet.att.net...
>Keith

Getting closer.

The Dim statement Dim db As DAO.Database, rs As DAO.Recordset, strSQL
As String does not compile so I split the dim statement up into three
seperate Dim statements as follows:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

The first two dim statements cause a compiler error error User type not
defined .
I looked through the add in manager but found nothing there at all.

Where do I get the definitions?

Again, thanks
"Keith Wilby" <he**@there.comwrote in message
news:49**********@glkas0286.greenlnk.net...
>>"RICHARD BROMBERG" <di******@worldnet.att.netwrote in message
news:Xc******************@bgtnsc05-news.ops.worldnet.att.net...
Please bear in mind that I am a newbie.
I am posting this question a second time because the responses to my
earlier post were a little wide of the mark.

People can only give as good a respose as the the original information
allows. If you consider them "wide of the mark" then I would suggest
that the OP was unclear.

So, here goes:

I am using Access 2000 and I would like to make a data entry form
(frmDataEntry) with a two text boxes.

The table (tblPeople) has fields Name and Age. Both are character
fields.
The Form has two text boxes (txtName and txtAge)

On the form I created a command button (cmdAddPerson)
I used a command button (the command button wizard, record
operations,Add
New Record) and it generates the code snippet below.
My understanding is that this creates a new unpopulated record in the
table.

Your understanding is correct but it isn't a sh!te of use to you.
Now I want to populate the new record from the txtName and txtAge text
boxes.

I think I have to add some code in the area I marked with question
marks in
the snippet, something like:
XXXXXXX = Me.txtName
YYYYYYYY = Me.txtAge.

Can I replace the XXXXXXX and YYYYYYY with a reference to the newly
created record?
Don't use the DoCmd method, you will need some code. Discard all of the
code and replace it with this:

Private Sub commandAddRecord_Click()

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb

strSQL = Select * from tblPeople"
Set rs = db.OpenRecordset(strSQL)

With rs
.AddNew
![fldName] = Me.txtName
![Age] = Me.txtAge
.Update
End With

rs.Close
Set rs = Nothing
Set db = Nothing

Me.Requery

End Sub

The requery line will take you to the beginning of the recordset so
you'll probably need a method of going to the new record (GoTo Last
might work). I have assumed that you have heeded previous advice to
rename the Name field (fldName in my example).

Keith.
www.keithwilby.co.uk



Nov 17 '08 #7
"RICHARD BROMBERG" <di******@worldnet.att.netwrote in message
news:Cj********************@bgtnsc04-news.ops.worldnet.att.net...
>
I am just learning and don't really have enough experience of insight to
determine the easiest or simplest way of doing things.
After I get the present program to work, I am going back and try the way
you suggest.
So you don't actually have a reason to be using an unbound form? If you're
a self-confessed newbie then I would urge you go with Larry's suggestion
pronto otherwise you're going to tie yourself in knots PDQ.

Keith.

Nov 18 '08 #8
"Larry Linson" <bo*****@localhost.notwrote in
news:ER****************@nwrddc01.gnilink.net:
Access makes data entry and editing so trivially simple with bound
forms that I continue to be amazed, sixteen years after Access was
first released, how many people think they have to use unbound
forms and code to do these simple tasks -- when they are
re-implementing built-in features of the product.
To be fair, these are often refugees from database programming
environments in which it was impossible to use bound controls easily
or reliably. That would include, well, ever programming environment
but Access, I think.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Nov 18 '08 #9
"RICHARD BROMBERG" <di******@worldnet.att.netwrote in
news:Cj********************@bgtnsc04-news.ops.worldnet.att.net:

[to Larry's suggestion to simply use bound forms/controls:]
I am just learning and don't really have enough experience of
insight to determine the easiest or simplest way of doing things.
The approach that Larry suggest *is* the easiest and simplest way of
doing things. The whole point of Access is that you can build a
reasonably complex database application without ever writing one
line of code. It's point and click. That's the default orientation
of Access, and should have been the way you started out using it, as
everything about Access is designed to make it easy for
non-programmers.
After I get the present program to work, I am going back and try
the way you suggest.
Are you a masochist, or what?

I've been programming Access database applications professionally
since 1996 and the number of unbound data entry forms I've
programmed is fewer than a dozen. Yes, I have lots of unbound dialog
forms (often more of those than I have bound data editing forms). I
know how to write unbound forms, and you lose so many of Access's
capabilities in doing so that you gain no time, and only in the most
high-volume environments do you actually gain anything worth having
(higher concurrency is the only significant benefit).

Learn *Access*, rather than trying to use Access the way you've used
other development platforms. This means starting out trying to avoid
writing any code at all. Once you've done that, you'll be
well-equipped to create rich, reliable Access apps in the minimal
time.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Nov 18 '08 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Dan | last post by:
Can anyone offer suggestions on how to do this or if it is possible? I have a form that uses a drop down box and 2 text fields. What I am trying to do is have the value of each text box set by...
7
by: Gertjan van Heijst | last post by:
Hi, I really hope someone can help me because I've already spend 2 days on this problem and I'm not getting anywhere. I think the problem is that I don't really understand how text boxes store...
4
by: Sigurd Bruteig | last post by:
If i type NewRecord in some code, VB change to newrecord with lower-case letter. If I open a new database on the same computer the NewRecord keyword act normally. Ha anybody experienced something...
2
by: John Kreps | last post by:
(acc 2002) I've got six unbound text boxes on a subform that has a white background. Each of those six boxes has an expression that when true, will change its background from white to another...
2
by: simonc | last post by:
I posted some questions about text boxes and received a reply which partially resolved the issues. 1. I want to construct a text box (or rich text box) where if you type a space a dot character...
11
by: Edson Peacock | last post by:
I have a report with sub reports, one of the subreports have 12 text boxes that are 2" high and I want them all to grow if one goes to 3" high. If anyone has any suggestions they are very much...
4
by: Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+ | last post by:
I have created a report. This report needs to display records between two dates entered by the user. I put two text boxes on the report so I can enter the start and end date - I set them to use an...
9
by: RICHARD BROMBERG | last post by:
I am using Access 2000 and I would like to make a data entry form (frmDataEntry) I want to append a newrecord to a table (tblPeople) and populate the new record from two Textboxes (txtName and...
11
by: jwessner | last post by:
I have a form (Form1) which contains basic Project data and a subform listing the personnel assigned to the Project as a continuous form. Selecting a person on that project and clicking on a command...
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...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.