Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 31st, 2006, 10:50 AM
Registered User
 
Join Date: May 2006
Location: , , USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Form & Subform Closing w/Completion Check

Hi,
I've been working on a simple database to record and track status of requests for files. I'm currently working on a form (frmManageRequests) and 2 subforms where a user would add actions (sfrmAddNewActions) that show movement of an existing file request and any history of prior actions (sfrmViewActions)
The main form opens to show only 1 request at a time based on a selection from a list on another form.

I do not allow fields on the main form to be updated or any on the subform that shows the history. There are three fields on the sfrmAddNewActions which can be updated-- Action_Type, ActionInitiator and Volumes. I am trying to require data entry for the first two, third is optional. I allow a cancel/delete with a button on the record level of the subform since I don't let users revise history. Upon close from a main form button, I need to check for required data entry for the first two fields in the subform without eliminating the possibility that someone may want to close the form without entering anything in any fields (ie decide not to add an action at all).

I am using the following code on the main form close button.

Private Sub cmdSaveandCloseManageRequests_Click()
On Error GoTo Err_cmdSaveandCloseManageRequests_Click

    If IsNull(Form!sfrmAddNewActions!Action_Type) Then
    MsgBox "Please Select the Action Taken"

    ElseIf IsNull(Form!sfrmAddNewActions!ActionInitiator) Then
    MsgBox "Please Select the Initiator"

    Else
        DoCmd.Save
        DoCmd.Close
    End If

Exit_cmdSaveandCloseManageRequests_Click:
    Exit Sub

Err_cmdSaveandCloseManageRequests_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveandCloseManageRequests_Click

End Sub

The above works beautifully for the required data entry check but when I add in a bit to handle the situation where a user may close w/out adding an "action" I begin to have problems.

If IsNull(Form!sfrmAddNewActions!Action_Type And Form!sfrmAddNewActions!ActionInitiator And Form!sfrmAddNewActions!Volumes) Then
        DoCmd.Close

    ElseIf IsNull(Form!sfrmAddNewActions!Action_Type) Then
    MsgBox "Please Select the Action Taken"

    ElseIf IsNull(Form!sfrmAddNewActions!ActionInitiator) Then
    MsgBox "Please Select the Initiator"

    Else
        DoCmd.Save
        DoCmd.Close
    End If
The code allows a nice main form close if all is null but as soon as I test by adding one required field and leaving the second blank, I get an error from access that says "type mismatch" . I've only ever seen type mismatch when I write queries (text and number mismatch), I confess I'm stumped as to why the problem crops up so suddenly here when the data entry check worked fine independent of adding the all fields null check before it. I hope it is a silly mistake on my part :) Any ideas about where I've gone wrong? Thank you for any assistance!
Reply With Quote
  #2 (permalink)  
Old May 31st, 2006, 11:25 AM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,

I use the following code on a form for Order Entry. This code will not allow the paperwork to be printed until all required fields are completed.
Private Sub PrintWorkOrder_Click()
On Error GoTo Err_PrintWorkOrder_Click

If IsNull(SandBlastRequired) Then
    MsgBox ("Must Complete ""Sandblast Required"" Field Before Work Order Can Be Printed.")
Else

If IsNull(CertificationRequired) Then
    MsgBox ("Must Complete ""Certification Required"" Field Before Work Order Can Be Printed.")
Else

If IsNull(ApprovedByID) Then
    MsgBox ("Must Complete ""Approved By"" Field Before Work Order Can Be Printed.")
Else

    Dim stDocName As String

    stDocName = "rptToolOrderReport"
    DoCmd.OpenReport stDocName, acNormal

Exit_PrintWorkOrder_Click:
    Exit Sub

Err_PrintWorkOrder_Click:
    MsgBox Err.Description
    Resume Exit_PrintWorkOrder_Click

End If
End If
End If

End Sub

Hopes this helps!

D. Bartelt
Reply With Quote
  #3 (permalink)  
Old May 31st, 2006, 02:25 PM
Registered User
 
Join Date: May 2006
Location: , , USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the suggestion dbartelt but the problem hasn't gone away. I'll keep plugging away at it and maybe something will give :)

Reply With Quote
  #4 (permalink)  
Old June 1st, 2006, 02:48 PM
Registered User
 
Join Date: May 2006
Location: , , USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I found the problem.

Instead of adding
If IsNull(Form!sfrmAddNewActions!Action_Type And IsNull Form!sfrmAddNewActions!ActionInitiator And Form!sfrmAddNewActions!Volumes)Then
        DoCmd.Close

I should have written

If IsNull(Form!sfrmAddNewActions!Action_Type) And IsNull(Form!sfrmAddNewActions!ActionInitiator) And IsNull(Form!sfrmAddNewActions!Volumes) Then
        DoCmd.Close

Writing separate IsNull with every field and connecting each with an "And" instead of inserting the "And" within the single IsNull was the difference.
This fixes the "type mismatch" error I'd been having.
Reply With Quote
  #5 (permalink)  
Old June 2nd, 2006, 07:22 AM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Glad you found the solution.

D. Bartelt
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Closing a SubForm Only (SOLVED) eusanpe Access VBA 2 April 16th, 2008 09:01 AM
Check if dataset was modified before closing form Kia VB Databases Basics 4 July 16th, 2007 08:46 PM
How to refresh owner form on closing of child form akumarp2p C# 2005 0 December 22nd, 2006 10:27 AM
Opening & Closing Database connections pauliehaha Classic ASP Databases 4 December 18th, 2006 08:50 AM
check subform fields damnnono_86 Access 1 December 29th, 2003 11:36 AM



All times are GMT -4. The time now is 07:15 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.