acNewRec general question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey all,

This is kinda interesting, so I thought I'd share and get some input:

By using VBA code on my 2K db, I found I couldn't add a record using
DoCmd.RunSQL because one of the fields to which the SQL was trying to write
was null, and the underlying field on the table had the "Allow Zero Length"
option set to "No". The attempt to write to the db gave me a validation rule
warning when it failed to update the db. This is fine; I adjusted the table
and all is good.

However, this begs a different question. I have a form that has been
successfully writing to the db all along. The command key to enter the info
uses the generic acNewRec command to enter the data; I can successfully enter
a new record through the form whether or not this aformentioned field is
populated.

Is there something about the acNewRec command that doesn't recognize that
particular validation rule? I realize indicating a failure of an established
process to produce a desired result is idiotic, as there is SO much user
error that can occur, but I swear I eliminated all other common traits in
trying to diagnose this, and it really does seem the same record, as a
DoCmd.RunSQL text string, will fail on that single field validation rule,
while succesfully insert using the form.

Thanks in advance,
Paul
 
the acNewRec doesn't create a new record, it just put in a position to enter
a new record, the new record created only after you entered data to the form
and when you exit the form, or when you moving to another record.
The question is, after you enter all the data to the form, before you exit
the form to you enter a value to this specific field.
If you do, then you save the new record with a value in it, so there is no
reason to get an error message.

Also if you have a default value in the field, you will still get an error
message if you try to insert null value in it, but you wont get any error
message if you are not trying to insert any value, because then it will
insert the default automatically.
 
Hi Ofer,

Thanks for the response.

The command button I use to send the new record to the database from the
form contains the line:

DoCmd.GoToRecord, ,acNewRec

and nothing after that (except for error handling, etc.). I'm using this
button as the last step in entering data; I enter the data in the text boxes
on the form, then hit the command button to send it to the database.

Also, the field does not have a default value.

Does this tell you anything helpful?

Thanks again. This is pretty fascinating.
Paul
 
When you run the command
DoCmd.GoToRecord, ,acNewRec
It's not adding any record to the table, its like openning a table and
moving to the last empty line in that table, it doesn't add any records until
you start typing data and moving to the next record.

So if you update the date field in the form before you update the table then
there is no reason to have an error message.
 
Back
Top