Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    18

    Unanswered: Help with TEXTBOX checking TABLE if values EXIST (DLOOKUP/DCOUNT)

    hello, ive tried searching but have not been able to find an answer that fits my needs.

    I have a textbox that a user must enter in a customerID. when a user presses a button on the form, it should run a validation where it checks the input with the values in a table to match it up with the values in there. If there is a match, it can proceed to the next validation, if not, then a message box appears.

    I have tried using dlookup and d count but no success. does it matter if my textbox is bounded to another table or not?

    the table i want it to look up is called tblcustomermaster, the field i want it to look up is CustomerID. I want it to search EVERY value in customerID not just one to find a match.

    This is what I have: DLookup("CustomerID", "tblCustomerMaster", "CustomerID = '" & me.frmtest.form.channel_id & "'")

    if anyone can help id appreciate it thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    DCount will return Null is no match is found, the value of CustomerID (that you already know) is one or more rows in the table have a matching value in their CustomerID column.

    This would probably more useful:
    Code:
    x = DCount("*", "tblCustomerMaster", "CustomerID = '" & me.frmtest.form.channel_id & "'")
    x = 0 --> no matching row (no customer with this ID)
    x > 0 --> x customers found with this ID
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    18
    wow thank you so much for the help!! it works, you just made my day!

    sorry if this is too much to ask but i just need one more thing i need help with which is related to my question. Once everything is validated and all the data the user inputs into the textboxes is good, the user presses a button and the information is added to a new table. However, the user cannot enter the same customerID for the same due date textboxes, how would i write and if/then statement for this validation? thanks so much

    here is a picture of what my form looks like:
    http://i130.photobucket.com/albums/p...rmeasytrak.jpg
    Last edited by taimysho0; 11-15-11 at 19:45.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure to understand. If you want to check if one row exists that already contains a given value if the CustomerID column AND a given date in another column (let's call it Due_Date), you can combine both conditions in the criteria argument of the DCount function:
    Code:
    Dim strCriteria As String
    strCriteria =  "CustomerID = '" & me.frmtest.form.channel_id & "' AND " & _
                   "Due_Date = #" & Format(me.frmtest.form.Due_Date, "mm/dd/yyyy") & "#"
    x = DCount("*", "tblCustomerMaster", strCriteria)
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Posts
    18
    actually this might sound confusing but, the textbox channel_id is actually bounded to a table called "requests". this textbox is also queried to another table called "customerid" and thats where the dcount looks for any matches.

    once that the dcount finds a match, it will move on to the next validation which is checking the table "requests" if there is a match with the fields "customerid" and "due date". if there is, then do not add to this table, since a user cannot enter the same customer id with the same due date. also the textbox and table already formats the date to "short date" so it wouldnt need to be formatted in the code right?

    would i need two different dcounts for this? they are referring to different tables. thanks!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can perform a second DLookup with the double criteria on the table "requests".
    Have a nice day!

Posting Permissions

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