Erro r In MS access database

I am using MS-Access database with vb6 frontend in multiuser enviornment. For inserting record i used transaction object. first i am inserting & updating a row in a table and setting bookmark (for lastupdated)property for this. Then with bookmark i retrieve ID for next table insertion & updation of row . Again i am setting bookmark (for lastupdated)for the new table retrieving autonumberid for 3rd table insertion and updation. I found some of the rscords with "#Error" in database. I am reparing  the database and using it againg.  I use Workspace(0) for tranction. Will the same workspace used by all users and creating problem in database or Will it open on different Machine?For reference I am giving  code part  as under.Please consider it as urgent


On Error GoTo Err_Save_Click
    Set wrkSpace = DBEngine.Workspaces(0)
    Set db = OpenDatabase(App.Path & "\mydatabase.mdb")
    If txtLName = "" Then MsgBox "LName is not Entered ": Exit Sub              'check for Lname
    If cmbCity = "" Then MsgBox "City is not Selected": Exit Sub                'check for City
'    If cmbArea.Columns(1).Value = "" Then MsgBox "Area is not Selected": Exit Sub   'check for Area
    If cmbArea.Text = "" Then MsgBox "Area is not Selected": Exit Sub   'check for Area
    Set rst = db.OpenRecordset("CustomerDetails", dbOpenDynaset)                'Open Customerdetails table
    wrkSpace.BeginTrans
    With rst                                                                    'adding new record to Customerdetails
        .AddNew
        !Title = Trim(cmbTitle)
        !FirstName = Trim(txtFName)
        !MiddleName = Trim(txtMName)
        !LastName = Trim(txtLName)
        !Address = Trim(txtAddress)
        !City = Trim(cmbCity)
        !PinCode = Trim(txtPin)
        !Landmark = Trim(txtLandmark)
        !HomePhone = Trim(txtHomePh)
        !WorkPhone = Trim(txtCompPh)
        !MobilePhone = Trim(txtMobilePh)
        If IsDate(Trim(txtPrfDate)) Then !PreferredContactDate = Trim(txtPrfDate)
        If Not (StrComp(Trim(txtPrfTime), "_:_:_") = 1) Then !PreferredContactTime = Format(Trim(txtPrfTime), "hh:mm:ss")
        If Not (Trim(txtAge) = "") Then !Age = txtAge
        !Qualification = cmbQualification
        !Occupation = cmbOccupation
        !EmploymentStatus = cmbEmpStatus
        !IdentifiedOn = txtCntIdentOn
        If Not (Trim(txtApplnIncome) = "") Then !ApplicantIncome = txtApplnIncome
        If Not (Trim(txtSpouseIncome) = "") Then !SpouseIncome = Trim(txtSpouseIncome)
        If Not (Trim(txtOthLoanInsta) = "") Then !OOInstallment = Trim(txtOthLoanInsta)
        If Not (Trim(txtBalInstal) = "") Then !OOBalance = Trim(txtBalInstal)
        !CompanyName = Trim(txtCompName)
        !DMA = cmbDMA.Text
'        !AreaID = cmbArea.Columns(1).Value
        !AreaID = dlbAreaId
        .Update
        .Bookmark = .LastModified                               'adding bookmark for last record added in the customerdetails table
    End With
  With rst
    txtCustID = !CustomerID
 End With
'***************************************
    'Add records to contacts
    Set rst1 = db.OpenRecordset("Contacts", dbOpenDynaset)
    With rst1
        .AddNew
        !Source = Trim(cmbSorceLead)
        !EnquiryPoint = Trim(cmbEnquiryPoint)
        !IdentifiedOn = Trim(txtCntIdentOn)
        If IsDate(Trim(txtDisbDate)) Then !DisbursementDate = Format(Trim(txtDisbDate), "mm/dd/yy")
        If IsDate(Trim(txtSancDate)) Then !SanctionDate = Format(Trim(txtSancDate), "mm/dd/yy")
        If IsDate(Trim(txtApplinDate)) Then !ApplicationDate = Format(Trim(txtApplinDate), "mm/dd/yy")
        !ActivityStatus = Trim(cmbActivity)
        !Remarks = Trim(txtRemark)
        !FollowupAction = Trim(txtFollupAction)
        !Product = Trim(cmbProduct)
        !CustomerID = Trim(txtCustID)
        .Update
        .Bookmark = .LastModified
    End With
 With rst1
    txtPID = !PID
 End With
'***************************************
    'Open loandetails and add records to it
    Set rst2 = db.OpenRecordset("LoanDetails", dbOpenDynaset)
    With rst2
        .AddNew
        !PID = txtPID
        !TypeOfLoan = cmbTypeohLoan
        If Not (Trim(txtLoanAmt) = "") Then !LoanRequired = Trim(txtLoanAmt)
        ![PropertyIdentified(Y/N)] = Trim(cmbPropertyIdent)
        !PropertyLocality = Trim(txtLocality)
        !AdditionalInformation = Trim(txtAddiInformation)
        .Update
    End With
    wrkSpace.CommitTrans
    rst.Close
    rst1.Close
    rst2.Close
'*************************************
amar_dAsked:
Who is Participating?
 
JetScootrConnect With a Mentor Commented:
The problem may be with interactions between where you're getting/setting bookmark and the Transaction actions.  Strange note about "ADDNEW":  You can also READ every field (including autonumber fields) between ADDNEW and UPDATE.  BUT when UPDATE occurs, recordset is ni an "undefined" location, i.e., no guarantee it's still on the record you just created.  SO:  Store off  field [CustomerID] prior to the Update.
Added suggestion: Store in a correctly typed variable, i.e., if it's autonumber, store in variable DIM'med as LONG.  Generally, I use the text/caption properties of controls ONLY for user interaction, and not for internal data use.  Code change shown below will also execute faster.
Change the code as shown:

      !AreaID = dlbAreaId
       txtCustID = !CustomerID      ' ADD THIS LINE
      .Update

' Delete the following lines:
      .Bookmark = .LastModified   'adding bookmark for last record added in the customerdetails table
     End With
     With rst
      txtCustID = !CustomerID
      End With
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
The only thing i can propose is to open and close the recordsets inside the transaction, for the rest there seems nothing to be wrong at first glance.
0
 
anthonycCommented:
since you are using MSAccess as a db, I don't see why you need the bookmarks.  If you place the lines of code that get the ID right above the .Update, you'll get the ID you need.  As soon as you issue the ADDNEW method, MSAccess makes the row, and grabs the next number from autonumber.

Try it without the bookmarks and see what happens.  
0
 
wesleystewartCommented:
"Will the same workspace used by all users and creating problem in database or Will it open on different Machine?"

The workspace is instantiated seperately for each client.  It is unique for each user.

Which records are failing?

Wes
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.