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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-27-2018, 10:04 AM   #16
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 31
Thanks: 6
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Re: If column is not empty

Hi Dear,

I got an error '3075' stacks the application if the DeptSeq table is empty to start creating not exist calendar dates from the first date of the current year until the end of it.

I think I have to add to the code ( Is not Null ) but I do not know how to do it.

Code:
  N = DCount("SenderID", "DeptSeq", "DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & _
"# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#")
Sorry for disturbing you with me.

Thanks in advance.

Alhakeem1977 is offline   Reply With Quote
Old 04-27-2018, 10:40 AM   #17
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,931
Thanks: 75
Thanked 1,218 Times in 1,135 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: If column is not empty

Quote:
Originally Posted by Alhakeem1977 View Post
Hi Dear,

I got an error '3075' stacks the application if the DeptSeq table is empty to start creating not exist calendar dates from the first date of the current year until the end of it.

I think I have to add to the code ( Is not Null ) but I do not know how to do it.

Code:
  N = DCount("SenderID", "DeptSeq", "DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & _
"# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#")
Sorry for disturbing you with me.

Thanks in advance.
No there's an easier solution using error handling. Busy at the moment. Will send instructions later
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 04-27-2018, 12:15 PM   #18
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,931
Thanks: 75
Thanked 1,218 Times in 1,135 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: If column is not empty

Hi
Just needed to check which procedure triggered the error and add 1 line of code to handle the error - shown in RED below
Error 3075 occurred because the table was empty - as we know that's the reason, we can tell Access to ignore it

Code:
Private Sub cmdUpdate_Click()
   
On Error GoTo Err_Handler
   
   'save changes
    If Me.Dirty Then Me.Dirty = False
   
    Dim N As Integer
    N = DCount("SenderID", "DeptSeq", "DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#")
    
    If MsgBox("You are about to execute a major operation ! Do you really want to continue ?" & vbCrLf, vbYesNo, "Major Operation") = vbNo Then
            MsgBox "Your operation execution is canceled !", vbOKOnly, "Operation Canceled"
            Exit Sub
    Else
        If N > 1 Then
            MsgBox N & " records in the selected date range will be deleted.", vbCritical, "Existing Data"
        ElseIf N = 1 Then
            MsgBox N & " record in the selected date range will be deleted.", vbCritical, "Existing Data"
        Else
            MsgBox "No records to delete"
        End If
       
       'this deletes all existing records - not just those where SenderID Is Not Null
        CurrentDb.Execute "DELETE DeptSeq.*" & _
            " FROM DeptSeq" & _
            " WHERE (DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#)", dbFailOnError
            
        'this would only delete those where SenderID Is Not Null
        'however it can't be used as your DAOAdding procedure replaces all values
       '  CurrentDb.Execute "DELETE DeptSeq.*" & _
            " FROM DeptSeq" & _
            " WHERE (((DeptSeq.DDate) Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#) AND ((DeptSeq.SenderID) Is Not Null))", dbFailOnError
            

        'add new records - NOT CHECKED or ALTERED
        'However an APPEND query would do the same jpb and MUCH faster
        DAOAdding
      '  On Error Resume Next
        MsgBox "Your operation execution has been completed successfully.", vbOKOnly, "Successful Operation"
    End If
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    If Err = 3075 Then Resume Next
    MsgBox "Error " & Err.Number & " in cmdUpdate_Click procedure: " & vbCrLf & Err.Description
    Resume Exit_Handler
    
End Sub
Updated version attached
Attached Files
File Type: zip Daily Movements-CR_v2.zip (90.9 KB, 7 views)

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
Alhakeem1977 (04-28-2018)
Old 04-28-2018, 02:44 AM   #19
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 31
Thanks: 6
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Thumbs up Re: If column is not empty

Very good thank you so much.
Works perfectly without any errors.

If you don't mind, is there a simple way to get those existing records in a text box in the same form but the [ID] number of DeptSeq?
Please do it if you have time I am happy with db you provided to me but it will add a fantastic feature to the application.

Please accept my sincere regards.
Alhakeem1977 is offline   Reply With Quote
Old 04-28-2018, 03:27 AM   #20
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,931
Thanks: 75
Thanked 1,218 Times in 1,135 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: If column is not empty

Not quite sure what you mean so I've guessed
New form frmDeptSeq with button added to frmHolidays
Hopefully that's similar to what you want

I expect you will want to adapt further yourself
Attached Files
File Type: zip Daily Movements-CR_v3.zip (111.6 KB, 5 views)
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
Alhakeem1977 (04-28-2018)
Old 04-28-2018, 05:33 AM   #21
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 31
Thanks: 6
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Re: If column is not empty

I am sorry, that's because of my limited English knowledge.

I mean when I press the Update button which stopped running the code with a
message box saying " 2 records in the selected date range will be deleted."
I want to get the [ID]'s of those records in a text box in the same form "frmHolidays" like:

* [ID] 07 of [DDate] 14-Jan-18
* [ID] 11 of [DDate] 15-Jan-18

I don't know if this conveys my need.

Please don't bother about it if it consumes from your time.

thanks for your kind patience.
Alhakeem1977 is offline   Reply With Quote
Old 04-28-2018, 06:40 AM   #22
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,931
Thanks: 75
Thanked 1,218 Times in 1,135 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: If column is not empty

OK - because you asked so nicely ....

I've done it by modifying the message box instead



This also gives users another chance to cancel
I've left the 6 records in the attached so you can easily test it

Hopefully this is OK for you now.
Busy doing other things for the rest of today

Good luck with your project
Attached Images
File Type: png Capture.PNG (6.3 KB, 40 views)
Attached Files
File Type: zip Daily Movements-CR_v4.zip (118.0 KB, 5 views)

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
Alhakeem1977 (04-28-2018)
Old 04-28-2018, 08:35 AM   #23
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 31
Thanks: 6
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Thumbs up Re: If column is not empty

Waw, what did you do for me, I really really thank from my heart, I do not know how to thank you for all this.
I really love from you to answer all my questions in the future, actually, this site made a lot for me.

Appreciate your kind efforts to have these features you provided to me in my application.

I will try in the nearest future to utilize the Barcode in other MS Access db any idea how to achieve that is there in Access an Add-Ins or Reference library to get the barcode?

I would like to thank you again for all your kind efforts.
Alhakeem1977 is offline   Reply With Quote
Old 04-28-2018, 09:33 AM   #24
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,931
Thanks: 75
Thanked 1,218 Times in 1,135 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: If column is not empty

Thanks very much - glad I could help
The forum doesn't operate like that and rightly so.
Anyone who feels able to answer a question can do so.
If someone else can see a better way, they may add their own solutions.

When you are ready to work with barcodes, post a new thread.
However you may be pleased to know that importing barcode data into Access is easy - its treated like normal keyboard input
No special reference library needed

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders 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
[SOLVED] filter datasheet based on empty dates column on top megatronixs Forms 7 10-07-2016 02:53 AM
[SOLVED] Update table where column cell is empty Heidestrand Queries 7 08-06-2015 04:40 AM
Crosstable column for empty number field Batox Modules & VBA 8 12-19-2011 04:12 AM
empty field comaprison and deleted column undergrad Queries 3 08-27-2003 04:39 PM
Empty column in subreport results in #error marko Reports 2 08-19-2003 11:20 AM




All times are GMT -8. The time now is 09:08 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