Multivalue Filed Update

[termsig]

Hi,

I have some difficulties to update table with the multi value record.

I look online but i can't find anything which will answer all question and point me in right direction.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_main")
rs.AddNew

rs![PO Number / Option] = Me.Option__PO_
rs!Style = Me.Style
rs![MKTGP Name] = Me.Marketing_Group
rs![Department Code] = Me.Department_Code
rs!Units = Me.Units
rs![Cartons / Sets] = Me.Cartons
rs![Date Rec in Processing] = Me.Date_Rec_in_Processing
rs![Processing Task] = Me.Porcessing_Task
rs![New PO] = Me.New_Option
rs![New Style] = Me.New_Style
rs!Processor = Me.Processor
rs![Processing Lane] = Me.Processing_Lane ' this is the field with multiple values.
rs![Processing Type] = Me.Porcessing_Type
rs!Consumable = Me.Consumable
rs![Consumables Rec Date] = Me.Consumables_Rec_Date
rs![Processing Status] = Me.Processing_Status
rs![Planned Completion Date] = Me.Planned_Completion_Date
rs!Comments = Me.Comments
rs![Intake Date / Request Date] = Me.Booking_Date

rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.GoToRecord , , acNewRec

When "rs![Processing Lane] = Me.Processing_Lane " is used to update the field in table no new record is added.

Is there any chance that someone can help with above?

Thanks,

 

[moke123]

i avoid using MVF's for reasons just like this. I'm not sure how you would handle it within a recordset.

from MS...

You might wonder why Office Access 2007 allows you to store more than one value in a field, when most relational database management systems forbid this. The answer is that the database engine in Office Access 2007 doesn't actually store the values in a single field. Even though what you see and work with appears to be a single field, the values are actually stored independently and managed in hidden, system tables. The Access database engine handles this for you, automatically separating the data and bringing it back together again to surface the values in one field.

 

[Uncle Gizmo]

See From Microsoft:-
How to: Manipulate Multivalued Fields With DAO

And a YouTube Video Here:-
Access2010 MultiValued ComboBox

 

[Uncle Gizmo]

How to update a multi-valued field in Access

 

[termsig]

Indeed a lot people don't like using MVF however it's only option which i might have... i look on the link and it doesn't make any sense for me..

Is there any chance that someone can advise on the changed to the current code?

Do i need loop MVF?

 

[jdraw]

however it's {MVF} only option which i might have

Please tell us more about your situation.

Please see this link re Recordset2

 

[Uncle Gizmo]

i look on the link and it doesn't make any sense for me..

Sorry I can't be of much help it's not something I've done. All of the expert advice is not to use them, so I have avoided them. However it's an interesting topic, so have kept an eye on it.

Someone on this forum may have more information and may find this thread and add to it.

I think it's more likely that people will have an interest in it. If that's the case they might be tempted to have a fiddle if you were to provide a sample database with a simple table with a couple of Fields, one field being multi value.

You could also experiment with the sample dB yourself, see if you can get the information out of the multi value field into a recordset object. Then see if you can manipulate that record set object.

If you post your code, I'm sure people will provide further input. People are always willing to offer their advice and help if they see that you are trying.

 

[termsig]

sample database attached:

when adding single field to CboSensor2 is returning "Data type conversion error".

Is there any option to add the data into table?

 

[moke123]

i am a little confused as to what your trying to do. In your sample, at the bottom of the AddClientInfo form you have a bound mvf combobox. it appears to work fine for me. Why are you trying to use a recordset?

 

[termsig]

i am a little confused as to what your trying to do. In your sample, at the bottom of the AddClientInfo form you have a bound mvf combobox. it appears to work fine for me. Why are you trying to use a recordset?

indeed it's showing MVF correct on form but it's showing error when adding record to table.

 

[static]

I think the point was... You don't need code to update a table from a bound form. It's automatic.

 

[termsig]

indeed it's showing MVF correct on form but it's showing error when adding record to table.

In that case what's the reason behind below two errors when trying add new client?

Run time error 3421 - multi value selected.

Run time error 64224 - with no value selected.

I can't imagine that nobody did anything similar in past even everyone telling that using MVF isn't good idea.

I'm brand new to VBA and not sure where i should go.

Look everywhere and couldn't find any working solution.

 

[Uncle Gizmo]

I thought you wanted to use Multi value Fields? Or was that the way it read, the only way you could see to do it... Most professionals don't use them (as far as I know) so if you are coming around to thinking that maybe you shouldn't be using them either, then we can probably make some headway.

If that's the case you need to explain your problem in simple terms, with some data would be good. We will probably have a whole thread on your problem, making suggestions. Then we should start to make some headway. I know this doesn't read right but it's late...

 

[static]

Fill in your controls and press F9.
Do not press the ''Add Client' button.
Open the table.

Can you see your new record?
Did you get an error?

 

[termsig]

I thought you wanted to use Multi value Fields? Or was that the way it read, the only way you could see to do it... Most professionals don't use them (as far as I know) so if you are coming around to thinking that maybe you shouldn't be using them either, then we can probably make some headway.

If that's the case you need to explain your problem in simple terms, with some data would be good. We will probably have a whole thread on your problem, making suggestions. Then we should start to make some headway. I know this doesn't read right but it's late...

Indeed i want use MVF on the form to update the table or at least this is the most easy way for user to choose multiple values.

I did include sample database were after choosing required values and adding record it's ending up with run time error.

if there is any other way how this can be done where user can choose multiple values and it can be populated into table it will be really amazing as I did already started pulling my hair to make it work.

I did attached sample database 2 post back and will be really appreciated if someone can advise the way to make it working; hopefully in couple months i will pass this knowledge to folks like me who started journey with vba.

@static

it's not working and returning run time error.

https://youtu.be/TeHmOeY7cTU

 

[Uncle Gizmo]

if there is any other way how this can be done where user can choose multiple values and it can be populated into table.....

I think there's a good chance.

I did attached sample database 2 post back and will be really appreciated if someone can advise the way to make it work.............

Extract:-
you need to explain your problem in simple terms, with some data would be good. We will probably have a whole thread on your problem, making suggestions. Then we should start to make some headway.

There are loads of problems with trying to discuss the issues by looking at a database constructed by the OP (that's you). The database is fine for giving an impression of how you would like the dB to look. From the point of view of the actual data, the way the tables are put together, then that really needs discussing. You need to show what data you have and explain what you want to do with that data. You need to show table structures that you consider a suitable and most likely we will tell you they're not suitable and show you a different way of constructing them. Once all this is out of the way then we can consider how to put the database together.

The other reason for getting you to write down what you're thinking is that this will get you to think about the problem. You will be surprised how difficult this is. Generally the process will help you iron out many problems yourself.

 

[moke123]

there is no need for vba to save the mvf to the table. It is a bound form. moving to a new record or closing the form will automatically save it.