I
Iona
Hi, I have a fairly normalised table.
Contacts Table
Contacts ID
etc
etc
Contacts Name Table
Contacts Name ID
Contacts Name
Contacts Type Table
Contacts Type ID
Contacts ID (links to contacts ID in contacts table)
Contacts Type (linked to contacts name ID)
And then I have a form, with a subform, with a listbox, set to
multiselect.
And I also have this vba (taken from this forum)
Private Sub lbContactTypes_Exit(Cancel As Integer)
' Comments : Update the tbContactTypes table based on the
' unbound multiselect listbox lbContactTypes.
' Newly selected rows will be added to the table,
' newly cleared rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR
Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("tbContactTypes", dbOpenDynaset)
With Me!lbContactTypes
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
' in the table
rs.FindFirst "[ContactID] = " & Me.ContactID & " AND " _
& "[ContactType] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!ContactID = Me.ContactID
rs!ContactType = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.lbContactTypes.Requery
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox "Error " & Err.Number & " in Update Contact Types:" _
& vbCrLf & Err.Description
Resume PROC_EXIT
End Sub
Now, my problem is this: I keep getting a message "Syntax Error,
(Missing Operator) in expession. This happens wether I use on exit, or
before update.
Am I missing something obvious? Any pointers would be very
appreciated. I have tried to do everything as per textbook and forums,
but it still won't work...
cheers
Iona
Contacts Table
Contacts ID
etc
etc
Contacts Name Table
Contacts Name ID
Contacts Name
Contacts Type Table
Contacts Type ID
Contacts ID (links to contacts ID in contacts table)
Contacts Type (linked to contacts name ID)
And then I have a form, with a subform, with a listbox, set to
multiselect.
And I also have this vba (taken from this forum)
Private Sub lbContactTypes_Exit(Cancel As Integer)
' Comments : Update the tbContactTypes table based on the
' unbound multiselect listbox lbContactTypes.
' Newly selected rows will be added to the table,
' newly cleared rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR
Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("tbContactTypes", dbOpenDynaset)
With Me!lbContactTypes
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
' in the table
rs.FindFirst "[ContactID] = " & Me.ContactID & " AND " _
& "[ContactType] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!ContactID = Me.ContactID
rs!ContactType = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.lbContactTypes.Requery
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox "Error " & Err.Number & " in Update Contact Types:" _
& vbCrLf & Err.Description
Resume PROC_EXIT
End Sub
Now, my problem is this: I keep getting a message "Syntax Error,
(Missing Operator) in expession. This happens wether I use on exit, or
before update.
Am I missing something obvious? Any pointers would be very
appreciated. I have tried to do everything as per textbook and forums,
but it still won't work...
cheers
Iona