Help Debugging my Database

[Adigga1]

Hello Everyone,

I am almost complete with my Database project but have a few bugs to iron out. I need some fresh eyes to pinpoint some areas where I may have overlooked.

This main bugs originates from the Form (F_PatientCase) with Subform:

a. I get an error each time I attempt to update the SubForm: 'Field cannot be updated'

b. Some of the Accts do not require a Donor entry, but if left empty an error: The Microsoft Jet database engine cannot find a record in the table <t_donor> with key DonorNum

c. The AcctNum, DonorNum and InsuranseID did not appear in Query (T_Ccpt Query2), in which I intend to use to build my report via AcctNum.
d. Finally, when I go to my original Tables (T_Ccpt) and (T_Physicians), duplicate date entries are present, which should not be occurring. They should only act as a lookup table not entering /updating data from a query.

I have enclosed a zip of the database with test data.

Thank you all in advance for your assistance with my mundane issues

Adigga

 

[speakers_86]

Without even looking at your db I would recommend getting rid of the "_" and any other special characters. Doing this may save you problems in the future, but will probably mean a lot of your code will be broken. If you do this, some of the queries and the forms will need to be fixed. Grouping on your reports may also be affected. Very tedious, but a good thing to do.

May be a good idea to look up naming conventions.

 

[Adigga1]

Without even looking at your db I would recommend getting rid of the "_" and any other special characters. Doing this may save you problems in the future, but will probably mean a lot of your code will be broken. If you do this, some of the queries and the forms will need to be fixed. Grouping on your reports may also be affected. Very tedious, but a good thing to do.

May be a good idea to look up naming conventions.

Thanks speakers for the reommendation, I will take that into consideration.

 

[neileg]

I don't like the underscore but I disagree with speakers that this will cause any issues. The underscore is the only special character that you can use with impunity.
Problem a appears to be because there is no parent/child link between form and subform.
Problem b is because you have referential integrity enforced. This does not allow you to have no record in the related table.
Problem c I don't understand. Add the fields you need.
Problem d appears to be the same issue as a.

 

[Adigga1]

I don't like the underscore but I disagree with speakers that this will cause any issues. The underscore is the only special character that you can use with impunity.
Problem a appears to be because there is no parent/child link between form and subform.
Problem b is because you have referential integrity enforced. This does not allow you to have no record in the related table.
Problem c I don't understand. Add the fields you need.
Problem d appears to be the same issue as a.

Thank you neileg, for the response.

I will address your suggestions.

With reference Answer a. I have a parent / child link established, I actually tried different link edits, but it still not syncing correctly with the subform. Anything I may be overlooking?

Thanks

 

[speakers_86]

Thank you for the correction. I dont like the underscores either. Camel Case makes objects easier to read than underscores. Plus, if a person is using Camel Case, those pesky special characters tend to stick out.

 

[Adigga1]

Thanks Speakers, I'l keep that in mind with my next project

 

[Adigga1]

I don't like the underscore but I disagree with speakers that this will cause any issues. The underscore is the only special character that you can use with impunity.
Problem a appears to be because there is no parent/child link between form and subform.
Problem b is because you have referential integrity enforced. This does not allow you to have no record in the related table.
Problem c I don't understand. Add the fields you need.
Problem d appears to be the same issue as a.

I'm still having problems with the Donor entry field (b), I released the Ref. integrity, but Jet database Error still came up, any alternatives?

 

[neileg]

With reference Answer a. I have a parent / child link established, I actually tried different link edits, but it still not syncing correctly with the subform. Anything I may be overlooking?

Thanks

I can't see the parent child links in the form F_PatientCase. Establishing them in in the Relationship pane is not enough. Providing the PK/FK exists in the dataset for each form with the same name, the subform wizard will establish the links for you.

The chain of queries makes it a bit tricky to see the flow of data, and your object names don't help. There's not much point in using T to indicate a table when you have forms that have the T prefix.

 

[Adigga1]

I can't see the parent child links in the form F_PatientCase. Establishing them in in the Relationship pane is not enough. Providing the PK/FK exists in the dataset for each form with the same name, the subform wizard will establish the links for you.

The chain of queries makes it a bit tricky to see the flow of data, and your object names don't help. There's not much point in using T to indicate a table when you have forms that have the T prefix.

I will attempt to re-estblish the link and follow-up with the results.

On another note; I was still experiencing issues with the The Donor portion in the PatientCase form, I tried the suggestion on the integrity enforce, but still get the Micosoft Jet database error.

Thanks