another multi-select combo box mess....

[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

 

[Keith Wilby]

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.

At which line of code does the error occur?

Keith.
www.keithwilby.com

 

[Iona]

Ahh for a while it was at:

Me.lbContactTypes.Requery

but now it doesn't seem to give me any indication,
I will look again to see if there is anywhere specific
cheers
Iona

 

[Iona]

Hi, its not showing me anywhere specific

Ahh for a while it was at:

Me.lbContactTypes.Requery

but now it doesn't seem to give me any indication,
I will look again to see if there is anywhere specific
cheers
Iona

 

[Iona]

Ok, again I've changed that line to

Me.tbContactTypes.Requery (more in line with the example)

and the error message says: method or data member not found

hope this helps

cheers
Honor

 

[Keith Wilby]

Hi, its not showing me anywhere specific

Try running your code line by line (set a breakpoint as near to the start as
you can).

Keith.

 

[Iona]

ok, we're stuck on:

rs.FindFirst "[ContactID] = " & Me.ContactID & " AND " _
& "[ContactType] = " & lngCondition

thanks
Iona

 

[Keith Wilby]

ok, we're stuck on:

rs.FindFirst "[ContactID] = " & Me.ContactID & " AND " _
& "[ContactType] = " & lngCondition

Do "Me.ContactID" and "lngCondition" both have values when this line is
executed?

 

[Iona]

excuse my ignorance, but I don't really understand how to asertain
this.
Iona

ok, we're stuck on:

rs.FindFirst "[ContactID] = " & Me.ContactID & " AND " _
& "[ContactType] = " & lngCondition

Do "Me.ContactID" and "lngCondition" both have values when this line is
executed?

 

[Iona]

Ok ContactID is null

ok, we're stuck on:

rs.FindFirst "[ContactID] = " & Me.ContactID & " AND " _
& "[ContactType] = " & lngCondition

Do "Me.ContactID" and "lngCondition" both have values when this line is
executed?

 

[Keith Wilby]

Ok ContactID is null

There's your problem, you need to find out why it's null. If "Me.ContactID"
is a text box on your form then it needs to contain a value in order for
your code to work. If null is an acceptable state then you need to handle
the null condition in your code, perhaps something like:

If IsNull(Me.ContactID) Then
rs.FindFirst "[ContactType] = " & lngCondition
Else
rs.FindFirst "[ContactID] = " & Me.ContactID & " AND " & "[ContactType]
= " & lngCondition
End If

Keith.

 

[AccessVandal via AccessMonster.com]

Iona,

-Me.lbContactTypes.Requery

Is that a Label or a textbox? I notice your prefix “lb”,
This is an indication of a label.
A label does not have a property called “Requery”.

-Me.tbContactTypes.Requery (more in line with the example)
-and the error message says: method or data member not found

Means, access can’t find that control. Check the control
Properties called “Name” to find out the actual name.

 

[Iona]

Ok, thanks Keith, I have remedied this (via some database corruption!
argh), however
I find that the requery command is not working, the listbox is holding
onto the selected values no matter what record is on the form.

I have tried

Me.lbContactTypes.Requery
Which it will accept,

however, if I use

Me.tbContactTypes.Requery
It gives me an error message.

Likewise It does not display what records have been selected after you
leave the record and then return to it.

sorry about the headache of this, I just really feel that the
continuous form solution won't work for my client.

any ideas would be appreciated.

regards,
Iona

Ok ContactID is null

There's your problem, you need to find out why it's null. If "Me.ContactID"
is a text box on your form then it needs to contain a value in order for
your code to work. If null is an acceptable state then you need to handle
the null condition in your code, perhaps something like:

If IsNull(Me.ContactID) Then
rs.FindFirst "[ContactType] = " & lngCondition
Else
rs.FindFirst "[ContactID] = " & Me.ContactID & " AND " & "[ContactType]
= " & lngCondition
End If

Keith.

 

[Iona]

Here is the error message:

Compile error:
Method or datamember not found

cheers & thanx

Ok, thanks Keith, I have remedied this (via some database corruption!
argh), however
I find that the requery command is not working, the listbox is holding
onto the selected values no matter what record is on the form.

I have tried

Me.lbContactTypes.Requery
Which it will accept,

however, if I use

Me.tbContactTypes.Requery
It gives me an error message.

Likewise It does not display what records have been selected after you
leave the record and then return to it.

sorry about the headache of this, I just really feel that the
continuous form solution won't work for my client.

any ideas would be appreciated.

regards,
Iona

Ok ContactID is null

There's your problem, you need to find out why it's null. If "Me.ContactID"
is a text box on your form then it needs to contain a value in order for
your code to work. If null is an acceptable state then you need to handle
the null condition in your code, perhaps something like:

If IsNull(Me.ContactID) Then
rs.FindFirst "[ContactType] = " & lngCondition
Else
rs.FindFirst "[ContactID] = " & Me.ContactID & " AND " & "[ContactType]
= " & lngCondition
End If

Keith.