Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    5

    Unanswered: RESOLVED - Populate textbox with count of unique records from SQL query

    So, new to VBA and SQL, but making quick progress. Here's my problem:

    I have table (LEKSITES) with two fields (LEKNAME and UNITNAME). The relationship between LEKNAME and UNITNAME is not 1:1, i.e. a given LEKNAME may correspond to multiple UNITNAMEs.

    I am building a form (F_TEST) with a combobox (cboLEKNAME) and a textbox (txtUNITS) in which I would like the user to select the LEKNAME from the combobox and have the number of UNIQUE records show up in the textbox.

    Basically something like this, but that actually works:

    Private Sub cboLEKNAME_AfterUpdate()

    Me.txtUNITS = COUNT OF "SELECT LEKSITES.UNITNAME FROM LEKSITES WHERE LEKSITES.LEKNAME = cboLEKNAME.Value GROUP BY LEKSITES.UNITNAME"

    I need the code that performs what's in the red font.

    Thanks!
    Last edited by cvdellen; 02-03-10 at 19:53. Reason: Problem resolved.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    DCount( )
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2010
    Posts
    5
    I tried DCount() but that returns the total record count, not the unique record counts grouped by UNITNAME that I need.

  4. #4
    Join Date
    Feb 2010
    Posts
    5
    Found a piece of code that looked promising:

    Me.txtUNITS = DCount("[UNITNAME]", "LEKSITES", "[UNITNAME] IN ( SELECT LEKSITES.UNITNAME FROM LEKSITES WHERE LEKSITES.LEKNAME = cboLEKNAME.Value GROUP BY LEKSITES.UNITNAME )")

    However, with this the text box returns the total number of UNITNAME entries in my entire table that matches my LEKNAME criteria. In essence, it is not performing the GROUP BY part of the query. When I run the query in Access using the exact same SQL language it works correctly. Something is lost when the query is performed within the DCount function.

    Anybody know why this is or how to fix it?

  5. #5
    Join Date
    Feb 2010
    Posts
    5
    SOLUTION:

    Private Sub cboLEKNAME_AfterUpdate()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    Set db = CurrentDb

    strSQL = "SELECT LEKSITES.UNITNAME FROM LEKSITES WHERE LEKSITES.LEKNAME = '" & cboLEKNAME.Value & "' GROUP BY LEKSITES.UNITNAME;"

    Set rs = db.OpenRecordset(strSQL)

    Me.cmbox_PMUNAME = rs.RecordCount

    rs.Close
    db.Close

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Couldn't you just make it a combobox (or even a listbox) and for the rowsource property, make it your totals (summization query) putting criteria such as: Like Forms!MyFormName!cboLEKNAME.

    You'd then just issue a requery command to the combobox or listbox in the Afterupdate of the 1st combobox. A listbox can easily be setup to show as a single field by sizing it.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •