Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: Problem with Lock Violations

    I have a client who until now has maintained customer details in a master Excel spreadsheet. Salespersons record data in an Excel template in the same format. Data is transferred to the master by simple cut and paste. I am creating a new structured database in Access and have developed a data loader which works like this.

    1. The spreadsheet is first linked to the database.

    2. A buffer table has been created which contains all of the fields in the spreadsheet, plus some others which contain derived data and others which record the processing date and the automunber keys of the database records which were created from the (flat) buffer table.

    3. A form has been created to display and modify the records in the buffer table (so that spelling errors etc. can be fixed before upload into the database proper). The form has a number of processing buttons.
    The "Load new data button" executes an SQL query which populates the relevant fields in the buffer table from the Excel spreadsheet.
    The "Save" button saves the contents of the buffer table to another Excel spreadsheet (in case reprocessing is ever required.
    The "Distribute" button updates several database tables from the buffer table.
    The "Clear" button empties the buffer table.
    All of the buttons (mostly) work. However, sometimes, the "Clear" button will report that it was unable to delete 0 records because of key violations (of course) and N records because of lock violations, where N is a number which varies. I can delete these leftover records by hand in datasheet view. Now, if I reload the data using "Load New Data", the data appears in the form, but remains stuck on the first record. The status message varies alternates between "Form View" and "Calculating". I am the only user, locking is the normal (lowest) default. All fields are duplicatable. Any ideas, colleagues all ?

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Hi, Colleagues !
    Well, I found out what is happening and while I have a fix, it is not altogether satisfactory. Some of the fields are forced to upper case with the following generic code snippet:

    Private Sub Title_Exit(Cancel As Integer)
    Me!Title = UCase(Me!Title)
    End Sub

    The cursor moves to the first field on the form, which happens to have this code attached to the OnExit event. When the navigation button is pressed, the cursor remains stuck in the field and no movement to another record takes place.

    The form works correctly when the code is commented out.

    I tried this variant, without success.

    Private Sub Title_Exit(Cancel As Integer)
    Dim strTemp As String
    strTemp = Me!Title
    Me!Title = UCase(strTemp)
    End Sub

    The crude answer is simply to hit the tab key to move the cursor to the next field. After that everything works normally.

    I think a more elegant solution will require some code in the OnOpen event.

    Jim Wright.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •