Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 13th, 2007, 10:16 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default do not validate record

I have a form where in the underlying table the primary key is set to autonumber. Then I have 6 other fields on this form for data input by the users.

On this form I have 3 action buttons. The first one the go to a new record, the second one to save the record and navigate back to the previous menu and a thirth one to navigate back without saving the record the user is working on.

This thirth action button is the one that I have a problem with. Because the primary key is autonumber, the record is automatically saved on exit if the user has put some information in either one of the 6 fields. What I would like is that the current record that the user is filling in is NOT saved on exit, but only navigation back to the previous menu.

The code I have so far is:

Private Sub cmdTerugZonderBewaren_Click()
On Error GoTo Err_cmdTerugZonderBewaren_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

Select Case Me.Afdeling.Value
    Case "BA01"
        stDocName = "frmInlogBA01"
    Case "BA02"
        stDocName = "frmInlogBA02"
    Case "BA03Bed"
        stDocName = "frmInlogBA03Bed"
    Case "BA03Pax"
        stDocName = "frmInlogBA03Pax"
    Case "BA04"
        stDocName = "frmInlogBA04"
    Case "BA05"
        stDocName = "frmInlogBA05"
    Case "BA06"
        stDocName = "frmInlogBA06"
    Case "BA08Bed"
        stDocName = "frmInlogBA08Bed"
    Case "BA08Textil"
        stDocName = "frmInlogBA08Textil"
    Case "BA09"
        stDocName = "frmInlogBA09"
    Case "BA10"
        stDocName = "frmInlogBA10"
    Case "BA40"
        stDocName = "frmInlogBA40"
    Case "BA50"
        stDocName = "frmInlogBA50"
    Case "Family"
        stDocName = "frmInlogFamily"
    Case "ZB"
        stDocName = "frmInlogZB"
End Select

    DoCmd.SetWarnings False
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmToDoComin"
    DoCmd.Maximize
    DoCmd.SetWarnings True

Exit_cmdTerugZonderBewaren_Click:
    Exit Sub

Err_cmdTerugZonderBewaren_Click:
    MsgBox Err.Description
    Resume Exit_cmdTerugZonderBewaren_Click

End Sub

Where do I have to change the code so the users exits the form without saving the current record? The autonumber primary key is set to random (don't know if this is important or not). The record is always saved, because the first field is a date type with a default value. This date field is also locked...

  #2 (permalink)  
Old February 13th, 2007, 10:40 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You have to add code to delete the record. Once the user enters text in one of the fields, it is saved.

Take the PK field in a variable, and then run a delete query.

Create a delete query, but select all the fields, rather than *.
Then in the criteria line for the delete query, put this code:

[Forms]![frmMyFormName].[PKFieldName]

Call this query something like qryDELETE_CurrentRecord

Then on the navigate back without saving button, do this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETE_CurrentRecord"
DoCmd.SetWarnings True

This may not work, but give it a try. If it doesn't work, we can put in on another event.


mmcdonal
  #3 (permalink)  
Old February 13th, 2007, 10:41 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Also, in this code:

    DoCmd.SetWarnings False
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmToDoComin"
    DoCmd.Maximize
    DoCmd.SetWarnings True

You don't need the DoCmd.SetWarnings statements.

mmcdonal
  #4 (permalink)  
Old February 13th, 2007, 11:37 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I changed the end to

    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryToDoCominHuidigeRecordWissen"
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmToDoComin"
    DoCmd.Maximize

but the record is still not deleted.

I first put the docmd.openquery after the docmd.close acform, "frmToDoComin", but then off course the query asked me the autonumber cause the form was already closed...

  #5 (permalink)  
Old February 13th, 2007, 12:05 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Well, I knew this would be problematic, but it can be done.
"The first one the go to a new record, the second one to save the record and navigate back to the previous menu and a thirth one to navigate back without saving the record the user is working on."
Why do you have these buttons on a bound form? Why not add a button to Add a new record, and make the On Current event of the form Me.AllowAdditions = False unless they click the button?

mmcdonal
  #6 (permalink)  
Old February 13th, 2007, 01:15 PM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

So if I understand correctly what you mean is that I have to set the default value of the form to allowadditions=no and then on the first 2 action buttons I have to change the value back to yes before going to a next record an then putting it back immediately to no after it has moved to the new record.

  #7 (permalink)  
Old February 13th, 2007, 01:37 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am still not sure why a user would start entering data when they were not creating a new record. Basically yes, you would have the form set to allow additions no unless they clicked a button to set this.

mmcdonal
  #8 (permalink)  
Old February 13th, 2007, 05:03 PM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I believe I'm going to remove the thirth button. It will make it more easy for me...

Thanks for trying to help



Similar Threads
Thread Thread Starter Forum Replies Last Post
Validate NULL Deep9 BOOK: Beginning Regular Expressions 1 August 24th, 2007 06:49 AM
? - Is there a way to validate 1 XML record at a t bobk544 XML 0 January 7th, 2005 02:37 PM
Record locking - user needs the next queued record cbtoolkit SQL Server 2000 0 December 6th, 2004 08:29 AM
validate textbox stoneman Access 5 January 30th, 2004 11:49 AM





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