Access Database Problem

I am developing an Access database for a local charity which teaches people to use the internet. One field is a tick box flagging that the user is a Member. There are other categories of user (User, Tenant, One to One) and the charity later required a separate field listing that status and allowing multiple entries into the field. This is done via a Combo Box set to allow multiple entries. Their requirements allow various possibilities of entering inconsistent data (e.g. Tick box says Member but the combo box field does not include Member) I wrote code to check for such inconsistencies. However with the multiple entry combo box code does not recognise the .Value of the combo box field so it can't check if Member is included. If the combo box is set to only allow one entry the same code works as required and can see what the field contains. I cannot find a solution to this. Does anyone recognise why?

Make a separate Access demo file that has just the form and code you are having trouble with and post it somewhere it can be downloaded.

It will be difficult to post as you suggest, partly but not completely as it would reveal real data. Instead I am posting the relevant code error. It is described as "runtime error 13, type mismatch"

Private Sub cmdConsistency_Click()
Dim Cancel As Integer
Call Check59_Exit(Cancel)
'Call cmbPersonType_Exit(Cancel)
Call PersonTypeID_Exit(Cancel)
'If Check59.Value = False And cmbPersonType.Value = 1 Then
'MsgBox "PersonType value is " & PersonTypeID.Value
If Check59.Value = False And PersonTypeID.Value = "Member" Then
MsgBox "Person Type is set to member but Member tick box is not set"
End If
If Check59.Value = True And PersonTypeID.Value <> "Member" Then
MsgBox "Member tick box is set but PersonType is not Member"
End If
End Sub

The section in bold is the type mismatch. Exactly the same code with the same data works fine when the field PersonTypeID is a combobox where only one value can be entered to the field.

This looks like some sort of compiler quirk to me. Try this (just a guess):

Private Sub cmdConsistency_Click()
Dim Cancel As Integer, x as String
Call Check59_Exit(Cancel)
'Call cmbPersonType_Exit(Cancel)
Call PersonTypeID_Exit(Cancel)
'If Check59.Value = False And cmbPersonType.Value = 1 Then
'MsgBox "PersonType value is " & PersonTypeID.Valuex=PersonTypeID.Value
x=PersonTypeID.Value
If Check59.Value = False And x = "Member" Then
MsgBox "Person Type is set to member but Member tick box is not set"
End If
If Check59.Value = True And x <> "Member" Then
MsgBox "Member tick box is set but PersonType is not Member"
End If
End Sub

Thanks for that. Still the same problem. This time Debug flags the line x = PersonTypeID.Value as the type mismatch

As it says 'Type Mismatch', it looks as though you need:

If Check59.Value = False And cstr$(PersonTypeID.Value) = "Member" Then

Also, if PersonTypeID is a control on a form, why do need to put .Value, as Value should be the default?

This is what I would use:If Check59.Value = False And cstr$(Me![PersonTypeID]) = "Member" Then

I should mention that using a query returns the value of PersonTypeID.Value (but only shows the first entry in the case where multiple entries have been selected). So the query recognises PersonTypeID. Value but Visual Basic does not for a multiple entry combo Box (but does for a single entry Combo box)

Tried both of nbcaldon's suggestions. Both still give the same type mismatch. Thanks for the ideas.

Maybe the value is null. Try testing for this first:

if isnull(Me![PersonTypeID]) then stop

and then check what is going on in the code.

No that's not it !