Access is saving records I don't want to save

[SCubed]

I am trying to understand why Access is saving blank records and saving when I'm not calling any save logic! One cause of blank record saving is the following code:


Private Function funcClearFields()

Dim ctrl As Control

For Each ctrl In Me.Controls
With ctrl
Select Case .ControlType

Case acComboBox
ctrl.Value = ""
.Requery
Case acTextBox
ctrl.Value = ""
.Requery
Case acMemoBox
ctrl.Value = ""
.Requery
Case acListBox
ctrl.Value = ""
.Requery
End Select
End With

Next

OptGrpAssetOwner.Value = ""

End Function

After it executes this, I have a blank record. Why does Access do this?

Thank you in advance.

SCubed

 

[MakeItSo]

You have it because you have cleared all fields.

If you have a form with bound controls, you directly alter the table - no need to save. That's what DBs behave like...

If you want to avoid this, you need unbound controls on your form and an extra "Save" button with an "INSERT INTO" resp. "UPDATE" SQL Statement (depending on if you've altered a record or created a new one)

Does that answer your question?

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[SCubed]

Just to clarify that I understand...

If I use bound controls in order to let the user select valid options from the database, Access is going to update that information regardless if I tell it to or not? When does this happen? What is my alternative for letting the user select valid options? I think I'm combining too many options. Let me briefly tell you what I have done:

The forms' source is linked to the Moves table.
A combobox's source is linked to the EquipmentType table. I have bound it to the EquipmentType field on the Moves table.
Another combobox source is linked to the Asset table based on the selection in the EquipmentType table. I have bound it to the AssetID on the Moves table.
3 other textbox fields are there to let the user enter applicable information. These are also bound to applicable fields on the Moves table.

When the user clicks the SAVE button, I want to save the information as a NEW record in the Moves table.

What am I doing wrong?

 

[MakeItSo]

You obviously have a wrong understanding of what a database is (at least an access db) and what forms do.

Unbind all controls (You can leave the row source property for the combos)

Adapt the following code to be put in the Save button AfterUpdate:

Dim sSQL as String
sSQL="INSERT INTO Moves (EquipmentType,AssetID,...) VALUES ('" & Me!Equipment & "', " & Me!Asset & ", '"... & ")"
DoCmd.RunsSQL

 

[SCubed]

Thank you for your response. I have a ton of experience with databases and programming language, but definitely NOT with Access forms. The development tools I've used did not handle binding in this manner. That was my confusion. Thank you for clearing it up.