By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,889 Members | 714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,889 IT Pros & Developers. It's quick & easy.

How to Edit Multivalued Fields

P: 9

I want to know if there is a way to edit a multivalued field?

please teach me how


Attached Files
File Type: zip (17.9 KB, 193 views)
Feb 14 '14 #1
Share this Question
Share on Google+
5 Replies

Expert 5K+
P: 8,745
This is a little more complicated then you may think. Using your Attached DB, and updating all occurrences of 'Bridge Cable' to 'RX157 Cable' in the [Description] Multi-value Field, the procedure would be:
  1. Create a Recordset on the Detail Table.
  2. Loop through all Records in the Detail Table via the Recordset.
  3. For each Unique [ID], create another Child Recordset based on all the Values [Description] for this Multi-value Field.
  4. Loop through these [Description] Records, and for each one that contains the text 'Bridge Cable' replace it with RX157 Cable.
  5. If a Record in any Multi-value Field contains this text, place the Parent Recordset and Child Recordsets in Edit Mode.
  6. Make the changes.
  7. Update both Recordsets.
  8. Do your Clean Up Chores!
  9. Requery the Form.
    Expand|Select|Wrap|Line Numbers
    1. Dim db As DAO.Database
    2. Dim rst As DAO.Recordset
    3. Dim rstComplex As DAO.Recordset2
    5. Set db = CurrentDb
    7. 'Open a Recordset on the Detail Table
    8. Set rst = db.OpenRecordset("SELECT * FROM Detail", dbOpenDynaset)
    9.   'Loop through all the Records
    10.   Do Until rst.EOF
    11.     'Get the Description Type Complex Field
    12.     Set rstComplex = rst!Description.Value
    13.       'Loop through all Description Complex Data Type Values
    14.       Do Until rstComplex.EOF
    15.         'Does Bridge Cable appear in the Description Field?
    16.         If InStr(rstComplex.Fields(0), "Bridge Cable") > 0 Then
    17.           'Wherever 'Bridge Cable' appears, Replace it with RX157 Cable
    18.           rst.Edit      'Parent Recordset must be in Edit Mode
    19.             rstComplex.Edit
    20.               rstComplex.Fields(0) = Replace(rstComplex.Fields(0), _
    21.                                      "Bridge Cable", "RX157 Cable")
    22.             rstComplex.Update
    23.           rst.Update
    24.         End If
    25.           rstComplex.MoveNext
    26.       Loop
    27.         rst.MoveNext
    28.   Loop
    30. Me.Requery
    32. 'Clean up chores
    33. rst.Close
    34. Set rst = Nothing
    35. Set rstComplex = Nothing
    36. Set db = Nothing
  10. I do realize that this is a lot to absorb, so if there are any questions, please feel free to ask.
Feb 14 '14 #2

P: 9
Hi ADezii,

Thanks for the reply..
kind a hard to absorb..
can you do that in my attached db.

Thank you so much
Feb 17 '14 #3

Expert Mod 5K+
P: 5,397
ADezii, has provided you with a very reasonable and generous amount of his time and knowledge. Certainly as much as I would have done if I were not so buried at work these past months. (^_^)

At this point, please make the attempt to do the work yourself.

It does you no good to have ADezii, myself, or anyone else here do your work for you - we try to "teach you how to fish" not "give you the fish" ...

If you are still having issues after following ADezii's example, please post back with the details.
Feb 17 '14 #4

P: 9
hi zmbd,

I'm sorry for that.
I know what you mean.Thank you for that.
I am a newbees in ms access/programmer.
It's easy for me to learn if i have some example to look up.
That's why i ask Adezii to try at my sample db.

Again sorry for the interpretation.
Feb 18 '14 #5

Expert Mod 5K+
P: 5,397
No worries yuanmiguel.

Ah, yes, we've all started there and the better the examples we have to work with, often the easier it is to grab on to the tool. In this case, ADezii has provided a hammer with the safety grip and teather so that it should be easy enough for even a novice, with some work, to be able to adapt to the project at hand. (^_^)

It is ultimately up to ADezii how much help is provided and quite often ADezii is "generous to a fault" and as such is well loved and respected here by many, including myself, for the amount of help provided. Hence my request that you attempt to work thru what has already been provided - sometimes, one simply needs to stand-up and put that foot forward and go-fo-it!

If you have any problems following ADezii's example within your database, post back with the details.

(oh, and BTW, this is one reason to avoid the MVF unless you are having to post to a sharepoint site. There is nothing to be gained using MVF that can not be obtained within a properly normalized database. > Database Normalization and Table Structures. )
Feb 18 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.