Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding record with VBA (Win ME/Office 97)

    I am using the following code to add a new record. When I reach the Cantidad field or the Period field, I get an error " Item not in collection". The debug window shows me values for the two fields. rstSetUp is a one record table. What am I doing wrong?

    Set rstColonmst = dbs.OpenRecordset("Colonmst", dbOpenDynaset)
    Do

    If rstColonmst!SPECIAL = True Then
    With rstCFactHis
    .AddNew
    !CODIGO = rstColonmst!CODIGO
    !UPTOYR = rstColonmst!UPTOYR
    !CANTIDAD = rstSetUp!CANTIDAD
    !PERIOD = rstSetUp!PERIOD
    End With
    rstCFactHis.Update

    End If
    rstColonmst.MoveNext
    Loop Until rstColonmst.EOF

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Adding record with VBA (Win ME/Office 97)

    First of all this code will copy every record in Colonmst to rstCFactHis.
    Also if there are no records in Colonmst you will get an error in the Do loop.
    Have you set the recordset rstSetUp?
    What is in the recordset (I presume that it is a recordset) rstCFactHis and rstSetUp.
    Have you checked and double checked all the fields mentioned are in rstSetUp, Colonmst and rstCFactHis?

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding record with VBA (Win ME/Office 97)

    Do you guys ever rest?

    I replaced the With statement with;

    If rstColonmst!SPECIAL = True Then
    'With "I commented this out."
    rstCFactHis.AddNew
    rstCFactHis!CODIGO = rstColonmst!CODIGO
    rstCFactHis!LASTNAME1 = rstColonmst!LASTNAME1
    rstCFactHis!PERIOD = rstSetUp!PERIOD

    etc

    'End With
    rstCFactHis.Update

    End If
    rstColonmst.MoveNext
    Loop Until rstColonmst.EOF

    and things are working OK. Why didn't it work the other way? I wish I new.

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding record with VBA (Win ME/Office 97)

    I am looping through the whole file, adding the new record only when SPECIAL is true. Is there a better way to do this?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding record with VBA (Win ME/Office 97)

    <P ID="edit" class=small>(Edited by charlotte on 15-Dec-02 07:47. to correct misstatement)</P>Since you're comingling 3 different recordset objects and only posted partial code, it's impossible to tell. Did you have a higher level With-End With, possibly? You cannot nest them reliably.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding record with VBA (Win ME/Office 97)

    Why not open a recordset that only includes those records in the first place? Try this:

    Set rstColonmst = dbs.OpenRecordset("SELECT * FROM Colonmst WHERE [SPECIAL]=True", dbOpenDynaset)
    Do Until rstColonmst.EOF
    ...etc.

    That will give you only the records where SPECIAL = True and will only attempt to add records to the other recordset if you have records in rstColonmst where SPECIAL=True.
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Adding record with VBA (Win ME/Office 97)

    Why not use an Append query.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding record with VBA (Win ME/Office 97)

    Your's is a better solution than mine. Thank you.

Posting Permissions

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