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 & "'")
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
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:
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)
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!