Can I modify A Text Box under program control?

R

Ray C

I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
R

Ray C

It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

scubadiver said:
What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


Ray C said:
I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
S

scubadiver

It does sound like you want a 1-to-many relationship! Do you have one table
at the moment? If so, what are the fields?

--
The 11th day of every month:

http://truthaction.org/forum/index.php


Ray C said:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

scubadiver said:
What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


Ray C said:
I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
R

Ray C

This is a fundmental problem for me that relates to a number of different
tables but, For the sake of simplicity, I have a Customer Table containing
Cusomer Name, Address, Contact, etc, etc. (if you want the specific field
names, let me know and I will list them for you. I have a Form called
frm_Main (that has it's Record Source as a Query, for various reasons) and on
that form are Text Boxes that have their record Source as the Fields in the
Query. They will display the information held in the Fields in the table that
the Query is based on. The way I do this is to have a Combo Box give me a
list of all the available info and when I select one, I use the information
held in the Combo to lookup and set a "Bookmark" in the Dynaset. This will
display the required record in the "Bound" Text Boxes. This all works OK but
when I want to Add a New Record I am not able to do that without gathering
all the new info in a parallel set of "Unbound" Text Boxes and then writing
that information to the table seperately.

Hope this helps Ray C

scubadiver said:
It does sound like you want a 1-to-many relationship! Do you have one table
at the moment? If so, what are the fields?

--
The 11th day of every month:

http://truthaction.org/forum/index.php


Ray C said:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

scubadiver said:
What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
S

scubadiver

It sounds to me that your source query is not updateable. if you open the
query will it allow you to alter records?

--

http://www.ready4mainstream.ny911truth.org/index.html


Ray C said:
This is a fundmental problem for me that relates to a number of different
tables but, For the sake of simplicity, I have a Customer Table containing
Cusomer Name, Address, Contact, etc, etc. (if you want the specific field
names, let me know and I will list them for you. I have a Form called
frm_Main (that has it's Record Source as a Query, for various reasons) and on
that form are Text Boxes that have their record Source as the Fields in the
Query. They will display the information held in the Fields in the table that
the Query is based on. The way I do this is to have a Combo Box give me a
list of all the available info and when I select one, I use the information
held in the Combo to lookup and set a "Bookmark" in the Dynaset. This will
display the required record in the "Bound" Text Boxes. This all works OK but
when I want to Add a New Record I am not able to do that without gathering
all the new info in a parallel set of "Unbound" Text Boxes and then writing
that information to the table seperately.

Hope this helps Ray C

scubadiver said:
It does sound like you want a 1-to-many relationship! Do you have one table
at the moment? If so, what are the fields?

--
The 11th day of every month:

http://truthaction.org/forum/index.php


Ray C said:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

:


What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
J

John Spencer

Add a button to your form "New Record"

In the click event add code to go to NEW Record.

THe code you need to add for the buttons click event should look
something like

On Error GoTo Err_sCmdAdd

With Me
If .Dirty = True Then .Dirty = False

If .AllowAdditions = False Then
.AllowAdditions = True
End If

DoCmd.GoToRecord , , acNewRec

End With

Exit_sCmdAdd:
Exit Function

Err_sCmdAdd:
MsgBox Err.Number & ": " & Err.Description,



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray said:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

scubadiver said:
What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


Ray C said:
I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
S

scubadiver

Sounds like you have your answer but I still don't quite understand how your
database works. I think it could be simpler.



Ray C said:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

scubadiver said:
What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


Ray C said:
I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
R

Ray C

Yes it will but I think that you are looking to answer for something that I
am not asking.
I Have a Form that has its Source as the Customer Table, I do not show the
navigation bar on that Form. I have Text boxes on that Form that show records
in the Table. I have an "Unbound" Text Box that I use to find the records
that I want to display. All that works Fine.
In that situation, How do I add a new Record? If I Type anything into the
Boxes that are on the Form, they will modify an existing record because they
are "Bound" to the appropriate field"
My answer is to create a new Form and populate it with a Duplicate set of
"Unbound" Text Boxes, type in the relavent information and then save that
information by using the code I indicated earlier.
There is probably a much better way of doing this fundamental action and you
are probably making the assumption that I would not be naive enough not to
use the better method. However, I don't know a better method and what I am
asking is if there is any way I could use the same set of Text Boxes and make
them "Bound" or "Unbound" under program control so that I do not have to
duplicate a whole mess of Text Boxes.
If there is a better way to add records to a Table, please let me know
Thanks RayC

scubadiver said:
It sounds to me that your source query is not updateable. if you open the
query will it allow you to alter records?

--

http://www.ready4mainstream.ny911truth.org/index.html


Ray C said:
This is a fundmental problem for me that relates to a number of different
tables but, For the sake of simplicity, I have a Customer Table containing
Cusomer Name, Address, Contact, etc, etc. (if you want the specific field
names, let me know and I will list them for you. I have a Form called
frm_Main (that has it's Record Source as a Query, for various reasons) and on
that form are Text Boxes that have their record Source as the Fields in the
Query. They will display the information held in the Fields in the table that
the Query is based on. The way I do this is to have a Combo Box give me a
list of all the available info and when I select one, I use the information
held in the Combo to lookup and set a "Bookmark" in the Dynaset. This will
display the required record in the "Bound" Text Boxes. This all works OK but
when I want to Add a New Record I am not able to do that without gathering
all the new info in a parallel set of "Unbound" Text Boxes and then writing
that information to the table seperately.

Hope this helps Ray C

scubadiver said:
It does sound like you want a 1-to-many relationship! Do you have one table
at the moment? If so, what are the fields?

--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

:


What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
R

Ray C

I am sure that it would be a lot simpler if I was cleaverer :-(
Thanks for your help it is very much appreciated,

Rayc

scubadiver said:
Sounds like you have your answer but I still don't quite understand how your
database works. I think it could be simpler.



Ray C said:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

scubadiver said:
What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
R

Ray C

Thanks John I will give it a try. Cureous about the "Dirty" bit though.

I realy appreciate your help. RayC

John Spencer said:
Add a button to your form "New Record"

In the click event add code to go to NEW Record.

THe code you need to add for the buttons click event should look
something like

On Error GoTo Err_sCmdAdd

With Me
If .Dirty = True Then .Dirty = False

If .AllowAdditions = False Then
.AllowAdditions = True
End If

DoCmd.GoToRecord , , acNewRec

End With

Exit_sCmdAdd:
Exit Function

Err_sCmdAdd:
MsgBox Err.Number & ": " & Err.Description,



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray said:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

scubadiver said:
What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
J

John Spencer

The dirty bit forces a save of the current record if it has not yet been
saved.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray said:
Thanks John I will give it a try. Cureous about the "Dirty" bit though.

I realy appreciate your help. RayC

John Spencer said:
Add a button to your form "New Record"

In the click event add code to go to NEW Record.

THe code you need to add for the buttons click event should look
something like

On Error GoTo Err_sCmdAdd

With Me
If .Dirty = True Then .Dirty = False

If .AllowAdditions = False Then
.AllowAdditions = True
End If

DoCmd.GoToRecord , , acNewRec

End With

Exit_sCmdAdd:
Exit Function

Err_sCmdAdd:
MsgBox Err.Number & ": " & Err.Description,



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray said:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

:

What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
R

Ray C

Ahh, interesting, I will try to remember that.
With regard to your excelent solution to my problem, do I need to have
something that will save the record or will it save automatically. Also,
should I not be putting something in there that asks the user if they want to
save or not?

Regards RayC

John Spencer said:
The dirty bit forces a save of the current record if it has not yet been
saved.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray said:
Thanks John I will give it a try. Cureous about the "Dirty" bit though.

I realy appreciate your help. RayC

John Spencer said:
Add a button to your form "New Record"

In the click event add code to go to NEW Record.

THe code you need to add for the buttons click event should look
something like

On Error GoTo Err_sCmdAdd

With Me
If .Dirty = True Then .Dirty = False

If .AllowAdditions = False Then
.AllowAdditions = True
End If

DoCmd.GoToRecord , , acNewRec

End With

Exit_sCmdAdd:
Exit Function

Err_sCmdAdd:
MsgBox Err.Number & ": " & Err.Description,



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

:

What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
J

John Spencer

Actually, it should save automatically. Access does that. I can't
remember why I put that bit in there - probably paranoia on my part.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray said:
Ahh, interesting, I will try to remember that.
With regard to your excelent solution to my problem, do I need to have
something that will save the record or will it save automatically. Also,
should I not be putting something in there that asks the user if they want to
save or not?

Regards RayC

John Spencer said:
The dirty bit forces a save of the current record if it has not yet been
saved.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray said:
Thanks John I will give it a try. Cureous about the "Dirty" bit though.

I realy appreciate your help. RayC

:

Add a button to your form "New Record"

In the click event add code to go to NEW Record.

THe code you need to add for the buttons click event should look
something like

On Error GoTo Err_sCmdAdd

With Me
If .Dirty = True Then .Dirty = False

If .AllowAdditions = False Then
.AllowAdditions = True
End If

DoCmd.GoToRecord , , acNewRec

End With

Exit_sCmdAdd:
Exit Function

Err_sCmdAdd:
MsgBox Err.Number & ": " & Err.Description,



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

:

What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
R

Ray C

Hi John, sorry to be so long in coming back to you but is there a way of
"Aborting" the saving of the new record? Going back to my earlier notes, I
have a load of Text Boxes tha are "Bound " to the appropriat fields in the
Customers Table. These Text Boxes are great for finding and displaying
records in the table but any user will, at some time, want to add new records
to the table. The way I have approached this in the past is to generate a
parallel set of "Unbound" Text Boxes and use these to gather the new record
information. I then follow this by writing out the contents of each
individual "Unbound Text Box to a new record in the table using the "Save
record" routine I set out at the begining of my query after first asking the
user if they want to Save the data or Abort. I can also check for required
fields and prompt the user if any are not completed correctly. Your solution
is great in that it allows me to work with only one set of "Bound" text
boxes. However, the solution does seem somewhat flawed to me in that the user
says that they want to Add a New Record but there is no method by which the
user can subsequently say that they have made a mistake and have the option
not to save "Duff" information. Am I missing something here?
Your help is very much appreciated. RayC

John Spencer said:
Actually, it should save automatically. Access does that. I can't
remember why I put that bit in there - probably paranoia on my part.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray said:
Ahh, interesting, I will try to remember that.
With regard to your excelent solution to my problem, do I need to have
something that will save the record or will it save automatically. Also,
should I not be putting something in there that asks the user if they want to
save or not?

Regards RayC

John Spencer said:
The dirty bit forces a save of the current record if it has not yet been
saved.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
Thanks John I will give it a try. Cureous about the "Dirty" bit though.

I realy appreciate your help. RayC

:

Add a button to your form "New Record"

In the click event add code to go to NEW Record.

THe code you need to add for the buttons click event should look
something like

On Error GoTo Err_sCmdAdd

With Me
If .Dirty = True Then .Dirty = False

If .AllowAdditions = False Then
.AllowAdditions = True
End If

DoCmd.GoToRecord , , acNewRec

End With

Exit_sCmdAdd:
Exit Function

Err_sCmdAdd:
MsgBox Err.Number & ": " & Err.Description,



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
It's only confusing because You know what I should be diong and I don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound" to fields
in a Table. This is great if all I want to do is look at the information in
that Table but I might want to add a new record at some time and it is this
that I am working my way through. I don't have navigation Buttons on my Form
so I am not able to go to the last record and enter new information then
save. Could I do that under program control?
Hope this helps RayC

:

What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new records at some
time and I also Duplicate those same Text Boxes and have them "Unbound" so
that I can enter new info into them. I then I write that data to a new record
by using the following :- where ****TxtBox is the "Unbound" Text Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
J

John Spencer

You can always UNDO the entire record if the user doesn't want to save it.

The code for a cancel button would be
Me.Undo

If you are using subforms in conjunction with a main form to enter data into
multiple tables then this won't work since the minute you leave the main
form and enter the subform the data bound to the main form is saved. Also
when you leave a subform and go to the main form (or another subform) the
data bound to the subform is saved

You can use a form's before update event to check the data that is entered.
If your requirements aren't met you can generate a message to the user and
cancel the update (save) and force the user to enter satisfactory data.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ray C said:
Hi John, sorry to be so long in coming back to you but is there a way of
"Aborting" the saving of the new record? Going back to my earlier notes, I
have a load of Text Boxes tha are "Bound " to the appropriat fields in the
Customers Table. These Text Boxes are great for finding and displaying
records in the table but any user will, at some time, want to add new
records
to the table. The way I have approached this in the past is to generate a
parallel set of "Unbound" Text Boxes and use these to gather the new
record
information. I then follow this by writing out the contents of each
individual "Unbound Text Box to a new record in the table using the "Save
record" routine I set out at the begining of my query after first asking
the
user if they want to Save the data or Abort. I can also check for required
fields and prompt the user if any are not completed correctly. Your
solution
is great in that it allows me to work with only one set of "Bound" text
boxes. However, the solution does seem somewhat flawed to me in that the
user
says that they want to Add a New Record but there is no method by which
the
user can subsequently say that they have made a mistake and have the
option
not to save "Duff" information. Am I missing something here?
Your help is very much appreciated. RayC

John Spencer said:
Actually, it should save automatically. Access does that. I can't
remember why I put that bit in there - probably paranoia on my part.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray said:
Ahh, interesting, I will try to remember that.
With regard to your excelent solution to my problem, do I need to have
something that will save the record or will it save automatically.
Also,
should I not be putting something in there that asks the user if they
want to
save or not?

Regards RayC

:

The dirty bit forces a save of the current record if it has not yet
been
saved.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
Thanks John I will give it a try. Cureous about the "Dirty" bit
though.

I realy appreciate your help. RayC

:

Add a button to your form "New Record"

In the click event add code to go to NEW Record.

THe code you need to add for the buttons click event should look
something like

On Error GoTo Err_sCmdAdd

With Me
If .Dirty = True Then .Dirty = False

If .AllowAdditions = False Then
.AllowAdditions = True
End If

DoCmd.GoToRecord , , acNewRec

End With

Exit_sCmdAdd:
Exit Function

Err_sCmdAdd:
MsgBox Err.Number & ": " & Err.Description,



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
It's only confusing because You know what I should be diong and I
don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound"
to fields
in a Table. This is great if all I want to do is look at the
information in
that Table but I might want to add a new record at some time and it
is this
that I am working my way through. I don't have navigation Buttons
on my Form
so I am not able to go to the last record and enter new information
then
save. Could I do that under program control?
Hope this helps RayC

:

What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the
appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new
records at some
time and I also Duplicate those same Text Boxes and have them
"Unbound" so
that I can enter new info into them. I then I write that data to
a new record
by using the following :- where ****TxtBox is the "Unbound" Text
Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of
Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would
save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
R

Ray C

John, Your a Gem !!! :)
I will check that out, I did not realise that it updated as you moved from
the Form and never thought about the Forms Update Event.
Once again, your a Star!! Regards RayC


John Spencer said:
You can always UNDO the entire record if the user doesn't want to save it.

The code for a cancel button would be
Me.Undo

If you are using subforms in conjunction with a main form to enter data into
multiple tables then this won't work since the minute you leave the main
form and enter the subform the data bound to the main form is saved. Also
when you leave a subform and go to the main form (or another subform) the
data bound to the subform is saved

You can use a form's before update event to check the data that is entered.
If your requirements aren't met you can generate a message to the user and
cancel the update (save) and force the user to enter satisfactory data.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ray C said:
Hi John, sorry to be so long in coming back to you but is there a way of
"Aborting" the saving of the new record? Going back to my earlier notes, I
have a load of Text Boxes tha are "Bound " to the appropriat fields in the
Customers Table. These Text Boxes are great for finding and displaying
records in the table but any user will, at some time, want to add new
records
to the table. The way I have approached this in the past is to generate a
parallel set of "Unbound" Text Boxes and use these to gather the new
record
information. I then follow this by writing out the contents of each
individual "Unbound Text Box to a new record in the table using the "Save
record" routine I set out at the begining of my query after first asking
the
user if they want to Save the data or Abort. I can also check for required
fields and prompt the user if any are not completed correctly. Your
solution
is great in that it allows me to work with only one set of "Bound" text
boxes. However, the solution does seem somewhat flawed to me in that the
user
says that they want to Add a New Record but there is no method by which
the
user can subsequently say that they have made a mistake and have the
option
not to save "Duff" information. Am I missing something here?
Your help is very much appreciated. RayC

John Spencer said:
Actually, it should save automatically. Access does that. I can't
remember why I put that bit in there - probably paranoia on my part.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
Ahh, interesting, I will try to remember that.
With regard to your excelent solution to my problem, do I need to have
something that will save the record or will it save automatically.
Also,
should I not be putting something in there that asks the user if they
want to
save or not?

Regards RayC

:

The dirty bit forces a save of the current record if it has not yet
been
saved.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
Thanks John I will give it a try. Cureous about the "Dirty" bit
though.

I realy appreciate your help. RayC

:

Add a button to your form "New Record"

In the click event add code to go to NEW Record.

THe code you need to add for the buttons click event should look
something like

On Error GoTo Err_sCmdAdd

With Me
If .Dirty = True Then .Dirty = False

If .AllowAdditions = False Then
.AllowAdditions = True
End If

DoCmd.GoToRecord , , acNewRec

End With

Exit_sCmdAdd:
Exit Function

Err_sCmdAdd:
MsgBox Err.Number & ": " & Err.Description,



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
It's only confusing because You know what I should be diong and I
don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound"
to fields
in a Table. This is great if all I want to do is look at the
information in
that Table but I might want to add a new record at some time and it
is this
that I am working my way through. I don't have navigation Buttons
on my Form
so I am not able to go to the last record and enter new information
then
save. Could I do that under program control?
Hope this helps RayC

:

What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the
appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new
records at some
time and I also Duplicate those same Text Boxes and have them
"Unbound" so
that I can enter new info into them. I then I write that data to
a new record
by using the following :- where ****TxtBox is the "Unbound" Text
Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of
Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would
save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 
R

Ray C

Hi Jojn
Sorry about draging this on but I am actualy using my Main Form to look at
data held in two Tables but I do this with a Query rather than a Sub Form (my
Form is "Bound" to the Query) This works fine to read the records and it
should work with new save providing I write somthing else to save the
"Primary ID" in the Secondary ID's, ID (if you know what I mean). All
customers are in the Main Customer Table (Primary Key is Auto number) but
some Customers have "Extra Info" held in the "Customers_Extra_Info" Table
with a non AutoNumber ID.
I will let you know.
RayC

Ray C said:
John, Your a Gem !!! :)
I will check that out, I did not realise that it updated as you moved from
the Form and never thought about the Forms Update Event.
Once again, your a Star!! Regards RayC


John Spencer said:
You can always UNDO the entire record if the user doesn't want to save it.

The code for a cancel button would be
Me.Undo

If you are using subforms in conjunction with a main form to enter data into
multiple tables then this won't work since the minute you leave the main
form and enter the subform the data bound to the main form is saved. Also
when you leave a subform and go to the main form (or another subform) the
data bound to the subform is saved

You can use a form's before update event to check the data that is entered.
If your requirements aren't met you can generate a message to the user and
cancel the update (save) and force the user to enter satisfactory data.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ray C said:
Hi John, sorry to be so long in coming back to you but is there a way of
"Aborting" the saving of the new record? Going back to my earlier notes, I
have a load of Text Boxes tha are "Bound " to the appropriat fields in the
Customers Table. These Text Boxes are great for finding and displaying
records in the table but any user will, at some time, want to add new
records
to the table. The way I have approached this in the past is to generate a
parallel set of "Unbound" Text Boxes and use these to gather the new
record
information. I then follow this by writing out the contents of each
individual "Unbound Text Box to a new record in the table using the "Save
record" routine I set out at the begining of my query after first asking
the
user if they want to Save the data or Abort. I can also check for required
fields and prompt the user if any are not completed correctly. Your
solution
is great in that it allows me to work with only one set of "Bound" text
boxes. However, the solution does seem somewhat flawed to me in that the
user
says that they want to Add a New Record but there is no method by which
the
user can subsequently say that they have made a mistake and have the
option
not to save "Duff" information. Am I missing something here?
Your help is very much appreciated. RayC

:

Actually, it should save automatically. Access does that. I can't
remember why I put that bit in there - probably paranoia on my part.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
Ahh, interesting, I will try to remember that.
With regard to your excelent solution to my problem, do I need to have
something that will save the record or will it save automatically.
Also,
should I not be putting something in there that asks the user if they
want to
save or not?

Regards RayC

:

The dirty bit forces a save of the current record if it has not yet
been
saved.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
Thanks John I will give it a try. Cureous about the "Dirty" bit
though.

I realy appreciate your help. RayC

:

Add a button to your form "New Record"

In the click event add code to go to NEW Record.

THe code you need to add for the buttons click event should look
something like

On Error GoTo Err_sCmdAdd

With Me
If .Dirty = True Then .Dirty = False

If .AllowAdditions = False Then
.AllowAdditions = True
End If

DoCmd.GoToRecord , , acNewRec

End With

Exit_sCmdAdd:
Exit Function

Err_sCmdAdd:
MsgBox Err.Number & ": " & Err.Description,



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Ray C wrote:
It's only confusing because You know what I should be diong and I
don't know
what I am doing :)
I have a Form and I have Text Boxes on thst Form that are "Bound"
to fields
in a Table. This is great if all I want to do is look at the
information in
that Table but I might want to add a new record at some time and it
is this
that I am working my way through. I don't have navigation Buttons
on my Form
so I am not able to go to the last record and enter new information
then
save. Could I do that under program control?
Hope this helps RayC

:

What are you trying to do? Sounds very confused to me.


--
The 11th day of every month:

http://truthaction.org/forum/index.php


:

I have a number of "Bound" Text Boxes that display the
appropriate records in
a Table (e.g. Mame, Address, etc) but I will want to add new
records at some
time and I also Duplicate those same Text Boxes and have them
"Unbound" so
that I can enter new info into them. I then I write that data to
a new record
by using the following :- where ****TxtBox is the "Unbound" Text
Box.

Private Sub SaveRecord()

Dim db As Database, rs1 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl_Customer", dbOpenDynaset)
rs1.MoveLast
With rs1
.AddNew ' Add new record to end of
Recordset
Object.
![Name] = NameTxtBox
![Addr1] = Addr1TxtBox
![Addr2] = Addr2TxtBox
Etc, Etc
.Update
End With
rs1.Close: db.Close

End Sub

If I could change the Text Box under Program control, it would
save
duplicating all those extra boxes.

Or is there a better way to do it?

Thanks RayC
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top