Form to write to a table on button click

[foxy]

Hi,

I want to create a data input form that does not write the data to the table until you click a ‘submit’ button. Does that have to be done with VBA code? If so, what is it?

At the minute, I have a form which you can enter data into but it writes to the table as you are typing, so if a user decides they don’t want to complete the form and closes it, it leaves the record half done in the table.

Cheers

Foxy

 

[missinglinq]

At the minute, I have a form which you can enter data into but it writes to the table as you are typing

I think we'd all be interested in exactly how you have a form where the data is written to the table as you are typing! I suspect it's only being written to the table when the record is saved.

The record will be saved to the form if

1. You move to a different record
2. You close the form
3. You explicitly issue a command to save the record.

The standard way to allow the user to save or dump a record would be to popup a message from the Form_BeforeUpdate event, asking if they want to save the record or not. Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 resp = MsgBox("Do you wish to save this record?", vbYesNo)
  If resp = vbNo Then
  Me.Undo
  End If
End Sub

If they respond Yes, you do nothing, simply allowing the

 

[foxy]

Ok, perhaps I got a bit confused myself & didnt explain it properly. It must be just saving the data when we close the form.

Basically, I've got a form that I want to insert a new record into a person table. Then there are a couple of subforms which update different tables with the person ID as foreign keys.

Basically i want the user to be able to type in all the information, but the info not write to the table until they click the 'submit' button. If the close the form or hit a cancel button I want it not to write to the table and just clear the form.

Cheers

Foxy

 

[Thinh]

there 2 ways you can do this.
You can create an unbound form and then write a vba code to insert the record using sql when user click on the submit button. or you can use the dirty event. when a record get edit it becomes dirty. what you can do is on unload event which is before close event the form check if me.dirty = true. if it true then do me.undo and it will not write that record to the table. on submit change the dirty status = false and that should take care of it for you. if you dont know much about SQL i prefer using the dirty method.

Private sub SubmitBtn_Click()

me.dirty = false​

end sub

Private sub unload()
if me.dirty = true then

me.undo​

end if
end sub

use the generate event that access generate the above is only for sample. they are missing the parameters.

 

[boblarson]

is on unload event

You can't use the Unload event to cancel a table update as the event (BEFORE UPDATE and then AFTER UPDATE has already fired by the time the form's UNLOAD event has fired). You use the BEFORE UPDATE event to validate and cancel the write to the table if you wish to be able to do so.

 

[foxy]

if you dont know much about SQL i prefer using the dirty method.

I am absolutely fine with SQL but I have never used it within a VBA code. How would I go about this?

Cheers

Foxy

 

[Thinh]

If you are gonna write to every field in your table the query will look something like this

private Sub SubmitBtn_Click()
docmd.runsql("Insert Into project ('" & me.txtProjectID & "','" & me.txtProjectNr & "'....')"
end sub

if you are gonna write to only few fields use the following syntax
docmd.runsql("Insert Into project (projectID,ProjectNr..) Values ('" & me.txtProjectID & "','" & me.txtProjectNr & "'....')"