Results 1 to 6 of 6
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Recordset not found Error 3201

    I think I am getting screwed up here with using 2 recordsets. I am getting a Runtime error '3201' no current record



    Table.[Horse_Blanket] is blank but does have the Field.[Visio ID]
    Table.[Data] has 7K records in it.

    I am attempting to take information from Data in 2 fields, manipulating it and adding it to Horse_blanket table. If I use HB.AddNew, it will hang up on HB.MoveNext with the same error.


    If I remove HB.Edit and HB.MoveNext it will run record 1 until rS.EOF, even though I have R set from 1 to 5.


    Code:
    Set dbs = CurrentDb()
    
    Set rS = CurrentDb.OpenRecordset("Data") ' Origin Table
    Set HB = CurrentDb.OpenRecordset("Horse_Blanket") ' Destination Table
    R = 1 Do While Not rS.EOF And HB.EOF For R = R To 5 'Last_Row_Num If rS![Row Type] = "FE" Or rS![Equip HB Name] = "." Then StrVisio = rS![unique_id] ElseIf rS![Row Type] = "Plat" Then StrVisio = rS![Parent Node ID] & "-" & rS![Platform_ID] End If Next HB.Edit ' Here is where I get the error. HB![Visio ID] = StrVisio HB.Update HB.MoveNext rS.MoveNext Loop

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    theres no need for code.
    this can be accomplished with a single update query.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you make the EOF BOF tests on the same domain (table/query) not on 2 different ones and the logical operator "and" should be in parentheses. You are also moving 5 times through the loop, likely changing the variable values without doing anything. Will study a bit more because there's likely other issues.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I have gotten it to wotk:
    Code:
    Do While Not rS.EOF And HB.EOF
                
                If rS![Row Type] = "FE" Or rS![Equip HB Name] = "." Then
                    StrVisio = rS![unique_id]
                    StrPlat = "Soldier"
                ElseIf rS![Row Type] = "Plat" Then
                    StrVisio = rS![Parent Node ID] & "-" & rS![Platform_ID]
                    StrPlat = rS![Equip HB Name]
                End If
                
                If IsNull(rS![Role / FE / Node Name]) Then
                        StrRole = ";"
                Else: StrRole = rS![Role / FE / Node Name]
                End If
                
                If IsNull(rS![Bumper # / Plat ID]) Or rS![Bumper # / Plat ID] = "" Then
                StrBump = ";;"
                Else: StrBump = rS![Bumper # / Plat ID] & ";;"
                End If
            
                    StrBumpRole = StrRole & " " & StrBump
        HB.AddNew
        HB![Visio ID] = RTrim([StrVisio])
        HB![BN] = rS![Unit]
        HB![CO] = rS![TOE Title]
        HB![PLT/SEC] = rS![Para Desc]
        HB![Role & Bumper #] = Trim(StrBumpRole)
        HB![Platform System] = Trim(StrPlat)
        HB.Update
        rS.MoveNext
    Loop
    I used this to make the table and columns:
    Code:
    dbs.Execute "CREATE TABLE Horse_Blanket ([Visio ID] CHAR(25), [BN] CHAR(85), [CO] Char (85), [PLT/SEC] Char(85), [Role & Bumper #] Char(85), [Platform System] Char(95));"
    C = 1
        For C = C To (Last_Col_Num - 5) 'Adds 40 columns - Equip 1 to Equip 40
            DoCmd.RunSQL "ALTER TABLE Horse_Blanket Add " & "[Equip " & C & "] text;"
        Next C
    The issue I have is when I open the HB table all the data has spaces behind the transferred data. I tried Trim & Rtrim.

    Ranman,
    I know, but I was trying to get it all VBA

    I can use: DoCmd.OpenQuery "qryConversion"\

    Code:
    SELECT IIf([Row Type]="Plat",[Parent Node ID] & "-" & [Platform_ID],[unique_id]) AS [Visio ID], Data.Unit AS BN, Data.[TOE Title] AS CO, Data.[Para Desc] AS [PLT/SEC], IIf(IsNull([Role / FE / Node Name]),";",[Role / FE / Node Name]) & IIf(IsNull([Bumper # / Plat ID]) Or [Bumper # / Plat ID]="",";;"," " & [Bumper # / Plat ID] & ";;") AS [Role & Bumper#], IIf([Row Type]="FE","Soldier",IIf([Row Type]="Plat",[Equip HB Name])) AS [Platform System] INTO Horse_Blanket
    FROM Data
    WHERE (((IIf([Row Type]="FE","Soldier",IIf([Row Type]="Plat",[Equip HB Name])))<>"") AND ((Data.[Row Type])<>"TOE" And (Data.[Row Type])<>"PH"));
    and
    Code:
    C = 1
        For C = C To (Last_Col_Num - 5) 'Adds 40 columns - Equip 1 to Equip 40
            DoCmd.RunSQL "ALTER TABLE Horse_Blanket Add " & "[Equip " & C & "] text;"
        Next C

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    CHAR creates fixed-width fields. Try TEXT(xx) instead, with xx being the desired length.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks Pbaldy Will try

    edited

    Worked

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error 3201
    By JennyL in forum Access
    Replies: 11
    Last Post: 06-15-2018, 11:14 AM
  2. Replies: 5
    Last Post: 04-02-2017, 04:55 PM
  3. Error Handeling for a 3201 and 3022 Error
    By starlancer805 in forum Access
    Replies: 3
    Last Post: 03-11-2015, 12:46 PM
  4. Error 3201 on Form / Subform
    By jerem in forum Programming
    Replies: 2
    Last Post: 12-01-2012, 12:34 AM
  5. Replies: 7
    Last Post: 06-08-2012, 09:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums