help with DCount

[lookforsmt]

Hi! I have DB with two tables:
tbl_linkchqbrcd
tbl_Masterchqbrcd

There is a form attached to tbl_linkchqbrcd
frm_MICR

When I scan chqbrcd it should look into table tbl_Masterchqbrcd if the record (chqbrcd) exist before accepting the scanned item, then accept else a pop up msg with the scanned details that it does not exist or duplicate barcode and field will be empty for scanning the next item

Below is the code currently works fine for DCount on tbl_linkchqbrcd but when I changed the table to another table tbl_Masterchqbrcd it does not work. Pls let me know what I am doing wrong or does it require any changes

Pls note this is taken from one of the forum and I have added it to my DB. I am completely new to vba codes

Code:
====

Option Compare Database
Private Sub chqbrcd_BeforeUpdate(Cancel As Integer)
Dim SID As String
Set rsc = Me.RecordsetClone
SID = Me.chqbrcd.value
stLinkCriteria = "[chqbrcd]=" & " ' " & SID & " ' "
'Check tbl_Masterchqbrcd for duplicate barcode
If DCount ("chqbrcd",tbl_Masterchqbrcd", 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

I also have another code on the chqbrcd afterupdate
=================================

Private Sub chqbrcd_AfterUpdate()
If Len(Me.chqbrcd.Text) = 14 Then MICR_Code.SetFocus
Me.chqbrcdDate = Now()
Me.chqbrcdUpdateby = fOSUSERName

End Sub

There is also code KeyPress on chqbrcd
=========================

Private Sub chqbrcd_KeyPress(KeyAscii as Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))

End Sub

 

[plog]

Does tbl_Masterchqbrcd have a field called chqbrcd?

 

[missinglinq]

Not sure about

stLinkCriteria = "[chqbrcd]=" & " ' " & SID & " ' "

It may work but really should, I think, be

stLinkCriteria = "[chqbrcd]= '" & SID & "'"

Then you've got a space between DCount and ("chqbrcd",tbl_Masterchqbrcd", stLinkCriteria) that shouldn't be there, and you're definitely missing a Double Quotation mark in the Domain parameter...so it should be:

If DCount("chqbrcd",[B][COLOR="Red"]"[/COLOR][/B]tbl_Masterchqbrcd", stLinkCriteria) > 0 Then

Linq ;0)>

 

[lookforsmt]

Yes tbl_masterchqbrcd has a field chqbrcd. And also tbl_linkchqbrcd has field with similar name. Is that the reason for code not to work as required.

I will try the response given by missingling and provide my feedback later during the day.

 

[Cronk]

The issue is probably the extra spaces in the string
stLinkCriteria = "[chqbrcd]=" & " ' " & SID & " ' "

If SID has the value "abc" then the criteria is
[chqbrcd]= ' abc ' (string length 5)
not
[chqbrcd]= 'abc' (string length 3)

 

[lookforsmt]

i have tried all the possible combinations mentioned but it still does not recognize the duplicate item in tbl_Masterchqbrcd and proceeds to the next field.

 

[JHB]

Post your database with some sample data, zip it, or show some sample data in an Excel sheet or database.

 

[lookforsmt]

Hi! all

I tried the below and it worked now. I don't know what happen earlier.

stLinkCriteria = "[chqbrcd]=" & " ' " & SID & " ' "

I want to thank everyone for their support and help. I will mark this as closed for me.

 

[lookforsmt]

hi! friends
sorry had to send this post again, I have a little problem in the Dcount.

my code is able to check if the particular "chqbrcd" is present in "tbl_Masterchqbrcd" this is working fine.

Now I have a challenge where user are inputting the "chqbrcd" but the code should also check for the "duedate" but in the same row and not in the entire column.

If DCount("chqbrcd","tbl_Masterchqbrcd", stLinkCriteria) > 0 Then

how can I achieve this. if anyone has a answer to my problem. Pls help

 

[plog]

Please demonstrate your issue with data. Give us a bunch of records from tbl_Masterchqbrcd (include field names), tell us what you the user submitted and then show us what the DCount should return.

 

[JHB]

Maybe the below:

stLinkCriteria = "[chqbrcd]=" & " ' " & SID & " '  And [duedate]=" & TheDateToCheckFor