Quit but don't save

[VBAhole22]

When I load a data entry screen I pass a variable that the user selected on my switchboard. This data entry screen then assigns an Autonumber. But I have a quit button that allows the user to bail out of the screen. But if they do, that record gets added to the table anyway and the Autonumber is incremented and I get a blank record.

I tried Me.Undo before closing the form but it didn't work.

I open the form as AddOnly from my switchboard

 

[Rob.Mills]

The best way around this is to create your data-entry form as unbound. Then depending on how the user closes the form, if you want it to save run a sub that will open the table in memory, add a new record with all the data in the form. If you don't want it save it will just be deleted.

 

[VBAhole22]

I like that idea and I think it will work... but bound forms seem much easier to set up and maintain for me. So my sub would open the table and insert the record through ADO? Would I have to build an SQL statement and execute it?
Isn't there a better way to bail out of a bound form?

 

[ghudson]

I would suggest you use the forms BeforeUpdate event to ask the user if
they want to save the changes to the current record.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
If MsgBox("Do you want to save the changes to the current record?" & Chr(13) & Chr(13) & "Clicking the No button will undo the changes to the current record.", vbQuestion + vbYesNo, "Save Record") = vbNo Then
    Cancel = True
    DoCmd.RunCommand acCmdUndo
Else
    'do nothing and save the changes
End If
    
End Sub

HTH

 

[VBAhole22]

Thanks for the suggestion, but even that didn't work, the record still gets incremented. It's got to be because I insert a value in there when the form opens. I guess Access considers this an entry into the table. If I went to unbound this would eliminate the problem. BUT, if I could find a way to imitate and Esc keystroke that clears the field I passed the variable to and then the form will rescind the autonumber. Or I guess I could have a hidden field that only gets loaded before update and an unbound form that shows the user the passed value. Options, options, but which will work

 

[R. Hicks]

Does the form contain a subform ....???
If it does .. then the form record (parent record) will be saved when you navigate to the subform .. so it must be deleted at that point.

But if there is no no subform ... Me.Undo should do the job.

RDH

 

[VBAhole22]

No subform. I thought Me.Undo would work also but it isn't and I don't know why and can't figure out why. Frustrating. I dislike having to do all that extra work to make the form unbound and I have 2 more just like this one. I tried using SendKeys "{ESC}" but that didn't seem to work either.

 

[Rob.Mills]

VB -- It's not that much work to create a sub that would save the data. Here's an example:

Dim rst as DAO.Recordset
Dim ctl as Control

Set rst=CurrentDb.OpenRecordset("TableName",DBOpenDynaset)

With rst

.AddNew

For each ctl in Forms!FormName.Controls

If TypeOf ctl is Textbox or TypeOf ctl is Combobox then

Make sure the name of the control matches exactly to the name of it's corresponding field in the table
.Fields(ctl.Name)=ctl.Value

End if

Next ctl

.Update

Set rst=nothing

That's about it.

 

[VBAhole22]

You know, it never even occured to me to check the actual table to see if those recs are going in. Using ghudson's code the DO NOT go in the table. The AutoNumber does get bumped, but you are right Pat, I don't really care what that number is. I just assumed, foolishly, that those records were getting in the table as blanks, but they ain't.
I appreciate everyone's help on this. Rob thanks for the code too. I may choose to go unbound on some of my forms because you do get more control. I am trying to avoid DAO like SARS though because lately many of my dbs have been getting upgraded to SQL Server and DAO is a no-go. Do you know what the equivalent ADO code would be?

 

[Rob.Mills]

I don't know it well enough to tell you. I've barely dabbled in it as my boss doesn't seem to think that we need an SQL Server for 150 users! Go figure.

But good luck to you.

 

[Shadez]

ADO is easy

to add a record to a table i tend to use


currnetproject.connection.execute "insert into TableName (field1,field2,fieldn) values (" & SomeVal & ",'" & SomeStr & "'," & SomeThinElse & ")"



Note u can use the query builder to create the query and then copy and paste into code.





ShadeZ