How to close a form without saving a new record? (1 Viewer)

[kajtam]

Hi,

I might be tired by now and it just started to be very annoying.
I have a form frmAddNewProject that is a Data Entry form.

When you click Add Project on the form, it creates folders and copies files to a location and also creates new record(s) in the ProjectT table with apriopriate information.

Here is my code:

Private Sub cmdAdd_Click()
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim FolderPath As String
Dim strType As String
Dim strYear As String
Dim strGPN As String
Dim strDesc As String
Dim intComp As Integer
Dim i As Integer
Dim rstProject As DAO.Recordset
Dim dbsProjectBoard As DAO.Database
strYear = DatePart("yyyy", Date)
strGPN = Left([GPNnumber], 2) & "-" & Right([GPNnumber], 4)
strDesc = Me.ProjectDescription
strType = DLookup("[Abbr]", "ProjectTypeT", "[ProjectTypeID] = " & Me.ProjectType)
intComp = CInt(txtComponents)
If intComp = 1 Then
 
    ToPath = "P:\APQP NPD Projects\" & strType & "\" & strYear & "\" & strGPN & " - " & strDesc
    FromPath = "P:\xDocuments\Templates\Project Template - Copy"
        If Right(FromPath, 1) = "\" Then
        FromPath = Left(FromPath, Len(FromPath) - 1)
        End If
        If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
        End If
    Set FSO = CreateObject("scripting.filesystemobject")
        If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
        End If
    FSO.CopyFolder Source:=FromPath, Destination:=ToPath
    Set FSO = Nothing
    MsgBox "destination path is: " & ToPath
 
        Set dbsProjectBoard = CurrentDb
        Set rstProject = CurrentDb.OpenRecordset("ProjectT")
        rstProject.AddNew
        rstProject!Status = Me.Status
        rstProject!ProjectType = Me.ProjectType
        rstProject!GPNnumber = Me.GPNnumber
        rstProject!ProjectDescription = Me.ProjectDescription
        rstProject!Customer = Me.Customer
        rstProject!ProjectLead = Me.ProjectLead
        rstProject!LeadBDM = Me.LeadBDM
        rstProject!PotentialValuePA = Me.PotentialValuePA
        rstProject!Folder = ToPath
        rstProject!Component = "01"
        rstProject.Update
        rstProject.Close
        Set rstProject = Nothing
End If
If intComp > 1 Then
    FromPath = "P:\xDocuments\Templates\Project Template - Copy"
    FolderPath = "P:\APQP NPD Projects\" & strType & "\" & strYear & "\" & strGPN & " - " & strDesc
    Set FSO = CreateObject("scripting.filesystemobject")
            If FSO.FolderExists(FolderPath) = False Then
            FSO.CreateFolder FolderPath
            End If
    Set FSO = Nothing
    For i = 1 To intComp
        ToPath = "P:\APQP NPD Projects\" & strType & "\" & strYear & "\" & strGPN & " - " & strDesc & "\0" & i
        Set FSO = CreateObject("scripting.filesystemobject")
            If Right(FromPath, 1) = "\" Then
               FromPath = Left(FromPath, Len(FromPath) - 1)
            End If
            If Right(ToPath, 1) = "\" Then
               ToPath = Left(ToPath, Len(ToPath) - 1)
            End If
 
            If FSO.FolderExists(FromPath) = False Then
            MsgBox FromPath & " doesn't exist"
            Exit Sub
            End If
 
        FSO.CopyFolder Source:=FromPath, Destination:=ToPath
        Set FSO = Nothing
        MsgBox "destination path is: " & ToPath
 
        Set dbsProjectBoard = CurrentDb
        Set rstProject = CurrentDb.OpenRecordset("ProjectT")
        rstProject.AddNew
        rstProject!Status = Me.Status
        rstProject!ProjectType = Me.ProjectType
        rstProject!GPNnumber = Me.GPNnumber
        rstProject!ProjectDescription = Me.ProjectDescription
        rstProject!Customer = Me.Customer
        rstProject!ProjectLead = Me.ProjectLead
        rstProject!LeadBDM = Me.LeadBDM
        rstProject!PotentialValuePA = Me.PotentialValuePA
        rstProject!Folder = ToPath
        rstProject!Component = "0" & i
        rstProject.Update
        rstProject.Close
        Set rstProject = Nothing
    Next i
End If
End Sub

Everything works fine till here.
Now I would like to close the form but closing it gives me an additional empty record.

I tried this:

Dim ctl As Control
 
On Error Resume Next
For Each ctl In Me.Controls
  ctl = ctl.DefaultValue
Next
Set ctl = Nothing

to remove all values, but then it just adds an empty record.

Is there a way to close that form without removing my legitimate new records and without adding empty ones?

Please help

 

[MarvinM]

Kajtam,

Look at this thread. Post #13 looks like the best solution for your issue.
http://www.access-programmers.co.uk/forums/showthread.php?t=173222

HTH
_________________
Regards,
Marvin M
Windows 7 Professional, MS Access 2007/2010
Windows 8 Professional, MS Access 2013
-------------------------------------------------------------------------------------------------------------------
If my post has helped you, please click the scales or click the 'Thumbs up'. Thanks!
-------------------------------------------------------------------------------------------------------------------

 

[kajtam]

Thanks a lot Marvin!

Can't believe I've spent 4 hours trying to figure it out.

Kind Regards
Michal

 

[MarvinM]

Michal,

I know how it is. I had a similar problem. Instead of having the form creating an automatic new record (with bound forms), I had an unbound form. In which case, no record is created until the user hits the Save button. In this situation, a user may think that they've entered all the data for a record and try to exit. I had to stop them and ask if they intended to create a record or do they want to flush away the data on the screen. Kind of a reverse of your situation.

Anyway, I always find good info on these forums. Here's a guy with a list that you might want to check out. http://allenbrowne.com/links.html#Forums
By the way, his coding samples have saved my butt many times. You'll want to browse through those too. http://allenbrowne.com/tips.html

 

[kajtam]

Thanks a lot Marvin.