Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Naperville, Illinois, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Problems (a2k/acc2003)

    I have a form that once the user accepts it, my program creates records in a local table for each form line that has data. My user noticed that only 4 lines were being captured.

    I checked the code and noticed that the program would write the records but the fifth record would over write the 4th record in the table. The 6th record would also overwrite the 4th record etc. But later in the code, new records are added as they should be. I have recreated the table, recreated the form data, tried it on different computers with the same results.

    Stymied in Chicago,

    Alan

    Here are snippits of the code:
    Set rstOut = New ADODB.Recordset
    rstOut.Open "tbltempreports", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTableDirect

    Note: I added the individual withs / end withs later trying to debug. If I stop the code after record 4, its in the table but gone after record 5. I also tried to close the table and reopen after the 4th record.

    nCnt = 1
    ' we know the first line has to have an item in it
    With rstOut
    .ADDNew
    .Fields("line") = nCnt
    .Fields("item") = rstIn!CiItem1Desc
    .Fields("scost") = IIf(rstIn!CIItem1Cost <> 0, FormatCurrency(rstIn!CIItem1Cost, 2), "")
    .Fields("ncost") = rstIn!CIItem1Cost
    .Update

    nCnt = nCnt + 1
    End With
    If Not IsNull(rstIn!CIItem2Desc) Then
    With rstOut
    .ADDNew
    .Fields("line") = nCnt
    .Fields("item") = rstIn!CIItem2Desc
    .Fields("scost") = IIf(rstIn!CIItem2Cost <> 0, FormatCurrency(rstIn!CIItem2Cost, 2), "")
    .Fields("ncost") = rstIn!CIItem2Cost
    .Update
    nCnt = nCnt + 1
    End With
    End If

    If Not IsNull(rstIn!CIItem3Desc) Then
    With rstOut
    .ADDNew
    .Fields("line") = nCnt
    .Fields("item") = rstIn!CIItem3Desc
    .Fields("scost") = IIf(rstIn!CIItem3Cost <> 0, FormatCurrency(rstIn!CIItem3Cost, 2), "")
    .Fields("ncost") = rstIn!CIItem3Cost
    .Update
    End With
    nCnt = nCnt + 1
    End If

    If Not IsNull(rstIn!CIItem4Desc) Then
    With rstOut
    .ADDNew
    .Fields("line") = nCnt
    .Fields("item") = rstIn!CIItem4Desc
    .Fields("scost") = IIf(rstIn!CIItem4Cost <> 0, FormatCurrency(rstIn!CIItem4Cost, 2), "")
    .Fields("ncost") = rstIn!CIItem4Cost
    .Update
    End With
    nCnt = nCnt + 1
    End If

    If Not IsNull(rstIn!CIItem5Desc) Then
    With rstOut
    .Fields("line") = nCnt
    .Fields("item") = rstIn!CIItem5Desc
    .Fields("scost") = IIf(rstIn!CIItem5Cost <> 0, FormatCurrency(rstIn!CIItem5Cost, 2), "")
    .Fields("ncost") = rstIn!CIItem5Cost
    .Update
    End With
    nCnt = nCnt + 1
    End If

    If Not IsNull(rstIn!CIItem6Desc) Then
    With rstOut
    .Fields("line") = nCnt
    .Fields("item") = rstIn!CIItem6Desc
    .Fields("scost") = IIf(rstIn!CIItem6Cost <> 0, FormatCurrency(rstIn!CIItem6Cost, 2), "")
    .Fields("ncost") = rstIn!CIItem6Cost
    .Update
    End With
    nCnt = nCnt + 1
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Problems (a2k/acc2003)

    There's nothing that catches my eye immediately. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    Naperville, Illinois, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Problems (a2k/acc2003)

    I deleted previous post, the problem was not fixed so I stripped down the database: WHen you open it, open the frmProjects. You will see a subform called invoices with one entry. Click on the command button with the caption V. It will open another form. On the bottom right, there is a command button for Print Invoice (make sure the TR3 Invoice option is clicked on the Invoice Options box). A module is called in the Module Module1 which populates the table. It only does 4 lines form the possible 10 lines on the form (tblTempResults).

    I had to convert to Acc97 to get under the 100k file size. Now the program just stops after the 4th record with an error message (row can not be located). In the table tblTempResults, there are only the 4 rows.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Problems (a2k/acc2003)

    I'm very sorry, I should have seen it immediately <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    You have forgotten to include the line

    .AddNew

    for the 5th and further items.

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    Naperville, Illinois, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Problems (a2k/acc2003)

    @!!&SA!@!@!!! (I used other words). Thanks. I must be getting too old for this.

Posting Permissions

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