Results 1 to 10 of 10
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    pCount to tick box is = 0



    Code:
    Private Sub CmdClose_Click()
        Dim pCount As Integer
        Dim TF As Boolean
        Dim AT As String
        AT = Nz(Me.CboPerson, "")
        'Count number of Preferred=True for this PersonID
        pCount = DCount("PersonID", "AddressTbl", "PersonID='" & AT & "'" & " AND Preferred =" & True)
        
        If pCount = 0 Then
            TF = True
        Else
            TF = False
        End If
        
    DoCmd.Close
    Forms!PersonFrm.Refresh
    End Sub
    I am trying to get this to work on a form I have so when I close the form, it counts the PersonID which is a number in table and if it =0 then check the Preferred checkbox, if not, then don't check the checkbox. I am getting a type Mismatch error on this and cant figure it out for the life of me?

    I could also use some assistance with code for the OnClick event of this Preferred checkbox, so when I click it manually, it makes the current record Preferred and unchecks any others that may be preferred as I only want one to be preferred. Sort of like a change to the default address I want display.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The message means you're passing the wrong type of data to something & often that value is null to a field that cannot except it.
    What I see as being potential problem is that you seem to be looking for form values on close (AT). At that point, Unload has already happened thus there shouldn't be any values to retrieve. Unload event might not be too late but if you're using a button to close the form, I would use that event. Sometimes the error is the result of forgetting that the combo value and the chosen value are not the same data type when 2 or more columns are involved.
    Last edited by Micron; 08-11-2019 at 07:11 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi,
    I am using a close button to fire that with. When on record 1 I debug I get the pCount=0 and the PersonID=1, the At=1 and the True=True when I hover over it. The PersonID in the table is a number, the CboPerson is a combo with PersonID and Name, Bound to the PersonID or 1. I am using similar code in an unbound form and it works just fine? I don't know why the pCount would = 0 when there is one Preferred ticked all ready? It should = 1

  4. #4
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Data.zip

    I went ahead and put in the sample db for reference. I have tried the code in afterupdate, beforeupdate, onclose_click. with no success at all. What I am trying to do is if I add an address and there is not one that is checked Preferred, then to check that preferred. If there is then no check it. Then also, if I manually check the Preferred and there is one that is checked, I want it to uncheck the existing and check the one I just ticked! Does this make sense. There can only be one preferred address.

    Code:
    Private Sub Form_AfterUpdate()
        Dim pCount As Integer
        Dim TF As Boolean
        Dim AT As String
        AT = Nz(Me.CboPerson, "'")
        'Count number of Preferred=True for this PersonID
        pCount = DCount("PersonID", "AddressTbl", "PersonID='" & AT & "'" & " AND Preferred =" & True)
        
        If pCount = 0 Then
            TF = True
        Else
            TF = False
        End If
    End Sub

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This will work. You need to add OPTION EXPLICIT to the top of the module. It will point out some spelling errors in your code.

    Code:
    Private Sub CmdClose_Click()
        Dim pCount As Integer
        Dim TF As Boolean
        Dim AT As String
        AT = [PersonID]
        'Count number of Preferred=True for this PersonID
        pCount = DCount("PersonID", "AddressTbl", "PersonID=" & AT & " AND Preferred =" & True)
        
        If pCount = 0 Then
            TF = True
        Else
            TF = False
        End If
        
        DoCmd.Close
    End Sub
    

  6. #6
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you so much! That worked like a charm....
    Any chance I can get some advice on the getting a Preferred_Click event if I manually check the box on a record that isn't a Preferred to uncheck the one that is checked?
    Thanks

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Data D9-davegri-v01.zip
    Have a look at this. I made a LOT of changes, mainly setting up a mainform/subform for persons and addresses.
    The subform lists all the addresses for the person and includes the preferred checkbox. The checkbox will only allow one address to be checked.

  8. #8
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you davegri
    you are the best of the best~
    That works real nice, wish there was a way to get around having a subform as it takes up so much room but I will live with it as this seams to be best way to handle it.
    Again, Thank you
    Dave

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    It's not really a datasheet, it's a continuous form. See attached. You can stack a continuous form into multi-lines like this one, unlike a data sheet.
    It makes it narrower, but a bit taller. You can take your pick...

    Data D9-davegri-v02.zip

  10. #10
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you davegri
    This is very nice and appreciated.
    Again, Thank you

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use a tick box in a query
    By phil123 in forum Access
    Replies: 7
    Last Post: 06-05-2018, 01:46 PM
  2. how to tick all checkboxes
    By darwin in forum Programming
    Replies: 4
    Last Post: 05-30-2015, 09:48 AM
  3. how to add tick boxs to filter
    By sspreyer in forum Programming
    Replies: 1
    Last Post: 11-05-2013, 09:13 AM
  4. Tick Box in a Query
    By Cran29 in forum Queries
    Replies: 1
    Last Post: 06-15-2012, 05:33 PM
  5. Password on a Tick Box?
    By wadey in forum Security
    Replies: 1
    Last Post: 04-25-2011, 12:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums