Results 1 to 8 of 8
  1. #1
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34

    Follow up on Dcount question

    I previously had a problem solved here using a Dcount. Previously I checked how many trainings there were when the TRNG_ID = (combobox value) and EMP_ID = (Combobox value). This worked out fine and I was able to check for duplicates using this method. I have now run into a problem however where it seems to be more beneficial to count how many different TRNG_IDs I have that have when TRAINING = (combobox value) and EMP_ID = (Combobox value). Here is the code I tried using to accomplish this.
    Dim count As Integer
    Dim lookup As Integer
    Dim emp As Integer
    emp = Me.TXT_EMP.Column(2)
    Dim trng As Integer
    trng = Me.TXT_TR.Column(1)
    count = DCount("[TRNG_ID]", "[EMP_TRNG_TBL]", "[TRAINING] ='" & trng & "'" And "[EMP_ID] = " & emp)



    This code is giving me a data type mismatch error and I am not sure where this is occurring. If you could enlighten me on what I am missing, that would be greatly appreciated. Thank you for your time.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    count using asterisk,
    and if : trng is string (your string delimiter was off)
    and emp is numeric:

    count = DCount("*", "[EMP_TRNG_TBL]", "[TRAINING] ='" & trng & "' And [EMP_ID] = " & emp)

  3. #3
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    It worked! So instead of counting how many ID's have the same TRNG and EMP as the combobox values, we're now just counting how often the TRNG and EMP both equal the comboboxes while in the same row?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Referencing record ID should produce same result because every record has an ID (no Nulls). Your error was caused by improper syntax due to extra quote marks - in red below.

    count = DCount("[TRNG_ID]", "[EMP_TRNG_TBL]", "[TRAINING] ='" & trng & "'" And "[EMP_ID] = " & emp)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    Thank you so much for the reply. If I can try to take it one step further, what if the trng field was a multiselect listbox, how would you cycle through every training chosen while keeping the same emp value? This code is to check and make sure that the user is not inputting an already input training. Thank you for your time.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Loop through listbox selection and either build CSV string for use as IN() function parameter array or test for individual training.

    Example of using IN()

    count = DCount("[TRNG_ID]", "[EMP_TRNG_TBL]", "[TRAINING] IN (" & trng & ") And [EMP_ID] = " & emp)

    Compiled criteria would look like: [TRAINING] IN (1, 4, 7) AND

    If TRAINING is text field would need apostrophes: [TRAINING] IN ('abc', 'def', 'ghi') AND

    However, if you need to test each training individually, run the DCount() test within listbox loop

    count = DCount("[TRNG_ID]", "[EMP_TRNG_TBL]", "[TRAINING] = '" & listbox item here & "' And [EMP_ID] = " & emp)


    Here is example of looping listbox to build string: http://allenbrowne.com/ser-50.html.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    That makes sense but I am still unsure of where to put the Dcount for the training listbox item to be able to loop through all selections

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you review code in referenced link? Instead of building CSV string, use DCount() - something like:
    Code:
    'Loop through the ItemsSelected in the listbox. With Me.lstTraining For Each varItem In .ItemsSelected If Not IsNull(varItem) Then If DCount("[TRNG_ID]", "[EMP_TRNG_TBL]", "[TRAINING] = '" & .ItemData(varItem) & "' And [EMP_ID] = " & emp) > 0 Then Exit Sub End If Next End With

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. DCount Question
    By perryc in forum Forms
    Replies: 6
    Last Post: 12-23-2019, 03:53 PM
  2. DCount / DLookup Question
    By TEXEIRC in forum Programming
    Replies: 4
    Last Post: 11-01-2017, 10:26 AM
  3. Replies: 1
    Last Post: 03-30-2015, 03:08 AM
  4. Dcount question
    By alsoto in forum Forms
    Replies: 2
    Last Post: 08-29-2011, 02:30 PM
  5. Dcount question
    By jpkeller55 in forum Access
    Replies: 4
    Last Post: 02-21-2011, 11:43 AM

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