Stop record from being added

S

Simon Harris

Hi All,

I have a button on a form, when the button is clicked, this code is called
to open another form:

Private Sub TimeLogButton_Click()
If Len([Forms]![FRM_Switchboard]![SUBFRM_view_employees]) > 0 Then
stDocName = "FRM_order_time_log"
DoCmd.OpenForm stDocName, datamode:=acFormAdd
Else
MsgBox ("Please select employee first")
End If
End Sub

The form has various fields, which the user fills in, then clicks a further
button, which runs this code:

Private Sub NewTimeLogEntry_Click()
If Me.JobsCombo.Value = 0 Or Me.EmployeeCombo.Value = 0 Or
IsNull(Me.hours_spent.Value) Then
MsgBox ("Please select job, employee and enter number of hours
before clicking add")
Else
JobsComboCurrentValue = Me!JobsCombo.Value
EmployeeComboCurrentValue = Me!EmployeeCombo.Value
IDateCurrentValue = Me!Idate.Value
'Save the new time log entry and update the time log list
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Forms![FRM_order_time_log]![TimeLog].Requery
DoCmd.GoToRecord , , acNewRec
Me!JobsCombo.Value = JobsComboCurrentValue
Me!EmployeeCombo.Value = EmployeeComboCurrentValue
Me!Idate.Value = IDateCurrentValue
End If
End Sub

Basically, the code saves the current record, and requeries the listbox on
the form, which displays a summary of records stored.

Problem is this: When I open the form, it seems to add a new record
immediatley - Before I have even clicked my save button.

Thanks lots,

Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
 
B

Brendan Reynolds \(MVP\)

Well, that's what acFormAdd does - it opens the form at the new record,
ready for the user to start entering data.

A bound form always displays *some* record - either an existing saved
record, or the new, empty record. When at the new, empty record, the form
will display default values for any fields that have default values defined.
The record will not actually be committed to the database unless data is
entered, either by the user, or by code.
 
S

Simon Harris

Hi Brenden,

Thanks for your reply - Is there any way round this?

My plan, after reading your reply was to do this:

- Unbind the form from the table
- My save button will then do this:
DoCmd.RunSQL ("INSERT INTO BLA....etc etc")

To confirm - When I open the form, a new record is created...this is visible
in the table after I close the form, as far as I know I dont have any code
that is 'committing' the record.

Thanks,
Simon.

Brendan Reynolds (MVP) said:
Well, that's what acFormAdd does - it opens the form at the new record,
ready for the user to start entering data.

A bound form always displays *some* record - either an existing saved
record, or the new, empty record. When at the new, empty record, the form
will display default values for any fields that have default values defined.
The record will not actually be committed to the database unless data is
entered, either by the user, or by code.

--
Brendan Reynolds (MVP)
(e-mail address removed)

Simon Harris said:
Hi All,

I have a button on a form, when the button is clicked, this code is called
to open another form:

Private Sub TimeLogButton_Click()
If Len([Forms]![FRM_Switchboard]![SUBFRM_view_employees]) > 0 Then
stDocName = "FRM_order_time_log"
DoCmd.OpenForm stDocName, datamode:=acFormAdd
Else
MsgBox ("Please select employee first")
End If
End Sub

The form has various fields, which the user fills in, then clicks a further
button, which runs this code:

Private Sub NewTimeLogEntry_Click()
If Me.JobsCombo.Value = 0 Or Me.EmployeeCombo.Value = 0 Or
IsNull(Me.hours_spent.Value) Then
MsgBox ("Please select job, employee and enter number of hours
before clicking add")
Else
JobsComboCurrentValue = Me!JobsCombo.Value
EmployeeComboCurrentValue = Me!EmployeeCombo.Value
IDateCurrentValue = Me!Idate.Value
'Save the new time log entry and update the time log list
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Forms![FRM_order_time_log]![TimeLog].Requery
DoCmd.GoToRecord , , acNewRec
Me!JobsCombo.Value = JobsComboCurrentValue
Me!EmployeeCombo.Value = EmployeeComboCurrentValue
Me!Idate.Value = IDateCurrentValue
End If
End Sub

Basically, the code saves the current record, and requeries the listbox on
the form, which displays a summary of records stored.

Problem is this: When I open the form, it seems to add a new record
immediatley - Before I have even clicked my save button.

Thanks lots,

Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
 
S

Simon Harris

Never mind....think I just fixed it, amended my exit form button event to be
this:

Me.Undo <-- Added this line
DoCmd.Close acForm, "FRM_job_costs"

Thanks,

Simon.

Simon Harris said:
Hi Brenden,

Thanks for your reply - Is there any way round this?

My plan, after reading your reply was to do this:

- Unbind the form from the table
- My save button will then do this:
DoCmd.RunSQL ("INSERT INTO BLA....etc etc")

To confirm - When I open the form, a new record is created...this is visible
in the table after I close the form, as far as I know I dont have any code
that is 'committing' the record.

Thanks,
Simon.

Brendan Reynolds (MVP) said:
Well, that's what acFormAdd does - it opens the form at the new record,
ready for the user to start entering data.

A bound form always displays *some* record - either an existing saved
record, or the new, empty record. When at the new, empty record, the form
will display default values for any fields that have default values defined.
The record will not actually be committed to the database unless data is
entered, either by the user, or by code.

--
Brendan Reynolds (MVP)
(e-mail address removed)

Simon Harris said:
Hi All,

I have a button on a form, when the button is clicked, this code is called
to open another form:

Private Sub TimeLogButton_Click()
If Len([Forms]![FRM_Switchboard]![SUBFRM_view_employees]) > 0 Then
stDocName = "FRM_order_time_log"
DoCmd.OpenForm stDocName, datamode:=acFormAdd
Else
MsgBox ("Please select employee first")
End If
End Sub

The form has various fields, which the user fills in, then clicks a further
button, which runs this code:

Private Sub NewTimeLogEntry_Click()
If Me.JobsCombo.Value = 0 Or Me.EmployeeCombo.Value = 0 Or
IsNull(Me.hours_spent.Value) Then
MsgBox ("Please select job, employee and enter number of hours
before clicking add")
Else
JobsComboCurrentValue = Me!JobsCombo.Value
EmployeeComboCurrentValue = Me!EmployeeCombo.Value
IDateCurrentValue = Me!Idate.Value
'Save the new time log entry and update the time log list
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Forms![FRM_order_time_log]![TimeLog].Requery
DoCmd.GoToRecord , , acNewRec
Me!JobsCombo.Value = JobsComboCurrentValue
Me!EmployeeCombo.Value = EmployeeComboCurrentValue
Me!Idate.Value = IDateCurrentValue
End If
End Sub

Basically, the code saves the current record, and requeries the
Click to expand...
Click to expand...
listbox
on
Click to expand...
Click to expand...
 
B

Brendan Reynolds \(MVP\)

Hi Simon,

It appears from your later post that you've solved your problem -
congratulations! I just wanted to clarify that when you open a form in add
mode, the new record is not written to the database until it is saved. If
the record is 'dirtied', either by the user entering data or by code
assigning values to any of the fields, then it will be saved automatically
when the form is closed, when the form moves to a different record, or when
the form is requeried or refreshed.

If you need more control over when and how the record is saved, you may be
able to use the form's BeforeUpdate event. You can cancel the saving of the
record by setting the Cancel argument of the BeforeUpdate event to True. You
can gain even more control by using an unbound form, but then you have to
write a lot of code to do things that Access does automatically for you when
you use bound forms.

--
Brendan Reynolds (MVP)
(e-mail address removed)

Simon Harris said:
Hi Brenden,

Thanks for your reply - Is there any way round this?

My plan, after reading your reply was to do this:

- Unbind the form from the table
- My save button will then do this:
DoCmd.RunSQL ("INSERT INTO BLA....etc etc")

To confirm - When I open the form, a new record is created...this is visible
in the table after I close the form, as far as I know I dont have any code
that is 'committing' the record.

Thanks,
Simon.

Brendan Reynolds (MVP) said:
Well, that's what acFormAdd does - it opens the form at the new record,
ready for the user to start entering data.

A bound form always displays *some* record - either an existing saved
record, or the new, empty record. When at the new, empty record, the form
will display default values for any fields that have default values defined.
The record will not actually be committed to the database unless data is
entered, either by the user, or by code.

--
Brendan Reynolds (MVP)
(e-mail address removed)

Simon Harris said:
Hi All,

I have a button on a form, when the button is clicked, this code is called
to open another form:

Private Sub TimeLogButton_Click()
If Len([Forms]![FRM_Switchboard]![SUBFRM_view_employees]) > 0 Then
stDocName = "FRM_order_time_log"
DoCmd.OpenForm stDocName, datamode:=acFormAdd
Else
MsgBox ("Please select employee first")
End If
End Sub

The form has various fields, which the user fills in, then clicks a further
button, which runs this code:

Private Sub NewTimeLogEntry_Click()
If Me.JobsCombo.Value = 0 Or Me.EmployeeCombo.Value = 0 Or
IsNull(Me.hours_spent.Value) Then
MsgBox ("Please select job, employee and enter number of hours
before clicking add")
Else
JobsComboCurrentValue = Me!JobsCombo.Value
EmployeeComboCurrentValue = Me!EmployeeCombo.Value
IDateCurrentValue = Me!Idate.Value
'Save the new time log entry and update the time log list
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Forms![FRM_order_time_log]![TimeLog].Requery
DoCmd.GoToRecord , , acNewRec
Me!JobsCombo.Value = JobsComboCurrentValue
Me!EmployeeCombo.Value = EmployeeComboCurrentValue
Me!Idate.Value = IDateCurrentValue
End If
End Sub

Basically, the code saves the current record, and requeries the
Click to expand...
Click to expand...
listbox
on
Click to expand...
Click to expand...
 
S

Simon Harris

Hi Brenden,

Thanks for your reply - That makes perfect sense, my user would be moving to
another record, and some of the fields are amended automatically, hence the
saving of the record.

Thanks for your explanation - Its always good to understand these things.

Cheers! :)

Simon.

Brendan Reynolds (MVP) said:
Hi Simon,

It appears from your later post that you've solved your problem -
congratulations! I just wanted to clarify that when you open a form in add
mode, the new record is not written to the database until it is saved. If
the record is 'dirtied', either by the user entering data or by code
assigning values to any of the fields, then it will be saved automatically
when the form is closed, when the form moves to a different record, or when
the form is requeried or refreshed.

If you need more control over when and how the record is saved, you may be
able to use the form's BeforeUpdate event. You can cancel the saving of the
record by setting the Cancel argument of the BeforeUpdate event to True. You
can gain even more control by using an unbound form, but then you have to
write a lot of code to do things that Access does automatically for you when
you use bound forms.

--
Brendan Reynolds (MVP)
(e-mail address removed)

Simon Harris said:
Hi Brenden,

Thanks for your reply - Is there any way round this?

My plan, after reading your reply was to do this:

- Unbind the form from the table
- My save button will then do this:
DoCmd.RunSQL ("INSERT INTO BLA....etc etc")

To confirm - When I open the form, a new record is created...this is visible
in the table after I close the form, as far as I know I dont have any code
that is 'committing' the record.

Thanks,
Simon.

Brendan Reynolds (MVP) said:
Well, that's what acFormAdd does - it opens the form at the new record,
ready for the user to start entering data.

A bound form always displays *some* record - either an existing saved
record, or the new, empty record. When at the new, empty record, the form
will display default values for any fields that have default values defined.
The record will not actually be committed to the database unless data is
entered, either by the user, or by code.

--
Brendan Reynolds (MVP)
(e-mail address removed)

Hi All,

I have a button on a form, when the button is clicked, this code is called
to open another form:

Private Sub TimeLogButton_Click()
If Len([Forms]![FRM_Switchboard]![SUBFRM_view_employees]) > 0 Then
stDocName = "FRM_order_time_log"
DoCmd.OpenForm stDocName, datamode:=acFormAdd
Else
MsgBox ("Please select employee first")
End If
End Sub

The form has various fields, which the user fills in, then clicks a
further
button, which runs this code:

Private Sub NewTimeLogEntry_Click()
If Me.JobsCombo.Value = 0 Or Me.EmployeeCombo.Value = 0 Or
IsNull(Me.hours_spent.Value) Then
MsgBox ("Please select job, employee and enter number of hours
before clicking add")
Else
JobsComboCurrentValue = Me!JobsCombo.Value
EmployeeComboCurrentValue = Me!EmployeeCombo.Value
IDateCurrentValue = Me!Idate.Value
'Save the new time log entry and update the time log list
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Forms![FRM_order_time_log]![TimeLog].Requery
DoCmd.GoToRecord , , acNewRec
Me!JobsCombo.Value = JobsComboCurrentValue
Me!EmployeeCombo.Value = EmployeeComboCurrentValue
Me!Idate.Value = IDateCurrentValue
End If
End Sub

Basically, the code saves the current record, and requeries the
Click to expand...
listbox
on
the form, which displays a summary of records stored.

Problem is this: When I open the form, it seems to add a new record
immediatley - Before I have even clicked my save button.

Thanks lots,

Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to
Click to expand...
another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

popup issue if no existing record 1
syntax error missing operator-open frm to spec. record 3
finding record from autonumber field 7
Access 2007 button code 4
Preview doesn't open current record 2
Launching Report from Form Only Shows Report for First Record 5
No match - Create a record 2
Copy Customer_ID from Current Record to Another Form/Table 13

Top