Code to update a multivalued field?

[cdaz]

Hi!
Like it or not, I have a form with (among others) a Y/N field and a multivalued field. The Y/N field indicates whether or not the resident is year-round or not (seasonal/snowbird). The multivalued field contains the months the resident it here. The user wants me to auto-select all months in the multivalued vield when the year-round field is set to yes in order to minimize data entry.

I have been trying to use an AfterUpdate Event using the following syntaxes as a test but Access doesn't like the format at all:
[Master Table].[Months Available].[Value] = "June" (this gives 2465 error)
Me ! [Months Available].[Value] = "June" (this gives a 424 error)

Any ideas? I'm not at all fluent with VBA but have been successful in populating form fields using data fields in another table so thought this would be similar.

Any thoughts, other than not to use multivalued fields? Because that isn't an option.
Thanks!

 

[pr2-eugin]

Hello cdaz, Like it or not, your table is not Normal(ized).. Unless you get it to be a good Normalized structure you will have troubles doing this.. I am not sure if any of us actually use Multivalued fields.. So replies would be very sparse..

BTW, there is always another way.. Its just sometime we do not want to take it..

Good Luck !!

 

[JANR]

If you insist on using MultiValue fields, then bookmark this webpage: http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx#BM9

See attached db for one way to do it, by using an Array to append each month to the table. Since you can't mass append to a multivalue field you have to loop it one month at a time.

Good luck

JR

Edit:

For the benefit of those who can't download an .accdb, here is the code behind the form:

Option Compare Database
Option Explicit

Const MonthList = "January,February,March,April,May,June,July,August,September,October,November,December"

Private Sub Seasonal_AfterUpdate()

Dim sSQL As String
Dim varValues As Variant
Dim intValues As Variant

On Error GoTo Handler

varValues = Split(MonthList, ",")

If Me.Seasonal = True Then
    If Me.Dirty Then
        Me.Dirty = False ' Force the record to save to table to get a hold on recordID
    End If
    
    For Each intValues In varValues

        sSQL = "Insert Into tblClients (Months.[value]) " & _
               "Values ('" & intValues & "') " & _
               "Where ID = " & Me.ID
               
        CurrentDb.Execute sSQL, dbFailOnError
    Next
        Me.Months.Requery
End If

ExitPoint:
Exit Sub

Handler:
    If Err.Number = 3022 Then 'Duplicat records error, gets handled by database engine
        Err.Clear
        Resume Next
    Else
        MsgBox "Error in Procedure Sesonal_AfterUpdate " & Err.Number & " Description: " & Err.Description
        Resume ExitPoint
    End If
End Sub

 

[cdaz]

Thanks so much - I will thoroughly review this info. However, I have to say that as I've searched the web for more info on multivalued fields, I'm now thinking that I need to press for removing them from the db and replacing them with another table or additional fields in the Master Table. This will be a bit of an uphill battle but might be easier than hitting my head against a brick wall.:banghead:

Thanks again!

 

[JANR]

No problem

JR