Windows 10: 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?

:)

 

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

After update from SQLSERVER 2012 to 2014, I can no longer access production database from second PC on LAN. SQL Server 2014 Management Studio returns error number 229, severity 14 whenever I try to access the database. SQL server 2014 on PC where database
resides has no problems with accessing the data.

 

dadtabase? or Works for Windows

Access is the database application that comes with Microsoft Office.

Works database to Access database - Recherche Google

 

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.

 

Is it returning a string at all? What does

Dim x as Integer
x = len(Me![PersonTypeID])

give you?

 

For a combo box set to allow multiple entries it generates a type mismatch. For a combo box set to allow only 1 entry it works fine. Text length is 6 for setting Member. This is the general situation. Everything woks correctly for single entry and generates the mismatch for multiple entry.

all the best

 

Hi robertd,

First, I have to say Thank You for introducing me to Multi-value fields and Multi-value combo boxes.
They look like a super idea, but I am finding the implementation a little confusing.
My impression is that using a multi-value field is just a way of avoiding creating another little table in your database - one that would be easy to set up, understand and use. Multi-valued fields seem to be unique to Access and I would avoid using them.

However, if you must...

To get at the data held in the field, you need to use:
Me!comboboxname.Text
which will give you a comma separated list of the values stored in the field.

You can also tell whether each option is selected by using:
Me!comboboxname.Selected(n)
where n is the index for the list. This will return True or False, but I have no idea how you know which index number relates to which value in the list - if you work this out, please let me know.

Note that Me!comboboxname.Value does not seem to return anything useful. It is not Null, but neither does it appear to be a string or a number.

 

Thanks for this. I have a big meeting tomorrow about the database but I will look at this ASAP