Refreshing a form

[jeffmoore64]

Hi,
I have a bound form that has a button to delete the current record.
When I delete the record I get the error:

Write Conflict
this record has been changed by another user since you started editing it. .... blab blab blab

I am the only user in the database.

The problem as I see it is that the form has not been refreshed to reflect that the record is not there.

I have tried:
me.refresh
me.requery
reseting the me.recordsource

All of these give me that error message.

Is there anyway to refresh the form ??
TIA
Jeff

 

[PHV]

Is by chance the underlaying table a linked one to SQL Server ?
If yes add a TimeStamp field in the SQL Server table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeffmoore64]

yes it is linked to a SQL server ....
What does adding a time stamp field do????
What is really wierd is that if I go to the menu up to and go to <records> and then <refresh> ... that will refresh the form ...

 

[PHV]

There is a bug with the BitFields.
The TimeStamp is the only safe way for access knowing if the record really was updated by another user.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeffmoore64]

so I should have a timestamp in all my tables where a delete may occur. Right?
How will the timestamp affect my relations if at all?

 

[PHV]

How will the timestamp affect my relations
No worry at all.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeffmoore64]

well... My recordset is no long undateable......

 

[PHV]

Have you refreshed the link ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeffmoore64]

if I open the recordsource in the query designer it is updateable without the time stamp ... if I add the timestamp it becomes not updateable ... BTW this is an .ADP file

 

[PHV]

Take a look here:

http://msdn.microsoft.com/library/en-us/dnvbdev00/html/vb00e1.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeffmoore64]

okay I had the timestamp in the primary table... moved it to the secondary (foreign key) table (where the delete is occuring) and the table is updatable but I get that dang write conflict error again! ... grrr

 

[jeffmoore64]

Sorry that was all pretty much greek too me....;(

 

[jeffmoore64]

I dont see where that link helps ...

Please help...

 

[PHV]

In this article search for
1) timestamp
2) What if the query is the result of a join
3) Specifying a unique table

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeffmoore64]

See what you can make of this ... I get the error at the requery command.

I added this:
rs.Properties("Update Criteria") = adCriteiaTimeStamp
but I get an error there now too.

Private Sub btn_del_Click()
On Error GoTo Err_btn_del_Click

    Dim rs_detail As ADODB.Recordset
    Dim connection, ssql_detail As String
    Dim cmdChange As ADODB.Command
    Set cnxn = New ADODB.connection

    connection = "provider='sqloledb';Data Source='server';Initial Catalog='rathmisc';User ID='sysadm';Password='sysadm';"
        Dim rs As ADODB.Recordset
        Dim rs1 As ADODB.Recordset
        Dim ssql As String
        ssql = "SELECT TOP 2 dbo.tbl_ITP.fld_co_num, dbo.tbl_ITP_Detail.* " & _
                "FROM dbo.tbl_ITP_Detail INNER JOIN " & _
                      "dbo.tbl_ITP ON dbo.tbl_ITP_Detail.fld_ITP_id = dbo.tbl_ITP.id " & _
                "WHERE (dbo.tbl_ITP_Detail.fld_activity_id = " & Me!txt_activity_id & ") AND (dbo.tbl_ITP.fld_co_num = '" & Me!txt_co_num & "') " & _
                "ORDER BY dbo.tbl_ITP_Detail.fld_ITP_id DESC"
        Set rs = New ADODB.Recordset
        rs.Properties("Update Criteria") = adCriteiaTimeStamp
        rs.Open ssql, connection, adOpenDynamic
            With rs
                ssql = "SELECT id, fld_rev_changes FROM dbo.tbl_ITP WHERE (id = " & Me!txt_ITP_id & ")"
                Set rs1 = New ADODB.Recordset
                rs1.Open ssql, connection, adOpenDynamic, adLockOptimistic
                If IsNull(rs1!fld_rev_changes) Then
                    rs1!fld_rev_changes = "Removed:" & Me!txt_activity_name
                    rs1.Update
                Else
                    tmp_rev = rs1!fld_rev_changes & ", Removed:" & Me!txt_activity_name
                    rs1!fld_rev_changes = tmp_rev
                    rs1.Update
                End If
                rs1.Close
            End With
        rs.Close
        Set rs = Nothing

    ssql_detail = "DELETE FROM tbl_ITP_detail WHERE fld_ITP_id = " & Me!txt_ITP_id & " AND fld_activity_id = " & Me!txt_activity_id & ";"
    
    cnxn.Open connection

    Set cmdChange = New ADODB.Command
    Set cmdChange.ActiveConnection = cnxn
    cmdChange.CommandText = ssql_detail
    Set rs_detail = New ADODB.Recordset

    ssql_detail = "tbl_ITP_detail"
    cmdChange.Execute


    Set rs_detail = Nothing
Me.Requery

Exit_btn_del_Click:
    Exit Sub

Err_btn_del_Click:
    MsgBox err.Description
    Resume Exit_btn_del_Click
    
End Sub

 

[Jechols]

Hi - Sorry to ask but I'm specifically facing the "write conflict" problem you were. Did you ever get this resolved? Thanks

Jennifer

 

[SQLHack]

I used to beat my head against the wall regarding this issue. I finally stumbled across the solution. I found that Access does not like it when bit field types in SQL Server allow nulls. Make sure that each bit field type does not allow nulls and has a default value. Once I cleaned up all of my bit field types to not allow nulls, I no longer had this problem.