data entry in a subform...read only???

[Tarnia]

I have a subform with client name as the main form, and data, payment amount,
account balance etc as the subform. What I would like to do, and don't know
if it is possible, is have the client be able to enter new data in the fields
from the form. For example I enter the mainform, enter a name, then procede
to enter a date, etc. WHen i try this I am told that the date field is
read-only. It is not the primary key or anything, so I don't understand why
it would be. No other fields present this problem

Any help is appreciated,
Tarnia

 

[Ken Snell [MVP]]

What is the Control Source of the control into which you're trying to enter
the date value?

 

[Tarnia]

The field in the query (through a table) from which the data came (form was
created).

 

[Ken Snell [MVP]]

Is the Locked property for that control set to No?

Do I understand correctly that all other controls in the subform allow you
to edit the data?
--

Ken Snell
<MS ACCESS MVP>

 

[Tarnia]

Yes, the locked property is set to no, and you understand correctly, the
other fields will allow data entry. However when I save data it doesn't save
it in the form, but in the underlying query without updating the form and if
I try and enter a date, I am told read-only, then CAN enter a value but
again if I save, it doesn't show up.

 

[Ken Snell [MVP]]

Let's back up a bit.

I'm confused over whether the other controls actually are saving any data to
the underlying table(s) that is/are in the form's recordsource.

When you make changes on the form, do the changes show up in the table where
you expect them to change? Or do the data just change on the form when you
change them, but when you close and then reopen the form and return to that
record, the data are what they were before you changed them?

Post the SQL statement of the form's recordsource query so we can see if it
has any contents that might prevent it from being updatable.

Is each control in which you're editing data on the form bound to a field?

--

Ken Snell
<MS ACCESS MVP>

 

[Tarnia]

-Originally, it was just the date field (or so I thought) but now any field,
if I try and add data (add a new record) it says read only, will then make
the changes but not save them to the form. It WILL save them to the
underlying table, but I was hoping I could save it in the form. Right now
changes are not viewable in the form, even if I close it then reopen it.
However, it DOESN'T save changes to the name field(main form) on the table
associated with the subform, despite the one-to-many relationship I created
between the two...I take it this is normal? I.e. if I have a table of
clients' names, then and other table with their names and associated data
(payment amount, method of payment, date of lesson) and I made a change to
the subform under a clients' name (say brent) it saves the data but leaves
the clients' name blank in the underlying table. I have two tables because
in the second, name is not unique: if someone has more than one lesson on a
given day, then their name is entered twice.
-if I open just the subform, I can make changes and it saves within the form
such that when I open the form again, I can see the update in the form,unlike
when I view it with the mainform.
-all controls are bound
-there is no underlying query just tables....is this a problem?

Thanks for your help,
Tarnia

 

[Ken Snell [MVP]]

What you're describing (the data change in the underlying table but not in
the form) does not make sense if the controls on the form are bound to the
fields in the underlying table. I think this question/thread is related to
the other thread that you and I are conducting?

See that other thread for questions about the setup of your form and
subform. I need that info in order to try to figure out what you're trying
to do here.
--

Ken Snell
<MS ACCESS MVP>

 

[Tarnia]

I need you to tell me more details about
--The tables that contain the data you are displaying in the mainform:
The table I am displaying as the mainform contains client names with the
names field as the primary key (a name only needs to be entered once). The
field is a textbox, text formatted , indexed with no duplicates, unicode
compression is allowed and IME Mode is no control and IME Sentence Mode is
none

-- the tables that contain the data that you're displaying in the
subform;
This table has a dateoflesson field formatted to short date as a date/time,
it is not required or indexed; a name field (a defined relationship to the
name table: a name may be entered more than once for different lessons in the
lesson table but not in the name table) that is text and not required or
indexed, lessontype, notes, makeup and horse text fields are formatted the
same as the name, and payment amount, subtotal, gst and total currency fields
formatted as currency not required or indexed.

-- the main form's Recordsource (if it's a query, post the SQL statement
of the query);
The "name" table

-- the subform's Recordsource (if it's a query, post the SQL statement
of the query).

A query built on the payment info table described above

SELECT [lesson and payment info2].Name, [lesson and payment
info2].Dateoflesson, [lesson and payment info2].Lesson_type, [lesson and
payment info2].Horse, [lesson and payment info2].Notes, [lesson and payment
info2].Makeup_Day, [lesson and payment info2].Makeup_Month, [lesson and
payment info2].Method_of_Payment, [lesson and payment info2].Payment_Amount,
IIf([Notes]="makeup" Or [Notes]="No
Charge",0,IIf([Lesson_Type]="group",60,IIf([Lesson_type]="private",95,80)))
AS Subtotal, [Subtotal]*0.07 AS GST, [Subtotal]+[GST] AS Total
FROM [lesson and payment info2]
ORDER BY [lesson and payment info2].Name, [lesson and payment
info2].Dateoflesson;

Thanks for all your help!
Stephanie

 

[Ken Snell [MVP]]

Do not use Name as the name of a field. It and many other words are reserved
words in ACCESS, and can create serious confusion for ACCESS and Jet. See
these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


I don't see anything obvious that would be a problem with your subform other
than your naming of the linking fields Name (which may be causing your links
to not work the way you expect, because your form and your subform both have
Name properties, and ACCESS may be trying to link the two forms by those
properties, not by your fields that you've named Name); or if you're not
binding the controls to the correct fields; or if you're trying to change a
value of a control that is bound to a calculated field in the subform.

Try changing the field names, change the LinkMasterFields and
LinkChildFields to the new names, and be sure that you're not trying to
change the value of controls that are bound to fields such as Subtotal, GST,
or Total.
--

Ken Snell
<MS ACCESS MVP>

I need you to tell me more details about
--The tables that contain the data you are displaying in the mainform:
The table I am displaying as the mainform contains client names with the
names field as the primary key (a name only needs to be entered once).
The
field is a textbox, text formatted , indexed with no duplicates, unicode
compression is allowed and IME Mode is no control and IME Sentence Mode is
none

-- the tables that contain the data that you're displaying in the
subform;
This table has a dateoflesson field formatted to short date as a
date/time,
it is not required or indexed; a name field (a defined relationship to the
name table: a name may be entered more than once for different lessons in
the
lesson table but not in the name table) that is text and not required or
indexed, lessontype, notes, makeup and horse text fields are formatted
the
same as the name, and payment amount, subtotal, gst and total currency
fields
formatted as currency not required or indexed.

-- the main form's Recordsource (if it's a query, post the SQL
statement
of the query);
The "name" table

-- the subform's Recordsource (if it's a query, post the SQL statement
of the query).

A query built on the payment info table described above

SELECT [lesson and payment info2].Name, [lesson and payment
info2].Dateoflesson, [lesson and payment info2].Lesson_type, [lesson and
payment info2].Horse, [lesson and payment info2].Notes, [lesson and
payment
info2].Makeup_Day, [lesson and payment info2].Makeup_Month, [lesson and
payment info2].Method_of_Payment, [lesson and payment
info2].Payment_Amount,
IIf([Notes]="makeup" Or [Notes]="No
Charge",0,IIf([Lesson_Type]="group",60,IIf([Lesson_type]="private",95,80)))
AS Subtotal, [Subtotal]*0.07 AS GST, [Subtotal]+[GST] AS Total
FROM [lesson and payment info2]
ORDER BY [lesson and payment info2].Name, [lesson and payment
info2].Dateoflesson;

Thanks for all your help!
Stephanie

What you're describing (the data change in the underlying table but not
in
the form) does not make sense if the controls on the form are bound to
the
fields in the underlying table. I think this question/thread is related
to
the other thread that you and I are conducting?

See that other thread for questions about the setup of your form and
subform. I need that info in order to try to figure out what you're
trying
to do here.

 

[Tarnia]

Changing the name field fixed it. I'm very sorry that you had to tell me to
read those articles again! I obviously wasn't paying attention the first
time.

Thanks again,
Tarnia

Do not use Name as the name of a field. It and many other words are reserved
words in ACCESS, and can create serious confusion for ACCESS and Jet. See
these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


I don't see anything obvious that would be a problem with your subform other
than your naming of the linking fields Name (which may be causing your links
to not work the way you expect, because your form and your subform both have
Name properties, and ACCESS may be trying to link the two forms by those
properties, not by your fields that you've named Name); or if you're not
binding the controls to the correct fields; or if you're trying to change a
value of a control that is bound to a calculated field in the subform.

Try changing the field names, change the LinkMasterFields and
LinkChildFields to the new names, and be sure that you're not trying to
change the value of controls that are bound to fields such as Subtotal, GST,
or Total.
--

Ken Snell
<MS ACCESS MVP>

I need you to tell me more details about
--The tables that contain the data you are displaying in the mainform:
The table I am displaying as the mainform contains client names with the
names field as the primary key (a name only needs to be entered once).
The
field is a textbox, text formatted , indexed with no duplicates, unicode
compression is allowed and IME Mode is no control and IME Sentence Mode is
none

-- the tables that contain the data that you're displaying in the
subform;
This table has a dateoflesson field formatted to short date as a
date/time,
it is not required or indexed; a name field (a defined relationship to the
name table: a name may be entered more than once for different lessons in
the
lesson table but not in the name table) that is text and not required or
indexed, lessontype, notes, makeup and horse text fields are formatted
the
same as the name, and payment amount, subtotal, gst and total currency
fields
formatted as currency not required or indexed.

-- the main form's Recordsource (if it's a query, post the SQL
statement
of the query);
The "name" table

-- the subform's Recordsource (if it's a query, post the SQL statement
of the query).

A query built on the payment info table described above

SELECT [lesson and payment info2].Name, [lesson and payment
info2].Dateoflesson, [lesson and payment info2].Lesson_type, [lesson and
payment info2].Horse, [lesson and payment info2].Notes, [lesson and
payment
info2].Makeup_Day, [lesson and payment info2].Makeup_Month, [lesson and
payment info2].Method_of_Payment, [lesson and payment
info2].Payment_Amount,
IIf([Notes]="makeup" Or [Notes]="No
Charge",0,IIf([Lesson_Type]="group",60,IIf([Lesson_type]="private",95,80)))
AS Subtotal, [Subtotal]*0.07 AS GST, [Subtotal]+[GST] AS Total
FROM [lesson and payment info2]
ORDER BY [lesson and payment info2].Name, [lesson and payment
info2].Dateoflesson;

Thanks for all your help!
Stephanie

What you're describing (the data change in the underlying table but not
in
the form) does not make sense if the controls on the form are bound to
the
fields in the underlying table. I think this question/thread is related
to
the other thread that you and I are conducting?

See that other thread for questions about the setup of your form and
subform. I need that info in order to try to figure out what you're
trying
to do here.