Help with DataEntry in a form

[Rob]

Hi all,

I currently have a form that I have set for DataEntry to allow a user
to create a new record. I have disabled the navigation buttons to
allow a custom 'Save' routine to be developed. I added a command
button to the form to allow the record to be saved using the wizard.
The wizard uses the DoCmd.DoMenuItem method, however I am concerned
that this will not be valid when I change menu bars later.
Consequently, I developed some code to run a Db.Execute command (see
below) to add the record manually.
--------------------------------
For Each Ctrl In Me.Controls
If TryGetControlValue(Ctrl) = True Then 'Check for
editable controls
If IsNull(Len(Ctrl.Value)) Then
boolNull = True
End If
strCol = strCol & Ctrl.ControlSource & ","
strVal = strVal & "'" & Ctrl.Value & "',"
End If
Next Ctrl

' Strip the additional comma from the string and add brackets
strCol = " (" & Left(strCol, Len(strCol) - 1) & ")"
strVal = "(" & Left(strVal, Len(strVal) - 1) & ")"

If boolNull = True Then
MsgBox "Data error. Please try again", vbInformation, "Data
error"
Else
strSQL = "INSERT INTO " & Me.RecordSource & strCol & " VALUES
" & strVal
Debug.Print strSQL
db.Execute strSQL
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End If
----------------------------------------------

This code seems to work well until I attempt to close the form. In
this case I get a "Duplicate record" error which I am sure is related
to the DataEntry property.I have experimented with Me.Dirty however
this does not work either.

Any help anybody could provide would be greatly appreciated,

Cheerss

 

[Jeanette Cunningham]

Rob,
in a bound form (has a recordsource) the data is saved to the table by
access when you move to another record or close the form.
So remove the code which adds the record using Db.Execute - no point writing
code to do something that access already does for you.

To save a record using a Close button, use the form's dirty property,
something like this:

Private Sub cmdClose_Click()
If (Me.Dirty = True) Then
Me.Dirty = False
End If
Docmd.Close acForm, Me.Name.

However, the record is automatically saved when you go to another new
record.
If the form didn't have a close button, but users just clicked the red X in
top RH corner, you wouldn't normally need any code to save the record.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Rob]

Thanks for your reply. The reason that I needed a "save" button was
that I was sending an email containing data from the form to a user.
I have adopted your suggestion and rather than generating the SQL, I
will use the "save" button to programatically close the form and let
Access save the record for me! I haven't coded is some years and it
is taking me some time to get my thoughts back to correct processes!

Thanks again!

 

[Jeanette Cunningham]

Rob,
you will need to save the record before you send the email.

Your save button could first save the record, send the email then close the
form.


Jeanette Cunningham -- Melbourne Victoria Australia


Thanks for your reply. The reason that I needed a "save" button was
that I was sending an email containing data from the form to a user.
I have adopted your suggestion and rather than generating the SQL, I
will use the "save" button to programatically close the form and let
Access save the record for me! I haven't coded is some years and it
is taking me some time to get my thoughts back to correct processes!

Thanks again!