How to insert multi valued field in a table using access vba

[habinalshaikh]

I'm writing a program by using MS Access VBA.


In this program I have textboxes that I fill them out and once I click on the button it inserts the values which are in the textboxes to a table named "Programs", but I have a lookup field that allows to select multi-values and I can't add the values into the Program table.

I have tried the following code:


Private Sub btnAdd_Click()

CurrentDb.Execute _
"INSERT INTO Programs " & _
"(Program_Name, Objectives, Targetted_Competency.[Value]) " & _
"VALUES ('" & Me.txtProgramName & " ', '" & Me.txtObjectives & " ', '" & Me.Targetted_Competency & " ')"

Programs_subform.Form.Requery


End Sub




but it displays error " Type mismatch", although all textboxes have been added into "Programs" table except the multivalued field which named "Targetted_Competency"

can any one help me with this?

[Micron]

First, multi value table fields are to be avoided. It is something you can create, but that doesn't mean you should. This design saves casual users from having to learn proper table design and relationships. The table that provides the mvf list is a hidden table that you cannot see, and I only know one person who might even know where it is (and he would never use them either). So forget about populating it with your list items. Instead, learn proper db normalization and ditch the mvf in your table. You will only have issues with it down the road.

[Gicu]

I agree that MVF should be avoided, but please let us know what happens if you try it like this:

CurrentDb.Execute _
"INSERT INTO Programs " & _
"(Program_Name, Objectives, Targetted_Competency.[Value]) " & _
"VALUES ('" & Me.txtProgramName & " ', '" & Me.txtObjectives & " ', '" & Me.Targetted_Competency.Value & " ')"


Programs_subform.Form.Requery

Here is a link to Colin's article about MVF:
https://www.isladogs.co.uk/multivalued-fields/

https://support.microsoft.com/en-us/...3-b6624e1e323a

[June7]

Cross-post https://stackoverflow.com/questions/...ing-access-vba

[Welshgasman]

Nice spot. Now on my IL.