Saving values in a field of a table from a textbox

[Guest]

Hello there,

I have a form bound to a table (I'll call them the patient form and the
patient table); there I enter data for each patient and have a AddNew button
that saves what I have entered and empties the fields so that I can enter the
data for a new patient. I just added to my database a main menu form with
command buttons so that the user can select an specific department. So if the
user clicks on the "Department A" button, the patient form is open and the
header (a text box) reads "Department A". The message in the textbox changes
based on the button that the user clicks on in the main menu.

What I would like to do is that when I click on the AddNew button in the
form, the new record not only gets saved in the table but also that a field
in the patient table, called "Department" gets the value of the text box in
the form. In short, what I'm trying to accomplish is that if the user has
already selected "Department A" in the main menu, he would not have to enter
the same info every time a new patient comes in.

Ideas?

Thanks!

 

[Marshall Barton]

I have a form bound to a table (I'll call them the patient form and the
patient table); there I enter data for each patient and have a AddNew button
that saves what I have entered and empties the fields so that I can enter the
data for a new patient. I just added to my database a main menu form with
command buttons so that the user can select an specific department. So if the
user clicks on the "Department A" button, the patient form is open and the
header (a text box) reads "Department A". The message in the textbox changes
based on the button that the user clicks on in the main menu.

What I would like to do is that when I click on the AddNew button in the
form, the new record not only gets saved in the table but also that a field
in the patient table, called "Department" gets the value of the text box in
the form. In short, what I'm trying to accomplish is that if the user has
already selected "Department A" in the main menu, he would not have to enter
the same info every time a new patient comes in.

A couple of ways to do that. I think the clearest way is to
use the patients form's Open event to set the department
text box's DefaultValue:

If Not IsNull(Forms!menuform.txtdept) Then
Me.txtDepartment.DefaultValue = Forms!menuform.txtdept
End If


That may not be quite right, but you didn't say how the
header text box gets the department from the menu form.

 

[Guest]

The code to add the text in the textbox is below. When the user clicks on the
button "Department A" this code is triggered:

Private Sub Command31_Click()
DoCmd.OpenForm "frm_Patient", acNormal
Forms!frm_Patient!txt_Department = "Department A"
End Sub

up to that point everything works fine. The problem is when I am in the
patient form and try to save a new record. This is the code I entered (as a
note, the assignment statement for the DEPARTMENT field doesn't work; I left
it there to give you an idea of what I'm trying to get):

Private Sub cmd_AddRecord_Click()
tbl_Patient!Department = Forms!frm_Patient!txt_Department
DoCmd.GoToRecord , , acNewRec
End Sub

All the fields in the patient form are bound to the patient table. The
txt_Department textbox is not. When I click on the AddRecord button I would
like to save the value of the txt_Department textbox in the Department field
of the patient table for that record that I entered in the form and am
saving. Hope I explained clearly what I neet.

Thanks for your help!

 

[Marshall Barton]

The code to add the text in the textbox is below. When the user clicks on the
button "Department A" this code is triggered:

Private Sub Command31_Click()
DoCmd.OpenForm "frm_Patient", acNormal
Forms!frm_Patient!txt_Department = "Department A"
End Sub

up to that point everything works fine. The problem is when I am in the
patient form and try to save a new record. This is the code I entered (as a
note, the assignment statement for the DEPARTMENT field doesn't work; I left
it there to give you an idea of what I'm trying to get):

Private Sub cmd_AddRecord_Click()
tbl_Patient!Department = Forms!frm_Patient!txt_Department
DoCmd.GoToRecord , , acNewRec
End Sub

All the fields in the patient form are bound to the patient table. The
txt_Department textbox is not. When I click on the AddRecord button I would
like to save the value of the txt_Department textbox in the Department field
of the patient table for that record that I entered in the form and am
saving. Hope I explained clearly what I neet.

Here's how I would do that:

Private Sub Command31_Click()
DoCmd.OpenForm "frm_Patient", acNormal, _
OpenArgs:= "Department A"
End Sub


In the patients form:

Private Sub Form_Load(...
Me.txt_Department = Me.OpenArgs
Me.boundDepartmenttextbox.DefaultValue=Nz(Me.OpenArgs,"")
End Sub

Private Sub cmd_AddRecord_Click()
DoCmd.GoToRecord , , acNewRec
End Sub

 

[steve]

Marshal is way more knowing about this stuff than I am but another way to do
this is to set the default value of the text box in the properties. This
only works for new records though.

Steve

 

[Guest]

Hello Marshall,

I did what you adviced me to, but I'm still stuck with issues here. The
first part works fine, the openargs value is shown in a text box in the form.
The problem is with the bound field. The code for the load event of the form
is:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
Me.txt_Header_Hospital = Me.openargs
Me.HOSPITAL.DefaultValue = Nz(Me.openargs, "")
End Sub

txt_header_Hospital shows the value that I entered before, but the textbox
that shows the field bound to the table, HOSPITAL, has an error (#NAME?).
Needless to say, when I save that record and then check the table, there's no
value for the HOSPITAL field. Can you think of anything I'm doing wrong?
Thanks.

 

[Guest]

Hi Steve,

Thanks for your advice. You know, that is something that I tried before
posting the question in this discussion group. If I entered in the default
value something like ="Department A" it would work fine, but the problem is
that the default value is variable, depending on the button that the user
clicks on in the main menu. So I tried by entering in the default value
something like =[txt_Department]...and it didn't work. That text box does
show the value that has to go to the table for that record, but when I
entered that expression in the default value box and changed from the design
view to "view", there was no default value; the field was blank. Are you sure
this should work even if I referenced to a text box instead of a fixed value?
If you're positive then, do you have an idea of what I'm doing wrong? Thanks!

 

[Guest]

I forgot to tell you: for some reason, although the OpenArgs property works,
when i typed it in (I actually selected it from the autofill box) after
clicking on Enter, it became all lowercase. I thought it was weird, in
general all those functions have capital letters as in the way you typed it
in before (OpenArgs), not openargs....not sure if that is part of my problem;
just wanted to let you know in case that has something to do with the fact
that I cannot get that value to be saved in the table.

Thanks again!

 

[Marshall Barton]

I did what you adviced me to, but I'm still stuck with issues here. The
first part works fine, the openargs value is shown in a text box in the form.
The problem is with the bound field. The code for the load event of the form
is:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
Me.txt_Header_Hospital = Me.openargs
Me.HOSPITAL.DefaultValue = Nz(Me.openargs, "")
End Sub

txt_header_Hospital shows the value that I entered before, but the textbox
that shows the field bound to the table, HOSPITAL, has an error (#NAME?).
Needless to say, when I save that record and then check the table, there's no
value for the HOSPITAL field. Can you think of anything I'm doing wrong?
Thanks.

 

[Marshall Barton]

Excuse the fat fingers.

I think the problem is that I forgot to quote the defaults
value. For text values, it should have been:

Me.HOSPITAL.DefaultValue = """" & Me.openargs & """"

 

[Marshall Barton]

I forgot to tell you: for some reason, although the OpenArgs property works,
when i typed it in (I actually selected it from the autofill box) after
clicking on Enter, it became all lowercase. I thought it was weird, in
general all those functions have capital letters as in the way you typed it
in before (OpenArgs), not openargs....not sure if that is part of my problem;
just wanted to let you know in case that has something to do with the fact
that I cannot get that value to be saved in the table.

This some times happens when Access thinks you have created
a VBA variable named openargs (or any other built in name).
You can usually straighten Access out, by declaring a
variable with the proper capitalization:
Dim OpenArgs
and click on some other line. All of the openargs should
change to OpenArgs. Then delete the declaration and
everything should(?) all be hunky dory again.

 

[Guest]

You're the man, Marsh!

Thanks a lot, you've solved a long-time problem that I had.

Please forgive my abuse, but since you've shown being really knowledgeable,
if you don't mind i would like to ask you another thing. I've posted this
problem in the discussion group as well, but so far I have not received a
response that solves the problem. So, again, if it is ok with you, I would
like to ask for some advice to solve this issue; thanks again for all your
help and patience!

-----------

Hello there,

I would like to add an statement to the close button on a main form that
prevents the main form from closing if there are values in the NAME field of
a subform. The subform is a datasheet form, so I can see several lines of
data. The idea is that the user would only be able to close the main form if
there are no values at all in the NAME field of the subform. This is the
query I have:

If Not IsNull(Me!sfrm_Pts_Queue_Or_InProcess.Form![PATIENT NAME]) Then
MsgBox "There are patients still being admitted"
DoCmd.CancelEvent
Else
DoCmd.Close acForm, frm_Pt_Info_FrontDesk
End If

However, when I run this I get "This actions requires an Object Name
argument"

Ideas?

Thanks!

alepag

-------

Excuse the fat fingers.

I think the problem is that I forgot to quote the defaults
value. For text values, it should have been:

Me.HOSPITAL.DefaultValue = """" & Me.openargs & """"
--
Marsh
MVP [MS Access]

I did what you adviced me to, but I'm still stuck with issues here. The
first part works fine, the openargs value is shown in a text box in the form.
The problem is with the bound field. The code for the load event of the form
is:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
Me.txt_Header_Hospital = Me.openargs
Me.HOSPITAL.DefaultValue = Nz(Me.openargs, "")
End Sub

txt_header_Hospital shows the value that I entered before, but the textbox
that shows the field bound to the table, HOSPITAL, has an error (#NAME?).
Needless to say, when I save that record and then check the table, there's no
value for the HOSPITAL field. Can you think of anything I'm doing wrong?
Thanks.

 

[Marshall Barton]

Thanks a lot, you've solved a long-time problem that I had.

Please forgive my abuse, but since you've shown being really knowledgeable,
if you don't mind i would like to ask you another thing. I've posted this
problem in the discussion group as well, but so far I have not received a
response that solves the problem. So, again, if it is ok with you, I would
like to ask for some advice to solve this issue; thanks again for all your
help and patience!
-----------
I would like to add an statement to the close button on a main form that
prevents the main form from closing if there are values in the NAME field of
a subform. The subform is a datasheet form, so I can see several lines of
data. The idea is that the user would only be able to close the main form if
there are no values at all in the NAME field of the subform. This is the
query I have:

If Not IsNull(Me!sfrm_Pts_Queue_Or_InProcess.Form![PATIENT NAME]) Then
MsgBox "There are patients still being admitted"
DoCmd.CancelEvent
Else
DoCmd.Close acForm, frm_Pt_Info_FrontDesk
End If

However, when I run this I get "This actions requires an Object Name
argument"

I don't think you can cancel a Click event. Actually, there
is no need anyway, just exit the event procedure or don't so
anything in the subsequent code.

I think the error message is coming from the Close method,
because you need the form name as a string:
DoCmd.Close acForm, Me.Name
or, less general:
DoCmd.Close acForm, "frm_Pt_Info_FrontDesk"

Your If statement is only checking the name field in the
subform's current record, not all records. If the subform
can have records without a patient name value, you will need
a bunch more code. If every subform record is required to
have a patient name, then just check the subform's record
count:

If Me!sfrm_Pts_Queue_Or_InProcess.Form.Recordset.RecordCount

 

[Guest]

Did I already tell you that you're the man?

Thanks so much!!

Thanks a lot, you've solved a long-time problem that I had.

Please forgive my abuse, but since you've shown being really knowledgeable,
if you don't mind i would like to ask you another thing. I've posted this
problem in the discussion group as well, but so far I have not received a
response that solves the problem. So, again, if it is ok with you, I would
like to ask for some advice to solve this issue; thanks again for all your
help and patience!
-----------
I would like to add an statement to the close button on a main form that
prevents the main form from closing if there are values in the NAME field of
a subform. The subform is a datasheet form, so I can see several lines of
data. The idea is that the user would only be able to close the main form if
there are no values at all in the NAME field of the subform. This is the
query I have:

If Not IsNull(Me!sfrm_Pts_Queue_Or_InProcess.Form![PATIENT NAME]) Then
MsgBox "There are patients still being admitted"
DoCmd.CancelEvent
Else
DoCmd.Close acForm, frm_Pt_Info_FrontDesk
End If

However, when I run this I get "This actions requires an Object Name
argument"

I don't think you can cancel a Click event. Actually, there
is no need anyway, just exit the event procedure or don't so
anything in the subsequent code.

I think the error message is coming from the Close method,
because you need the form name as a string:
DoCmd.Close acForm, Me.Name
or, less general:
DoCmd.Close acForm, "frm_Pt_Info_FrontDesk"

Your If statement is only checking the name field in the
subform's current record, not all records. If the subform
can have records without a patient name value, you will need
a bunch more code. If every subform record is required to
have a patient name, then just check the subform's record
count:

If Me!sfrm_Pts_Queue_Or_InProcess.Form.Recordset.RecordCount

0 Then