More Strange Behavior (1 Viewer)

[gakiss2]

The issue is that I get an error when a form is refreshed. Debug shows the Me.refresh line of code. The 'error' is that the due date is not a weekday.

I tracked that back to a validation rule that I had put into the table. It was behaving properly, I just didn't want that behavior any more. I wanted to allow users to use the due date on a weekend if they like.
The table is linked So I deleted the Validation Rule from the back end table first.
Same behavior.
I found the validation rule showing up in the Linked Table on the front end. I had thought the deletion of the validation rule on the Back end would carry forward, apparently wrong I deleted that validation rule as well.
Same behavior, throws an error if I put in a due date that is on a weekend.

The only thing that I can think of that I haven't done is to delete the due date field in the source (back end) table. I'm holding off because it would be some work to restore all the due dates. That's fine if that will fix the problem but I was hoping I could get an Amen or even another easier way to get Access to stop obeying a rule that isn't there anymore.

I could attach but not sure that will help. the only code that I can imagine is Me.Refresh. I can say that I can remove the Me.Refresh line the error does not come up but then a function of the database doesn't work (refresh is needed to stimulate a 'save' for a Document Change history using Access Append). Also it does not change the due date and does not warn the user it didn't change. Arguably an even worse outcome.

Let me know if there is some attachment you need.

Thank You for your time helping with this issue.

 

[theDBguy]

Hi. Have you checked if there are any validation rules in your forms using the same table?

 

[Isaac]

I found the validation rule showing up in the Linked Table on the front end. I had thought the deletion of the validation rule on the Back end would carry forward, apparently wrong I deleted that validation rule as well.

Re-link the table?

 

[gakiss2]

Re-link the table?

I made the linked table a local table. I deleted it. then I made a new linked table. Same behavior.

 

[gakiss2]

Hi. Have you checked if there are any validation rules in your forms using the same table?

I checked all forms in the database and looked for anything referencing DueDate and even ended up deleting a couple forms I didn't need. I DID find a validation in the form with the bad behavior but that was >Date(). Still I deleted that as well just to be sure, same behavior.

 

[gakiss2]

I went back to check behavior and got the same but I also noticed the error message says error '3316'. Not sure if that adds but <shrug>. Also exact text of the error box after Run-time error '3316': is: You Entered a Due Date on Saturday or Sunday - Please correct.
I am almost certain that that is the text I had typed into the box for Validation Text on the Table Design and have since deleted.

 

[theDBguy]

I went back to check behavior and got the same but I also noticed the error message says error '3316'. Not sure if that adds but <shrug>. Also exact text of the error box after Run-time error '3316': is: You Entered a Due Date on Saturday or Sunday - Please correct.
I am almost certain that that is the text I had typed into the box for Validation Text on the Table Design and have since deleted.

Maybe it's time to share a copy of your db, if possible.

 

[The_Doc_Man]

Debug shows the Me.refresh line of code.

According to what I could research, the .Refresh only works with what is "Current" - I.e. it is like a "localized" requery only affecting the current record. Which limits the scope of data and data properties that could be causing it. By the way, you say it throws an error. What exactly is the error it throws?

EDIT: Your post and mine crossed paths. No need to answer that.

The underlying table, any queries, the form's relevant bound or computed controls, and the form's class module (i.e. event code or functions) are the only players in this mix. You are looking at field properties and constraints for the offending field, but did you ever use one of the more complex cross-field validations? You could have seen something about that on the forum since the question comes up from time to time.

If you have looked through all of these and found nothing then the problem may be a subtle type of corruption. If a Compact & Repair doesn't clean it up, the next thing would be to rebuild the table - but you did that. In the extreme case, you might have to create new empty databases and export/import everything to the new files.

 

[MajP]

I am going to take a wild guess, but based on what you described I would not be surprised that you have a copy of your backend. The backend table you are modifying is not the backend you are linked to. Double, triple check this.
Those are the kind of mistakes that make you think you are going crazy. Did it recently.

 

[gakiss2]

Maybe it's time to share a copy of your db, if possible.

No prob. It will take a minute. So I copied the back end, gave it a different name. Deleted the DueDate Field. Added a new field DueDate. Of course I did NOT add anything to the Validation Rule for the new DueDate field. I went to the front end. Deleted the linked table. Then made a new linked table and linked to table previously mentioned which had NOTHING in the Data validation for DueDate. Exact same behavior, looking for a weekday.

So, better to share the database I started with or the new one I made then deleted the due date field? And You will need a backend db. Same question, before or after due date delete? I hadn't put data in the newly added DueDate(after deleting it) so I'm sure that will cause issues with some things running right (I know in at least some circumstances a non null entry is required for DueDate - in code).

 

[gakiss2]

I am going to take a wild guess, but based on what you described I would not be surprised that you have a copy of your backend. The backend table you are modifying is not the backend you are linked to. Double, triple check this.
Those are the kind of mistakes that make you think you are going crazy. Did it recently.

Earlier today I was doing some trouble shooting in which I would make a form move then check the result in the back end table. I didn't always see what I expected but I did clearly see data going into the back end that I personally had put in the form on the front end. And I had deleted the linked table then made a new one and linked that new one to the back end. So all in all I am fairly certain I am pulling from the table that I think I am pulling from. Now it DID have the weekday validation rule at one point in the recent past but I deleted it and have since done compact and repair to the back end and the front end.

 

[theDBguy]

No prob. It will take a minute. So I copied the back end, gave it a different name. Deleted the DueDate Field. Added a new field DueDate. Of course I did NOT add anything to the Validation Rule for the new DueDate field. I went to the front end. Deleted the linked table. Then made a new linked table and linked to table previously mentioned which had NOTHING in the Data validation for DueDate. Exact same behavior, looking for a weekday.

So, better to share the database I started with or the new one I made then deleted the due date field? And You will need a backend db. Same question, before or after due date delete? I hadn't put data in the newly added DueDate(after deleting it) so I'm sure that will cause issues with some things running right (I know in at least some circumstances a non null entry is required for DueDate - in code).

Hi. For now, I just want to see the FE and the forms in it that are based on the table with the DueDate field. Thanks.

 

[MajP]

. So all in all I am fairly certain I am pulling from the table that I think I am pulling from

Go to the linked table manager and look at the path to be 100% certain. May want to rename the back end just in case.
Also make sure to check "always prompt for new location" just to be double sure.

 

[gakiss2]

According to what I could research, the .Refresh only works with what is "Current" - I.e. it is like a "localized" requery only affecting the current record. Which limits the scope of data and data properties that could be causing it. By the way, you say it throws an error. What exactly is the error it throws?

EDIT: Your post and mine crossed paths. No need to answer that.

The underlying table, any queries, the form's relevant bound or computed controls, and the form's class module (i.e. event code or functions) are the only players in this mix. You are looking at field properties and constraints for the offending field, but did you ever use one of the more complex cross-field validations? You could have seen something about that on the forum since the question comes up from time to time.

If you have looked through all of these and found nothing then the problem may be a subtle type of corruption. If a Compact & Repair doesn't clean it up, the next thing would be to rebuild the table - but you did that. In the extreme case, you might have to create new empty databases and export/import everything to the new files.

You mentioned queries. I hadn't looked there. I will. Cross field validation? No, don't think so. I only check if the day number was a weekday. I did have a 'double validation' at one point. The table had the weekday thing and the form had a greater than today validation. They are both gone now of course. Just didn't know if that would add to the finding the issues.

in another post I mentioned the run-time number as 3316. google just said it was a date validation.
You mentioned a few things I don't know about such as bound and computed controls and class module. Seems doubtful but I guess you never know.

One point I hadn't got to: the fields after update call a Document history sub and that code is actually firing the Me.refresh. Its needed to update the document history field as I am using the long text/ Append method for that.

 

[The_Doc_Man]

Near as I can tell, 3316 is a table-based validation violation but Access somehow "neatens up" the error to show the violation rule verbally.

With regard to your history sub: Is that in the form's class module or a general module? Because "Me." syntax doesn't work so well in a general module. I might have expected a different error though. And the fact that code is involved makes me want to suggest that you look up /DECOMPILE as the first step in a way to force a recompile of the DB.

 

[gakiss2]

Hi. For now, I just want to see the FE and the forms in it that are based on the table with the DueDate field. Thanks.

 

[gakiss2]

Here is the latest working. Working doesn't mean the behavior for the DueDate field is correct. As far as I know that has always been the case. A bit more background. Some time after I had put in the input validation, I coded such that the due date was just always calculated to be on a weekday. So it has been a long time since that validation even got put to use. Later(recently) I added some customization so each user could say how many default days to add for the due date per document type. At that level of complexity I just decided to heck with Due Dates only on weekdays. So now the due date isn't calculated to be a weekday, as expected. I had forgotten all about the data validation but when it popped up I thought I could just take it out of the table.

I suppose I could go back to calculating the next weekday after the new default days are added. Probably easier that solving this monster. But then how could we look back with pride that we solved this unrelenting mystery )

I'm about done for the day. I appreciate all your help. I may work a bit on it at home, or maybe drink some bourbon. Haven't had much luck doing both at the same time. ).

 

[gakiss2]

Near as I can tell, 3316 is a table-based validation violation but Access somehow "neatens up" the error to show the violation rule verbally.

With regard to your history sub: Is that in the form's class module or a general module? Because "Me." syntax doesn't work so well in a general module. I might have expected a different error though. And the fact that code is involved makes me want to suggest that you look up /DECOMPILE as the first step in a way to force a recompile of the DB.

Yes , I'm convinced its table based. The validation text is exactly as I had originally programmed it. It being there in the first place isn't a mystery. I want to get rid of it so I deleted the line for data validation and validation text form the table design view. Expected it to go away but it won't.

I may have abbreviated too much. the actual line is : Forms("frmDocDetail").Refresh. I was using me.refresh as a quick way to communicate it. The form called out is correct.

I will look at Decompile as a means of learning more. I may just surrender and program my way around it by calculating a weekday due date. then the only issue would be when a user intentionally chose a due date which they rarely do and then not sure why they would CHOOSE a weekend. but users sometimes do unpredictable stull. At any rate I learn more by solving probs than working around them.

 

[gakiss2]

Yes , I'm convinced its table based. The validation text is exactly as I had originally programmed it. It being there in the first place isn't a mystery. I want to get rid of it so I deleted the line for data validation and validation text form the table design view. Expected it to go away but it won't.

I may have abbreviated too much. the actual line is : Forms("frmDocDetail").Refresh. I was using me.refresh as a quick way to communicate it. The form called out is correct.

I will look at Decompile as a means of learning more. I may just surrender and program my way around it by calculating a weekday due date. then the only issue would be when a user intentionally chose a due date which they rarely do and then not sure why they would CHOOSE a weekend. but users sometimes do unpredictable stull. At any rate I learn more by solving probs than working around them.

Quick question. are you talking about decompile for the front end? or back? or both??

 

[theDBguy]

Here is the latest working.

Okay. I searched through the forms you provided but didn't find any validation rules other than the >Date() for DueDate in frmDocDetail. I also checked the VBA code but didn't find anything suspicious there either.

Not sure if that's helpful, since you probably knew that already. Next step would be to have the data as well and make the forms function for us. Right now, I just checked them in Design View.