command button to save form data

[Guest]

I must apologize if this is not the right forum for this question. I am not
sure the problem isn't a field type mismatch, but here goes...

I have a form to collect data for a student report card. It collects data
from database tables by way of form fields as well as user-typed text in
unbound fields (including one for comments).

Once all the data is collected, the user is promted to save it to a database
table before printing. This is accomplished by a macro running an append
query on the click of a command button.

The problem is with a comment field. As long as the comments are kept within
a few words, the save operation completes successfully. However, if the field
size exceeds about 120 characters (or there abouts...) the save operation
hangs the whole application. There is no error message comes up, but if you
try and close the form or the database when nothing seems to be happening, it
fails because somewhere there is still code running, but the error that then
does come up doesn's say where or what the code is, nor can I see anything
wrong if I open the VBA code window.

With prolific English teachers <G>, I would like the comment field not to be
limited, or at least to be able to capture the error when it fails to save
and report it to users!

I have tried making the comment field in the table a text field with a field
size of 255 characters (the max, yes?) as well as a memo field. Both choices
result in the same failure, thus, my thinking it might be an issue with the
query that appends the table data with the new form data.

I hope this makes sense!

 

[Rick B]

Why are you using unbound controls and macros to update your tables? Pretty
cumbersome.

Why not just bind all the form controls to the table and use the automatic
features of Access to save when the user moves to another record? The
comment field would be a memo field and should work fine for you then.

I often see similar posts and rarely find a good reason why they went
through all the trouble to write routines to update a table when Access does
such a good job of this by default. I'm not saying there are not reasons to
do it the way you do, but it is rare.

 

[Guest]

--
BJM
ACE Assistant
Gary Allan High School

Thank you for your response, Rick.

Why are you using unbound controls and macros to update your tables? Pretty
cumbersome.

Yes. Normally, I would agree.

Why not just bind all the form controls to the table and use the automatic
features of Access to save when the user moves to another record?

The data is being brought in from a number of different tables (student,
teacher, enrolment, attendance...) and some fields are not from an underlying
table at all, but are being collected by the teachers typing information into
them. It is this that is being saved back to a report card table. I hope this
makes sense!

The

comment field would be a memo field and should work fine for you then.

I often see similar posts and rarely find a good reason why they went
through all the trouble to write routines to update a table when Access does
such a good job of this by default. I'm not saying there are not reasons to
do it the way you do, but it is rare.

If there were a simpler way to do this, I would be happy to hear it!

Thanks again,

 

[Rick B]

Without knowing more about the structure of your database, it would be very
difficult to answer your question. Most of the time when you are adding
records, you will pull some data and store others. For example...

In the Northwind sample database when you go to create an invoice, you pull
in customer information from one table, you enter part numbers and pull in
part data from another, but you store the details of the order in yet
another table. No code is needed to accomplish this.

You should be able to enter a student Id, have their name and other
information display, select a subject from a table of subjects and have the
subject name display, select a teacher ID and have that teacher name
display, then enter data to record grades and have that stored in a table of
grades. None of this would require a button to append or update a table.
The form is build on your grades table (or a query) and the other fields are
lookups from other tables.

I would not think your described structure would require anything out of the
ordinary.

--
Rick B

--
BJM
ACE Assistant
Gary Allan High School

Thank you for your response, Rick.

Why are you using unbound controls and macros to update your tables?
Pretty
cumbersome.

Yes. Normally, I would agree.

Why not just bind all the form controls to the table and use the
automatic
features of Access to save when the user moves to another record?

The data is being brought in from a number of different tables (student,
teacher, enrolment, attendance...) and some fields are not from an
underlying
table at all, but are being collected by the teachers typing information
into
them. It is this that is being saved back to a report card table. I hope
this
makes sense!

The

comment field would be a memo field and should work fine for you then.

I often see similar posts and rarely find a good reason why they went
through all the trouble to write routines to update a table when Access
does
such a good job of this by default. I'm not saying there are not reasons
to
do it the way you do, but it is rare.

If there were a simpler way to do this, I would be happy to hear it!

Thanks again,