Getting a Validation Rule error when I don't have any validation

[dverdi]

Hello,

Over the last two days an error has developed in the Access 2003 application I am developing. I can't seem to get rid of it, or work my way around it.

Essentially I have a Form that has two modes, browse data and add new record.
When in browse mode, it draws in data from several tables. It took me a while, but I finally worked out a great huge SELECT statement to do this, using several LEFT JOIN clauses.

However, it seems that you can't add a record when there are Left Joins.
So in the Add new record mode, I change the Form.RecordSource to a simple SELECT statement -
"SELECT * FROM [Parts Data]"
and I Lock and remove the ControlSource of all the controls that are not Bound directly to this [Parts Data] table. Then I can successfully make changes on the form (I only want to edit the fields bound to the [Parts Data] table).

Then I try to Save the record (or even abort about by a DoCmd.RunCommand acCmdUndo) and then the problem occurs:

First I save the record, or Undo as the case maybe.
Secondly I restore the Form RecordSource to the original great huge SELECT statement.
Finally, I reset the controls so that they are once again Bound.

However, the instant I change the Form.RecordSource back to the huge SELECT statement, I am hit with Error number 2107:
The value you entered does not meet the validation rule defined for the field or control

None of my controls have any Validation Rules defined.
As far as I can tell, none of the underlying tables have Validation Rules defined either.

Any suggestions as to what is causing this error?

 

[dverdi]

Addenda to the above problem.

I have no problem going from the browse data mode with the huge SELECT statement to the add record mode with the simple SQL statement

However, whatever I change the RecordSource to after the Add record phase causes the error.
Even if I change the RecordSource to

 

[DrSimon]

Check the data type properties of the fields that are joined. I suspect that if they are not compatible you would get that error.
Simon Rouse

 

[dverdi]

Sorry Dr Simon,
I don't really understand this advice
Most of the DataType properties are text, with one Date and a couple of Numbers that all Bind to textboxes, and a couple of Booleans that Bind to check boxes.

Besides, what does that matter in the case when I set the Form.RecordSource = ""?

As a bit of background, I have only done simple SQL queries to Access databases prior to this using VB3, many years ago. This is the first time I have actually made an application in MS-Access, or used DAO or any of the other new features that have appeared in the last 10 years.
I am making this up by trial and error, and some very frustrating attempts to find relevant information in the on-line MSDN database.

Dale

 

[DrSimon]

It's difficult to see what the problem is but it may well be that the query is not updateable because of the joins. Have you checked that?

 

[dverdi]

Somewhere in my fiddling with the code I half solved the problem.
I can now successfully Save the new record. However, I still can't successfully "Not Save" the new record and go back to browse mode.

However, because I was doing much of my testing by going through the "Not Save" section of my code, I don't know when I managed to get the Save record section working.

To enter the Add new record mode from Browse mode, currently I do the the following:
1) Unbind all the controls.
2) re-Bind only the controls that will be using the simplified Form ControlSource.
3) Unbind the Form from the database Form.RecordSource = ""
4) Bind the Form to a single table Form.RecordSource = "SELECT * FROM [Parts Data]"
5) Make a new record DoCmd.GoToRecord acDataForm, Me.Name, acNewRec


When I choose not to Save this New record, I try to do things in the following order:
6) Undo the new record DoCmd.RunCommand acCmdUndo
7) Unbind all the controls
8) Unbind the Form from the database Form.RecordSource = ""
9) Bind the Form to multiple tables Left Joins as before.
10) Bind all the controls to the multi-table possibilities as before.


It is at 8) that the application throws a wobbly and won't do what it is supposed to.
Am I doing 6) to 10) in the wrong order?
Is 6) the wrong command? I have tried DoCmd.RunCommand acCmdDeleteRecord

 

[DrSimon]

Sorry Dale, can't really help with the undo. I've never used it in Access, but have you thought about using commit and rollback as an alternative approach?

 

[dverdi]

I haven't heard of commit or rollback before.
The current method I am trying is to make two copies of the form - one devoted to browsing the records, and one devoted to adding/editting records, and flip between them via buttons.
That way I don't have to worry about changing record and control sources. Indications are that this may work.

I just thought there might have been a more elegant solution.
Thanks for looking into the problem.