DCount Help (1 Viewer)

[lookforsmt]

HI! all

Request your help on DCount. I have attached my db for better clarification

Hi! I have attached my db:

I want to achieve the below 1 & 2:

1) When i open the form "frm_scan_chqbrcd" to do the below task:

a) When input date in the field "capture_date" on the frm_scan_chqbrcd" it should validate with the chq_brcd_c(Which is EN-ddmmyy-0001),
where ddmmyy is the capture date and the format is "dd/mm/yyyy" and only accept if it is valid else it should popup msg as "wrong date selected"

b) Before I scan chq barcode in field "chq_brcd_c" it should check in the table, "tbl_Master_chqbrcd" and validate the field "chq_brcd" is available in the entire COLUMN,
and only accept if it is valid, else it should popup msg as "chq_brcd_c" is not valid code before it moves to the next field "MICR"

c) Similarly when I input MICR it should check in another table, "tbl_Master" and validate the field "MICR" in tbl_Master and except if it is validated,
else it should popup msg as "MICR" is not valid MICR code and allow user to check the re-typed the code.

Below is the data which will be inputted in "frm_scan_chqbrcd"
chq_brcd_c Capture_date_c MICR
EN-040117-0001 04/01/2017 6286858020201257100540010040117
EN-040117-0002 04/01/2017 7831450022201010011430158040117
EN-040117-0003 04/01/2017 0001357033201170793177758040117
EN-040117-0004 04/01/2017 0001557033201170793177758040117

On the frm_scan_chqbrcd, there is button when clicked will run the query "qry_update_chq_env" it will update the table "tbl_Master before matching the MICR in that particular ROW.

2) When I open the form "frm_scan_envbrcd" to do the below task:
a) Will search for envbrcd and go the attached subform, "subfrm_temp_envbrcd_e" field "chq_brcd_e" to capture the chq barcode. The system should validate the below before accepting the record.

i) validate chq_brcd_e is matching with tbl_Master_chqbrcd field "chqbrcd" and If it is matching then go do the second field and
ii) validate due_date_e is matching with tbl_Master_chqbrcd field "duedate". If not popup msg, "Invalid chq barcode mis-match with chq duedate"

b) when I scan the envbrcd (envelope barcode, format: E-ddmmyy-1) it should check for the envdate (envelope date) which is the "due_date" and the format is "dd/mm/yyyy", before accepting the barcode.
If the date is not matching, it should stop the user and pop msg, ""Invalid date does not match with the envdate"

a) Before I scan envelope barcode in field "env_brcd_c" it should check in the table, "tbl_Master_envbrcd" and validate the field "chq_brcd" is available in the entire column.
Similarly when i input MICR it should check in another table, "tbl_Master" and validate the field "MICR" in tbl_Master.

Below is the data which inputted in "frm_scan_envbrcd"
chq_brcd_e Due_date_e
EN-040117-0001 11/01/2017
EN-040117-0002 12/01/2017
EN-040117-0003 13/01/2017
EN-040117-0004 15/01/2017

Thanks

 

[sneuberg]

It's looks like your post is being ignored and you'll have to so this yourself. Here's some reading material that might help.

https://support.office.com/en-us/article/DCount-Function-f6b5d78b-ad0b-4e42-be7a-11a64acbf3d3
https://www.techonthenet.com/access/functions/domain/dcount.php
http://www.iaccessworld.com/use-dcount-function/

Video

https://www.youtube.com/watch?v=9JTgR7ojwuY

I think posts that ask for help with a problem a person is having with something they have tried get better responses. Posts that look like a work assignment from a superior seem to get ignored.

 

[lookforsmt]

Thanks Steve for your feedback,

I did post for help earlier in the thread " Help with DCount" and I got clarification but that was not enough for me to solve the issue. So opened another thread and posted the full problem.

I was not in anyway imposing my problem on anyother head. Just looking for help. Now that you have given me some reading material, I will check and come back with my questions.
hope no one ignores me that time

have a nice day
Thanks

 

[Minty]

As per Sneuberg, I think your posted is very informative, but is asking way too many questions in one go.

Look at each point you have listed and make them individual tasks - when you get stuck on one, post up what you have tried for that particular problem.

 

[lookforsmt]

HI!

I have done the below code and working fine for checking the "chq_brcd_e" on form "frm_scan_envbrcd"
Code:

Private Sub chq_brcd_e_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stlinkcriteria As String
Set rsc = Me.RecordsetClone
SID = Me.chq_brcd_e.Value
stlinkcrieria = "[chq_brcd_e]" & "'" & SID & "'"
'Check tbl_Masterchqbrcd for duplicate barcode
If DCount("chq_brcd_e", "tbl_Master", stlinkcriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
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

The problem is I also want to check another field "due_date_e" on form "frm_scan_envbrcd" with table "tbl_Master" field "DueDate"
It should check in the same row and not in the column

I hope I am not putting my problem on you guys and just seeking help to solve this. Hope any help is welcomed
Thanks

 

[sneuberg]

Maybe

If DCount("chq_brcd_e", "tbl_Master", stlinkcriteria) > 0 Or _
    DCount("due_date_e", "tbl_Master", stlinkcriteria) > 0 Then
...

is what you want.


PS: When you post code please put it in code tags, i.e., click the Advance button, select the code, and click the pound sign(#)

 

[lookforsmt]

Hi! Thanks for the code, but it still gives me error popup msg that it is duplicate barcode and it does update the record.

when I scan the chq_brcd_e in the form it should check the chqbrcd in table tbl_Master if it exist which in this case is correct and then check for the field duedate in table tbl_Master is matching with the field due_date_e in form frm_scan_envbrcd

 

[lookforsmt]

sorry read in the first line as " it does Not update the record"

 

[sneuberg]

Oh, in that case I think the criteria needs to be different. Maybe

If DCount("chq_brcd_e", "tbl_Master", stlinkcriteria) > 0 [COLOR="Blue"]Or[/COLOR] _
    DCount("*", "tbl_Master", "[due_date_e] = #" & Me.due_date_e & "#") > 0 Then
...
End Sub

This assumes the field in the table and the form both have the name "due_date_e" and this field has the date type. Then this code would give you the error message and not do the update if either the chq_brcd or due_date_e where in the table. If this is suppose to only stop the update when both are in the table then change the Or to And.

 

[lookforsmt]

HI! I am still not able to get the desired result.

I have tried "And" instead of "Or" but it still without any success

kindly note tbl_Master has fields "chqbrcd" & "DueDate" whereas form which is linked to tbl_envbrcd has fields "chq_brcd_e" & "due_date_e"

It is accepting this barcode EN-040117-0001 on form "frm_scan_envbrcd" for any date but I want it to accept only for date 11/01/2017 as this is the date mentioned in tbl_Master in field "duedate" any other date will be incorrect.

I have attached my db (MICRv1.4) with your code provided. Can you help me to complete this pls.

 

[static]

Private Sub chq_brcd_e_BeforeUpdate(Cancel As Integer)

    SQL = "select 1 from tbl_Master where [chqbrcd] ='" & chq_brcd_e & "' and [duedate] = " & SQLDate(Due_date_e)
    Debug.Print SQL
    
    If Not CurrentDb.OpenRecordset(SQL).EOF 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

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

 

[sneuberg]

@static Wouldn't the DCount in this

Private Sub chq_brcd_e_BeforeUpdate(Cancel As Integer)

    If DCount("*", "[tbl_Master]", "[chqbrcd] ='" & chq_brcd_e & "' and [duedate] = " & SQLDate(Due_date_e)) > 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

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

produce the same results as your code. Is there some reason why you avoided DCount in your code?

 

[static]

No specific reason. Except since the syntax is the same as SQL why not just use SQL?
DWhatever() functions seem kind of pointless. Also considering that if you need to resort to using them in your forms or recordsets, your tables are probably badly designed.

 

[lookforsmt]

Hi! all

I have applied static code, its kind of working but it does not stop if the due date is different. Not sure how to amend the code.

whereas for sneuberg code, I have amended the code by changing the > sign to = sign to take only the specified duedate and it works flawlessly.

I want to thank you both for the support given to help m solve this issue.

Now I can focus on the next step.
Will close this thread once I am able to solve the next issue.

Hope you guys will be here to help me.

 

[lookforsmt]

hi!

I am now trying to run the insert query in the on-click event. The system is off, so the pop-msg prompt is built in the code, but it does not give the nos of records updated. It gives a zero count. I am not sure how to make the actual count show in the pop msg.
Any suggestions in the below code.

Private Sub cmd_Updatecbqbrcd_Click()
  Application.Echo False
  Dim strSQl As String
  Dim a As Long
  DoCmd.SetWarnings False
  On Err GoTo Park
  strSQl = "UPDATE (temp_chqbrcd " & vbNewLine
  strSQl = strSQl & "  INNER JOIN tbl_Master " & vbNewLine
  strSQl = strSQl & "          ON (temp_chqbrcd.MICR = tbl_Master.MICR) " & vbNewLine
  strSQl = strSQl & "         AND (temp_chqbrcd.Capture_date_c = tbl_Master.CaptureDate)) " & vbNewLine
  strSQl = strSQl & "  INNER JOIN tbl_CaptureDate " & vbNewLine
  strSQl = strSQl & "          ON tbl_Master.CaptureDate = tbl_CaptureDate.Capture_date SET tbl_Master.chqbrcd = [temp_chqbrcd].[chq_brcd_c]" & vbNewLine
  strSQl = strSQl & "       WHERE (((tbl_Master.chqbrcd) Is Null))"
  
  DoCmd.RunSQL strSQl
  
  MsgBox a & " " & "chq_brcd records updated to  tbl_Master"
    Application.Echo True
    DoCmd.SetWarnings True
Park:
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If
End Sub

thanks

 

[sneuberg]

I suggest leaving the query as a querydef rather that putting this SQL string in code. Let's say the name of this query was "qryUpdateMaster". You could get the number of records updated by the records affected property of the querydef. Then this code might be what you want.

Private Sub cmd_Updatecbqbrcd_Click()
  On Err GoTo Park
  
Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.QueryDefs("qryUpdateMaster")
qdef.Execute
MsgBox qdef.RecordsAffected & " " & "chq_brcd records updated to  tbl_Master"
Set qdef = Nothing

Park:
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If
End Sub

 

[lookforsmt]

Thanks Steve

that worked for me, how can add another delete query just after this action in the same code.
Query name is "qry_update_chq_brcd" and giving me the same msg of deleting records from the table.

Just curious, whether query is faster or vba code is faster. Pardon my question if it does not make sense

 

[sneuberg]

that worked for me, how can add another delete query just after this action in the same code.
Query name is "qry_update_chq_brcd" and giving me the same msg of deleting records from the table.

If you want to do the same this with a delete query let's say "qry_delete_chq_brcd" I think the same type of code would work, e.g,

Set qdef = CurrentDb.QueryDefs("qry_delete_chq_brcd")
qdef.Execute
MsgBox qdef.RecordsAffected & " " & "temp chq_brcd records deleted"
Set qdef = Nothing

Just curious, whether query is faster or vba code is faster. Pardon my question if it does not make sense

They (some the AWF members) say that its faster using the querydef as it is precompiled but I doubt if you would notice the difference. I think it's better to keep it as a querydef just because it's easier to maintain.

 

[lookforsmt]

Thanks Steve, I have added the delete code in the same code and one click it updates tbl_master and deleted records from the temp table.

Thanks once again.
As is said I am doing a project so I am trying to fix one problem at a time.

 

[lookforsmt]

Hi! Steve, I have a slight problem. When I click the button the code behind the button updates the data, but for some reason the data is incomplete so it will not update the full records, but the next code, that is delete the temp table will delete the entire data which is issue.

Is there a way that it can only delete those records which are added.

If not then can a Yes or No msgbox prompt to either accept or cancel the delete event only. This way atleast I will avoid all the data being deleted.
I have the code of Yes-No which I put in the exiting code and it prompts for yes or no, but still deletes the record.

Park:
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If

    Set qdef = CurrentDb.QueryDefs("qry_delete_chq_brcd")
    qdef.Execute
    
Const cstrPrompt As String = _
        "Are you sure you want to Delete this record? Yes/No"
    If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
        If Me.Dirty Then
            Me.Dirty = False ' save the record
        End If
        End If
    Set qdef = Nothing

End Sub

if I am placing the code in the right place