By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,085 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

How to handle Primary Key violations in a bound form without causing and ODBC error.

P: n/a
FYI:

This message is for the benefit of MS Access Community. I found that
this prblem has been encounterd by many but there is hardly any place
where a complete solution is posted. So I thought I should give back
to the community by posting our findings.
Thanks you all for all your help till now by posting problems and
their solutions.

~Abhijit
http://www.ececs.uc.edu/~joshiabh/Ab...ML_General.htm

Problem: 1. How to handle Primary Key violations in a bound form
without causing and ODBC error.
2. Cannot Trap Specific ODBC Errors on OnOpen Property of a Form
Members involved in the research: Joe, Jason, Loren, Abhijit
Special Thanks to Joe for researching and intelligent implementations
of these ideas!
Findings:
1. How to handle Primary Key violations in a bound form without
causing and ODBC error.
The best way we found to handle Primary Key violations in a bound form
without causing and ODBC error is to prevent user from inserting or
changing invalid data into the fields.
In our case for example, we have state and zone as composite primary
key.
The way we can stop use from entering a duplicate combination of state
and zone is as follows:
Private Sub Form_BeforeUpdate_GOOD(Cancel As Integer)
'If Me.txtFeatureCosts.OldValue <> Me.txtFeatureCosts.Value Then
If Me.comboStates.OldValue <> Me.comboStates.Value Or _
Me.comboZones.OldValue <> Me.comboZones.Value Or _
Me.NewRecord Then
If Not IsNull(DLookup("zone", "table_name", "State = """ &
comboStates & """ AND Zone = """ & comboZones & """")) Then
MsgBox "This State Zone Combination Already Exists." &
vbNewLine & "Please try a different State/Zone Combination and try
again.", vbCritical + vbOKOnly, "Duplicate State/Zone Detected"
Cancel = True
End If
End If
End Sub

OR another method is to create a recordsetclone of the query or table
on which the form is based and find if user is trying to make any
changes to the primary keys.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rc As Recordset
Set rc = Me.RecordsetClone
rc.Bookmark = Me.Bookmark
If rc.Fields(Me.comboStates.ControlSource) <> Me.comboStates.Value Or
_
rc.Fields(Me.comboZones.ControlSource) <> Me.comboZones.Value Or _
Me.NewRecord Then
'If Me.NewRecord Then
If Not IsNull(DLookup("zone", "table_name", "State = """ &
comboStates & """ AND Zone = """ & comboZones & """")) Then
MsgBox "This State Zone Combination Already Exists." &
vbNewLine & "Please try a different State/Zone Combination and try
again.", vbCritical + vbOKOnly, "Duplicate State/Zone Detected"
Cancel = True
End If
End If
Set rc = Nothing
End Sub
2. Catching the ODBC error:
This solution is priovided by Microsoft for Access 2000. Joe changed
it to suit Access 97.
Access 97 has RecordsetClone Propery whereas Access 2000 has
Recordset.Clone Propery. We hhad to make that change in the code in
the following article.
http://support.microsoft.com/default...NoWebContent=1
Basically the ODBC errors caused by Primary Key violations with linked
tables are not directly possible to catch. We have to simulate that
violation on a local recordset and catch the error thathas been caused
and give out the customised error message. But for this we have to
make a complete recordsetClone of the whole table or Query on which
the form is based on. This is an inefficient and at times impractical
solution because the whole table is replicated on the client side,
which is not desirable. I am posting the solution here. If you have
any questions please let me know.
Public Function SaveRecODBC(SRO_form As Form) As Boolean
'************************************************* **************
'Function: SaveRecODBC
'
'Purpose: Updates a form based on a linked ODBC table
' and traps any ODBC errors.
'
'Arguments: SRO_Form, which refers to the form.
'
'
'Returns: True if successful or False if an error occurs.
'************************************************* **************

On Error GoTo SaveRecODBCErr
Dim fld As Field, ctl As Control
Dim errStored As Error
Dim rc As DAO.Recordset

' Check to see if the record has changed.
If SRO_form.Dirty Then

Set rc = SRO_form.RecordsetClone
If SRO_form.NewRecord Then
rc.AddNew
For Each ctl In SRO_form.Controls
' Check to see if it is the type of control
' that has a ControlSource.
If ctl.ControlType = acTextBox Or _
ctl.ControlType = acComboBox Or _
ctl.ControlType = acListBox Or _
ctl.ControlType = acCheckBox Then
' Verify that a value exists in the ControlSource.
If ctl.Properties("ControlSource") <> "" Then
' Loop through the fields collection in the
' RecordsetClone. If you find a field name
' that matches the ControlSource, update the
' field. If not, skip the field. This is
' necessary to account for calculated controls.

For Each fld In rc.Fields
' Find the field and verify
' that it is not Null.
' If it is Null, don't add it.
If fld.Name = ctl.Properties("ControlSource") _
And Not IsNull(ctl) Then
fld.Value = ctl
' Exit the For loop
' if you have a match.
Exit For
End If
Next fld

End If ' End If ctl.Properties("ControlSource")

End If ' End If ctl.controltype

Next ctl
rc.Update

Else
' This is not a new record.
' Set the bookmark to synchronize the record in the
' RecordsetClone with the record in the form.
rc.Bookmark = SRO_form.Bookmark
rc.Edit

For Each ctl In SRO_form.Controls
' Check to see if it is the type of control
' that has a ControlSource.
If ctl.ControlType = acTextBox Or _
ctl.ControlType = acComboBox Or _
ctl.ControlType = acListBox Or _
ctl.ControlType = acCheckBox Then

' Verify that a value exists in the
' ControlSource.
If ctl.Properties("ControlSource") <> "" Then

' Loop through the fields collection in the
' RecordsetClone. If you find a field name
' that matches the ControlSource, update the
' field. If not, skip the field. This is
' necessary to account for calcualted controls.

For Each fld In rc.Fields

' Find the field and make sure that the
' value has changed. If it has not
' changed, do not perform the update.
If fld.Name = ctl.Properties("ControlSource") _
And fld.Value <> ctl And _
Not IsNull(fld.Value <> ctl) Then

fld.Value = ctl
' Exit the For loop if you have a match.
Exit For
End If

Next fld

End If ' End If ctl.Properties("ControlSource")

End If ' End If ctl.controltype

Next ctl

rc.Update

End If ' End If SRO_form.NewRecord

End If ' End If SRO_form.Dirty
' If function has executed successfully to this point then
' set its value to True and exit.
SaveRecODBC = True

Exit_SaveRecODBCErr:
Exit Function

SaveRecODBCErr:
' The function failed because of an ODBC error.
' Below are a list of some of the known error numbers.
' If you are not receiving an error in this list,
' add that error to the Select Case statement.

For Each errStored In DBEngine.Errors
Select Case errStored.Number
Case 3146
MsgBox " No action -- standard ODBC--Call failed error."
Case 2627
MsgBox " Error caused by duplicate value in primary key."
MsgBox "You tried to enter a duplicate value " & _
"in the Primary Key."
Case 3621
MsgBox " No action -- standard ODBC command aborted error."
Case 547
MsgBox " Foreign key constraint error."
MsgBox "You violated a foreign key constraint."
Case Else
MsgBox errStored.Description
MsgBox errStored.Number
' An error not accounted for in the Select Case
' statement.
On Error GoTo 0
Resume

End Select
Next errStored

Dim MyError As Error
MsgBox Errors.count & " Of Errors Found "
For Each MyError In DBEngine.Errors
With MyError
MsgBox .Number & " Of Errors Found " & .Description
End With
Next MyError
SaveRecODBC = False
Resume Exit_SaveRecODBCErr

End Function
Nov 13 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Gee, this posting is rather long...don't you think?

I mean, if you are going to spend the time to post an example of how to stop
a primary key violation, why so much example code on combo box stuff?

Why not post a nice clean example for ONE field? Like, say a company name
field?
(easier to understand...and much cleaner then a bunch of combo box stuff).

Further, you are often MUCH better off to use the before update of the
actual control in place of the form.

The reason why you would want use the controls before update
event (in place of the forms before update event) is that the controls
event DOES NOT fire
unless the value is changed!. Thus, during data entry, you don't have to
run all your code checks with "oldvalue" as you have. Further, even
better for general data entry, you now don't have to run all that code
EACH TIME a record is updated! So, there is code saved to tell
if the field was changed (ie: the before update event does not run!!)
And, further, you don't need to run the check code each time a
record is updated..but ONLY when those controls are updated.

So, no need to run all that code each time a record is updated???)

Further, the added benefit is that user gets feedback right away as opposed
to filling out some huge form.......ONLY to get a nasty message AFTER all
the data has been entered in the form.

So, now we got less code, less system resources, and better UI for the user!

As for trapping the odbc error?. Hum, as you point out, that error don't
happen if you put in a simple check in the controls before update event.

Further, you CAN trap odbc errors. Just use the forms "error" event.

You can go:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

MsgBox "You can't update data"

Response = acDataErrContinue

End Sub

So, I count what...2 lines of code to trap the error message?

I am not really sure of the wisdom to use such a large hunk of code when the
above is just fine?

Am I missing some point here?

Look, I not trying to rain on your efforts to help people here (that is
wonderful). However, when I look at the amount code you posted...it seems
you are rather angry..then really trying to help anyone!!!

So, as a side note...the forms "on error" does trap odbc, and any reason why
you did not use that approach?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #2

P: n/a

"Abhi" <ja*******@yahoo.com> wrote in message
news:80*************************@posting.google.co m...
Ah, look at all the Access Morons
Ah, look at all the Access Morons

All the Access Morons
Where do they all come from?
All the Access Morons
Where do they all belong?

Ah, look at all the Access Morons
Ah, look at all the Access Morons
Nov 13 '05 #3

P: n/a
hey Mr Poet, why are you singing your funeral song of your brain? If you
lick my behind I will probably tell you where do Access morons come
from. By that time you can sell your ass and buy some brains. Anyway I
am not interested in your shit attitude. If you post a reply, I will not
be reading it. But atleast you I will make you waste some more energey
!! haha, Loser!!
~a
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

P: n/a
Actually, the point of the code is to replace the entire record update or add
operation with the same operation performed directly on the form's
RecordsetClone, so errors can be trapped at the point the recordset is saved
by the code. Each control's BeforeUpdate handler is not a good place to check
for changes in this case, because you would simply have to maintain a list of
which fields had changed, then loop through that.

By the way, I've experimeted with code like this before, and it's very
problematic in Access 2000. Don't use Access 2000 if you need to do this kind
of thing, but it can be made to work OK in Access 97 or 2002.

On Tue, 31 Aug 2004 21:29:15 GMT, "Albert D. Kallal"
<Pl*******************@msn.com> wrote:
Gee, this posting is rather long...don't you think?

I mean, if you are going to spend the time to post an example of how to stop
a primary key violation, why so much example code on combo box stuff?

Why not post a nice clean example for ONE field? Like, say a company name
field?
(easier to understand...and much cleaner then a bunch of combo box stuff).

Further, you are often MUCH better off to use the before update of the
actual control in place of the form.

The reason why you would want use the controls before update
event (in place of the forms before update event) is that the controls
event DOES NOT fire
unless the value is changed!. Thus, during data entry, you don't have to
run all your code checks with "oldvalue" as you have. Further, even
better for general data entry, you now don't have to run all that code
EACH TIME a record is updated! So, there is code saved to tell
if the field was changed (ie: the before update event does not run!!)
And, further, you don't need to run the check code each time a
record is updated..but ONLY when those controls are updated.

So, no need to run all that code each time a record is updated???)

Further, the added benefit is that user gets feedback right away as opposed
to filling out some huge form.......ONLY to get a nasty message AFTER all
the data has been entered in the form.

So, now we got less code, less system resources, and better UI for the user!

As for trapping the odbc error?. Hum, as you point out, that error don't
happen if you put in a simple check in the controls before update event.

Further, you CAN trap odbc errors. Just use the forms "error" event.

You can go:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

MsgBox "You can't update data"

Response = acDataErrContinue

End Sub

So, I count what...2 lines of code to trap the error message?

I am not really sure of the wisdom to use such a large hunk of code when the
above is just fine?

Am I missing some point here?

Look, I not trying to rain on your efforts to help people here (that is
wonderful). However, when I look at the amount code you posted...it seems
you are rather angry..then really trying to help anyone!!!

So, as a side note...the forms "on error" does trap odbc, and any reason why
you did not use that approach?


Nov 13 '05 #5

P: n/a
Albert:
You sound very logical. Your constructive critisim is useful. But I also
noticed a slight tone mockery and it is quite unsophisticated, given I
dont know you.

Here are answers to your queries:
Gee, this posting is rather long...don't you think?
No. It is much smaller than one you posted. Cause I have done very
little typing as compared to you. I have directly copied my code + code
from Microsoft website and posted here. So I was surprised to see at
what length you had written to make your point.

why so much example code on combo box stuff?
I didnot at all try to make my posting easy to understand or did not
make up an example. Code here is as almost as is from my code.

Am I missing some point here?
Yes you probably may. Becasue it doesnot help to check whether only
State exists in the table on it before update event, neither does it
help to know if zone exists saperately, given that they make a compiste
priimary key as I mentioned in my first message. Since I am anyway going
to hit the database for each change, I might as well write that code in
form_beforeupdate. But your suggestion of putting it control's before
update is also good and could have been implemented. But it was not
necessary for our small form.
Second point you are probably missing is: Form_Error catches ODBC error
but it does not stop that nasty error. We cannot "HANDLE" that error
"ODBC -- Call Failed". For that the only way given by Microsoft is to
Clone the table or query on which the form is based and recreate that
error by trying to insert into it. So your Form_Error doesnot "Handle"
ODBC errors. It just catches them.
I am new to Access and as you can see not an expert. In my opinion and
Access sucks and becoming an expert at Access 97 is not very rewarding
in the age of .NET and Java. (No offence to existing experts, I am
talking for myself here)

I was giving my what I found as information and not as best possible
solution to this problem. If people have better solutions than this,
they can post it here for social service, withought questiong my
methods.

Thanks for your constructive remarks though.

Abby
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #6

P: n/a

"Abhi Jose" <ja*******@yahoo.com> wrote in message
news:41**********************@news.newsgroups.ws.. .
hey Mr Poet, why are you singing your funeral song of your brain? If you
lick my behind I will probably tell you where do Access morons come
from. By that time you can sell your ass and buy some brains. Anyway I
am not interested in your shit attitude. If you post a reply, I will not
be reading it. But atleast you I will make you waste some more energey
!! haha, Loser!!
~a


You're a pretty sick reptile. But you've come to the right place. CDMA
where all the creatures meet! I did that tune over to better suit your
licking, uh, liking. You'll probably meet all the gays, uh, guys mentioned
in the tune pretty soon.
__________________________

Ah, look at all the Access homos.
Ah, look at all the Access homos.

Larry the Lickass, picks through the posts
Where a newsgroup had been.
Lives in a dream.
Waits at his PC, wearing the thong
That he keeps on a hook by the door.
Who is it for?

All the Access homos,
Where do they all come from?
All the Access homos,
Where do they all belong?

Tony the Twinkie, pasting together
The words of a post that no one will hear.
No one comes near.
Look at him working, pulling his pud
In the night when there's nobody there.
What does he care?

All the Access homos,
Where do they all come from?
All the Access homos,
Where do they all belong?

Ah, look at all the Access homos.
Ah, look at all the Access homos.

David the Deviant trolls through the posts,
Looking for boys he can meet.
All boys are sweet.
Down in the bathhouse, wiping the steam
From his laptop he frowned.
No one was found.

All the Access homos,
Where do they all come from?
All the Access homos,
Where do they all belong?
Nov 13 '05 #7

P: n/a
How expected sick-man!! haha I made you waste a lot more of your time
that I had attempted to. You are a nice pery, keep it up, I am enjoying
the game hahaha!! I didnot care to read your post though, thats another
victory over you.
See people this person who wrote this sick poem is showing what is
filled in his brain. Techncially he is probably very dumb that is why he
is doing all these attempts to show his "sick-no-brains"
Keep making fool of yourself dumbass!!
one more post from you and you have licked my a** once more.
~aa

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #8

P: n/a
"XMVP" <ac***********@hotmail.com> wrote:
X-Complaints-To: ab***@vnet-inc.com


Please ignore the above posting and others which are somewhat bizarre.

Note to persons new to this newsgroup. One person, with many identities, is being
rather disruptive. If you see a bizarre posting it's probably the work of this
disruptive person..

Check the headers of the posting. If you see the following the posting likely can be
ignored. Of course, there will likely be other headers to be added to this list.

Comments: This message did not originate from the Sender address above.
It was remailed automatically by anonymizing remailer software.
or
X-Abuse-Report: ab***@teranews.com
or
Organization: Posted via Supernews, http://www.supernews.com
or
Organization: 100ProofNews.com - Unlimited Downloads - $8.95/Month
or
X-Complaints-To: ab***@vnet-inc.com
or
Message-ID: <something>.nntpserver.com

You can also change your NewsReader program settings to ignore off-topic posts. See
http://www.hyphenologist.co.uk/killfile/ for more information.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #9

P: n/a
"your_boss" <ca*************@notcaught.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Here are answers to your queries:
Gee, this posting is rather long...don't you think?

No. It is much smaller than one you posted. Cause I have done very
little typing as compared to you. I have directly copied my code + code
from Microsoft website and posted here. So I was surprised to see at
what length you had written to make your point.
Well, I don't think the issue is how much typing one does..but simply how
long the post is!

However, right off the bat, I do apologize to you. I did not mean to mock,
or condescend to you in any way.

Fact is, I just found that post really long..and that was a LOT of code.
Second point you are probably missing is: Form_Error catches ODBC error
but it does not stop that nasty error. We cannot "HANDLE" that error
"ODBC -- Call Failed".
I agree. And, I am rather surprised (shocked) that someone don't have a
better workaround!

And, I DO NOT have a solution to your problem!!!!! Golly, is there not some
@@ var we can grab from sql server?

I am thinking about his right now...and don't see a easy solution. Even
using a pass-through query to grab the @@ERROR is too late, but it *some*
possibility solution might exist if one uses a ODBC direct connection, and
perhaps calls a stored procedure..but then again, I don't seen any way to
grab the error code for that existing connection.

Further, it really sad that both of us have to deal with the other poster
making trouble for all of us!

I really do apologize for this kind of noise here. Fact is, you are tying to
participate in this news group and you are sharing your ideas with us. I am
MOST appreciative of people who take the time to share and post ideas.
I am new to Access and as you can see not an expert. In my opinion and
Access sucks and becoming an expert at Access 97 is not very rewarding
in the age of .NET and Java. (No offence to existing experts, I am
talking for myself here)
Well, a97 is 4 versions old now! The newer versions support ADO..and further
you can even consume web services if you wish.
I was giving my what I found as information and not as best possible
solution to this problem. If people have better solutions than this,
they can post it here for social service, withought questiong my
methods.
I accept the above. In fact, as mentioned, what really got me what the large
amount of code you needed. And, to be honest...I don't have a quick solution
for getting the sql error code either with dao..

Thanks for your constructive remarks though.

Abby


You are welcome, and once again, my sincere apologies, especially since I
don't have a better (or any) solution as compared to what you shared with
us!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #10

P: n/a
Albert D. Kallal wrote:
Second point you are probably missing is: Form_Error catches ODBC error
but it does not stop that nasty error. We cannot "HANDLE" that error
"ODBC -- Call Failed".

I agree. And, I am rather surprised (shocked) that someone don't have a
better workaround!

And, I DO NOT have a solution to your problem!!!!! Golly, is there not some
@@ var we can grab from sql server?


I have come across this form error when deleting a record using the
built in Access delete functions (RunCommand, DoMenuItem, etc) and got
around it by sending a delete SQL statement to the server and catching
the error that way.

It's a bit more complex and involved but I think you could do the same
sort of thing for an insert, you'd have to loop the bound controls on
the form to generate an insert statement, then you could handle any ODBC
error generated by looping DbEngine.Errors. If no error is generated
then Undo the form, requery the underlying recordset and go to the newly
inserted record.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

Nov 13 '05 #11

P: n/a
"your_boss" <ca*************@notcaught.com> wrote in message
news:41**********************@news.newsgroups.ws.. .
I see your point, you were talking about length of the
posting. Eventhough I felt little offened by your posting, your
apologizing shows that you are a good man and probably did not mean to
offend me. So its all good. no worries about it anymore.
Yes...we are not little boys..but just adults trying to get our work done!
I am not surprised that there is no better method to catch the ODBC
error, becasue from whatever I have learned about Access in last year, I
have realized that learning access is basically learning its quirks and
unexpected behaviours. Most other things you can learn from help or a
book, but Access seems to have more "Dont"s to remember that simple
"Do"s. So I am not at all surprised. You may call it frustrated and
angry rather.
Well, part of the problem is ODBC here.

To be fair, Ms-access does have more quirks then a lot of software tools I
used. However, it really is just VB6 wrapped in a IDE with bound forms. The
forms and object model in ms-access is FAR MORE difficult to master then say
just VB6, and the simple forms that VB has (ms-access forms has at least
double the events..and double the methods and properties that a VB6 form
has). So, once this complex model is learned, then you can do a LOT more
with a lot less work.

There is a minimal level of expectancy from a software
which Access doesnt meet. For example you cannot dynamically set the
Report variables in Access
Actually, you can do a lot of dynamic stuff in a report. It is again a
simple matter of learning the forms object et model.

( without using table or a query), you need
the report to be open in design mode. This is more than ridiculous. You
cannot create an MDE with report open in design mode. Someone will say
use crystal reports. But we have to pay for it. I spent many painful
hours figuring out this one.


I at least agree that you do NOT want to modify the report at runtime. You
do want to note that a MDE is the same as a .exe, and thus it kind of don't
make sense to have a self modifying .exe. (a mde is in a sense a .exe for
the ms-access runtime).

However, you can use un-bound boxes in a report, and set the data source for
those text boxes. I used nearly every report writer on the planet, and if
there is one gold bright spot in ms-access..it is the report writer. I rate
the report writer as a killer app. Interestingly, with the advent of HTML,
and web based stuff...we are seeing a lot more developers believe that the
report writer should be a lot MORE dynamic. However, those types of HTML
reports used TONS of code, and on a per report bases are VERY expensive in
terms of developer labor content.

Note that ms-access reports have the full use of VB6 code, and I have NEVER
been stumped, or held back by the ms-access report writer. This is ONLY
report writer where I can say that!. If you are looking for a solution, or
have some "problem" that you needed to solve with the report writer, feel
free to email me with the question, or even post here.

If you take look at the following screen shots for "report prompt": screens,
all of these screens drive reports that allow the user to select options,
and all of the reports are as a mde. And, all take MUCH less code then
alternative solutions
http://www.attcanada.net/~kallal.msn.../ridesrpt.html

And, I am going to look into a better odbc catch solution (perhaps a
trigger,or some environment var on the server side can be saved..and then it
retrieved via a stored procedure in the forms on error).

At any rate...good luck!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #12

P: n/a
"Trevor Best" <nospam@localhost> wrote in message
news:41***********************@auth.uk.news.easyne t.net...

I have come across this form error when deleting a record using the built
in Access delete functions (RunCommand, DoMenuItem, etc) and got around it
by sending a delete SQL statement to the server and catching the error
that way.
yes, good stuff.....and in fact most of the time I code my own deletes
anyway.

I bet that if you use a form bound to a ADO reocrdset..then catching the
odbc (well, now oleDB) error likely would be better (however, the poster is
working with a97).

It's a bit more complex and involved but I think you could do the same
sort of thing for an insert, you'd have to loop the bound controls on the
form to generate an insert statement, then you could handle any ODBC error
generated by looping DbEngine.Errors. If no error is generated then Undo
the form, requery the underlying recordset and go to the newly inserted
record.


Essentially, that was the solution posted..and it is too much work in my
opinion. And, no one has yet mentioned to use a ADP project, but lets leave
that for another day!!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #13

P: n/a
Albert D. Kallal wrote:
It's a bit more complex and involved but I think you could do the same
sort of thing for an insert, you'd have to loop the bound controls on the
form to generate an insert statement, then you could handle any ODBC error
generated by looping DbEngine.Errors. If no error is generated then Undo
the form, requery the underlying recordset and go to the newly inserted
record.

Essentially, that was the solution posted..and it is too much work in my
opinion. And, no one has yet mentioned to use a ADP project, but lets leave
that for another day!!


Nothing is too much work if it's the only way. I have done this b4 back
when I had to do replication in Access 2.0, I would generate a SQL
statement for every insert, delete and update made on a form, all
updates in code avoided DAO and used a SQL statement as well. All the
SQL statements would be logged and when sent to the other site, executed.

I admit it was a LOT of work, but I would never have considered it too much.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

Nov 13 '05 #14

P: n/a
your_boss <ca*************@notcaught.com> wrote in
news:41**********************@news.newsgroups.ws:
For example you cannot dynamically set the
Report variables in Access ( without using table or a query), you
need the report to be open in design mode.


What do you mean by "dynamically set the Report variables?"

I change things dynamically in reports all the time, including the
criteria for the underlying data set, labels captions, label and
field visibility, field position and size, text attributes (bold,
italic) and line visibility and positioning.

That you don't know *how* to do these things may very well be, but
there are many, many things you can do dynamically in a report
without resorting to design mode. Indeed, I've never encountered
anything I wanted to do dynamically in a report that I was unable to
do.

Please explain what you are trying to do and maybe those of us who
understand Access reports can explain how to accomplish the task.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.