UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Need Help With Dcount() Syntax, Access 2013    
 
   
smlogan
post Sep 22 2017, 01:10 PM
Post#1



Posts: 58
Joined: 21-September 17



BruceM I read the link from this thread and kinda understand what it told me. I always declare my variables anyway (Dim something as something) so although it is possibly an extra step I can see the benefits...and its simple and takes seconds As with the me ...if its from the calling form its easy, though I'm not sure there is a benefit but again its easy. What I'm still struggling with is the use of []. Even today I had issues using that syntax,,, which I do whenever a field is referenced. Using the brackets resulted in no defined value but quotation marks alone did. I guess I will learn thru practice. Trust me before I come here to ask for help I try every combination on my own . Reason is if I learn to do it myself I will remember how, if somebody tells me how I have to remember to lookup their suggestion. TY so much for extending my learning
Go to the top of the page
 
smlogan
post Sep 22 2017, 01:13 PM
Post#2



Posts: 58
Joined: 21-September 17



And I understand what you are describing with the sytax but really a wizard to take the entries and do the syntax cant be that difficult....or maybe it is lol
Go to the top of the page
 
smlogan
post Sep 25 2017, 12:11 PM
Post#3



Posts: 58
Joined: 21-September 17



OK I'm back with the same problem sorta. Seems I'm still not understanding the syntax. I got the other DLookup examples all working then I decided to confuse myself with multiple criteria on a Dsum which is basically the same. I know its syntax with the apostrophes but I have read and tried just about every combination.

Dim strname As String
strname = [ffpoinv#]
MsgBox [ffpoinv#]
Dim FoundOrNot As Long
FoundOrNot = DCount("ID", "balancesheet", "[ffpoinv#] = " & Chr(34) & strname & Chr(34))
MsgBox [FoundOrNot]

This works and returns the correct values but when I try and add a numeric variable to the query I get lost

FoundOrNot = DCount("ID", "balancesheet", "[ffsupplier] = [me.ffsupplier] And "[ffpoinv#] = " & Chr(34) & strname & Chr(34))

What am I doing wrong. I have used the single criteria both text and numeric many places after your explanation but I'm kinda stumped here.
Table for Dcount is balancesheet with numeric ffsupplier and text ffpoinv form is "balancesheet query" with the same fields. Effectively I'm trying to check if the same supplier has a duplicate invoice in the system when adding a record
Go to the top of the page
 
theDBguy
post Sep 25 2017, 12:26 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,202
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif
The criteria section can get confusing when dealing with a mix of number and text fields. To better understand what is happening, you could try it this way:

CODE
Dim strCriteria As String
strCriteria = "[ffsupplier]=" & Me.ffsupplier & " And [ffpoinv#]='" & Me.[ffpoinv#] & "'"
MsgBox strCriteria
If DCount("*", "balancesheet", strCriteria) > 0 Then
    'duplicate
Else
    'not duplicate
End If

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
smlogan
post Sep 25 2017, 03:08 PM
Post#5



Posts: 58
Joined: 21-September 17



Ok so I tried it

Dim strCriteria As String
strCriteria = "[ffsupplier]=" & Me.ffsupplier & " And [ffpoinv#]='" & Me.[ffpoinv#] & "'"
MsgBox strCriteria
If DCount("*", "balancesheet", strCriteria) > 0 Then
MsgBox "duplicate"
Else
MsgBox "notduplicate"
End If
It recognizes the duplicate in the invoice number but does not recognize that they are from different suppliers when I force the table to have duplicates. This is the same problem I was having, getting it to recognize the duplicate invoice number was easy but a different supplier number was not recognized. Help
Go to the top of the page
 
theDBguy
post Sep 25 2017, 03:25 PM
Post#6


Access Wiki and Forums Moderator
Posts: 71,202
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Are you able to post a small copy of your db with test data?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
smlogan
post Sep 25 2017, 03:55 PM
Post#7



Posts: 58
Joined: 21-September 17



how do I upload a db I created with just the form and the table
Attached File(s)
Attached File  dbase.zip ( 86.63K )Number of downloads: 5
Attached File  dbase.zip ( 86.63K )Number of downloads: 0
 
Go to the top of the page
 
smlogan
post Sep 25 2017, 03:55 PM
Post#8



Posts: 58
Joined: 21-September 17



apparently I did
Go to the top of the page
 
theDBguy
post Sep 25 2017, 03:59 PM
Post#9


Access Wiki and Forums Moderator
Posts: 71,202
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Thanks for uploading your database. I think maybe part of your problem is you're using lookup fields at the table level. However, I can't verify this because the file you posted is missing the "balancesheet Query," to which your form is bound.

Also, I don't see a command button on your form. Does this mean you were using the code to check for duplicates in the form's BeforeUpdate event? If not, this could also explain why duplicates are being created.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
smlogan
post Sep 25 2017, 05:07 PM
Post#10



Posts: 58
Joined: 21-September 17



I was using a lost focus as the triggger
Go to the top of the page
 
smlogan
post Sep 25 2017, 05:09 PM
Post#11



Posts: 58
Joined: 21-September 17



balancesheet query is a straight query of balance sheet
Go to the top of the page
 
theDBguy
post Sep 25 2017, 05:24 PM
Post#12


Access Wiki and Forums Moderator
Posts: 71,202
Joined: 19-June 07
From: SunnySandyEggo


Re: "I was using a lost focus as the trigger"

Yes, that was the problem. Try the following modified version of your db, which uses the form's BeforeUpdate event.

Hope it helps...
Attached File(s)
Attached File  Database4.zip ( 29.25K )Number of downloads: 3
 

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
smlogan
post Sep 26 2017, 06:33 AM
Post#13



Posts: 58
Joined: 21-September 17



Ok that works but why only in beforeform update. I wanted to trap the duplicate immediately and set the focus back to [ffpoinv#] and the user user would have to change it before he could move on(he would be stuck in a loop till corrected) While its easy to remember what record you created last, if he edits a previous record I would have to create code to find the record based on the same dcount ,which will find 2 records the same, set the focus to that record, then to the next, and I have to hope he edits the correct one. Is there any way I can trap the duplicate on the lost focus to prevent the duplicate immediately (Even if I have to refresh). Alternatively I suppose I could create a pop up window showing both records and he could choose which to edit but it seems complicated to achieve the same thing
Go to the top of the page
 
smlogan
post Sep 26 2017, 08:00 AM
Post#14



Posts: 58
Joined: 21-September 17



Cancel last I put the following in lost focus of ffpoinv#

If Not IsNull(Me.ffsupplier) Then
If DCount("*", "balancesheet", "ffsupplier=" & Me.ffsupplier & " AND [ffpoinv#]='" & Me.[ffpoinv#] & "'") > 0 Then
MsgBox "Duplicate invoice."
Me.[ffpoinv#].SetFocus
End If
End If

And the corresponding code in ffsupplier and its working great
Go to the top of the page
 
smlogan
post Sep 26 2017, 09:35 AM
Post#15



Posts: 58
Joined: 21-September 17



Ok I spoke too soon now its finding duplicates that aren't there attached db the balance sheet is still not working for duplicate invoices
Attached File(s)
Attached File  Database51.zip ( 53.44K )Number of downloads: 2
 
Go to the top of the page
 
theDBguy
post Sep 26 2017, 09:46 AM
Post#16


Access Wiki and Forums Moderator
Posts: 71,202
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I just saw your posts. I haven't looked at your latest upload but to answer your question about the user being able to modify an existing invoice to create a duplicate, I'd say try it out on the demo I posted. Open the form, modify an existing record to create a duplicate and see if it will let you.

I'll let you know what I find out with your latest upload after I take a look at it.

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
smlogan
post Sep 26 2017, 10:18 AM
Post#17



Posts: 58
Joined: 21-September 17



It did not let me but it didn't return where the error was and was unreliable sometimes finding duplicates that were not there . understanding that as work is processed multiple invoices may be entered as part of the process of adding a po ie: i get several different parts invoices on a job I will only enter them when I go to close the worder. ty for your help thus far. This should be easy but apparently not. I got pretty good using FoxPro many years ago and understood the steps but you had to do the steps one by one and it took a lot of coding, but it worked. VBA is faster but the syntax is killing me. Goes to show you how old I am lol
Go to the top of the page
 
theDBguy
post Sep 26 2017, 11:05 AM
Post#18


Access Wiki and Forums Moderator
Posts: 71,202
Joined: 19-June 07
From: SunnySandyEggo


"It did not let me..."

I think that's good. It's what we want.

"...but it didn't return where the error was..."

That's because we haven't told the code to do so. You should be able to modify the code to flag the error and return the user there.

"...and was unreliable sometimes finding duplicates that were not there ."

You may have to explain this one a bit more. I am not sure I understand what you mean by "finding duplicates that were not there."

Your criteria was to check if the same supplier and po already exist in the table. Are you saying you're entering a different supplier or po but the code is saying it's already in the table?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
smlogan
post Sep 26 2017, 11:34 AM
Post#19



Posts: 58
Joined: 21-September 17



using more code to search for the error was what I was hoping to avoid. As far as unreliable try the latest db I sent you (its more complete than last time) you will see that simply exiting the screen or adding a line or changing an existing value will find a duplicate that's not there. Getting frustrated lol, maybe Ill remove the error checking altogether and let my accountant scream at me if he finds it lol And yes to "Your criteria was to check if the same supplier and po already exist in the table Are you saying you're entering a different supplier or po but the code is saying it's already in the table?".
Go to the top of the page
 
smlogan
post Sep 26 2017, 12:10 PM
Post#20



Posts: 58
Joined: 21-September 17



If it helps I'm building a program to run my motorcycle repair shop paperwork. Completed and working to date are invoice and estimate creating and printing, maintaining and accessing supplier, client, vehicle, and lookup tables. Right now I'm working on balancesheet which will trap credits off closed invoices and expenditures (PO's) and produce a table I can query at tax time to submit to my accountant with a report (date to date). Later to do is link an inventory table to use stuff I already own but that is low priority as I've built myself as a supplier and can charge it out that way. I'm 61 so learning new tricks is hard but I enjoy the challenge and find it keeps the brain synapsis working lol
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 09:38 PM