Problem with trying to force and validate w/Save button

[jangalky]

I am totally confusing myself at this point. I have Access 2007 FE with SQL Server 2005 BE. I have linked the tables and using ODBC DSN connection.

I have a bound form with a PTQ as it's record source. This form contains a Save button, New Button and Close button. The detail of the form contains a two page tab control. This same form is used to edit existing records or add new records depending on how the form is called.

I have a "save" button as I want to validate that they have entered necessary data (I have my db set up with PK on each table and foreign key relationships to also insure data integrity). When the Form Dirty event is fired, I enable the "Save" button. On the click event of the Save button I have the following code:

Me.ChkOK2Save = True
If Me.Dirty Then
Me.Dirty = False
End If

This forces the Form_BeforeUpdate event with following code (code will be improved but this was written just as a quick check for flow thru and how it would work:

If IsNull(Me![Customer_Name]) Then
StrMsg = " Customer Name "
End If

If IsNull(Me![Customer_Status]) Then
StrMsg = StrMsg + " Status "
End If

If IsNull([Customer_StateCode]) Then
StrMsg = StrMsg + " State "
End If

If StrMsg > "" Then
MsgBox (" The following fields MUST be entered: " & StrMsg)
Cancel = True 'this will cancel update but leave info entered
Me.ChkOK2Save = False

End If

Seems to work fine but after BeforeUpdate code executes I receive "Error 2101 You entered an expression that has an invalid reference to the property" and highlights the "Me.Dirty = False" in the Click event of the save button.

I have tried different ways of handling with no luck.

Any help in what I am doing wrong would be greatly appreciated!

 

[lameid]

You can't edit a Pass-Through Query data so you can't edit a form record that has one as it's source. Fix that and it may work...

You can link a view or a table as a table and use it as your recordsource and then it would be updatable.

The another way to go would be to use an unbound form, populate the data and update the data on the server.

Finally there is a technique using an offline recordset that you later update back to the source data (I've not done it and the best terms are not coming to me).

 

[MazeWorX]

Validating the data in a form can be handled by looping through the controls on a form checking the Tag properties of the control. code below loops through the controls and any control that contains no data and has a tag "Needed" highlights that field in yellow and notifies the user with one message box As Lameid has suggested fix that first

Private Sub cmdSave_Click()
On Error GoTo Err_CmdSave_Click
Dim txtMessage As String


For Each ctl In Me.Controls
If ctl.Tag = "Needed" Then
If Trim(ctl.Value & "") = "" Then
'Highlight missing data fields
Me(ctl.Name).BackColor = vbYellow
blnError = True
Else
Me(ctl.Name).BackColor = vbWhite
End If
End If
Next

If blnError = True Then

txtMessage = "All Required fields must be entered before the record can be added." & vbNewLine & _
"Enter a value or press 'Cancel' to abort the record."
MsgBox txtMessage, vbOKCancel

Else
"Save Code goes here"
end if

You would have to add additional code to reset the backcolor of the textboxes to white.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work