Force a Table Resort?

[tomco22]

I have two tables (one-to-many relationship) and various queries that pull from these and other tables. I have an “Add Record” form that solicits basic info to create a new record. When the user clicks “OK” that triggers some DoCmd.RunSQL stuff that inserts a record in the first table and populates the other table with a date range, among other things. But then, when I open an "Edit Record" form and try to update the new data I get an error message. If I compact and repair everything works fine. I’ve tried forcing a requery for the form and the subForm that produce the error but that doesn’t help. Is my problem that the underlying table is not properly sorted after the insert operation and if so is there a way to force a resort?

Tom
MCSD
I Nab Terrorists

 

[willir]

What is the error message? This info might help us determine the problem.

 

[tomco22]

...Can't update 12 records due to lock violatins...

Tom
MCSD
I Nab Terrorists

 

[willir]

When updating your tables, especially when using RecordSets, you have to explicitly Update the change...

With rst
   .AddNew
   !YourField = ...
   .Update
End With

With rst
   .Edit
   !YourField = ...
   .Update
End With

Next, it is possible that you may have the same record open more than once -- once on your screen and also with the SQL update statement.

Lastly, you can play with how Access locks records. From the menu, "Tools" -> "Options" -> "Advanced"

Richard

 

[tomco22]

I'm using record level locking in a shared database. There is not many users and I've tried this code when I was the exclusive user. Same result. All records are properly added but the edit form returns a "lock violation". After compact & repair everything works great.

Private Sub cmdSave_Click()

DoCmd.RunSQL "INSERT INTO tblEmployee (HashID, NameL, NameF, " _
    & "NameM, FISsort, Rank, Assignment, Description, UserType) " _
    & "Values ([txtHashID], [txtNameL], [txtNameF], ' ', '10', " _
    & "[cboRank], [cboAssignment], [txtDescription], 'User');"

DoCmd.RunSQL "INSERT INTO tblSlate ([Date], HashID, HourName, DaysName) " _
    & "SELECT DISTINCT tblDate.Date, tblEmployee.HashID, tblShifts.HourName, " _
    & "tblDay.DaysName FROM tblDate, tblShifts, tblEmployee, tblDay " _
    & "WHERE (((tblDate.Date) Between [Forms]![frmEmployeeNew]![cboStartDate] And (SELECT Max(tblSlate.Date) " _
    & "AS MaxOfDate FROM tblSlate;)) AND " _
    & "((tblEmployee.HashID)=[Forms]![frmEmployeeNew]![txtHashID]) " _
    & "AND ((tblShifts.HourName)='<BLANK>') AND ((tblDay.DaysName)= '<BLANK>')) " _
    & "ORDER BY tblDate.Date;"

DoCmd.Close

End Sub

Tom
MCSD
I Nab Terrorists