Modules & VBA :: 3 Field Duplicate Dcount
Jun 18, 2015
I have a tblManualRewardIncident in there I have 3 fields I want to have my staff in put these rewards via a frmRewardIncidentEntry However im concerned over another staff member in putting the same data so I want the form to highlight on update that a duplicate is there already so cancel the data entry.
The 3 fields that need to check and match are StrataID , DateOfIncident , EndDateOfIncident these are all in the tblManualRewardIncident and appear as buttons in the form frmRewardIncidentEntry the buttons are as follows
cboStrataID , txtDateOfIncident , txtEndDateOfIncident
I want the VBA to run when I CLICK another button on same form cboSQCDP..All 3 fields must match exactly to flag a issue.
StrataID , DateOfIncident , EndDateOfIncident from tblManualRewardIncident
If it doesn't find a match continue and add the inputs in these boxes to the table if it finds a match flag a message and delete the data from the form.
View Replies
ADVERTISEMENT
Jun 15, 2014
If expression to determine if a duplicate records has been entered.
DateAte is a date
MealType is a list box (text)
All I want to do is prevent two lunches, etc. from being entered on the same date. Code that results in the message box every time, regardless if a duplicate record or not.
Private Sub MealType_AfterUpdate()
Dim strDA As String
Dim DA As Date
DA = Me.DateAte.Value
strDA = "[DateAte]=" & "#" & DA & "#"
If DCount("[MealType]", "tblmeals", strDA) > 1 Then
[code]....
View 7 Replies
View Related
Jun 8, 2015
How can I create an alert message to tell the user that there are an existing work order number in the table? I don't want it to restrict the entry, just inform the user that there is an existing work order number, do they want to continue.
Btw, the code will be attached to a combo box (cbowonum).
View 8 Replies
View Related
Jun 13, 2013
I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".
The code should copy the parent form fields and create x number of duplicate records according to a field on sub-form. It then runs an append query to add the information from the subform.
Code:
Private Sub GuestsInParty_LostFocus()
Dim partymsg As Integer
Dim dbs As dao.Database, rst As dao.Recordset
Dim F As Form
Dim intHowMany As Integer
Dim intCounter As Integer
[code]....
View 1 Replies
View Related
May 31, 2014
How can I prevent duplicate records from being added from a form, the dcount in the text field property, trigged before update is where this should be used is what I know but having trouble with the syntax.
I've got the table tblInvnetoryDetail with InvID(AutoNumber), and SerialNumber. The form text field is txtSerialNumber. I've managed this far with the expression
DCount([InvID],"tblinventoryDetail","[txtSerialNumber]=&"'") but this did not work.
How can I get the expression to avoid duplicates.
View 4 Replies
View Related
Jul 14, 2014
I need to check the combination of a text box and a combo box that are bound to the same table for duplicates and then give a message box telling of the existing records. The 2 fields are set up as a compound index so access displays a generic message but I am trying to customize the message without doing it through error trapping (because I want it earlier in the processes on the form).
I am trying to us Dcount as follows but this is not working. It seems to be checking for either rather than a combination of the 2 fields because I get my message if either the Description OR the Group is duplicated (both of the text box and the combo box can have duplicates and it is only the combination that is singular) .
If DCount("[Description]", "[ItemTBL]", "[Description] = '" & Description & "'")
And DCount("[Group]", "[ItemTBL]", "[Group] = '" & Group & "'") > 0 Then
MsgBox "This Item is already in the database.", vbExclamation, "Already in Database"
View 7 Replies
View Related
Jul 11, 2015
Im a relative novice with access VBA, and I'm really struggling with using Dcount with date variable. All I want to do is count if a certain date appears in a table. Here is the extract from my code:
Dim WorkoutDate as Date
Dim datecount as integer
datecount = DCount("[WorkOut Date]", "tblworkoutlogs", "[workout date]= " & WorkoutDate)
Whatever dates are in tblworkoutlogs, datecount is still = 0...I've trawled the net and tried many variations of the code but no success!if I change all the date formats to strings in the code and the tables, it works so I know i'm looking in the correct place.
View 5 Replies
View Related
Jun 8, 2015
Upgraded from Access 2003 to Access 2010. My DCount query was working fine in Access 2003 for many years but now I am having issues. Everything is written in VBA.
Routine:
1.) Insert records into table tblEmployee
2.) Query table tblEmployee to count how many employees are in each department using DCount.
Issue:
DCount returns 0 for all Departments.
Findings:
If I manually open table tblEmployee and edit an employees department (I actually named it the same department), DCount will then find 1 person in that department (the record I manually edited). It seems a manually edited record will be counted but anything written by the insert query is ignored.
View 14 Replies
View Related
Sep 24, 2013
In my database when an item is currently being loaned the item's loaned field will have a 1 to indicate this. When the item is returned the value is 0. Only so many items are available and when I use the DCount function it is counting the 0s as 1s.
View 1 Replies
View Related
Mar 6, 2015
I'm trying reference a dcount on two fields, one is a string and works fine, the other is on a date which does not.
I've investigated the problem and found a couple of references to making sure I use a # symbol to reference the date but I think the thing that's throwing the comparison out is that the date field stores time as well, even if you can't see it?
Code:
MsgBox DCount("*", "[healthcheck]", "[check_date]=#" & Format([Forms]![MAINTENANCE_FRM]![MAINTENANCE_DETAIL_TBL subform].[Form]![FIRST_HEALTH]) & "#")
I thought should count the records in the healthcheck table that match the check_date field against the first_health field on the current form, but I keep getting a 0 count even though the dates seem to match.
View 6 Replies
View Related
Sep 11, 2013
My DCount statement is not finding a record that I know is there.
Code:
Private Sub Transfer_WN_Books_Click()
Dim ST As String
ST = "H"
If DCount("*", "Books", Author = " & Me!AuthorID & " And StatFlag = " & ST & ") > 0 Then
DoCmd.Beep
End If
End Sub
It never beeps!
View 10 Replies
View Related
Feb 3, 2014
I can't get the following DCOUNT working for the life of me: DCount("EmployeeName", "tblAttendance", "EmployeeName = '" & cboEmployee Name. Value & "'" And "AttendanceDate = #" & txtAttendanceDate.Value & "#")I have checked this forum along with Google and there seems to be no easy way to do this? I have tried editing the syntax over and over again. I have been able to run a query and get the desired result in that query; however I am unable to call that result in VBA.
View 7 Replies
View Related
Jun 19, 2013
Mycode works great when I use the DCount function, but fails when I change it to DSum which errors with: Error 94 - invalid use of null.
dblCntr = DSum("[intEventCount]", "tblResourceEvents", "[ResourceEventTypeID] = " & myKey & " AND [TimeFrameID] = " & Me.cbo1)
The entire table has valid data - no nulls. myKey and Me.cbo1 both have correct values.
View 2 Replies
View Related
Jan 21, 2014
I have having an issue with determining how to use a customized fiscal year with a DCount function.
My Code:
ThisYear: DCount("*","Open Issues","year([RequestDate]) = " & Year(DateAdd("y",-1,Date())))
The code only does from Jan 1, YYYY TO Dec 31, YYYY
I'd like the code to read from Oct 1, YYYY TO Sept 30, YYYY
View 2 Replies
View Related
Jul 17, 2014
I am trying to check if a record exists, but I keep getting a error!!!
Private Sub Command10_Click()
If DCount("*", "tbl", "[ID] = "") <> 0 Then
MsgBox "This record already exists.
Else
stDocName1 = "McrAddNewRecord"
DoCmd.RunMacro stDocName1
End If
End Sub
View 5 Replies
View Related
Oct 15, 2013
I am trying to put in a control measure which won't allow the user to accidentally add a client to areferral more than once. The simplified relationship structure reads:
tblClient 1-------n tblClientReferral n---------1 tblReferral
I am as a far as using a dlookup which correctly gives the error message, but only if it is the first client added to the tblClientReferral table. If the hypothetical situation arises in which the user adds a client, then adds a second client to the referral and accidentally adds the second clientn twice, the Dlookup is not picking this up.
I suppose the solution is using a dlookup which has the criteria saying if the referralID = 'this' AND the clientID = 'this' Then 'do this'. But as far as I am aware you cannt use AND in the criteria?? atleast, it hasn't worked when I tried. A snippet of the vba code used is below:
Dim objClient As Object
Set objClient = Forms![frmAddClient]![cboClientID]
If (DLookup("clientID", "tblClientReferral", _
"referralID = Forms![frmReferral]![referralID]")) = objClient Then
MsgBox "Client already added"
GoTo ExitSub
Else
View 5 Replies
View Related
Apr 15, 2014
I am having an issue using DCount to validate against 3 fields within my database. I have a booking form which contains a Staff member, viewing slot, and Viewing Date which is used to book property viewings.
I want the form to check that the booking doesn't already exist when the process booking button is pressed.
I am using the following statement:
Code:
If DCount("*", "Viewing", "[Staff_ID]=" & Me.[Staff_ID] & " AND [Viewing_Period] = " & Me.Viewing_Period & "' AND Viewing_Date = '" & Me.Viewing_Date) & "'" > 0 Then
MsgBox "Cannot book, booking already exists", vbCritical
End If
I always get the error "Syntax Error (Missing Operator)".
View 3 Replies
View Related
Jan 3, 2006
I'm having abit of a problem with the DCount function where date fields are involved.
Basically, I have a table - TblMain.
Within that table there is a field -DateRegist. The data type of this field is set to Date / Time - Format short date.
Elsewhere I have set up a form with a text box. The idea being this text box will count the number of 'DateRegist' fields where a date has been entered between two set dates. (There will be four text boxes in total, one for each quarter of the year)
When I use Date() etc in the control their are no problems. However, when I try and use an actual date it all goes pear. I figure it has something to do with it being a date field bit not sure exactly what.
This is what I have so far (don't laugh, I know its miles off the mark but I am trying)
=DCount("[DateRegist]","[TblMain]","[DateRegist]=Between #" & 1/4/2006 & "# AND #'" & 30/6/2006 & "#")
I know I can do four separate queries, each pulling off a quarters data and then draw off the Dcount from each of them but after two days of trying to figure it out I would like to know where I'm currently going wrong - (If you consider doing to four queries will be quicker for the abstraction of the data then let me know but I would still like to know what I'm doing wrong for future reference)
Thanks in anticipation
View 4 Replies
View Related
Dec 8, 2013
I am working with DCount in a query based on a calculated value [MyCalValue] that is a status.
The status can be: Complete, On Going, Past Due, Additional Information, No Date.
In the DCount, I want to return 0 if null.
Sample that works fine
Code:
Green: DCount("[MyCalValue]","Open Issues","[MyCalValue] = 'COMPLETE")
But I want combine values in an OR statement but can't get it to work.
Code:
Green: DCount("[MyCalValue]","Open Issues","[MyCalValue] = 'COMPLETE OR On Going")
Also not working is when I am trying to get a count based on the month like this:
Code:
LastMonth: DCount(Month("[RequestDate]","Open Issues","[RequestDate] = fLastMonth(Now())",0)
View 7 Replies
View Related
Oct 9, 2014
I am getting something wrong but I don't know what - date related.
Take this code for example:
Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=" & varDate) > 0 Then
MsgBox "Go Away", vbOKOnly
Else
Go on and do the real work
End If
End Sub
The DCount line is being completely ignored, no error message, it just cracks on as though there were no records found (but they are definitely there!) I have tried:
Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate='" & varDate & "'") > 0 Then
MsgBox "Go Away", vbOKOnly
But that throws up a Type Mismatch (which is what I would expect). Then I tried
Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=#" & varDate & "#") > 0 Then
MsgBox "Go Away", vbOKOnly
And it gets ignored again.
I am having similar trouble trying to open another form using the same field as the open argument, and getting similar results, so it is clearly me that is wrong.
View 7 Replies
View Related
Apr 14, 2014
I created a query in Query Builder which contains a DCount with multiple parameters and it runs as it should. I am trying to convert it to VBA, but my inability to put in the quotations marks correctly is frustrating me terribly.
Here is the SQL version from Query Builder:
UPDATE [Daily Status Update Table] SET [Daily Status Update Table].NumberOfChases = DCount("[ChaseOtherID5]","[Chases_View_ALL - TX_Mbr 9 Digit]","[ChaseOtherID5] = 'U - Initial Contact' AND [ChaseStatus] = 'A'"), [Daily Status Update Table].ChaseStatus = "A", [Daily Status Update Table].NewStatus = "A", [Daily Status Update Table].ChaseAssignment2 = "Unscheduled"
WHERE ((([Daily Status Update Table].ChaseOtherID5)="U - Initial Contact"));
View 3 Replies
View Related
Jul 19, 2015
The following code is giving me a "Run-Time error '13' Type mismatch. I have tried isolating both criteria and they seem to be fine but joined together with "AND" they error. Workdate is a Shortdate. Flightnumber and flightID are numbers. FlightID source is a cmb within my form.
Private Sub FlightID_BeforeUpdate(Cancel As Integer)
If DCount("[WorkDate]", "Main_tbl", "[WorkDate]= #" & Me.WorkDate & "#" And "[FlightNumber] =" & Me.FlightID.Column(0)) > 0 Then
Do this....
End If
View 1 Replies
View Related
Sep 10, 2013
It is basically a DCount, and it should find records, but returns 0 all the time. My code is:
Code:
Function cntkit(sftd As Date, sftn As String, typid As Integer, specpaint As Boolean) As Integer 'Counts jobs kitted during shift given by sftd and sftn
Dim timeformat As String
timeformat = "#mm/dd/yyyy hh:nn:ss#" 'need this, to convert it to US datetime format
cntkit = DCount("[JOB]", "Archive", "[Type] =" & typid & " And [Autfinish]=False And [SpecPaint] =" & specpaint & " And ([Kit] BETWEEN " & Format(sftstart(sftd, sftn), timeformat) & " AND " & Format(sftstart(sftd, sftn), timeformat) & ")")
End Function
sftstart and sftend are functions which are returning dates. The funcion works fine if I omit the Between part of the criteria. So the problem is in that part.
View 5 Replies
View Related
Dec 3, 2013
I ahve declared custCount as an integer and id as a string that comes from the user form (id = me.CustID) and trying to count the number of matching records in the recordset (rst) using the following:
custCount = DCount("[customerId]", rst, "[customerId] like '" & id & "'")
I'm getting a type mismatch error on the DCount statement?
View 3 Replies
View Related
Jul 9, 2013
I have a main form called "frmMain" and on it I have a text control named "txtBoatStatus". The control source for txtBoatStatus is determined by code that I have in the Form_Current event. The code is as follows:
(begin code sample)
Dim BoatStatus as Variant
BoatStatus = DCount("[BoatComplete]", "tblBoatOrders", "[BoatComplete] = -1 And [OrderPresent] = -1")
txtBoatStatus.Value = "There are " & BoatStatus & " boat orders that are complete."
(end code sample)
Now when I first open the frmMain everything works great and the control will say, "There are 5 boat orders that are complete." Perfect. However, In the instance if I open the form with my Boat Orders and mark off another order as complete (bringing my total to 6), the txtBoatStatus does not update when I reopen frmMain. It will only update and show, "There are 6 boat orders that are complete." if I manually hit SHIFT+F9.
How can I do this automatically? I've tried various things such adding "Me.Recalc" or "Me.Requery" to differnt events, controls, etc. but I still cant get it to show the updated number automatically. How can I get it to update without SHIFT+F9?
View 2 Replies
View Related
Sep 23, 2014
I have a question concerning the automatically sequential numbering of a primary key as part of a composite primary key.
Tbl_treatment:
ID=numeric field (also in tbl_pt and in tbl_tumor)
Tumornr=numeric field (also in tbl_tumor)
Treatmentnr=numeric field
[ID] and [tumornr] are fixed and i would like to automatically number [treatmentnr] per [ID] AND [tumornr] in a way that the output will look like this:
1001 1 1
1001 1 2
1001 1 3
1001 2 1
1001 2 2
1001 3 1, etcetera.
However, when i use the SQL-formula below my output looks like this. It seems as if the function is not properly taking the composite primary key of [ID] AND [tumornr] into account or not finding the true max value:
1001 1 1
1001 1 2
1001 1 3
1001 2 2
1001 2 3
1001 3 3, etcetera.
If (DCount("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) = 0 Then Me.treatmentnr = fRowNum(False)
Else Me.treatmentnr = (DMax("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) + 1
View 3 Replies
View Related
