Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2008
    Posts
    15

    Thumbs up Unanswered: VBA DAO Recordset2: Copying 2 Column ComboBox Bug?

    Will try and keep this brief

    Am using VBA DAO to make a back-up of ten tables into a separate database file which will be sent via email to update the master dataset with the records which have been updated weekly.

    The current code is working fine for all field types except ones which are using Lookup with two columns. Am receiving error 'object not available' when doing RS_S("Medical Conditions") = RS_C("Medical Conditions").

    All four Databases are identical apart from the data.

    Table: [Register]
    Field: [Medical Conditions]
    Type: [Text; Lookup ComboBox; Source is Table [Medical Conditions]
    Bound to Column 2; Width 0cm,1cm;]

    [Medical Conditions] has two fields,
    Field 1: [MedCondID] [autonumber] Key
    Field 2: [Medical Condition] [Text]

    VBA Code to copy RecordSets
    Code:
        Dim dbsOutgoing As Database
        Dim strDBName as String
        strDBName = "Databases/DB_Exchange"
        
        Dim RS_C As Recordset2
        Dim RS_S As Recordset2
       
        Dim strSQL_C0, strSQL_S1 As String
                    
        Dim errLoop As Error
        On Error GoTo Err_Execute
        DoCmd.Hourglass True
        Beep    
       
        If (boolDbIsServer = True) Then GoTo Server_Side
    
    Client_Side:
      
        strSQL_C0 = "SELECT * FROM [Register] WHERE ([ReportStatus] = '1');"
        Set RS_C = CurrentDb.OpenRecordset(strSQL_C0, , dbOpenDynamic)
        'Open up the Reports DataBase
        Set dbsOutgoing = OpenDatabase(strDBName)
        strSQL_S1 = "SELECT * FROM [Register];"
        Set RS_S = dbsOutgoing.OpenRecordset(strSQL_S1, , dbOpenDynamic)
            
            While Not RS_C.EOF
                RS_S.AddNew
                    RS_S("GuardianID") = RS_C("GuardianID")
                'Copies over all fields types ok.
                'Fails on the following.
                    RS_S("Medical Conditions") =  RS_C("Medical Conditions")
                RS_S.Update
                    RS_C.Edit
                    RS_C("ReportStatus") = "0"
                    RS_C.Update
                RS_C.MoveNext
            Wend
            RS_S.Close
            RS_C.Close
            
            DoEvents            
    'Continues for the other tables..
    
    Server_Side:
    
    'Identical but RS_C = RS_S..
    
    DoEvents
    
    Both_Continue_FromHere:
        dbsOutgoing.Close
    End_Here:
    On Error Resume Next
        Set RS1 = Nothing
        Set RSo = Nothing
        Set RS_C = Nothing
        Set RS_S = Nothing
        Set dbsOutgoing = Nothing
        DoCmd.Hourglass False
    Exit Sub
    
    Err_Execute:
       ' Notify user of any errors that result from
       ' executing the query.
       If DBEngine.Errors.Count > 0 Then
          For Each errLoop In DBEngine.Errors
             MsgBox "Error number: " & errLoop.Number & vbCr & _
                errLoop.Description
          Next errLoop
       End If
       GoTo End_Here
    End Sub
    Thinking this is a bug in DAO 12..
    TIA

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hence why developers say never to include spaces in fieldnames.

    Instead of RS_S("Medical Conditions") = RS_C("Medical Conditions"), try:

    RS_S("[Medical Conditions]") = RS_C("[Medical Conditions]")

    or

    RS_S![Medical Conditions] = RS_C![Medical Conditions]
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2008
    Posts
    15
    Thanks StarTrekker,

    I agree on this, but much quicker (to lazy) to drag them onto the form than to rename each in reports etc.
    Although other fields without spaces fail the same when using the Look up bound to column 2.
    Will remove the spaces from all these fields to see if this has any effect.

    Thanks

  4. #4
    Join Date
    May 2008
    Posts
    15
    Tried putting in the [] and now:
    In Error Trap no errors listed in DBEngine, so removing on error throws the following error.

    Run time Error '64224' Method 'Value' of object 'Field2' Failed.

    Thanks

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Can you repost the code and tell me which line the error is now occurring? The more information you give me, the easier it is to find the problem and advise to fix it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2008
    Posts
    15
    Using:- RS_S![Medical Condition] = RS_C![Medical Condition] gives:
    Error '64224' Method 'Collect' of object 'Recordset2' failed.

    It makes me think there is a bug in Access 2007 DAO

    Field RS_S![Placement] = RS_C![Placement] gives the same error messages

    Thanks

  7. #7
    Join Date
    May 2008
    Posts
    15
    The fields which are failing are all ones which are using the following field settings:-

    Field Name: [Medical Condition], [Placement], etc..
    Data Type: Text
    In Lookup:-
    Display Control: Combo Box
    Row Source Type: Table/Query
    Row Source: [Medical Conditions], [Placement Types], etc.
    Bound Column: 1
    Column Count: 2
    Column Heads: No
    Column Widths: 0cm,2cm
    List Rows: 16
    List Width: Auto
    Limit to list: Yes
    Allow Multiple Values: Yes
    Allow Value List Edits: Yes
    List Items Edit Form: blank
    Show Only Row Source Values: No

    The Code works fine for all other Fields, but is failing on these records which are using the Lookup Combo Box.

    Thanks

  8. #8
    Join Date
    May 2008
    Posts
    15
    After commenting out the 'On Error GoTo Err_Execute' the debug window is now displaying the 'Error 64224' ones on the lines using these Lookup fields.
    Incidentally, with the error check in place the 'DBEngine.error.count = 0'

    Code:
            While Not RS_C.EOF
                RS_S.AddNew
                    RS_S("GuardianID") = RS_C("GuardianID")
                'Fails at the same line below.
                    
                    RS_S("Medical Conditions") =  RS_C("Medical Conditions")
    
                RS_S.Update
                    RS_C.Edit
                    RS_C("ReportStatus") = "0"
                    RS_C.Update
                RS_C.MoveNext
            Wend
            RS_S.Close
            RS_C.Close
    Thanks

  9. #9
    Join Date
    May 2008
    Posts
    15
    Error Recap.

    The errors are as follows:
    With the on Error goto Err_Execute':- No Error in DBEngine (In database object, not currentDB?)
    With no on error:-

    Using ("fields") gives: Run time Error '64224' Method 'Value' of object 'Field2' Failed

    Using [Fields] gives: Run time Error '64224' Method 'Value' of object 'Field2' Failed

    Using ![fields] gives: Error '64224' Method 'Collect' of object 'Recordset2' failed.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this VB6. VB.NET or VBA

    in VBA I'd expect to see something like
    Code:
    RS_S!Medical Conditions =  RS_C!Medical Conditions
    RS_C!ReportStatus = "0"
    If its VB6/VB.NET then you'd be better off asking the question in the VB section on this site, as its NOT an Access question. If that is the case let use know and we'll get the question moved there
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    May 2008
    Posts
    15
    This is Visual Basic for Access (VBA), using Code Builder from within Access 2007 Form Designer.

    Still receive Error 64224 Method 'Value' of object 'Field2' Failed on using RS_S!

    The problem is within Access 2007 itself.

    Thanks

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    oops
    RS_S![Medical Conditions] = RS_C![Medical Conditions]
    RS_C!ReportStatus = "0"

    allowing space names in columns is an uneccesary complication.. its a bad habit...

    failing that I'd try the fullstop object qualifier
    RS_S.[Medical Conditions] = RS_C![Medical Conditions]
    RS_C.ReportStatus = "0"
    sadly you are using Access 2007, something i don't and wont touch at present so Im not sure I can be of any other assistance

    I'd try to comment out the medical conditions update for now and see if JET will accept the ! or . style, and then revisit the column name with the sapce in it.

    failing that you may have to reference the column by its index.. which may mean scannign through the object collection to find the index rather than the name of the relevant column

    eg
    RS_S.index(x) = RS_C.index(y)
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    May 2008
    Posts
    15
    Thanks for you help healdem, but still receiving these errors any way I try to Set the multi-valued fields to another recordset.

    Along with using INSERT Queries which cannot use multi-valued fields this is looking more and more like an access deficiency.

    Will try and see if I can switch to XML to transfer over the data, as for Access itself, I do not like it one bit.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it may be, I've never used the lookup wizard.
    its a well documented problem if you use the lookup wizard
    http://www.mvps.org/access/lookupfields.htm

    I suspect that that is your man cause of problems.

    In my books its daft to use the lookup wizard when a far more relaible technique is available (using tables to implement the lookup)
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    May 2008
    Posts
    15
    AAARRRGGGHHH!!! Nothing EXPORTS the Multiple-Values fields......
    Stupid BUGGED software.!!!.
    Have now to re-table complete database, and add in form functions to select multiple tables!
    If only could use .net and SQL server, would be up and running by now!

Posting Permissions

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