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    
 
   
theDBguy
post Sep 26 2017, 12:36 PM
Post#21


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


Hi,

Okay, I moved your code from LostFocus to BeforeUpdate. See if it makes any difference.
Attached File(s)
Attached File  Database51.zip ( 40.57K )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, 01:34 PM
Post#22



Posts: 58
Joined: 21-September 17



Ok so it seems to work although if its a duplicate I get windows error of "The value violates the validation rule for field or record". Why? also what made the difference and why? on the focus or update. Gotta learn here or I gotta keep getting spoonfed
Edit: not working if I change supplier on previous entry
Go to the top of the page
 
theDBguy
post Sep 26 2017, 02:01 PM
Post#23


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


Hi,

Data validation is best performed in the BeforeUpdate event because this event can be cancelled, preventing the record from saving invalid data into the table.

I think the "duplicate" you're getting when you update previous records is inherent to the use of DCount() against the table. When you are modifying the data on the form, the changes are not yet reflected in the table (it only reflects after the record is saved). So, although you may be seeing a different information on one of the boxes, it actually has a different value in the table. This is an issue with your requirement of comparing more than one field. If you were only avoiding duplicates for one field, the DCoutn() approach would probably catch all of them. But for a two-field criteria, when you change the first one and then change the second one, the last check still sees the original value from the first field in the table.

As for the "The value violates..." This is a setting you probably created at the table level. You can either remove it, or we can try to trap it.

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 26 2017, 02:13 PM
Post#24



Posts: 58
Joined: 21-September 17



Ok how to I provide a refresh on the field from code so I see an actual of what's in the table.
As for the "The value violates..." This is a setting you probably created at the table level. You can either remove it, or we can try to trap it.
How? I'm close but at this point I have to wonder if its my problem or the accountant. I would like to make my data stable but at what cost
Go to the top of the page
 
smlogan
post Sep 26 2017, 02:24 PM
Post#25



Posts: 58
Joined: 21-September 17



The table doesn't care if there are duplicates (not indexed) so how can I get a fresh set of data saved on every keystroke then validate and change afterwards
Go to the top of the page
 
theDBguy
post Sep 26 2017, 02:26 PM
Post#26


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


Hi,

I suspect the "value violates" error may be caused by using lookup fields at the table level. Try removing them. Take a look at this website for some reasons why it's not a good idea.

The Access Web

--------------------
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
 
theDBguy
post Sep 26 2017, 02:33 PM
Post#27


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


Re: "The table doesn't care if there are duplicates (not indexed) so how can I get a fresh set of data saved on every keystroke then validate and change afterwards"

Lots of ways, but I thought you're trying to minimize work/code. One approach is to use a temp table. After validating the input, save the record to the permanent table.

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 26 2017, 02:34 PM
Post#28



Posts: 58
Joined: 21-September 17



Hi
Ty for your help thus far...so I can fix that error but the more persistent error is the changing of fields resulting in incorrect duplicates. can I refresh the data to the table and do real time dlookup. If so how?
Go to the top of the page
 
theDBguy
post Sep 26 2017, 02:35 PM
Post#29


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


Re: "Ok how to I provide a refresh on the field from code so I see an actual of what's in the table."

To make sure what's in the field is the same as what's in the table, you'll have to force a save. However, this is a catch-22 because you were trying not to save the data if it will create a duplicate value/record. And you can't tell if it's a duplicate value if you're changing an existing record because you're trying to check the data in the table, which doesn't include the new changed data yet.

--------------------
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, 02:38 PM
Post#30



Posts: 58
Joined: 21-September 17



Ya ok so forcing a save doesn't work lol. How else can I overcome this? create a record set and compare. I haven't the foggiest how to do this but I suppose I can learn
Go to the top of the page
 
smlogan
post Sep 26 2017, 02:45 PM
Post#31



Posts: 58
Joined: 21-September 17



Re: "Lots of ways, but I thought you're trying to minimize work/code. One approach is to use a temp table. After validating the input, save the record to the permanent table."
Is this difficult or would a record set be better. I'm lost here but willing to learn
Go to the top of the page
 
theDBguy
post Sep 26 2017, 02:45 PM
Post#32


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


I mentioned one possible approach earlier - by using a temp table. Another approach is, like you said, create a recordset based on the form and check duplicate values only within the recordset (leaving the table out).

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
 
theDBguy
post Sep 26 2017, 02:48 PM
Post#33


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


Hi,

To make sure I fully understand your dilemma, when I open the form and try to enter a new record matching the last one, I get the following screen.

Attached File  dup.png ( 23.83K )Number of downloads: 1


Now, can you give me the steps to duplicate the problem you're having with updating/changing existing records? Thanks.

--------------------
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
 
theDBguy
post Sep 26 2017, 03:07 PM
Post#34


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


Here's the latest copy I have. I couldn't get this one to show the "value violates" error anymore. I can't remember how to make it show. Could you please try? Thanks.
Attached File(s)
Attached File  Database51.zip ( 40.33K )Number of downloads: 1
 

--------------------
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 27 2017, 04:04 AM
Post#35



Posts: 58
Joined: 21-September 17



I changed the form data source to directly on the balancesheet table instead of balancesheet query and added the undo commands you changed. No more errors it seems. I was calling the dcount on values in the table and the form was using data from the query and I guess the query wasn't updating the table quick enough and/or the query was holding the old duplicate values that you removed with the undo. The value violates was a line of code in the lostfocus that I missed when I commented the whole thing out. You deleted it complete so error was gone. All is good ty for all the help you provided. I was about ready to give up.
Go to the top of the page
 
theDBguy
post Sep 27 2017, 10:44 AM
Post#36


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


Hi,

Glad to hear you got it sorted out. Good luck with your project.

--------------------
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 27 2017, 01:17 PM
Post#37



Posts: 58
Joined: 21-September 17



TY
Go to the top of the page
 
theDBguy
post Sep 27 2017, 01:56 PM
Post#38


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


You're welcome. Glad we could assist.

--------------------
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
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 03:57 AM