Unable to perform dcount (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 18:42
Joined
Dec 26, 2011
Messages
672
HI! all

I am trying to do validate a field on form and subform with a table field.

Below is my code i am not sure what i am doing wrong
Code:
Option Compare Database

Private Sub chqbrcd_AfterUpdate()
    Me.creditAccount_ac.SetFocus
    DoCmd.GoToRecord , , acNewRec

End Sub

Private Sub creditAccount_ac_BeforeUpdate(Cancel As Integer)
    Dim SID As String
    Dim stlinkcriteria As String
    Set rsc = Me.RecordsetClone
    Dim a As String
    Dim b As String
        a = Me.creditAccount_ac.Value
'        b = Left(a, 2)
'    Debug.Print c
'    If b <> "EN" Then
'    Me.Undo
'    MsgBox "EN is not the first character! Kindly check  " & b
'    End If
    Set rsc = Nothing
    
    stlinkcrieria = "[creditAccount_ac]" & "'" & SID & "'"
    'Check tbl_Masterchqbrcd for duplicate barcode
    If DCount("*", "[tbl_DailyData]", "[CreditAC] ='" & creditAccount_ac & "' and [duedate_ac] = " & SQLDate(dueDate)) = 0 Then
        Me.Undo
        MsgBox "Warning Cheque Barcode " _
            & SID & " has already been Scanned, " _
            & vbCr & vbCr & "Kindly check previous record and Re-scan correct Barcode,", vbInformation _
            , "Duplicate Barcode Information"
    End If
    Set rsc = Nothing
End Sub

Function SQLDate(dt As Date) As String
On Error Resume Next
    If IsDate(dt) Then SQLDate = Format(dt, "\#dd-mmm-yyyy#")
End Function

any suggestions pls
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:42
Joined
Aug 30, 2003
Messages
36,118
You haven't said what the issue is. Do you get an error? Wrong result? If duedate_ac is a date/time field, it needs # as a delimiter.
 

lookforsmt

Registered User.
Local time
Today, 18:42
Joined
Dec 26, 2011
Messages
672
Thanks for the reply and sorry, i had go away from my laptop for a while

actually i want to validate the creditAccount_ac against CreditAC in tbl_DailyData, when i input chqbrcd.
its hard to explain unless i upload my db
 

lookforsmt

Registered User.
Local time
Today, 18:42
Joined
Dec 26, 2011
Messages
672
i have uploaded the error snapshot

"ByRef Argument type mismatch"
 

Attachments

  • Byref typ mismatch.jpg
    Byref typ mismatch.jpg
    99.1 KB · Views: 188

isladogs

MVP / VIP
Local time
Today, 14:42
Joined
Jan 14, 2017
Messages
18,186
I assume dueDate is a date/time field

The date needs to be in the format mm/dd/yyyy or yyyy-mm-dd

Suggest you scrap your SQLDate function as its superfluous ... unless you will use it a lot
Then change your DCount function to:
Code:
DCount("*", "[tbl_Daily_Data]", "[CreditAC] = '" & creditAccount_ac & "' And [dueDate_ac] = #" & Format(dueDate,"mm/dd/yyyy") & "#")

Also STRONGLY recommend you have Option Explicit as the 2nd line in each code module.
To do this automatically for all new code modules, tick Require Variable Declaration in VBE Options.
This will prevent errors due to variables not being properly defined
 
Last edited:

lookforsmt

Registered User.
Local time
Today, 18:42
Joined
Dec 26, 2011
Messages
672
Dear Ridders,

i have modified the code with your suggested dcount code, but i am still gettting an error.

I am updating my db for better understanding.
 

isladogs

MVP / VIP
Local time
Today, 14:42
Joined
Jan 14, 2017
Messages
18,186
What error do you get?
Should it be tbl_DailyData rather than what I wrote.
I was copying from your screenshot as I forgot you had posted the code before
 

lookforsmt

Registered User.
Local time
Today, 18:42
Joined
Dec 26, 2011
Messages
672
here is the db
 

Attachments

  • FieldValidation_1.accdb
    520 KB · Views: 184

isladogs

MVP / VIP
Local time
Today, 14:42
Joined
Jan 14, 2017
Messages
18,186
You forgot to add '= 0 Then' at the end of the DCount line
 

lookforsmt

Registered User.
Local time
Today, 18:42
Joined
Dec 26, 2011
Messages
672
Thanks,

i wanted guidance on how to go about. Sorry did not mention at the start of the thread.

i have data captured by one user in tbl_DailyData as mentioned below which is downloaded from my core system.
PHP:
SrNo	dueDate		CreditAC	                chqbrcd
1	12/06/2018	1011234567801	EN-120618-001
2	12/06/2018	1013458693445	EN-120618-002
3	12/06/2018	1012340977619	EN-120618-003
4	12/06/2018	1012340977645	EN-120618-004
5	12/06/2018	1011248875445	EN-120618-005

i want to validate the same data by another user to capture in another table (temp_tblDailyData) like 4 eye concept to identify if any wrong data is captured.

The user captures correct data which validates below 3 fields and also does not allow the user to capture the same data more than once.
PHP:
- duedate
- chqbrcd
- CreditAC

can you let me know whether the attached db is the right approach

Thanks
 

isladogs

MVP / VIP
Local time
Today, 14:42
Joined
Jan 14, 2017
Messages
18,186
First of all is the DLookup working for you now?

I won't have time to study your database today bot hopefully someone else will.
Never heard the phrase '4 eye concept' before. Do you just mean 'to look at'?
 

lookforsmt

Registered User.
Local time
Today, 18:42
Joined
Dec 26, 2011
Messages
672
Dlook up is not working based on the 3 fields

4 eye phrase on a high level, one user process and then second user types exactly the same thing, if it gets match the kind of move to next item, else whichever is incorrect has to rectify.
 

isladogs

MVP / VIP
Local time
Today, 14:42
Joined
Jan 14, 2017
Messages
18,186
Dlook up is not working based on the 3 fields

4 eye phrase on a high level, one user process and then second user types exactly the same thing, if it gets match the kind of move to next item, else whichever is incorrect has to rectify.

You only had 2 fields in your filter criteria.

The 4 eye bit sounds like two step verification to me
 

lookforsmt

Registered User.
Local time
Today, 18:42
Joined
Dec 26, 2011
Messages
672
when i say 4 eye its the users (user A does input of 3 fields and like wise user B does the same) not the number of fields
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:42
Joined
Aug 30, 2003
Messages
36,118
I'm thinking this type of thing, though you'd need to make the chgbrcd field visible:

Code:
If DCount("*", "[tbl_DailyData]", "[CreditAC] = '" & creditAccount_ac & "' And [dueDate] = #" & Format(Me.checkDate_ac, "mm/dd/yyyy") & "# AND chqbrcd = '" & Me.chqbrcd & "'") > 0 Then
 

Cronk

Registered User.
Local time
Tomorrow, 01:42
Joined
Jul 4, 2013
Messages
2,770
Rather than duplicating data into a temporary table, add the fields holding the check date and the user to the main data table.


If you want, hide the checking fields for the user inputting data and make them visible for the second set of eyes checking the data.


You'll need to have all table fields on the form, especially the primary key field.


At the moment, it is not possible to add data because your validity checking code with the Dcount includes the field DueDate, which is not on the form.


Incidentally, you do not need the sub form. The unbound controls at the top could be put in the header section of a continuous form.
 

lookforsmt

Registered User.
Local time
Today, 18:42
Joined
Dec 26, 2011
Messages
672
Thanks Cronk for the suggestions. Let me clarify those in line with that:

PHP:
Rather than duplicating data into a temporary table, add the fields holding the check date and the user to the main data table.

If you want, hide the checking fields for the user inputting data and make them visible for the second set of eyes checking the data.

You'll need to have all table fields on the form, especially the primary key field.

Temp table was created to avoid db performance, first the daily volumes goes more than 1000 records with overall total of more than 1.5K in the given table. Secondly there would be these same fields available in the main table. Once the user inputs in the temp table, it would be inserted in the main table by using update query.

PHP:
At the moment, it is not possible to add data because your validity checking code with the Dcount includes the field DueDate, which is not on the form.

Incidentally, you do not need the sub form. The unbound controls at the top could be put in the header section of a continuous form.

i can create a duedate date field in the temp table for the user so he can input before capturing the data.

if the above approach can be adopted?
 

lookforsmt

Registered User.
Local time
Today, 18:42
Joined
Dec 26, 2011
Messages
672
Dear pbaldy,

i have put the dcount code, but because i have kept unbound fields on my main form, i guess it is a bit inconsistency. currently the combination is: dueDate + chqbrcd + crediAccount_ac

if you can let me know if have to exclude the dueDate and keep only chqbrcd + creditAccount_ac. How can i write this dcount code
 

Users who are viewing this thread

Top Bottom