How to avoid adding row to a table when closing form

[JJ]

I have a set of fields on a form that are bound(using Control Source
property) to a table. Everytime I close the form by clicking X, it always add
new row to the table. Is there a way to avoid this without clearing out
Control Source property ?

Thanks,

JJ

 

[Dirk Goldgar]

In

I have a set of fields on a form that are bound(using Control Source
property) to a table. Everytime I close the form by clicking X, it
always add new row to the table. Is there a way to avoid this without
clearing out Control Source property ?

Assuming you don't have code in the form's Unload or Close event that
specifically adds a record, the most likely reason for this is that you
have code running, possibly in the form's Current event, that
automatically dirties the record (by assigning a value to one or more
fields). Since the record is dirty, it gets saved when you close the
form, even though you haven't otherwise modified it.

Check the code behind the form to see what you are doing to dirty the
record, and decide whether you really want to be doing that. For
example, you may be setting some control's value to a default value,
when you could set its Default Value property instead.

 

[JJ]

Dirk, none of form events is associated to code.

In

Assuming you don't have code in the form's Unload or Close event that
specifically adds a record, the most likely reason for this is that you
have code running, possibly in the form's Current event, that
automatically dirties the record (by assigning a value to one or more
fields). Since the record is dirty, it gets saved when you close the
form, even though you haven't otherwise modified it.

Check the code behind the form to see what you are doing to dirty the
record, and decide whether you really want to be doing that. For
example, you may be setting some control's value to a default value,
when you could set its Default Value property instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

In

Dirk, none of form events is associated to code.

Are you saying that if you open the form, then close it from the X
button -- without modifying any control on the form in any way -- it
creates a new record in the form's bound table?

I can't think of any way that could happen unless either code or a macro
modifies a control on the form, or else modifies the table directly.

 

[JJ]

Here's what actually happens. I launch the form, populate the fields, and
click a button to insert a row to a table. When I X out, it would create
another row with the set of info.

 

[Dirk Goldgar]

In

Here's what actually happens. I launch the form, populate the fields,
and click a button to insert a row to a table. When I X out, it would
create another row with the set of info.

Ah, I was picturing a different problem. It sounds like you may be
doing your own independent update of the table, and then Access is doing
the one that it automatically does when you close the form with a dirty
record. Please post the code behind the button that inserts a row into
the table.

 

[JJ]

Private Sub addTask_Click()
Dim Msg, Style, Response, MyString, SQL As String
Msg = "Are you sure to add this task ?"
Style = vbYesNo
Title = "Confirmation"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
SQL = "INSERT INTO ASSIGNMENT (resource, project, phase, task) VALUES
" & _
"([Forms]![Add Task]![resourceName],[Forms]![Add
Task]![projectName]," & _
"[Forms]![Add Task]![phaseName],[Forms]![Add Task]![taskName])"
DoCmd.RunSQL SQL
End If
Me![projectName].Requery
Me![phaseName].Requery
Me![taskName].Requery
End Sub

 

[Rick Brandt]

Private Sub addTask_Click()
Dim Msg, Style, Response, MyString, SQL As String
Msg = "Are you sure to add this task ?"
Style = vbYesNo
Title = "Confirmation"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
SQL = "INSERT INTO ASSIGNMENT (resource, project, phase, task)
VALUES " & _
"([Forms]![Add Task]![resourceName],[Forms]![Add
Task]![projectName]," & _
"[Forms]![Add Task]![phaseName],[Forms]![Add
Task]![taskName])" DoCmd.RunSQL SQL
End If
Me![projectName].Requery
Me![phaseName].Requery
Me![taskName].Requery
End Sub

Either use a bound form and get rid of this code (you don't need it)
OR
Use an unbound form and your code. Doing both is redundant and is why you are
getting two records.

 

[Albert D. Kallal]

Here's what actually happens. I launch the form, populate the fields, and
click a button to insert a row to a table. When I X out, it would create
another row with the set of info.

Just remove your code all together. When you close the form, ms-access posts
(writes) the data to the table for you.

If you want a button to save the data (post it), then just go

if me.dirty = true then
me.dirty = false)
end if

If you want to close the form without saving the data, go

if me.dirty = true then
me.undo
end if

Really, you don't need any code at all, and you only using up time and
resouces that could be used to feed the poor...

 

[JJ]

It works. Thanks.

Private Sub addTask_Click()
Dim Msg, Style, Response, MyString, SQL As String
Msg = "Are you sure to add this task ?"
Style = vbYesNo
Title = "Confirmation"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
SQL = "INSERT INTO ASSIGNMENT (resource, project, phase, task) VALUES
" & _
"([Forms]![Add Task]![resourceName],[Forms]![Add
Task]![projectName]," & _
"[Forms]![Add Task]![phaseName],[Forms]![Add Task]![taskName])"
DoCmd.RunSQL SQL
End If
Me![projectName].Requery
Me![phaseName].Requery
Me![taskName].Requery
End Sub

In

Ah, I was picturing a different problem. It sounds like you may be
doing your own independent update of the table, and then Access is doing
the one that it automatically does when you close the form with a dirty
record. Please post the code behind the button that inserts a row into
the table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)