New_Transactions_Not_Working (1 Viewer)

[access2010]

Until this week, we could enter new buy or sell records, but for some reason now, new records are not being created.

Could I please be advised as to what has happened and how would you suggest to fix this problem for our Access 2003 database?

Thank you.
Crystal

 

[conception_native_0123]

Crystal you provided very limited amount of information. Perhaps the best thing to do is to upload a copy of your database so people can take a look. I would assume my suggestion is shared with others too.

 

[jdraw]

Agree - a copy of the database with instructions and some sample data would be helpful.
Has there been any update(s) to the code, the data or the PC/network?
Do you have a backup of the "working" system?

 

[access2010]

Thank you, all for your notes.
I had thought that the file causing us the problem was uploaded.

Till recently, we could add new records by Clicking the New Button, but this past week, the New Button does not work

Your suggestions on how to fix this problem WILL be appreciated/

Please find our problem 2003 database attached attached.

Crystal.

 

[access2010]

Crystal you provided very limited amount of information. Perhaps the best thing to do is to upload a copy of your database so people can take a look. I would assume my suggestion is shared with others too.

Thank you, conception_native_0123, Crystal's database has been uploaded.
Nicole

 

[access2010]

Agree - a copy of the database with instructions and some sample data would be helpful.
Has there been any update(s) to the code, the data or the PC/network?
Do you have a backup of the "working" system?

Thank you, jdraw for your assistance.
Crystal's database has been uploaded.
I can not find a backup of the working database.
This database was working up to and including is last use on June 15th.

Nicole

 

[conception_native_0123]

The query for the subform is being filtered on loading of the main form. And no records are loading in the sub form. Have you checked the query to see for any changes? Some queries are not updateable.

 

[CJ_London]

your query must have been changed since there are two tables. Forms should only be based on one table. Not sure why you have made the change because you are not using any fields from Investments01_tbl. However if you need to keep this table in the query for some reason, there is a workaround that should work in this case providing you are only updating one table. In the subform form properties change the recordset type from Dynaset to Dynaset (Inconsistent Updates)

 

[access2010]

Crystal you provided very limited amount of information. Perhaps the best thing to do is to upload a copy of your database so people can take a look. I would assume my suggestion is shared with others too.

Thank you, conception_native_0123.
I just returned to our office after being away for a few months and looking at the Query it does not seem to have changed during the period that I was gone.
Up to the time I left the office the Query worked, While now, it does not work.

During my enquiries, no one seems to have changed the query.
Any assistance that you can supply me with will be appreciated>

Thank you,
Crystal.

 

[conception_native_0123]

Well I have personally not seen that message pop up in any of my files I have ever worked on. Might I suggest that you create a new query with the same SQL statement and Link it to your form and try that? I know that sounds a bit generic but it just might work if there is no other reason to be had for the misbehavior of the objects in the file of yours.

 

[access2010]

your query must have been changed since there are two tables. Forms should only be based on one table. Not sure why you have made the change because you are not using any fields from Investments01_tbl. However if you need to keep this table in the query for some reason, there is a workaround that should work in this case providing you are only updating one table. In the subform form properties change the recordset type from Dynaset to Dynaset (Inconsistent Updates)

Thank you, CJ_London..
I have removed the Table = Investments01_Tbl from the Query
but now have an error message which is attached.
Could you please advise me how to fix this error problem?

Thank you.
Crystal

 

[access2010]

Well I have personally not seen that message pop up in any of my files I have ever worked on. Might I suggest that you create a new query with the same SQL statement and Link it to your form and try that? I know that sounds a bit generic but it just might work if there is no other reason to be had for the misbehavior of the objects in the file of yours.

Thank you for your suggestion, conception_native_0123.
I Deleted the query
Compacted the Database, restarted the database.
Reinstalled the same Query and the Error Message still appears.

Do you have any other suggestion.

Thank you.
Crystal

 

[The_Doc_Man]

The error display associated with post #11 suggests that your failure is caused by a "constraint violation" - i.e. you are attempting to enter a record that violates the rules defined for the various fields in that record. You have a null source in a field where data cannot be null.

Before you made this last change that led to this error message, did you have an error of any kind? Or did you have error messages disabled? I refer to the version that you "came back to" after your absence. If you have error messages disabled, enable them IMMEDIATELY so that you can get some evidence.

I hesitate to mention it, but the number of fields in this table is awfully suspicious. I would bet dollars to donuts that the table is not properly normalized. Having said that, the issue for me is that I don't know what to do with this form to duplicate the error. The size of the file wasn't that big, so I doubt it is a capacity error. The prime key is a LONG autonumber, and you only had a handful of records, less than 50, so sizing doesn't seem to be the problem there either.

However, you said this is an MDB. What version of Access are you using to open it?

Also, you said "until this week." By any chance, was it a Wednesday morning when things went haywire? No, that is not a frivolous question. What version of Windows are you running? If you can find the "Windows Update" screen you should be able to find the "Update History" option. Look for a Windows update to have occurred. Microsoft's pattern is to auto-patch on Tuesday nights and they have been patching the heck out of the operating system due to a printer bug that is a proven "hack" vector.

Right now there are too many open possibilities, so we need to start eliminating some of them.

 

[CJ_London]

As Doc says the error message is clear enough. The question for you is should that field allow nulls (i.e. is not required)? If yes, change the rule in the table. If no, you need to populate it with something but no idea what that something should be. You don't bring it through in your recordsource so it is not on the form so you can't populate it via the form. A workaround might be to set the default value to 0, but since I don't know the purpose, this may not be the right thing to do.

Since you have existing records with nulls, this suggests someone changed the rule from not required to required once these entries had been made (whoever changed it would have been told the data integrity rules had been changed and given the option to test existing records - clearly they selected no)

Note that you have the same issue with the currencycost field (rule has been changed) however you do bring this value through on the form

Seems to me someone was making some changes and didn't finish what they were doing

 

[conception_native_0123]

Do you have any other suggestion.

I think doc man has covered it all. constraints are an advanced concept, but it seems as though that is the issue.

 

[access2010]

The query for the subform is being filtered on loading of the main form. And no records are loading in the sub form. Have you checked the query to see for any changes? Some queries are not updateable.

Thank you for your assistance, conception_native_0123;.
I have reconstructed the form, using one table and now everything is working.
Nicole

 

[access2010]

The error display associated with post #11 suggests that your failure is caused by a "constraint violation" - i.e. you are attempting to enter a record that violates the rules defined for the various fields in that record. You have a null source in a field where data cannot be null.

Before you made this last change that led to this error message, did you have an error of any kind? Or did you have error messages disabled? I refer to the version that you "came back to" after your absence. If you have error messages disabled, enable them IMMEDIATELY so that you can get some evidence.

I hesitate to mention it, but the number of fields in this table is awfully suspicious. I would bet dollars to donuts that the table is not properly normalized. Having said that, the issue for me is that I don't know what to do with this form to duplicate the error. The size of the file wasn't that big, so I doubt it is a capacity error. The prime key is a LONG autonumber, and you only had a handful of records, less than 50, so sizing doesn't seem to be the problem there either.

However, you said this is an MDB. What version of Access are you using to open it?

Also, you said "until this week." By any chance, was it a Wednesday morning when things went haywire? No, that is not a frivolous question. What version of Windows are you running? If you can find the "Windows Update" screen you should be able to find the "Update History" option. Look for a Windows update to have occurred. Microsoft's pattern is to auto-patch on Tuesday nights and they have been patching the heck out of the operating system due to a printer bug that is a proven "hack" vector.

Right now there are too many open possibilities, so we need to start eliminating some of them.

Thank you for your assistance, The Doc Man;.
I have reconstructed the form, using one table and now everything is working.
Nicole

 

[access2010]

As Doc says the error message is clear enough. The question for you is should that field allow nulls (i.e. is not required)? If yes, change the rule in the table. If no, you need to populate it with something but no idea what that something should be. You don't bring it through in your recordsource so it is not on the form so you can't populate it via the form. A workaround might be to set the default value to 0, but since I don't know the purpose, this may not be the right thing to do.

Since you have existing records with nulls, this suggests someone changed the rule from not required to required once these entries had been made (whoever changed it would have been told the data integrity rules had been changed and given the option to test existing records - clearly they selected no)

Note that you have the same issue with the currencycost field (rule has been changed) however you do bring this value through on the form

Seems to me someone was making some changes and didn't finish what they were doing

Thank you for your assistance, CJ_London;.
I have reconstructed the form, using one table and now everything is working.
Nicole

 

[The_Doc_Man]

Excellent news, Nicole. Glad to have played a part in getting you going again.

 

[access2010]

Excellent news, Nicole. Glad to have played a part in getting you going again.

It was EASIER to start the form over, than trying to fix it.

We DO appreciate your advice.
Nicole.