How to autopopulate an MVF via button VBA? (1 Viewer)

[Harry Paraskeva]

The title pretty much says it all. Assume we have an MVF on an Access form with some values in the list behind it (either Value List or Table/Query).

Is it possible to have a button that when clicked can populate the MVF for the current record with a value that is already in the list of the MVF? If yes, how would you go about it?

I've tried with the INSERT INTO command, but it keeps giving errors. The code I used is as follows:

Dim strsql As String
strsql = "INSERT INTO Table1(Field1.[Value]) Values ('C')" & "Where ID= '" & Me.ID & "');"
DoCmd.RunSQL qst
Me.Refresh

Any help appreciated!

 

[Harry Paraskeva]

And I think I might have a case of too late at night to think...it was a type mismatch issue...

Dim qst As String
qst = "INSERT INTO Table1(Field1.[Value]) Values ('C')" & "Where ID= " & Me.ID & ";"
DoCmd.RunSQL qst
Me.Refresh

 

[Uncle Gizmo]

Not sure if this applies in your case, as I think it's more to do with combo boxes however I thought it worth pointing it out to you:- "Access2010 MultiValued ComboBox" https://youtu.be/queKMe9MiSs

There's also this from Microsoft;- How to: Manipulate Multivalued Fields With DAO https://msdn.microsoft.com/en-us/library/bb258183.aspx

And then This:- "How to update a multi-valued field in Access" https://social.msdn.microsoft.com/F...a-multivalued-field-in-access?forum=accessdev

Finally:- "Recordset2 Object (DAO)" https://msdn.microsoft.com/en-us/library/office/ff197737.aspx

 

[Harry Paraskeva]

Not sure if this applies in your case, as I think it's more to do with combo boxes however I thought it worth pointing it out to you:- "Access2010 MultiValued ComboBox" https://youtu.be/queKMe9MiSs

There's also this from Microsoft;- How to: Manipulate Multivalued Fields With DAO https://msdn.microsoft.com/en-us/library/bb258183.aspx

And then This:- "How to update a multi-valued field in Access" https://social.msdn.microsoft.com/F...a-multivalued-field-in-access?forum=accessdev

Finally:- "Recordset2 Object (DAO)" https://msdn.microsoft.com/en-us/library/office/ff197737.aspx

I already had in mind the Microsoft resources, but didn't want to go for a DAO approach (already have a quite complex database and didn't want to fiddle around with recordsets), but the video provided an excellent and very functional alternative, so thank you for the resource!

 

[Uncle Gizmo]

I would be interested in your solution if you have the time and inclination to publish it here and it would also help other people with this problem.

Sent from my SM-G925F using Tapatalk

 

[Harry Paraskeva]

I would be interested in your solution if you have the time and inclination to publish it here and it would also help other people with this problem.

Since the video solution was much simpler for what I wanted to do (autopopulate 5 mvfs and some other checkboxes with a specific value - saves the user about 15 clicks in the db), I followed that solution! The INSERT INTO alternative was more complex and I was not certain whether it would support assigning values that derive from other linked tables. In any case, the full code is the following:

Private Sub Command2222_Click()
Me.PartCertainty = True
Me.Shape_Certainty = True
Me.TechniqueCertainty = True
Me.FunctionCertainty = True
Me.Dating_Specific_Certainty = True
Me.Check1143 = True
MVF1
MVF2
MVF3
MVF4
MVF5
End Sub

Private Sub MVF1()
Dim tmp(0) As Variant
tmp(0) = 1&
Me.Part.Value = tmp
End Sub

Private Sub MVF2()
Dim tmp(0) As Variant
tmp(0) = 186&
Me.ShapeSpecificType.Value = tmp
End Sub

Private Sub MVF3()
Dim tmp(0) As Variant
tmp(0) = 1&
Me.Technique_General.Value = tmp
End Sub

Private Sub MVF4()
Dim tmp(0) As Variant
tmp(0) = 10&
Me.Combo1142.Value = tmp
End Sub

Private Sub MVF5()
Dim tmp(0 To 1) As Variant
tmp(0) = 10&
tmp(1) = 14&
Me.DatingSpecific.Value = tmp
End Sub