{"id":100,"date":"2012-10-24T01:21:11","date_gmt":"2012-10-24T01:21:11","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=100"},"modified":"2016-01-01T14:39:00","modified_gmt":"2016-01-01T19:39:00","slug":"count-how-many-times-a-value-appears-in-a-table","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=100","title":{"rendered":"Count how many times a value appears in a table…"},"content":{"rendered":"
Ever want to…<\/p>\n
\n
Track how many times the Item has been returned for repairs<\/li>\n
Make sure the Item on the Packing Slip has a valid Return Authorization<\/li>\n
Make that Spare Part hasn\u2019t already been used for another repair<\/li>\n<\/ul>\n
For those times I use…<\/p>\n
Place the below code in a Module remembering not to name the Module the same as the Function.\u00a0 Mine is placed in modUtilities.<\/p>\n
\r\nFunction IDOccurs(strTable As String, lngID As Long) As Integer\r\nOn Error GoTo ErrHandler\r\n'From http:\/\/www.access-diva.com\/vba12.html\r\n'For numeric values<\/em><\/span>\r\n\r\nDim rst As DAO.Recordset\r\nDim db As DAO.Database\r\nDim myQuery As String\r\n\r\n myQuery = "SELECT * FROM " & strTable & " WHERE lngMyID = " & lngID & ";"\r\n\r\n Set db = CurrentDb()\r\n Set rst = db.OpenRecordset(myQuery, dbOpenDynaset, dbSeeChanges)\r\n\r\n rst.MoveFirst\r\n rst.MoveLast\r\n\r\n IDOccurs = rst.RecordCount\r\n\r\nComplete:\r\n Set rst = Nothing\r\n db.Close\r\n Set db = Nothing\r\n Exit Function\r\n\r\nErrHandler:\r\n MsgBox ("Error:" & Err.Description)\r\n IDOccurs = True\r\n Resume Complete\r\nEnd Function\r\n<\/pre>\n
And here\u2019s an example of its use…\u00a0 Note: UpdateUser() is shown below.<\/em><\/strong><\/p>\n