Unable to perform dcount

lookforsmt · Jun 14, 2018

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 · Jun 14, 2018

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.

pbaldy · Jun 14, 2018

Oh, just noticed your function. This may be relevant:

http://allenbrowne.com/ser-36.html#Format

lookforsmt · Jun 14, 2018

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 · Jun 14, 2018

i have uploaded the error snapshot

"ByRef Argument type mismatch"

isladogs · Jun 14, 2018

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

lookforsmt · Jun 14, 2018

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 · Jun 14, 2018

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 · Jun 14, 2018

here is the db

isladogs · Jun 14, 2018

You forgot to add '= 0 Then' at the end of the DCount line

lookforsmt · Jun 15, 2018

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 · Jun 15, 2018

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 · Jun 15, 2018

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 · Jun 15, 2018

lookforsmt said:
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 · Jun 15, 2018

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

lookforsmt · Jun 15, 2018

yes you are right its 2 step verification.

pbaldy · Jun 15, 2018

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 · Jun 15, 2018

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 · Jun 16, 2018

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 · Jun 16, 2018

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

Unable to perform dcount

Registered User.

Wino Moderator

Wino Moderator

Registered User.

Registered User.

Attachments

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Attachments

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Registered User.

Wino Moderator

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread