Access Database Problem

Page 1 of 2 12 LastLast

  1. Posts : 24
    Windows 10
       #1

    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?
      My Computer


  2. Posts : 355
    Win10 Ver. 1809 Build 17763.`
       #2

    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.
      My Computer


  3. Posts : 24
    Windows 10
    Thread Starter
       #3

    Access dadabase problem


    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.
      My Computer


  4. Posts : 355
    Win10 Ver. 1809 Build 17763.`
       #4

    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
      My Computer


  5. Posts : 24
    Windows 10
    Thread Starter
       #5

    Access Database Problem


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


  6. Posts : 16
    Windows 10 V1709
       #6

    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
      My Computer


  7. Posts : 24
    Windows 10
    Thread Starter
       #7

    access database problem


    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)
      My Computer


  8. Posts : 24
    Windows 10
    Thread Starter
       #8

    access database problem


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


  9. Posts : 16
    Windows 10 V1709
       #9

    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.
      My Computer


  10. Posts : 24
    Windows 10
    Thread Starter
       #10

    access database problem


    No that's not it !
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd
All times are GMT -5. The time now is 23:20.
Find Us




Windows 10 Forums