I am getting duplicate records, I mean everything is duplicate even Access record number 
Author Message
 I am getting duplicate records, I mean everything is duplicate even Access record number

I have a database program in VB6 service pack 3 with a Access 2000 database
file
and using the microsoft active data objects 2.0 library.

I am getting duplicate records, I mean everything is duplicate
even the Access Record number. What I mean is if you open the database file
in Access 2000
the left column is then record number access uses to store your line item
and I see two record numbers with the same and somtimes different fiels
values.

So has anyone experienced this and does the
microsoft active data objects 2.5 library fix this?

Thank You
Bill A



Sat, 30 Nov 2002 03:00:00 GMT  
 I am getting duplicate records, I mean everything is duplicate even Access record number
Can you post some code?

--

Andrew Grillage
http://www.concresco.com


Quote:

> I have a database program in VB6 service pack 3 with a Access 2000
database
> file
> and using the microsoft active data objects 2.0 library.

> I am getting duplicate records, I mean everything is duplicate
> even the Access Record number. What I mean is if you open the database
file
> in Access 2000
> the left column is then record number access uses to store your line item
> and I see two record numbers with the same and somtimes different fiels
> values.

> So has anyone experienced this and does the
> microsoft active data objects 2.5 library fix this?

> Thank You
> Bill A



Sun, 01 Dec 2002 03:00:00 GMT  
 I am getting duplicate records, I mean everything is duplicate even Access record number
This is the code behind the OK button which is on a form to add a new
production job, it is pretty long.

Private Sub cmbOk_Click()

 Dim temp_CustomerName$, temp_x, NewBidNumber2$, NewJobNumber2$

 AddNewJob.NewJobNumber = ""

 'check to see job class is selected
 If optBid.Value = False And optEngineering.Value = False And optProduction
= False Then
     Msg$ = "You must select a Job class of " & vbCrLf
     Msg$ = Msg$ & "Bid or Engineering or Production"
     MsgBox Msg$
     Exit Sub
 End If

 ' check to see job type is selected
 If optRoof.Value = False And optFloor.Value = False And optIBeam.Value =
False And optLVL.Value = False Then
     Msg$ = "You must select a Job Type of " & vbCrLf
     Msg$ = Msg$ & "Roof or Floor or I-Beam or LVL"
     MsgBox Msg$
     Exit Sub
 End If

 ' check to see if customer name is selected
 temp_x = CustomerNameList.ListIndex
 temp_CustomerName$ = Trim$(CustomerNameList.List(temp_x))
 temp_x = 0

 If temp_CustomerName$ = "" Then

    MsgBox "You must select a customer."
     Exit Sub
 End If

 If optBid.Value = True Then
     ' increment bid number
     NewBidNumber2$ = GetNextBidNumber()

     lblJobNumber.Caption = FormatBidNumber(NewBidNumber2$)

     AddNewJob.NewJobNumber = NewBidNumber2$

 End If

 If optEngineering.Value = True Or optProduction.Value = True Then
     ' increment job number

     NewJobNumber2$ = GetNextEngProdNumber()

     lblJobNumber.Caption = FormatJobNumber(NewJobNumber2$)

    AddNewJob.NewJobNumber = NewJobNumber2$

 End If

  'StatusMsg.Caption = "Saving record"
  InfoManager.StatusBar1.Panels(1).Text = "Saving record"
  AddNewJob.Refresh

  Set cn = New ADODB.Connection
    ' The ConnectionString contains the path of the database.
    With cn

        '.Provider = "Microsoft.Jet.OLEDB.3.51"   ' this line for office 97
file format
        .Provider = "Microsoft.Jet.OLEDB.4.0"     ' this line for office
2000 file format
        .ConnectionString = InfoManager.DataBasePath
        .Open
    End With

  Dim temp_ResidenceName$, temp_JobAddress$, temp_Lot$
  Dim temp_Block$, temp_Subdivision$, temp_Model$, temp_ModelDescription$,
temp_ZipCode$
  Dim temp_CountyName$, temp_GarageType$, temp_BudgetSubTotal$
  Dim temp_SubTotal$, temp_SalesTax$
  Dim temp_Total$, temp_DateAtlasRecieved, temp_CashPaymentReceivedDate$
  Dim temp_CashPayment$, temp_CashDollarAmount$, temp_PurchaseOrderNumber$
  Dim temp_ApprovedForConstructionBy$, temp_DateApprovedForConstruction$
  Dim temp_JobComments, temp_DeliveryDirections

     Dim rsToNew As New ADODB.Recordset

     Set rsToNew = New ADODB.Recordset

      ' table to add new items to
      If optBid.Value = True Then

          TargetTable = "AtlasBids"
      Else
          TargetTable = "AtlasJobs"

      End If

      With rsToNew

            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open TargetTable, cn, , , adCmdTable

      End With

      ' trim all spaces from text
      temp_x = CustomerNameList.ListIndex
      temp_CustomerName$ = Trim$(CustomerNameList.List(temp_x))
      temp_x = 0

      temp_CustomerSalesman$ = AddNewJob.Salesman_Name
      temp_ResidenceName$ = Trim$(ResidenceName.Text)
      temp_JobAddress$ = Trim$(JobAddress.Text)
      temp_Lot$ = Trim$(Lot.Text)
      temp_Block$ = Trim$(Block.Text)

      temp_Subdivision$ = Trim$(cboSubdivision.Text)
      temp_Model$ = Trim$(Model.Text)
      temp_ModelDescription$ = Trim$(ModelDescription.Text)
      temp_ZipCode$ = Trim$(ZipCode.Text)

      temp_x = CountyNameList.ListIndex
      temp_CountyName$ = Trim$(CountyNameList.List(temp_x))
      temp_x = 0

      If optGarageLeft.Value = True Then

          temp_GarageType$ = "Left"
      End If

      If optGarageRight.Value = True Then

          temp_GarageType$ = "Right"
      End If

      temp_BudgetSubTotal$ = Trim$(BudgetSubTotal.Text)

      temp_SubTotal$ = Trim$(SubTotal.Text)

      temp_SalesTax$ = Trim$(SalesTax.Text)

      temp_Total$ = Trim$(Total.Text)

      ' get the date that was selected from date control
      temp_Date = DateAtlasRecieved.Value    'GetDate

      temp_Date = Left(temp_Date, 10)   ' get the date like this, FromDate =
07/30/1999
      temp_DateAtlasRecieved = temp_Date

      'Debug.Print "CashPayment.ListIndex " & CashPayment.ListIndex

      If CashPayment.ListIndex = -1 Then
          ' if it is -1 then notheing is selected in drop down list
          ' so set text to ""
          temp_CashPayment$ = ""
          temp_CashDollarAmount$ = ""
          temp_CashPaymentReceivedDate = ""
      Else

         temp_x = CashPayment.ListIndex
         temp_CashPayment$ = Trim$(CashPayment.List(temp_x))
         temp_x = 0

          ' get the date that was selected from date control
         temp_Date = CashPaymentReceivedDate.Value   '.GetDate

         temp_Date = Left(temp_Date, 10)   ' get the date like this,
FromDate = 07/30/1999
         temp_CashPaymentReceivedDate$ = temp_Date

         temp_CashDollarAmount$ = Trim$(CashDollarAmount.Text)

      End If

     'StatusMsg.Caption = "Saving record .."
     InfoManager.StatusBar1.Panels(1).Text = "Saving record .."
     AddNewJob.Refresh

      temp_PurchaseOrderNumber$ = Trim$(PurchaseOrderNumber.Text)
      temp_ApprovedForConstructionBy$ =
Trim$(ApprovedForConstructionBy.Text)

       ' get the date that was selected from date control
      temp_Date = DateApprovedForConstruction.Value     '.GetDate
      If Len(temp_Date) > 0 Then
         temp_Date = Left(temp_Date, 10)   ' get the date like this,
FromDate = 07/30/1999
         temp_DateApprovedForConstruction$ = temp_Date
      End If

      temp_JobComments = AddNewJob.JobComment

      temp_DeliveryDirections = AddNewJob.JobDeliveryDirections

        'rsToNew.MoveLast

        rsToNew.AddNew

        '[Job Type] Roof, Floor, I-beam,LVL, R for Roof, F for Floor, I for
I-Beam, L for LVL
        If optRoof.Value = True Then
           'Job Type
            rsToNew![Job Type] = "R"
        End If

        If optFloor.Value = True Then
           'Job Type
            rsToNew![Job Type] = "F"
        End If

        If optIBeam.Value = True Then
           'Job Type
            rsToNew![Job Type] = "I"
        End If

        If optLVL.Value = True Then
           'Job Type
            rsToNew![Job Type] = "L"
        End If

        '[Job Class] B for Bid,J for job, E for Engineering

        If optBid.Value = True Then

            rsToNew![Job Class] = "B"

            'Call SaveNumbers("Bid")
            'AddNewJob.JobNumbersSaved = True
        End If

        If optEngineering.Value = True Then

            rsToNew![Job Class] = "E"
            'Call SaveNumbers("Job")
            'AddNewJob.JobNumbersSaved = True

        End If

        If optProduction.Value = True Then

            rsToNew![Job Class] = "J"
            'Call SaveNumbers("Job")
            'AddNewJob.JobNumbersSaved = True
        End If

        rsToNew![Salesman] = temp_CustomerSalesman$

        ' get new job / bid number
      '  rsToNew![Job Number] = lblJobNumber.Caption
        rsToNew![Job Number] = AddNewJob.NewJobNumber

        rsToNew![Customer Name] = temp_CustomerName$

        rsToNew![Year] = DatePart("yyyy", Now)

        If Len(temp_ResidenceName$) > 0 Then
            rsToNew![Residence Name] = temp_ResidenceName$
        End If

        If Len(temp_JobAddress$) > 0 Then
            rsToNew![Job Address] = temp_JobAddress$
        End If

        If Len(temp_Lot$) > 0 Then
            rsToNew![Lot Number] = temp_Lot$
        End If

        If Len(temp_Block$) > 0 Then
            rsToNew![Block Number] = temp_Block$
        End If

        If Len(temp_Subdivision$) > 0 Then
            rsToNew![Subdivision] = temp_Subdivision$
        End If

        If Len(temp_Model$) > 0 Then
            rsToNew![Model] = temp_Model$
        End If

        If Len(temp_ModelDescription$) > 0 Then
            rsToNew![Model Description] = temp_ModelDescription$
        End If

        If Len(temp_ZipCode$) > 0 Then
            rsToNew![Zip Code] = temp_ZipCode$
        End If

        If Len(temp_CountyName$) > 0 Then
               rsToNew![County] = temp_CountyName$
        End If

        ' ba

        If optEngineering.Value = True Or optProduction.Value = True Then
          ' only include this if job is Engineering Or Production
          ' new job so Job Status is forced to N - New
          rsToNew![Job Status] = "N" & ""

        End If

        If Len(temp_GarageType$) > 0 Then
               rsToNew![Garare Type] = temp_GarageType$
        End If

        If Len(temp_SubTotal$) > 0 Then
            rsToNew![Sub Total] = CDec(temp_SubTotal$)
        End If

        If Len(temp_SalesTax$) > 0 Then
            rsToNew![Sales Tax] = CDec(temp_SalesTax$)
        End If

        If Len(temp_Total$) > 0 Then
            rsToNew![Total] = CDec(temp_Total$)
        End If

        If Len(temp_DateAtlasRecieved) > 0 Then

             rsToNew![Date Atlas Received] = CDate(temp_DateAtlasRecieved)
        End If

        'StatusMsg.Caption = "Saving record ...."
        InfoManager.StatusBar1.Panels(1).Text = "Saving record ...."
        AddNewJob.Refresh

        If optBid.Value = False Then
            'if it is not a bid, then do the below lines
            If Len(temp_CashPayment$) > 0 Then
               rsToNew![Cash Payment] = temp_CashPayment$
            End If

            If Len(temp_CashPaymentReceivedDate$) > 0 Then
               rsToNew![Cash Payment Received Date] =
CDate(temp_CashPaymentReceivedDate$)
            End If

            If Len(temp_CashDollarAmount$) > 0 Then
               rsToNew![Cash Dollars Amount] = CDec(temp_CashDollarAmount$)
            End If

        End If

        If Len(temp_BudgetSubTotal$) > 0 Then
               rsToNew![Budget Subtotal] = CDec(temp_BudgetSubTotal$)
        End If

        If optBid.Value = False Then
            'if it
...

read more »



Sun, 01 Dec 2002 03:00:00 GMT  
 I am getting duplicate records, I mean everything is duplicate even Access record number
Wow! For readability purposes, I strongly recommend that you break that up
into several subprocedures, maybe with a maximum of 20 lines of code each.


Quote:
> This is the code behind the OK button which is on a form to add a new
> production job, it is pretty long.

> Private Sub cmbOk_Click()

>  Dim temp_CustomerName$, temp_x, NewBidNumber2$, NewJobNumber2$

>  AddNewJob.NewJobNumber = ""

>  'check to see job class is selected
>  If optBid.Value = False And optEngineering.Value = False And
optProduction
> = False Then
>      Msg$ = "You must select a Job class of " & vbCrLf
>      Msg$ = Msg$ & "Bid or Engineering or Production"
>      MsgBox Msg$
>      Exit Sub
>  End If

>  ' check to see job type is selected
>  If optRoof.Value = False And optFloor.Value = False And optIBeam.Value =
> False And optLVL.Value = False Then
>      Msg$ = "You must select a Job Type of " & vbCrLf
>      Msg$ = Msg$ & "Roof or Floor or I-Beam or LVL"
>      MsgBox Msg$
>      Exit Sub
>  End If

>  ' check to see if customer name is selected
>  temp_x = CustomerNameList.ListIndex
>  temp_CustomerName$ = Trim$(CustomerNameList.List(temp_x))
>  temp_x = 0

>  If temp_CustomerName$ = "" Then

>     MsgBox "You must select a customer."
>      Exit Sub
>  End If

>  If optBid.Value = True Then
>      ' increment bid number
>      NewBidNumber2$ = GetNextBidNumber()

>      lblJobNumber.Caption = FormatBidNumber(NewBidNumber2$)

>      AddNewJob.NewJobNumber = NewBidNumber2$

>  End If

>  If optEngineering.Value = True Or optProduction.Value = True Then
>      ' increment job number

>      NewJobNumber2$ = GetNextEngProdNumber()

>      lblJobNumber.Caption = FormatJobNumber(NewJobNumber2$)

>     AddNewJob.NewJobNumber = NewJobNumber2$

>  End If

>   'StatusMsg.Caption = "Saving record"
>   InfoManager.StatusBar1.Panels(1).Text = "Saving record"
>   AddNewJob.Refresh

>   Set cn = New ADODB.Connection
>     ' The ConnectionString contains the path of the database.
>     With cn

>         '.Provider = "Microsoft.Jet.OLEDB.3.51"   ' this line for office
97
> file format
>         .Provider = "Microsoft.Jet.OLEDB.4.0"     ' this line for office
> 2000 file format
>         .ConnectionString = InfoManager.DataBasePath
>         .Open
>     End With

>   Dim temp_ResidenceName$, temp_JobAddress$, temp_Lot$
>   Dim temp_Block$, temp_Subdivision$, temp_Model$, temp_ModelDescription$,
> temp_ZipCode$
>   Dim temp_CountyName$, temp_GarageType$, temp_BudgetSubTotal$
>   Dim temp_SubTotal$, temp_SalesTax$
>   Dim temp_Total$, temp_DateAtlasRecieved, temp_CashPaymentReceivedDate$
>   Dim temp_CashPayment$, temp_CashDollarAmount$, temp_PurchaseOrderNumber$
>   Dim temp_ApprovedForConstructionBy$, temp_DateApprovedForConstruction$
>   Dim temp_JobComments, temp_DeliveryDirections

>      Dim rsToNew As New ADODB.Recordset

>      Set rsToNew = New ADODB.Recordset

>       ' table to add new items to
>       If optBid.Value = True Then

>           TargetTable = "AtlasBids"
>       Else
>           TargetTable = "AtlasJobs"

>       End If

>       With rsToNew

>             .CursorType = adOpenKeyset
>             .LockType = adLockOptimistic
>             .Open TargetTable, cn, , , adCmdTable

>       End With

>       ' trim all spaces from text
>       temp_x = CustomerNameList.ListIndex
>       temp_CustomerName$ = Trim$(CustomerNameList.List(temp_x))
>       temp_x = 0

>       temp_CustomerSalesman$ = AddNewJob.Salesman_Name
>       temp_ResidenceName$ = Trim$(ResidenceName.Text)
>       temp_JobAddress$ = Trim$(JobAddress.Text)
>       temp_Lot$ = Trim$(Lot.Text)
>       temp_Block$ = Trim$(Block.Text)

>       temp_Subdivision$ = Trim$(cboSubdivision.Text)
>       temp_Model$ = Trim$(Model.Text)
>       temp_ModelDescription$ = Trim$(ModelDescription.Text)
>       temp_ZipCode$ = Trim$(ZipCode.Text)

>       temp_x = CountyNameList.ListIndex
>       temp_CountyName$ = Trim$(CountyNameList.List(temp_x))
>       temp_x = 0

>       If optGarageLeft.Value = True Then

>           temp_GarageType$ = "Left"
>       End If

>       If optGarageRight.Value = True Then

>           temp_GarageType$ = "Right"
>       End If

>       temp_BudgetSubTotal$ = Trim$(BudgetSubTotal.Text)

>       temp_SubTotal$ = Trim$(SubTotal.Text)

>       temp_SalesTax$ = Trim$(SalesTax.Text)

>       temp_Total$ = Trim$(Total.Text)

>       ' get the date that was selected from date control
>       temp_Date = DateAtlasRecieved.Value    'GetDate

>       temp_Date = Left(temp_Date, 10)   ' get the date like this, FromDate
=
> 07/30/1999
>       temp_DateAtlasRecieved = temp_Date

>       'Debug.Print "CashPayment.ListIndex " & CashPayment.ListIndex

>       If CashPayment.ListIndex = -1 Then
>           ' if it is -1 then notheing is selected in drop down list
>           ' so set text to ""
>           temp_CashPayment$ = ""
>           temp_CashDollarAmount$ = ""
>           temp_CashPaymentReceivedDate = ""
>       Else

>          temp_x = CashPayment.ListIndex
>          temp_CashPayment$ = Trim$(CashPayment.List(temp_x))
>          temp_x = 0

>           ' get the date that was selected from date control
>          temp_Date = CashPaymentReceivedDate.Value   '.GetDate

>          temp_Date = Left(temp_Date, 10)   ' get the date like this,
> FromDate = 07/30/1999
>          temp_CashPaymentReceivedDate$ = temp_Date

>          temp_CashDollarAmount$ = Trim$(CashDollarAmount.Text)

>       End If

>      'StatusMsg.Caption = "Saving record .."
>      InfoManager.StatusBar1.Panels(1).Text = "Saving record .."
>      AddNewJob.Refresh

>       temp_PurchaseOrderNumber$ = Trim$(PurchaseOrderNumber.Text)
>       temp_ApprovedForConstructionBy$ =
> Trim$(ApprovedForConstructionBy.Text)

>        ' get the date that was selected from date control
>       temp_Date = DateApprovedForConstruction.Value     '.GetDate
>       If Len(temp_Date) > 0 Then
>          temp_Date = Left(temp_Date, 10)   ' get the date like this,
> FromDate = 07/30/1999
>          temp_DateApprovedForConstruction$ = temp_Date
>       End If

>       temp_JobComments = AddNewJob.JobComment

>       temp_DeliveryDirections = AddNewJob.JobDeliveryDirections

>         'rsToNew.MoveLast

>         rsToNew.AddNew

>         '[Job Type] Roof, Floor, I-beam,LVL, R for Roof, F for Floor, I
for
> I-Beam, L for LVL
>         If optRoof.Value = True Then
>            'Job Type
>             rsToNew![Job Type] = "R"
>         End If

>         If optFloor.Value = True Then
>            'Job Type
>             rsToNew![Job Type] = "F"
>         End If

>         If optIBeam.Value = True Then
>            'Job Type
>             rsToNew![Job Type] = "I"
>         End If

>         If optLVL.Value = True Then
>            'Job Type
>             rsToNew![Job Type] = "L"
>         End If

>         '[Job Class] B for Bid,J for job, E for Engineering

>         If optBid.Value = True Then

>             rsToNew![Job Class] = "B"

>             'Call SaveNumbers("Bid")
>             'AddNewJob.JobNumbersSaved = True
>         End If

>         If optEngineering.Value = True Then

>             rsToNew![Job Class] = "E"
>             'Call SaveNumbers("Job")
>             'AddNewJob.JobNumbersSaved = True

>         End If

>         If optProduction.Value = True Then

>             rsToNew![Job Class] = "J"
>             'Call SaveNumbers("Job")
>             'AddNewJob.JobNumbersSaved = True
>         End If

>         rsToNew![Salesman] = temp_CustomerSalesman$

>         ' get new job / bid number
>       '  rsToNew![Job Number] = lblJobNumber.Caption
>         rsToNew![Job Number] = AddNewJob.NewJobNumber

>         rsToNew![Customer Name] = temp_CustomerName$

>         rsToNew![Year] = DatePart("yyyy", Now)

>         If Len(temp_ResidenceName$) > 0 Then
>             rsToNew![Residence Name] = temp_ResidenceName$
>         End If

>         If Len(temp_JobAddress$) > 0 Then
>             rsToNew![Job Address] = temp_JobAddress$
>         End If

>         If Len(temp_Lot$) > 0 Then
>             rsToNew![Lot Number] = temp_Lot$
>         End If

>         If Len(temp_Block$) > 0 Then
>             rsToNew![Block Number] = temp_Block$
>         End If

>         If Len(temp_Subdivision$) > 0 Then
>             rsToNew![Subdivision] = temp_Subdivision$
>         End If

>         If Len(temp_Model$) > 0 Then
>             rsToNew![Model] = temp_Model$
>         End If

>         If Len(temp_ModelDescription$) > 0 Then
>             rsToNew![Model Description] = temp_ModelDescription$
>         End If

>         If Len(temp_ZipCode$) > 0 Then
>             rsToNew![Zip Code] = temp_ZipCode$
>         End If

>         If Len(temp_CountyName$) > 0 Then
>                rsToNew![County] = temp_CountyName$
>         End If

>         ' ba

>         If optEngineering.Value = True Or optProduction.Value = True Then
>           ' only include this if job is Engineering Or Production
>           ' new job so Job Status is forced to N - New
>           rsToNew![Job Status] = "N" & ""

>         End If

>         If Len(temp_GarageType$) > 0 Then
>                rsToNew![Garare Type] = temp_GarageType$
>         End If

>         If Len(temp_SubTotal$) > 0 Then
>             rsToNew![Sub Total] = CDec(temp_SubTotal$)
>         End If

>         If Len(temp_SalesTax$) > 0 Then
>             rsToNew![Sales Tax] = CDec(temp_SalesTax$)
>         End If

>         If Len(temp_Total$) > 0 Then
>             rsToNew![Total] = CDec(temp_Total$)
>         End If

>         If Len(temp_DateAtlasRecieved) > 0 Then

>              rsToNew![Date Atlas Received] = CDate(temp_DateAtlasRecieved)
>         End If

>         'StatusMsg.Caption = "Saving record ...."

...

read more »



Sun, 01 Dec 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. I am trying to update a record, i am not using data control

2. I am trying to update a record, i am not using data control

3. Am I understanding everything correctly? (DCOM)

4. duplicate records inserted into access DB

5. How do you delete duplicate records from Access table

6. How to Delete Duplicate records in Access Table ??

7. How to Delete Duplicate records in Access Table ??

8. duplicate records inserted into access DB

9. avoid duplicate record when import data from Excel to Access table

10. How to INSERT with duplicate records into Access 2000

11. Holy holy duplicates duplicates Batman Batman!

12. I am learning VB.NET and am wondering....

 

 
Powered by phpBB® Forum Software