Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2013
    Posts
    29

    Unanswered: How to create variable from multivalued field

    I have a defined variable in form ("beforeM" as string)

    In event before update in field "x" i would like to do:
    Code:
    beforeM = me.id_gora_zlecenia
    but id doesn't work.
    I tried to
    Code:
    beforeM = me.id_gora_zlecenia.value
    still dosen't work.

    Is there anyone here who knows how to writing code with multivalued field?

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    Multivalue fields are essentially a table within a table and so need to be treated as a separate recordset in order to use VBA to acquire the actual values. You can try something similar to this:
    Code:
    Dim BeforeM As String, ChildRst As DAO.Recordset
    
    With Me.RecordsetClone
    .Filter = "PKID=" & Me.PKID 'must have something to filter record, example uses primary key ID assuming autonumber
        With .OpenRecordset
           Set ChildRst = .fields("id gora zlecenia").Value
            If ChildRst.RecordCount > 0 Then
                ChildRst.MoveFirst
                BeforeM = ChildRst!Value.Value
                ChildRst.MoveNext
                    Do Until ChildRst.EOF
                    BeforeM = BeforeM & ", " & ChildRst!Value.Value
                    ChildRst.MoveNext
                    Loop
                Debug.Print BeforeM
            End If
                ChildRst.Close
                Set ChildRst = Nothing
        End With
    End With

  3. #3
    Join Date
    Mar 2013
    Posts
    29
    Ok on event before update this code works good, but I want to do the same with variable "afterm" which take the value on event after update. The problem is, this code save old value not new value. I tried to me.id_gora_zlecenia.requery on first line after update - but ms access doesn't allow requery before your code (error ms access) .
    So how coud I take new value on after update event?
    Last edited by andrzejfox; 08-01-13 at 09:49.

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    I'm not sure what you are looking to accomplish, you'll have to do a better job explaining it to me.

  5. #5
    Join Date
    Mar 2013
    Posts
    29
    I made example.accdb, to let you know what I tray to do.
    If you open database. Please change content in "Product" column (Details subform), then see "history" coulmn (using arrows). So in this column I have something like an Audit Trial.
    I try to do exactly the same in "products" column (realization subform), the problem is, that's multivalued field:/. I don't know how to get value from second column (in this multivalued field), I tried to me.products.text method, but it doesn't work.
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2005
    Posts
    146
    Your example DB does not contain anything like you described.

  7. #7
    Join Date
    Mar 2013
    Posts
    29
    Sorry! Please see this
    Attached Files Attached Files

  8. #8
    Join Date
    Jan 2005
    Posts
    146
    See frmNewDetails. In the Product After Update event it will save the current items in the list to your history field. You can add any addition text before and after to make it have meaning.
    Attached Files Attached Files

  9. #9
    Join Date
    Mar 2013
    Posts
    29
    When I not select any of value and click "ok" then in "history" appears values what I chose before. Is there any possibility, to get string "no value" in this situation? The second problem is that I have a datasheet (from this code problem is when I try to create a new record).
    Last edited by andrzejfox; 08-08-13 at 05:59.

  10. #10
    Join Date
    Jan 2005
    Posts
    146
    Replace the code with:
    Code:
    Private Sub Product_AfterUpdate()
    Dim ChildRst As DAO.Recordset
    With Me.RecordsetClone
    .Filter = "Id_Details=" & Me.Id_Details 'must have something to filter record, example uses primary key ID assuming autonumber
    With .OpenRecordset
           Set ChildRst = .Fields("Product").Value
            If ChildRst.RecordCount > 0 Then
                ChildRst.MoveFirst
                AfterM = ChildRst!Value.Value
                ChildRst.MoveNext
                    Do Until ChildRst.EOF
                    AfterM = AfterM & ", " & ChildRst!Value.Value
                    ChildRst.MoveNext
                    Loop
            Else
                AfterM = "No Value"
            End If
                ChildRst.Close
                Set ChildRst = Nothing
        End With
    End With
    Me.History = AfterM & vbNewLine & Me.History
    End Sub

  11. #11
    Join Date
    Mar 2013
    Posts
    29
    Now problem occur only if I try to create a new record. Please see Screenshot.
    Attached Thumbnails Attached Thumbnails 1.jpg  

  12. #12
    Join Date
    Jan 2005
    Posts
    146
    No problem, the record has yet to be created. Use this code:
    Code:
    Private Sub Product_AfterUpdate()
    Dim ChildRst As DAO.Recordset
    If Me.Dirty Then Me.Dirty = False
    With Me.RecordsetClone
    .Filter = "Id_Details=" & Me.Id_Details 'must have something to filter record, example uses primary key ID assuming autonumber
    With .OpenRecordset
           Set ChildRst = .Fields("Product").Value
            If ChildRst.RecordCount > 0 Then
                ChildRst.MoveFirst
                AfterM = ChildRst!Value.Value
                ChildRst.MoveNext
                    Do Until ChildRst.EOF
                    AfterM = AfterM & ", " & ChildRst!Value.Value
                    ChildRst.MoveNext
                    Loop
            Else
                AfterM = "No Value"
            End If
                ChildRst.Close
                Set ChildRst = Nothing
        End With
    End With
    Me.History = AfterM & vbNewLine & Me.History
    End Sub
    It saves the record before running the code.

  13. #13
    Join Date
    Mar 2013
    Posts
    29
    Yes but, I see another problem. In Your file, the Row source is
    "Glue";"Paper";"Sticker";"Poster";"Folder";"Produc t";"business card"
    but must have:
    (frmRealization) SELECT tblDetails.id_details, tblDetails.Product FROM tblDetails WHERE (((tblDetails.ID_Orders)=[Forms]![frmOrders]![ID_Orders])) ORDER BY tblDetails.[Product];
    So it doesn't work:/

    But the good news is, that I found another way.

    If I'll set focus before define "afterm" then, MS Access do it correctly. I don't know why, because the focus all the time is in this field.

    So consequently in my first example.

    If I have event after update:
    Code:
    afterM = Me.ID_gora_zlecenia.Text
    Ms access show me *text actually which was before actualization. - this problem appears only with multivalued field.
    But If I put this code:
    Code:
     Me.ID_gora_zlecenia.SetFocus
    afterM = Me.ID_gora_zlecenia.Text
    Ms Access show me *text correctly (text after update) - I know that. *text method working only if field has focus but focus was all the time in this field
    nonetheless I had to set focus

Posting Permissions

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