TABLE tblBrowsePTIs:
strBrowseName      String holding the names of saved searches
fDefault           YES if a saved search is the default, the rest are NO

FORM frmBrowsePTIs:
cmdMakeDefault     Button that makes the current named search the default
chkDefault         INvisible checkbox with its ControlSource = fDefault
cboRunSavedSearch  Unbound combobox that runs a saved search when a choice
                   is selected (AfterUpdate event).  The RowSource is the
                   list of strBrowseName.

PROCEDURE:
Private Sub cmdMakeDefault_Click()

    'Find the old default and UNcheck it.
    DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE " & Me.RecordSource & " SET " & _
        Me.RecordSource & ".fDefault = False WHERE " & _
        Me.RecordSource & ".fDefault = True;"
    DoCmd.SetWarnings True
    
    'Set this search as the new default and save this record.
    Me.chkDefault = True    '<======== ERROR OCCURS HERE!
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    'Disable the MAKE DEFAULT button.
    Me.cboRunSavedBrowse.SetFocus
    Me.cmdMakeDefault.Enabled = False
    Me.cmdMakeDefault.ControlTipText = ""

End Sub


The procedure cmdMakeDefault_Click() works great.  But if I make Search1 a 
default, go to Search2 and make it the default, and then come back to 
Search1 make it the default for the second time, I get this error message:

"Run-Time Error '-2147352567 (80020009)': the data has been changed."

As long as I pick a new default every time (no repeats), the button works 
fine.  Any clues?  Is the fact that I'm running an update query to a table 
that is the RecordSource for this very form a problem?  If so, how can I 
fix it?  Thanks.

Greg

This may have somehting to do with your connections colliding.  I think your
guess is correct, running the query creates a second transaction and they
step on each other.

Try making your form completely unbound and do all updates and reads using
SQL and VBA variables.



-----Original Message-----
From: Gregory Serrano [mailto:SerranoG@m...]
Sent: Thursday, August 22, 2002 3:56 PM
To: Access
Subject: [access] SQL Statement Changing Data on Form's RecordSource


TABLE tblBrowsePTIs:
strBrowseName      String holding the names of saved searches
fDefault           YES if a saved search is the default, the rest are NO

FORM frmBrowsePTIs:
cmdMakeDefault     Button that makes the current named search the default
chkDefault         INvisible checkbox with its ControlSource = fDefault
cboRunSavedSearch  Unbound combobox that runs a saved search when a choice
                   is selected (AfterUpdate event).  The RowSource is the
                   list of strBrowseName.

PROCEDURE:
Private Sub cmdMakeDefault_Click()

    'Find the old default and UNcheck it.
    DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE " & Me.RecordSource & " SET " & _
        Me.RecordSource & ".fDefault = False WHERE " & _
        Me.RecordSource & ".fDefault = True;"
    DoCmd.SetWarnings True
    
    'Set this search as the new default and save this record.
    Me.chkDefault = True    '<======== ERROR OCCURS HERE!
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    'Disable the MAKE DEFAULT button.
    Me.cboRunSavedBrowse.SetFocus
    Me.cmdMakeDefault.Enabled = False
    Me.cmdMakeDefault.ControlTipText = ""

End Sub


The procedure cmdMakeDefault_Click() works great.  But if I make Search1 a 
default, go to Search2 and make it the default, and then come back to 
Search1 make it the default for the second time, I get this error message:

"Run-Time Error '-2147352567 (80020009)': the data has been changed."

As long as I pick a new default every time (no repeats), the button works 
fine.  Any clues?  Is the fact that I'm running an update query to a table 
that is the RecordSource for this very form a problem?  If so, how can I 
fix it?  Thanks.

Greg