Can't search from a field; form is stuck!?

I have a form in Access 2000 that gets "stuck" on the record being viewed, such that the user can't use nav buttons to go to other records (the form stays put on the current record), and the user can't do a Ctrl-F search for other records (none are found).  It's as if the form's recordset suddenly becomes ONLY the record being viewed.  To clear the problem, the user has to close and re-open the form.

The problem occurs explicitly after a certain checkbox control is checked or unchecked.  The AfterUpdate event on this control must have something to do with the problem...  However, nearly identical AfterUpdate events on other controls do not cause this effect.  As a matter of fact, this checkbox control calls the same event function as some other controls -- and yet only this checkbox is followed by the "sticky" situation.

What causes a form to get "stuck" on the current record, and how can I fix it?

--Galisteo8

P.S.  For more info and the event code(s), please see http://www.experts-exchange.com/Databases/Q_21481308.html.  A solution to either question gets the points from both.
LVL 8
Galisteo8Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectCommented:
Is clicking the checkbox implementing a filter somehow, thus filtering out everything but the current record? If so, it should be simple enough to reveal the complete recordset by clicking the 'Remove Filter' button.

Just a thought!
Sayad Aziz AhmadCommented:
remove Me.Dirty=False and check if it works or behaves as before.

Aziz
jmantha709Commented:
Is there any code on the CCFee control ?

If you comment out RecalcRequery in the CCheck_AfterUpdate procedure, is the problem still there ?

If you comment out everything except the M.Dirty & RecalcRequery lines, is the problem still there ?
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Galisteo8Author Commented:
I will review your suggestions to-date this evening or tomorrow, when I am back at that job site.  I don't *think* there's any code on CCFee, except an OnClick event that opens a msgbox telling the user that CCFee's value is calculated automatically, and therefore cannot be manually changed.

I think I included Me.Dirty = False so that any changes the user had typed into any other controls would be "locked in" before the RecalcRequery function gets called.  It may be that with the Resync used in the RecalcRequery function, there may be no need to set Me.Dirty to False beforehand...?
(Previously, I used a Refresh approach instead of Resync, but that had disadvantages (since I've got an Access ADP project with a SQL back-end); plus the Refresh got slower and slower as the form's recordset got bigger and bigger over time.  I seem to recall that the Me.Dirty had to be False before the Refresh ran....)

Anyway, I will report back...

Any other thoughts from the Experts?
Sayad Aziz AhmadCommented:
i think Me.Dirty =False is giving u the problem .... although i m not sure 100%. let us see tomorrow when u test it

Aziz
ArjiCommented:
I would make sure there is data in both the Inner Join fields of your tables.  If you are missing a piece of data on a link field, that could cause limited records being displayed.  It's usually on the many side of a one to many relationship.
Galisteo8Author Commented:
Arji -  I assume you are referring to the INNER JOIN in the code at my other link?  That join is in the RecalcRequery function, which is called if the user clicks (changes) CCheck.  It is also called if the user changes EbayListFee, and yet the form does NOT get stuck on the current record after EbayListFee is changed.  Wouldn't the join either cause a problem in both situations or in neither situation?  (I'm just thinking of program logic...)

Sayedaziz -  Likewise, Me.Dirty = False is in the AfterUpdate event of both CCheck and EbayListFee, prior to the RecalcRequery function being called.  How could setting Dirty to False be a problem when it runs from CCheck, but not when it runs from EbayListFee...?

fredthered -  I will check for filters.

jmantha709 -  I will test it in that fashion.
ArjiCommented:
>Wouldn't the join either cause a problem in both situations or in neither situation?  (I'm just thinking of program logic...)
That would be an obvious conclusion but if there is not a record in the many side of a join, that could easily act like a sort of 'Filter'.
Galisteo8Author Commented:
Hmm...  The only join is this one:
wRS_Client.Open "select ProfitSplit, RinSplit, AuctPercent FROM Client INNER JOIN LOT ON CLIENT.FirstName = LOT.Client WHERE LotNum = '" & Me![LotNum] & "'"

Where could there be "no record" in the many side of this join?

I suppose I could verify the # of records in the resulting recordset.... How do you count records in a recordset (in this case, wRS_Client)?
ArjiCommented:
Assuming LotNum is in the LOT table, Try this:

wRS_Client.Open "select Client.ProfitSplit, Client.RinSplit, Client.AuctPercent, LOT.LotNum FROM Client INNER JOIN LOT ON CLIENT.FirstName = LOT.Client WHERE LOT.LotNum = '" & Me![LotNum] & "'"

wRS_Client.RecordCount after you open the recordset.
Galisteo8Author Commented:
Want to be sure I'm ready to go with all these suggestions, so...

fredthered -- what "remove filter" button are you referring to?
ArjiCommented:
The mods to your query won't hurt anything and might even help. :-)  It's just more clearly defining what fields are in what tables.  Just make sure the fields are in the tables like LOT.Lotnum.  I just guessed LotNum is in the LOT table. You should verify it.
Galisteo8Author Commented:
jmantha709 said:
>If you comment out RecalcRequery in the CCheck_AfterUpdate procedure, is the problem still there ?

Yes, the problem occurs even if the RecalcRequery function is not called.

For that matter, I then DELETED the entire CCheck_AfterUpdate procedure... and the problem still occured.

What does that tell us?  Without an AfterUpdate event on the control, NOTHING should happen anywhere on the form when it gets clicked, except perhaps the form's own OnCurrent event, which has nothing to do with CCheck (and for that matter, would run everytime I clicked _any_ control on the form, not just CCheck -- but the problem only occurs when I click CCheck).  Why is this happening?

(P.S. I already rebuilt my ADP my creating a new ADP and importing all forms into it, so as to eliminate any chance of form corruption.)
Galisteo8Author Commented:
Please see previous post before reading this one.

My continued testing has yielded confusing but consistent results, which I summarize below.  CCheck is bound to a bit-type column in the Auction table called CCYes.  When the user clicks CCheck checkbox, the first part of its AfterUpdate event causes a value to be calculated and put into another control called CCFee:

If Me![CCheck] = True Then
   Me![CCFee] = Me![WinBid] * 0.03
Else: Me![CCFee] = 0
End If

Then the 2nd part of the AfterUpdate ensures that the form isn't dirty, and then runs the RecalcRequery function:

Me.Dirty = False
RecalcRequery

In my testing, I tried commenting out both the first part and the second part of the AfterUpdate event, with the control either bound or unbound.  The results:  _IF_ the control is bound to the CCYes column of the SQL table, _OR_ if the first part of the AfterUpdate event is allowed to run (regardless of being bound or not bound), the form gets "stuck" on the current record and will not move to other records.  (The form does not get stuck when the CCheck is not bound _AND_ the first part of the AfterUpdate is commented out.)

I don't get it.... mainly because the control HAS TO BE BOUND, and it HAS TO CALCULATE the CCFee...  And this all used to work before I changed the RecalcRequery function, which does not appear to have ANY friggin' bearing on the problem.

What should I do, where should I look now?
Galisteo8Author Commented:
Please see previous two posts before reading this one.

P.S.  I did try Arji's suggestion of specifying table names in my queries.  Made no difference - problem still occured.
jmantha709Commented:
Hum...

Could you post the sql statement of the form's recordsource before you click CCheck

And

After you click CCheck.

Also, is CCfee a bound column and if so what field is it bound to ?

and my first question still stands, Is there any code on the CCFee control ?
Galisteo8Author Commented:
What do you mean by "sql statement of the form's recordsource" before _and_ after clicking CCheck?

CCFee is just a textbox control on the form; it may be bound to a column in the Auction table as well, but I will have to double-check that.

As for code on CCFee, the only code is an OnClick event with a msgbox that informs the user not to enter a value directly into the CCFee control, but rather to use the CCheck to turn on/off the Credit Card feature.  CCheck's AfterUpdate caclulates the value and assigns it to the CCFee control (that's what I referred to as the "1st part of the AfterUpdate"), just before it calls the RecalcRequery function (the "2nd part of the AfterUpdate").
ArjiCommented:
If you are having a problem after removing AfterUpdate code from CCheck (and there is no other associated code) then I would consider doing a compact and repair and maybe even create a new clean database and import the objects from this db.  I once had a problem with a checkbox, with no code behind it, would cause Access to shut down without any warning.  I ended up having to create a new DB and import all objects.  It may even be a corrupt form......just my thoughts and experiences...
Galisteo8Author Commented:
I already tried the compact/repair, as well as importing all forms and reports into a new clean Access ADP.  No effect.
jmantha709Commented:
1. I mean what is the value of the recordsource property ?

2. Make a copy of the form, then set the HasModule property to false and try it...  Still having the problem ?
Galisteo8Author Commented:
The form's Record Source property is simply:  Auction.  That's the name of the underlying SQL table to which the form is bound.  The CCHeck control is bound to Auction.CCYes.  I still need to go back and see if the CCFee control is bound to a column in the table or not.

As for the HasModule property...  It is probably set to whatever is the default, since I have never messed with it.  I may have to wait until Monday, however, to verify and test with it set to False.
jmantha709Commented:
Since you have code in that form the HasModule property will be True.  DO NOT set it to false in your original form because it will remove your code for that form.  First make a copy of the form, then set it to false and try your form.  If the problem is still there, then it's not code related.
Galisteo8Author Commented:
Okay.  I'll try that on Monday.
Galisteo8Author Commented:
Well, the problem did not occur when I set the form's HasModule to false.

Since CCheck's AfterUpdate runs the RecalcRequery function, and the EbayListFee textbox AfterUpdate ALSO runs the RecalcRequery function, then we can presume that the problem is not in the RecalcRequery function since the problem occurs after CCheck but NOT after EbayListFee.

That leaves us with the first part of CCheck's AfterUpdate event:
If Me![CCheck] = True Then
   Me![CCFee] = Me![WinBid] * 0.03
Else: Me![CCFee] = 0
End If

When the user clicks CCheck, a calculation is performed on the value in the form's WinBid control, and then the result is put into the form's CCFee control.  CCFee is bound to a column in the underlying SQL table (Auction); the column is also called CCFee.

It seems that the only difference between updating EbayListFee and updating CCheck is that CCheck's AfterUpdate event tries to put a value into another control, whereas EbayListFee does not.  This was not an issue previously (previous to using Resunc command to refresh my form) so I don't know why that would be an issue now.

Any other ideas?  I am completely mystified, and this is a critical issue for our users.

--Galisteo8

P.S.  After a Resync command, do any other form events run automatically -- OnCurrent? OnActivate?
Galisteo8Author Commented:
Please see previous post before reading this one.  Thanks.

I was just now putting msgbox flags into my form's code to track it better, including the start and end of the form's OnCurrent event.  I then triggered the 'stuck" problem by clicking the CCheck box, but then... I discovered that the form actually WILL go to another record after getting "stuck," but it "snaps back" to the "stuck" record.

Say I'm "stuck" on record 1720.  When I click the nav button to go to 1721, 1721 does indeed appear -- and my msgbox pops up to say that the OnCurrent event has started.  Click OK and the msgbox appears to say that OnCurrent has ended.  Click OK again and then...record 1720 appears and the msgbox pops up to say that the OnCurrent has started again.  Naturally, click OK and the msgbox appears to say that OnCurrent has ended.  After all that, I'm back where I started: at 1720.

Same thing happens if I attempt to search for another record by using Ctrl-F on any of the control fields -- I bounce to the destination record just long enough to see it and bounce back.

What does this mean??
jmantha709Commented:
Wow...

Ok, now the next step I would try...

1. Make a new test copy of your form with all the code in it.

2. Delete one code event only.

3. Test to see if the problem is there.

4. Repeat step 2 & 3 until the problem disappears.

When the problem isn't there anymore, the last event deleted should be the problem (or at least part of it) !  Do this for all events, even if they seem unimportant or non-related.
ragoranCommented:
Don't adjust your monitor, you just enter the twilight zone my friend... (doodedoo dedoo dedoo...)


Seriously,

Some point for you to check.

1- Setting me.dirty to false will run any event associated to saveing the record (beforeupdate, after update, etc).  Any code there worht mentionning ?

2- Your last post is curious,  Any code in the OnCurrent event beside the msgboxes ?

3- could be that some module vairables don't get initialize correctly (pulling straw here)

4- what type of column is EbayListFee  bound to ?


BTW, if you don't want a user to change the content of a bound text box, simply set its LOCK property to true.

I will check back tomorrow.

Ragoran






Galisteo8Author Commented:
jmantha,
I did try eliminating one event at a time.  When I deleted the form's OnActivate event, the problem changed -- it didn't go away, but just changed.  Instead of having the problem occur upon clicking the CCheck form control once, the problem instead occurred after I clicked CCheck a _second_ time.

ragoran,
1)   I believe setting me.dirty to False was a way to ensure that when the RecalcRequery function gets called, all the values in the controls are "set" -- that is, you want to make sure that the calculations are using the values most recently entered by the user, not the values that may have been in some of the controls before the user made changes.  As far as I can tell, Me.Dirty = False runs in the AfterUpdate event of _both_ CCheck and EbayListFee controls... but the problem only occurs after CCheck.  I will, however, double-check the form's Before/AfterUpdate events and post here.
2)   I _knew_ I should have posted that.  I'll post the OnCurrent event this evening, too.
3)   I'll check.
4)   EbayListFee is bound to a column of type "currency," in the Auction table. (The form is bound to the Auction table.)
Galisteo8Author Commented:
Please see comments in previous post, too.  Thanks.

ragoran,
Here's what is in the form's OnCurrent event:
------------------------------------------------------------
Private Sub Form_Current()

If Nz(Me.ctlRelist, False) = True Then
    Me.capRelistAlert.Visible = True
Else
    Me.capRelistAlert.Visible = False
End If

If Nz(Me.ctlRefund, False) = True Then
    Me.AuctionPmt_Subform.Locked = True
    Me.capRefundAlert.Visible = True
Else
    Me.AuctionPmt_Subform.Locked = False
    Me.capRefundAlert.Visible = False
End If

End Sub
------------------------------------------------------------

Basically, what this means is that when the user clicks a checkbox control (ctlRelist) to relist (or un-relist) an item, the OnCurrent event causes a modal form to appears (or disappear) indicating that the lot in that auction has been relisted in another auction.

Same goes for when the user clicks a refund checkbox control (ctlRefund) -- the OnCurrent event causes a modal form to appear informing the user of the auction's refunded status.  The OnCurrent event also ensures that the payment subform gets locked when ctlRefund is TRUE.

Does this help explain anything?  This code event should run each time a checkbox is clicked (including ctlRelist, ctlRefund, and CCheck), as well as when moving to view a new record...correct?
Galisteo8Author Commented:
Hello?  Please -- Last 2 posts await experts' review.  I am at my wits' end!
jmantha709Commented:
You said that the problem changed with the removal of the OnActivate event...  Can you post the code of that event ?
ArjiCommented:
Just a thought but have you deleted the checkbox and added a new one?
Galisteo8Author Commented:
jmantha,
I will post the OnActivate code this evening.

Arji,
Yes, I did try that -- to no avail.  There is something inherently wrong with the checkbox in collusion with my code, but only AFTER the resync commands were inserted into the RecalcRequery function (which is here: http://www.experts-exchange.com/Databases/Q_21481308.html).  Strangely, the problem does not seem to be related to directly to the RecalcRequery function itself, but emanates from elsewhere.
ragoranCommented:
Sorry for the delay, During the summer, I am not home as often...

Juts to eliminate that possibility, I will like you to try to decompile the access front end (this is a undocumented feature that sometimes fixes odd problem).  

1- Close Access
2- From Windows start button, select Run and type in "msaccess /decompile" (without the quotes) and click ok.
    This will start Access in a special mode, to be used ONLY for this operation.
3- Open youor front-end file WHILE PRESSING SHIFT so the application does not start.
4- KEEP YOUR FINGER ON SHIFT while doing a compact/repair
5- Close the file and quit access.

you can then restart Access normally and recompile your module.  TRy to see if this solve the problem.  Probably not, but I want to get that possibility out of the way.


I will get back to you after you try this.
Galisteo8Author Commented:
jmantha,
Here is the OnActivate code for the form:

--------------------------------------------------------------
Private Sub Form_Activate()
Dim auPK As String

If Len(Nz(Me.EbayNum, "")) > 0 Then
    If Me.Dirty = True Then
      If Len(Nz(Me.LotNum, 0)) = 0 Then
         Exit Sub
      Else
         Me.Dirty = False
      End If
    End If
   Me.Recordset.Resync adAffectCurrent, adResyncAllValues
Else
  DoCmd.GoToRecord , , acNewRec
End If

End Sub
--------------------------------------------------------------

ragoran,
I will try the decompile and report back.
Galisteo8Author Commented:
jmantha, see previous post for OnActivate code...

ragoran,
I used msaccess /decompile to open Access, and the (normal) pop-up window asked which file I wanted to open.  I held down Shift key and selected the ADP file to open.  Keeping Shift pressed, I did a compact & repair, then let up on the Shift key and closed Access.  I then re-opened Access in the normal manner, and opened the ADP file.  Does opening the file automatically recompile it?  Anyway, after doing all this, the problem still existed.
ragoranCommented:
Yes, Access  will recompile the application on demand.  As I said, this was just to eliminate this possibility.


1- Do you do your test on the first record of the recordset? if so,  then try to move toa record in the middle of the record set and see if you come back to the same record or the first one.

2- In the OnActivate event, you set me.dirty=false, thus saving the record but you don't call yourRecalcRequery function.  Is ti call on the BeforeUpdate event ?

3- What will happen if you don't do resync in the OnActivate event ?


Galisteo8Author Commented:
Ragoran:

1) I do the test on various records in the middle of the recordset.

2) The RecalcRequery function is called in the AfterUpdate of the CCheck control.

3)  I will test that at the jobsite today or tomorrow.
ragoranCommented:
Lets tackle this from another angle.  

I am assuminng that you did not stumble on an Access bug.  So the behavior is the result of something in your code.

Changing records could be accomplish by these methods:

1- Set the recordset bookmark to a value
2- use DoCmd.gotorecord command
3- Some requery method may reset the current record to the first one in the dataaset.

The first one is explicit in the sense that you have to specified the form from which the bookmark is changed.  

But the second method is more devious as it can be called from amodult to act on the "active" form, which is not always the one you think of...

I thnik we can eliminate the third method because of your previous comments where you say you have the same behavior with any records you test.

So....

I want you to search in your form first, then in all the modules, for instances of

  bookmark =

and

  docmd.gotorecord

And try to see if these could be causing the problem.  Also, if you find set.. bookmark statement, try to see where the value is coming from.

Galisteo8Author Commented:
ragoran,
What do you mean "see if these could be causing the problem"?  I have found only one instance of a bookmark in my AuctionForm code -- however it is in a sub that is not called anymore (the GotToAuction sub) -- at least, it is not called by any other code on the Auction Form itself.  So, I turn my attention to instances of docmd.gotorecord.  Again, how can I tell if one of these may be causing problems?  If I comment it out, that will limit functionality...
ragoranCommented:
What I meant is to look at these instance and try to understand if they could be called at the wrong time, or witht he wrong parameters.  It may seems a bit far fetch, but there is no magic invovled.  Something is causing the application to move back a record.

I just have a new idea.  Your test sequence is :

open the form
goto a record
click on ccheck
try to move to another record

Do you always try to move to the next record or can ou try to move to the previous or first or last record.  In these cases, do you still go back to the record you edited  ?
Galisteo8Author Commented:
If I try to go to the next record with the nav button, go to the previous record with the nav button, or do a Find/Search to go to any other record, then the form will BRIEFLY go to that record, and then go right back.  The Auction Form's OnCurrent event does run when the form lands on the destination record, but then the form goes back to the record where I started.  (I put a msgbox flag in the OnCurrent event to test this -- the pause caused by the msgbox was the only way I could actually see that the form did in fact go to the record I was telling it to go.)
ragoranCommented:
This is how I see the sequence of event:

1- you go to a record

2- edit (click on ccheck) (this save the record and resynch in the afterupdate event)

3- click on a navigation button
   3a - the code behind the button move to the record
       3b - this trigger the onCurrent event (you see your msgbox)
   
4- the form is back were we started !
   4a the OnCurrent event runs again

So something after the OnCurrent event of the form is moving the record

Have you put msgbox in the code behind the navigation button to see if the form revert to its previous record within this procedure ar after it ends ?

Don't despair, I have a feeling we are moving forward...

Galisteo8Author Commented:
I should specify that the Nav Button I refer to is the built-in nav button at the bottom of the Access form.  it is not customized.
jmantha709Commented:
You could try to put a breakpoint in the oncurrent event and then execute step by step to see were the form reverts to the previous record...  after each line of code, check wich record you're on...
ragoranCommented:
>>I should specify that the Nav Button I refer to is the built-in nav button at the bottom of the Access form.  it is not customized.

oh...

Galisteo8Author Commented:
Hi, experts --

I have reduced the problem to its basic elements by creating a dummy form that still generates the problem. This TestForm is still bound to my Auction table in SQL, although I've removed all the form controls that bind to it, except for the following which I created specifically for this test:

1) A textbox control called Number1, bound to a money-type column;
2) A textbox control called Number2, bound to a money-type column;
3) A checkbox control called CheckBox, bound to a bit-type column.

The checkbox has the following AfterUpdate event:
***********************************
Private Sub CheckBox_AfterUpdate()
If Me![CheckBox] = True Then
   Me![Number2] = Me![Number1] * 0.03
Else: Me![Number2] = 0
End If
Me.Dirty = False
End Sub
***********************************

The TestForm has the following OnActivate event:
***********************************
Private Sub Form_Activate()
Me.Recordset.Resync adAffectCurrent, adResyncAllValues
End Sub
***********************************

With this bare-bones form, the problem as described in this thread will occur as long as the Resync is in the form's OnActivate event and a) the Checkbox control is bound to an underlying column, and/or b) the Number2 control is bound to an underlying column.

Note: If NEITHER Checkbox nor Number2 is bound to an underlying SQL column, then the problem does NOT occur.  Likewise, if I remove the Resync from the form's OnActivate event, the problem does NOT occur.

Some additional observations:
a) The form's OnActivate event does not in fact run prior to the problem occuring. The Checkbox AfterUpdate event does not cause the form's OnActivate event to run.
b) If I move the Resync command out of the form's OnActivate event and into the Checkbox AfterUpdate event (right before End Sub) then the problem does NOT occur.

Try this out, somebody, and see if it gives you a problem, too.  This is Access 2000 front-end with a SQL 2000 back-end.

--Galisteo8
--Galisteo8
jmantha709Commented:
I tried the exact scenario you posted and couldn't reproduce your problem...

Can you post the SQL script of your table ?
Galisteo8Author Commented:
Please remind how to do that...
jmantha709Commented:
In SQL Server Enterprise Manager, find your server, database & table.

Right-click on the table name and choose All Tasks->Generate SQL Script...

On the Options tab, mark all checkboxes under Table Scripting Options.

Go back to the General tab.

Click the Preview button

There's the script, paste it here...


Galisteo8Author Commented:
Can this be done in MSDE, too?
Galisteo8Author Commented:
Ah -- it CAN be done in MSDE!  Here is the SQL Script for the Auction table, which is what my AuctionForm is bound to.
******************************************

if not exists (select * from master.dbo.syslogins where loginname = N'sa')
BEGIN
      declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
      if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
            select @logindb = N'master'
      if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
            select @loginlang = @@language
      exec sp_addlogin N'sa', null, @logindb, @loginlang, @sid = 0x01
END
GO


CREATE DATABASE [trc-w3]  ON (NAME = N'TRC-w2_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TRC2_Data.MDF' , SIZE = 13, FILEGROWTH = 10%) LOG ON (NAME = N'TRC-w2_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TRC2_Log.LDF' , SIZE = 132, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'trc-w3', N'autoclose', N'false'
GO

exec sp_dboption N'trc-w3', N'bulkcopy', N'false'
GO

exec sp_dboption N'trc-w3', N'trunc. log', N'false'
GO

exec sp_dboption N'trc-w3', N'torn page detection', N'true'
GO

exec sp_dboption N'trc-w3', N'read only', N'false'
GO

exec sp_dboption N'trc-w3', N'dbo use', N'false'
GO

exec sp_dboption N'trc-w3', N'single', N'false'
GO

exec sp_dboption N'trc-w3', N'autoshrink', N'false'
GO

exec sp_dboption N'trc-w3', N'ANSI null default', N'false'
GO

exec sp_dboption N'trc-w3', N'recursive triggers', N'false'
GO

exec sp_dboption N'trc-w3', N'ANSI nulls', N'false'
GO

exec sp_dboption N'trc-w3', N'concat null yields null', N'false'
GO

exec sp_dboption N'trc-w3', N'cursor close on commit', N'false'
GO

exec sp_dboption N'trc-w3', N'default to local cursor', N'false'
GO

exec sp_dboption N'trc-w3', N'quoted identifier', N'false'
GO

exec sp_dboption N'trc-w3', N'ANSI warnings', N'false'
GO

exec sp_dboption N'trc-w3', N'auto create statistics', N'true'
GO

exec sp_dboption N'trc-w3', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
      exec sp_dboption N'trc-w3', N'db chaining', N'false'
GO
if not exists (select * from dbo.sysusers where name = N'dbo' and uid < 16382)
      EXEC sp_grantdbaccess N'sa', N'dbo'
GO


if not exists (select * from dbo.sysusers where name = N'Domain Users' and uid < 16382)
      EXEC sp_grantdbaccess N'Domain Users'
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AUCTION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AUCTION]
GO

CREATE TABLE [dbo].[AUCTION] (
      [EbayNum] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [LotNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [EndDate] [datetime] NULL ,
      [WinBid] [money] NULL ,
      [Winner] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PaidStatus] [bit] NULL ,
      [Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [EbayListFee] [money] NULL ,
      [EbayComm] [money] NULL ,
      [CCFee] [money] NULL ,
      [Gross] [money] NULL ,
      [CCYes] [bit] NULL ,
      [RefundYN] [bit] NULL ,
      [RinnerRefund] [money] NULL ,
      [ClientRefund] [money] NULL ,
      [RelistedYN] [bit] NULL ,
      [CheckYN] [bit] NULL ,
      [Number1] [money] NULL ,
      [Number2] [money] NULL
) ON [PRIMARY]
GO



ALTER TABLE [dbo].[AUCTION] ADD
      CONSTRAINT [PK_AUCTION] PRIMARY KEY  CLUSTERED
      (
            [EbayNum]
      )  ON [PRIMARY]
GO


ALTER TABLE [AUCTION] WITH NOCHECK ADD
      CONSTRAINT [DF__DbaMgr_Tm__Relis__2610A626] DEFAULT (0) FOR [RelistedYN]
GO


ALTER TABLE [AUCTION] ADD
      CONSTRAINT [FK_AUCTION_LOT] FOREIGN KEY
      (
            [LotNum]
      ) REFERENCES [LOT] (
            [LotNum]
      )
GO
Galisteo8Author Commented:
Any thoughts regarding the SQL Script for my Auction table (above)?
jmantha709Commented:
I'll have a look at it tonight...
Galisteo8Author Commented:
Any thoughts?
jmantha709Commented:
Unfortunatly, I can't reproduce the problem...

I'm not sure what to suggest next, that hasn't already been suggested...
Galisteo8Author Commented:
Is there ANY reason that assigning a value to Control Number2 that is based on the value contained in Control Number1 (as seen in the following bit of code from the CheckBox_AfterUpdate) might have any bearing on anything?
...
If Me![CheckBox] = True Then
   Me![Number2] = Me![Number1] * 0.03
Else: Me![Number2] = 0
End If
Me.Dirty = False
...

This is, of course, the ONLY code in my bare-bones test from a few posts ago (7/25/05 2:02 AM CDT), but it is relevant to the real-life problem in my database since the CCheck AfterUpdate event is the ONLY place on the form where this kind of value assignment occurs.
jmantha709Commented:
I don't see any reason why it should...

One thought : It seems your field name in the table and your control name on the form are the same (ex: Number2)

Being out of ideas, I would try this :
In the properties of your controls change the name property like this

Number1 -> txtNumber1
Number2 -> txtNumber2
Checkbox -> chkCheckbox

The control source property remain the same.

Change the code to :

If Me.chkCheckBox = True Then
   Me.txtNumber2 = Me.txtNumber1 * 0.03
Else: Me.txtNumber2 = 0
End If
Me.Dirty = False


Note that the CheckBox_AfterUpdate event must now be chkCheckBox_AfterUpdate

Any different results ?
Galisteo8Author Commented:
Nope, no difference.
Galisteo8Author Commented:
!!!!
Experts -- I have coded my way to a solution!
!!!!

The problem, as has been noted here, centered on the unholy combination of a control value assignment (Me![Number2] = Me![Number1] * 0.03) in one control's AfterUpdate event, and a Resync statement in the form's OnActivate event.  However, the controls in question were related to payments being entered into a subform. So... I automated the calculation of the CCFee in the subform.

It sure would be easier to see it than to describe it....  But it works just like my payment total, which Ragoran helped me with many months ago when I first got this AuctionForm designed: Payment amounts are totaled in the subform via a puplic property that makes the total available to a control on the main AuctionForm, where it can be accessed by the AuctionForm's RecalcRequery function.  Likewise, credit card type payments are now being "counted" in a puplic property in the subform, and that total is made available to a control on the AuctionForm.  If the count > 0, then the AuctionForm's RecalcRequery function calculates the CCFee, stores it in the underlying table, and the value updates to the form during a Resync statement at the end of RecalcRequery.

That's the gist of it, anyway.  For business reasons, I needed to leave a Resync in the AuctionForm's OnActivate event, so the solution focused on getting that CCFee calculation out of the main form control events.  I still don't know WHY the problem was occuring (especially since no one could duplicate it), but it is a problem no longer.

I appreciate everyone's attention to this "undocumented feature" of Access, especially as we eliminated every possibility for what was causing it....  ;)  Since I found a solution (or, rather, a better way of coding my forms to eliminate the problem), I will submit for this question and  http://www.experts-exchange.com/Databases/Q_21481308.html to be PAQ'd with the points refunded.
ragoranCommented:
I am glad you find a way aroud your problem.  Sometimes, it is the only "solution"; but it is very frustrating not to know the actual cause of the problem.

I agree with the recommandation to PAQ with points refund.

Ragoran.
moduloCommented:
Closed, 250 points refunded.

modulo
Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.