Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-15-2018, 05:35 AM   #16
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: Unable to perform dcount

yes you are right its 2 step verification.

lookforsmt is offline   Reply With Quote
Old 06-15-2018, 07:18 AM   #17
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,929
Thanks: 9
Thanked 3,844 Times in 3,787 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Unable to perform dcount

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
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 06-15-2018, 12:01 PM   #18
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,863
Thanks: 2
Thanked 399 Times in 394 Posts
Cronk will become famous soon enough
Re: Unable to perform dcount

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.

Cronk is offline   Reply With Quote
Old 06-15-2018, 10:19 PM   #19
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: Unable to perform dcount

Thanks Cronk for the suggestions. Let me clarify those in line with that:

PHP Code:
Rather than duplicating data into a temporary tableadd the fields holding the check date and the user to the main data table.

If 
you wanthide 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 Code:
At the momentit is not possible to add data because your validity checking code with the Dcount includes the field DueDatewhich is not on the form.

Incidentallyyou do not need the sub formThe 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 is offline   Reply With Quote
Old 06-16-2018, 01:23 AM   #20
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: Unable to perform dcount

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
lookforsmt is offline   Reply With Quote
Old 06-16-2018, 07:09 AM   #21
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: Unable to perform dcount

HI! i have managed to get the desired results but may not be the correct method

i have attached the updated db. Just one final check on the before update event
on field chqbrcd on the mainform. If anyone can help me to restrict input of number as 1st two characters. The code is already there i just need to change it
Attached Files
File Type: accdb FieldValidation_2c.accdb (528.0 KB, 13 views)
lookforsmt is offline   Reply With Quote
Old 06-18-2018, 08:27 AM   #22
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,929
Thanks: 9
Thanked 3,844 Times in 3,787 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Unable to perform dcount

I'm not clear on the question. The only code I see right off dealing with first two characters is on the subform, not the main form.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 06-18-2018, 09:52 AM   #23
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: Unable to perform dcount

Hi! pbaldy, i have moved the beforeupdate event to the main form on field name "searchFor" but i am not able to get the result.

I am looking for matching the below:

The user will input the "duedateFor" and "chqbrcd" on the mainform and input in field "creditAccount_ac" on subform. If the same is matching in "tbl_DailyData" then it will accept or else it will pop-up msg as invalid input.

Before that when capturing "chqbrcd" if the starting alphabet is not "EN" then it popup msg as invalid input

hope the above is clear.
lookforsmt is offline   Reply With Quote
Old 06-21-2018, 12:46 PM   #24
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: Unable to perform dcount

HI! i managed to get the name displayed on the main form from the table.

Quote:
accountName_ac.Value = DLookup("[ACName]", "[tbl_DailyData]", "chqbrcd =" & Chr(34) & SearchFor.Value & Chr(34))
What if i want to add some more fields from the same table on to the main form. How can i write this code.
Any sugggestions
lookforsmt is offline   Reply With Quote
Old 06-21-2018, 08:23 PM   #25
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,863
Thanks: 2
Thanked 399 Times in 394 Posts
Cronk will become famous soon enough
Re: Unable to perform dcount

If your aim is to ensure that there is no more than one record with the same values in DueDate, CreditAC, and chqbrcd, I would create a unique index using all three fields.

Access will then prevent a duplicate being saved in the table.
Cronk is offline   Reply With Quote
Old 06-22-2018, 06:20 AM   #26
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: Unable to perform dcount

The idea is for the user to input some of the fields and view some of the fields on an unbound field. I managed to apply the same code to the other fields to be visible on form.

I may not be doing this project in a structured way, but as long as i am getting my result, it fine for the moment, but i may look at it once i get this completed.

Thanks for suggestion.
lookforsmt is offline   Reply With Quote
Old 06-23-2018, 10:52 PM   #27
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,863
Thanks: 2
Thanked 399 Times in 394 Posts
Cronk will become famous soon enough
Re: Unable to perform dcount

I know what you mean about the end is more important than the means. I have been known to knock a nail in using the head of a pair of pliers (seriously) - works but not the best way.

Cronk is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I perform this query? Tsung90 Queries 1 04-25-2012 07:35 PM
How to perform this task? Tsung90 Queries 11 04-25-2012 04:45 AM
Perform SQL on Recordset JaedenRuiner Modules & VBA 4 10-12-2006 07:24 PM
How Do Perform Lookup main-man General 1 03-12-2006 08:10 PM
[SOLVED] how do i perform calculations on forms? maeg Forms 2 05-28-2004 05:24 AM




All times are GMT -8. The time now is 05:01 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World