Update Query Running From Form

[Jeff Freilich]

Hi All

I am having a problem that doesnt make any sense to me - here is what
I have

A form that runs an update query to add records to a table
Everything was working fine - then I set up Referential Integrity on
the table and now I am havnig problems

The designed set up is that I open a form to add a new record and
after running some functions the update query is triggered. Couple of
things to point out:

The Form is opened in Dialogue Mode
When adding a new record the record is not saved (obviously) - I have
put a line ot code in - DoCmd.RunCommand (acCmdSaveRecord) - which is
supposed to force a save on the record before the update query is
triggered

The Update Query will work properly:
The 2nd time I open the form (i.e the record has been saved)
I step through the code to the DoCmd.RunCommand (acCmdSaveRecord) line
and it actually saves the record
I open the form first 1st time not in dialogue mode
I remove referential integrity

It will not work on the frst open of the form when adding a record
If Referential integrity is set up
If I open the form in dialogue mode

When the update query does not work I seem to be getting key vialotion
errors on my update query - it is as if the record is not being saved
if the form is open in dialoge mode and me DoCmd.RunCommand
(acCmdSaveRecord) is not being run on the dialogue form if I am not
stepping through it.

Any thoughts on what else I might be dong wrong - or anything else I
should look to try

If you need any more details please let me know

Thanks in advance for your assistance

Jeff

 

[Jeff Boyce]

Jeff

Not sure I followed the explanation, but here's what I did notice:

1. You had a form that worked
2. You added referential integrity ("to the table" -- ?what table? ?any
queries using that table?)
3. The form stopped working

If that's an accurate paraphrase, I'm going to guess that the way you set up
RI is in conflict with what you are expecting the form to do. That error
message you mentioned about "key violation" is another clue.

Referential Integrity is usually set between tables ... but you only
mentioned one, and didn't describe it.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

What Jeff said, plus:

1. Update queries don't add records, they modify field values.

2. Key violations are an indication that you have indexes or validation
rules setup in your table. Check your indices. The referential Integrity
rules are just validation rules against another table. Make sure you are not
trying to save a text value from a bound combo box or list (probably with a
RowSource that points to one of the tables in your relationships diagram) in
a numeric field.

3. In forms, I like to use:

me.Dirty = False

to force the current record to save. Just a technique difference, but
shorter to type than:

docmd.runcommand accmdsaverecord

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.