Validate form data before saving record?

[adamskiii]

I am starting to learn to use form is access and have created an unbound form
because I believe you have more control over table records. My question is,
how do I validate fields on my form first and then once all validation are
correct and no errors appear it saves the record? This is currently my code:

Option Explicit
Option Compare Database

Dim connection As New ADODB.connection
Dim part As New ADODB.Recordset

Private Sub Form_Load()
Set connection = CurrentProject.connection
part.Open "SELECT * FROM part ORDER BY partID", connection, _
adOpenDynamic, adLockOptimistic

populateForm

BrowseMode (True)
End Sub


Private Sub populateForm()
If part.EOF Then
part.MoveLast
ElseIf part.BOF Then
part.MoveFirst
End If

Me.txtPartId = part.Fields("partId")
Me.txtCost = part.Fields("cost")
Me.txtDescription = part.Fields("description")
Me.txtOnHand = part.Fields("onHand")
Me.txtOnOrder = part.Fields("onOrder")
Me.txtListPrice = part.Fields("listPrice")
Me.cmboVendor = part.Fields("vendorID")
End Sub

Private Sub BrowseMode(value As Boolean)

Me.txtCost.Locked = value
Me.txtDescription.Locked = value
Me.txtListPrice.Locked = value
Me.txtOnHand.Locked = value
Me.txtOnOrder.Locked = value
Me.txtPartId.Locked = value
Me.cmboVendor.Locked = value

Me.cmdFirst.Enabled = value
Me.cmdLast.Enabled = value
Me.cmdNext.Enabled = value
Me.cmdPrevious.Enabled = value

If value Then
Me.cmdNext.SetFocus
Else
Me.txtPartId.SetFocus
End If

Me.btnAdd.Enabled = value
Me.btnCancel.Enabled = Not value
Me.btnEdit.Enabled = value
Me.cmdExit.Enabled = value
Me.btnSave.Enabled = Not value

End Sub


Private Sub cmdFirst_Click()
part.MoveFirst
populateForm
End Sub

Private Sub cmdLast_Click()
part.MoveLast
populateForm
End Sub

Private Sub cmdNext_Click()
part.MoveNext
populateForm
End Sub

Private Sub cmdPrevious_Click()
part.MovePrevious
populateForm
End Sub

Private Sub btnCancel_Click()
BrowseMode (True)
part.CancelUpdate
populateForm
End Sub


Private Sub btnEdit_Click()
BrowseMode (False)
End Sub

Private Sub btnAdd_Click()
BrowseMode (False)

part.AddNew
populateForm
End Sub

Private Sub btnSave_Click()

part.Fields("partId") = Me.txtPartId.value
part.Fields("cost") = Me.txtCost.value
part.Fields("description") = Me.txtDescription.value
part.Fields("onHand") = Me.txtOnHand.value
part.Fields("onOrder") = Me.txtOnOrder.value
part.Fields("listPrice") = Me.txtListPrice.value

part.Update
BrowseMode (True)
End Sub

Private Sub cmdExit_Click()
DoCmd.Close
End Sub


In the btnSave_Click() I want it to first check a function called EDIT for
all validations. Once all have passed and no errors appear then the form data
will be saved. How can I do this? I tried adding the EDIT function:

Private Function edit(editMe As Boolean)
If (Me.txtCost >= Me.txtListPrice) Then
MsgBox "ERROR"
End If
End Function



And then in my btnSave_Click() I added:

Private Sub btnSave_Click()
if edit = True Then
part.Fields("partId") = Me.txtPartId.value
part.Fields("cost") = Me.txtCost.value
part.Fields("description") = Me.txtDescription.value
part.Fields("onHand") = Me.txtOnHand.value
part.Fields("onOrder") = Me.txtOnOrder.value
part.Fields("listPrice") = Me.txtListPrice.value

part.Update
BrowseMode (True)
End If
End Sub

But this is not working. Any help?

Thanks

 

[Mr. B]

Create a user defined function that will do the verification for every
control on your form, verifying what ever your needs are and when the
criteria is met, make the button that saves your data to your table to be
enabled, else make the save button disabled.

Now with that said, I would like to ask you to consider the fact that Access
forms and/or controls have plenty of properties to allow you to manage
records. I realize that your desire to use unbound forms is certainly your
perogative but you really have to do much more work than is necessary to
accomplish the same goals when you do not use the built-in functionality of
Access.

Just my two cents worth. And that's really about all it's worth.

Good luck with your project.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Tom van Stiphout]

On Tue, 16 Mar 2010 17:09:02 -0700, adamskiii

Towards the bottom of your code:
if edit = True Then
This "edit" seems to refer to a function you wrote, but that function
takes an argument. So you should pass a boolean argument.

The larger issue is that you are doing WAY TOO MUCH work, and that
especially as a beginner you should use bound forms and very little
VBA. Then when your "believe" comes true and you need more control,
you can post back here and we can help you.

-Tom.
Microsoft Access MVP

 

[Maurice]

Return the value in your function. The function should return something.

Private Function edit(editMe As Boolean) as boolean
If (Me.txtCost >= Me.txtListPrice) Then
MsgBox "ERROR"
edit=false
else
edit=true '- all fields ok..
End If
End Function

This will return either a true or false for the 'edit'

Start the save with edit=false

so:
Private Sub btnSave_Click()
dim edit as boolean
edit=false 'we start by not saving before checks have been done.

(....)

hth

 

[Linq Adams via AccessMonster.com]

I'd have to agree with Tom! A big part of the reason to use Access for
database development is the speed with which it can be created, using bound
forms. Several developers I know, experienced in Visual Basic database
development and Access development, estimate that development using unbound
forms by ***experienced*** developers takes twice as long as it does when
using Access and bound forms.

The belief that unbound forms are necessary in order to do data validation is
simply not true!

If you insist on using unbound forms, you'd be far better off using a
straight VB or C++ front end with a SQL Server or Oracle back end.

You can create an EXE file which gives total protection to your code/design

You can distribute the db to PCs without a copy of Access being on board

Your data security if far, far better than anything you can do in Access