'False' error message 'The value that you entered isn't valid for this field'

[BrianBoyes]

I have a (fairly) simple database with;
Main table (call this DNT)
a subtable (DRT) with full referential integrity to DRT
a 3rd level table (D2T) with full ref integrity to DRT but is a multi - multi as its only other field (apart from date and time stamps) is linked to the primary key in another table (TNT).

All 4 tables are represented in a single master form with DRT & D2T sub & sub-sub forms and TNT independent (displayed to make addition of look-up values simple).
For each unique value of DNT+DRT there can be multiple values in D2T entered by menas of a combo box with look-up values given from TNT.

All this was working as required until I ran first an update query (to insert values in a previously empty field in TNT) and an append query, also to the table TNT.

Now, each time that I enter revised data in the form for DNT, DRT or D2T I get the (false) error message "The value that you entered isn't valid for this field" (Error Number -2147352567). On closing the error message the data is duly entered and the record posted.

Data entry is thus rendered both slow and frustrating

I imagine that there is (as ever) a simlpe solution but, if that cannot be found is there somewhere that I can put some code to automatically close the error message box ? That might not be a "pure" approach but would remove the annoyance and improve productivity.

 

[Adam Caramon]

If you're absolutely certain that the error message needs to be ignored (I'd research it more personally), you could try something like:

       With DoCmd
            .SetWarnings False
            .OpenQuery "NameofQuery"
            .SetWarnings True
        End With

 

[RainLover]

I would like to see you fix your problem rather than create a poor work around. It will come back one day and bite you.

A good start would be to introduce some better naming conventions. That will make it easier for you to explain your problem and for others to understand.

There are pleanty examples on the net for you to look at.

Also I have a link in my signature. You can find an example there.

 

[BrianBoyes]

Thank you for the response, I too always prefer to solve a problem rather than smear over it, I am just having to balance the triple conflict of having to produce a result at the same time as developing the database and simutaneously learning Access (having been used to Borlands Paradox)!!
> I have attatched 3 pdf files,
> Object definition for relationships
> Object definition for forms (extract of pages relating to combo box look-up
Pictorial of relatonships for overview. I imagine that it is possible to improve the relationships.
The look-up combo box that might be implicated in the problem (perhaps in concert with the date & time stamp fields in all tables) is on Page 63 of the forms report.
The database is not fully normalised (as you can see) as I preferred to see some of the relationships with actual data rather than autoInc numbers, particularly during my educational/development phase.

 

[RainLover]

All 4 tables are represented in a single master form with DRT & D2T sub & sub-sub forms and TNT independent (displayed to make addition of look-up values simple).

For each unique value of DNT+DRT there can be multiple values in D2T entered by means of a combo box with look-up values given from TNT.

I have no idea what you are talking about. Can you write in plain English.
represented in a single master form with DRT This makes no sense at all. Sorry, I must be offending you. You write as though your audience has the exact same experience as you. Sort of like that honky stuff the dropouts talk in America.

All this was working as required until I ran first an update query (to insert values in a previously empty field in TNT) and an append query, also to the table TNT.

Where is this table TNT. It is not in the Relationships Pic.
Your Naming Conventions are very poor. How do I tell the difference between the name of a Table to a Query or Form etc.
You use spaces and special characters. This will force you to put these in Square Brackets when there is normally no need to.

The database is not fully normalised (as you can see) as I preferred to see some of the relationships with actual data rather than autoInc numbers, particularly during my educational/development phase.

You have glossed over the most important part of database design as though it does not matter.

What are you going to do with the Date Stamp and the Time Stamp? Do you have a plan or is this something you thought might help. Some people think they can go back to find when something went wrong. This is a waste of time. You need to know what went wrong, fix it and move on.

You have a table filled with Field names all starting with DOC or DTZ or TAG. You would be better off using the space to create more meaningful names.

The SQL for the Combo Box appears to be correct.

I have been a little hard on you because you do have previous experience and should know better.

One last thing. Creating Relationships. Always enforce Referential Integrity. Do not cascade. Never use any other type of join other than both side equal. Number One. If you need to then change this when creating your query.

 

[BrianBoyes]

No offence taken, reponse is appreciated.

The relationship between Table names in the attached files and the orriginal post are;
Doc_Master (was referred to as DNT)
Doc_Master_Rev (was DRT)
Doc2Tag (wasD2T)
Tag_Master (was TNT)

The reason for starting field names with a ref to the table is to make them very clear in subsequent use, especially for date fields where there are fields with the same function in each of the tables.
The post and update date stamps are for use in queries as there will be results that change according to these dates. (There will be a monthly data download that has to detect all new/revised records since the previous data download. (I can dispense with the timestamp).

I will not be able to open the database now until Monday but will start with the relationships.

 

[RainLover]

You want to Make things clear. This is good.

With Primary Keys and Foreign Keys you should use the same name for both. Some add ID to the end. I prefer PK or FK as a suffix.

Go back to Post #3 and follow my suggestion. That will give you something to do on the week end.

 

[BrianBoyes]

Since the last post, I have completed the core of the database except for one remaining look-up table. The monthly data exports are workig as required (making use of the datestamp fields). Append queries are working thus speeding up data population.
However, I still have the errror message. This I suspect is because the code is executing at the wrong point in the posting sequence.
The record revised datestamps run from code (found on the web) attached to the 'Before Update' Event (as the website stated). Perhaps it should be attached to a different event in order that validity checks are completed before the datestamp is entered (or visa versa) ?
The resolution of this irritating message would free up time to progress with the first round of refinements (enhanced automation).
Post #3 does not address this particular point. (The offer of using the embedded signature link is not available until there have been 10 posts, any reply to this will thus reach that trigger).

 

[RainLover]

The record revised datestamps run from code (found on the web) attached to the 'Before Update' Event (as the website stated). Perhaps it should be attached to a different event in order that validity checks are completed before the datestamp is entered (or visa versa) ?

If you are using the Event Before Update of the Form, then this is the correct place to put it.

The "validity checks" could go in the same Event at the beginning. Then if it fails exit the routine before the Date Stamp part.

 

[BrianBoyes]

Problem solved !

The VBA code to trigger auto date & timestamp was:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err
' Set bound controls to system date and time.
DateModified = Date
TimeModified = Time()
BeforeUpdate_End:
Exit Sub

BeforeUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End
End Sub

When time field code modified to
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err
' Set bound controls to system date and time.
DateModified = Date
TimeModified = Now() - Date
BeforeUpdate_End:
Exit Sub

BeforeUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End
End Sub

it works without error message.

 

[boblarson]

I would suggest the usage of a SINGLE Date/Time field which simplifies matters and is way easier to use in other things, including calculations. Splitting them into different fields is not so good and can be a pain in the backside.