You tried to assign the Null value to a variable that is not a Variant data type. (Er

[maw230]

tblChecks holds all of the information on each Check sent out. A Store can have a CheckNum whether or not they pass or fail the audit, so it doesnt directly relate to tblPass/Fail. However, there is only a CheckNum when there is a Fine, which is why I put tblFines in between. Also, there can be a Fine even if a store passes an audit..

I wish it were more cut and dry.
Maybe we need to re-think the entire system that he uses to track these audits. It doesn't make for easy database design when something can be this way but also that way or maybe sometimes this way.

 

[vbaInet]

Did you read up on those normalization links I gave you?

So I can infer that:

1. There can be many fines for one PassFailID? That is, a PassFailID of 1234 can have more than one fine related to it.

2. There is one Check per fine?

 

[maw230]

I have an Access textbook that I keep handy, but I learn much better though practice.

Each Pass/FailID should have only one Fine associated with it. Pass/FailID is an autonumber PK.
Each InspectionID does not have to have a Pass/FailID, because there are some cases where Pass/Fail is left blank (possibly a poor practice).. At least with the data I was given to build the database in the first place.

There can be either 1 or 0 Checks per Fine. There can be a fine without a check, but not a check without a fine.

Just writing this is leading me to believe that I need more tables to reflect the other potential scenarios available (i.e. - rather than only Pass of Fail).

 

[vbaInet]

There can be either 1 or 0 Checks per Fine. There can be a fine without a check, but not a check without a fine.

I can see your reasoning of creating a Fines table. I've changed a few things around so that it fits into what you're trying to do.

It works with your Fines table but in actual fact I wouldn't use a Fines table. However, without the Fines table you would have to handle the statement I quoted above in code. This is how I would have done it.

 

[maw230]

I really appreciate your help. I will take a look at it as soon as I can and get back to you.

 

[maw230]

Ok, had some time this morning to look at it. The only big change i can see immediately is the changes to the table relationships and the addition of the PassFailID_fk to tblFines.

Can you explain me to me the logic behind that so hopefully i can remember in the future?

I will need to make these changes to the main database now, so I have a few questions. Will I need to re-add my form and subforms to the main form after making the relationship changes?
Will Access automatically add a Pass/FailID_fk to each entry in tblfines or will I need to add it manually and/or start from the beginning and re-add all of my data?

Thank you again for your help, this forum and its members have once again proven to be an invaluable resource to newbies like myself.

 

[vbaInet]

Can you explain me to me the logic behind that so hopefully i can remember in the future?

The logic follows what you mentioned in your other post with one caveat, a PassFailID can have many fines. That's the reason why I took out the navigation buttons from the Fines subform so that multiple fines cannot be added. If you want a true one-to-one then do the following:

1. delete the Fines table and add a Fines field to your PassFail table
2. add the PassFailID_fk field to tblChecks and create a relationship to the PassFail table (with referential integrity and cascade delete/update)

But like mentioned, you would need to handle whether or not a new Checks record can be entered using code.

Will I need to re-add my form and subforms to the main form after making the relationship changes?

No need to redesign. You can follow the design I used in the NoQueries form (I think it's called).

Will Access automatically add a Pass/FailID_fk to each entry in tblfines or will I need to add it manually and/or start from the beginning and re-add all of my data?

Yes it will if the subform is linked to the parent form via Link Master/Child fields properties.

 

[maw230]

I'm not sure if you still have the database, but I noticed that you made StateInvoiceNum the PK in tblChecks. Is that for any particular reason?

 

[vbaInet]

My reasoning was for every check there must be a StateInvoiceNum, but that may nto be the case. In fact I think I made StateInvoice and FineID primary keys. Up to you to decide which PK to use.

 

[maw230]

Great. Thanks again.

 

[vbaInet]

You're welcome.

 

[maw230]

Quote:
Originally Posted by maw230
Will Access automatically add a Pass/FailID_fk to each entry in tblfines or will I need to add it manually and/or start from the beginning and re-add all of my data?

Yes it will if the subform is linked to the parent form via Link Master/Child fields properties.

I see that it will automatically add Pass/FailID_fk when i add a new record, but what about the existing records? How do I assign a Pass/FailID_fk to them? I've attached a screen shot of the current records in tblFines.

 

[vbaInet]

Use the FineID in PassFail from your old db to UPDATE that field. It would require a query or two.

 

[maw230]

Thanks, seems to be working so far. Now to clean up these dirty tables.

 

[vbaInet]

Good luck with the clean up.