Help with a Null Variable Issue

[EDNYLaw]

Hello:

I'm having an issue in a database I created. Whenever the user goes to frmOpenInvoice and clicks on New Invoice (which opens frmInvoice) then enters a date (on subform frmServiceDate) an error pops up stating "You tried to assign an Null value to a variable that is not a Variant data type." I cannot figure this out. I tried deleting all queries and vba code to see if it was something I messed up but I still get the error.Copy of Consulting.zip



The odd part is, when I open the subform on its own, it doesn't give me the error. It must be something linking the two forms.

Any help would be greatly appreciated as I'm at a total loss.

[June7]

Shouldn't you save the InvoiceID into tblServiceDate instead of pkDateID into tblInvoices? If not, your form/subform arrangement is backwards.

[EDNYLaw]

Shouldn't you save the InvoiceID into tblServiceDate instead of pkDateID into tblInvoices? If not, your form/subform arrangement is backwards.

Yeah I think you're right about that. However, when I change that, now every time a new date is entered, it creates a new record on frmServiceDate so I'm not sure how to prevent frmServiceDate from adding new records every time the a field is updated or the form is updated.

I'm also still getting the Null value error which is completely eluding me. In fact, if reworking the tables is going to be a giant pain, I'd rather leave it be as literally every other single thing in the database is working correctly except for this Null value error. Any insight as to what might be causing it? I've tried to correctly format the fields in the data table, I've looked into the queries where I used the nz() function and eliminated all VBA code.

However, if you think that the error is being caused (in part or in whole) by the tables being incorrectly related, I'll go ahead and make the change provided I can figure out how to stop a new record from being added on frmServiceDate every time frmInvoice is updated.

Thanks for your help, I really do appreciate it. This stuff can get extraordinarily frustrating when you're not a database programmer and learning how to do it using Google.

[June7]

Explain more about relationship of invoices and service dates. Will one service date have many invoices? Or will each invoice have many service dates? Is that why tblServiceDates has multiple date fields? Is this really supposed to be a 1-to-1 relationship? Each invoice will have one service record and that record can have up to 6 service dates? If this should be 1-to-1, these tables could be just one table because tblServiceDates is not normalized structure.

I removed the relationship between those tables, put a field in tblServiceDates for fkInvoiceID. Changed subform Master/Child Links properties. Reset relationship between tables on the InvoiceID keys. No longer getting the null error, however, for some reason is not enforcing referential integrity.

[EDNYLaw]

This is how the database should work. The consultant has a contract for $100,000 (which is associated with a Vendor). The consultant then goes to different schools that are under the vendor's control. When the consultant goes to school "a" that is associated with vendor "a" then the amount of the invoice is deducted from the total contract amount. The invoice is calculated by the daily rate times the number of days the consultant was in that particular school.

The subform frmServiceDate allows the consultant to record which dates and how long (a full day, half day, etc...) the consultant was at the school. This information needs to be displayed on the rptInvoice for contract purposes. The way I have it set up right now, the day lengths are totaled (I used the nz() function to turn nulls into "0") and then it records the sum (using a query) to an invisible listbox on frmInvoice. The textbox "days" on frmInvoice then just mirrors the invisible listbox. The invoice total is then calculated by multiplying the Daily Rate textbox and the Days textbox using a query to display the result in the InvoiceTotal textbox. Having read up on queries and calculations, I tried to never have a total value recorded on to a table, I just let the queries calculate everything on the fly.

The thing that is curious, and why I never picked up on the error until a tester did something, was that if frmServiceDate already had a date in it, then no error. However, when I just put a default value of date() into the service date textboxes, it still gives the error.

I'm sure there's a better way to program entering the dates. I randomly picked 6 days (because it's unlikely it'd ever go over that amount). In an ideal world where I knew how to actually program (and not make hacky workarounds) I'd figure out how to have an indefinite amount of textboxes created when the user enters in a value into the first textbox, then the second then so on... But I'm happy with 6, and for the most part, it worked.

I hope that explains the though process behind the database. If not, please let me know any other information I can provide. If you have some insight into why frmServiceDate (once the relationship is corrected) continues to add new records instead of just filling in fields, let me know and I will try to fix this issue. I'm guessing that's what you meant by the referential integrity issue.

Thanks for the help, again, I really appreciate it.

[June7]

Actually, that confuses me more.

The tblServiceDates documents visits to a school under a contract?

So really schools and service dates are related. Each school could have up to 6 visits (because your table is not normalized) under a single contract. You are trying to accomplish relates of schools, contracts, visits in tblInvoices.

So will each Contract be billed on only once?

And what is your (or your company) role in this process? Who is billing whom?

[EDNYLaw]

Somehow, it got solved. Your suggestion of changing the relationship to put fkInvoiceID on tblServiceDate, combined with deleting and reinserting the subform (and setting up the parent/child relationships) worked. No more Null value, the subform doesn't add a new record every time it or the main form is updated. For some reason I can't attach an updated database, so when I figure that one out, I will.

However, in the interest of being thorough, the way it would work is like this:
1. Consultant has a contract with Suffolk BOCES for $100,000 (or whatever the amount is) and we'll call them Vendor "a"
2. There are various schools within Suffolk BOCES (in NY BOCES = Board of Cooperative Educational Services and provides services for schools within that BOCES jurisdiction)
3. The consultant signs a contract with BOCES to perform various services for specific schools within that BOCES
4. If School "a" wants to hire the consultant for 2.5 days (let's say March 26, 27, 28) then the invoice needs to reflect which contract is appropriate (here, Vendor "a") for a calculation later on
5. The consultant needs to be able to indicate which days (March 26, 27, 28) they performed the service for that particular school and the number of days (2.5)
6. The days spent at the school are totaled, multiplied by the daily rate which gives the invoice total
7. The invoice total is submitted to Vendor "a", who then pays the consultant the invoice amount (the schools don't pay the consultant, they pay BOCES)
8. The invoice total also needs to be deducted from the contract amount (so in this case 2.5 days x $800/day = $2000 subtracted from $100,000)
9. If the consultant performs a service for School "b" (who is under the auspices of Vendor "a") then the total invoice amount needs to be deducted from Vendor "a"s contract (ie, if the consultant spent one day in School "b" the total amount left on the contract for Vendor "a" would now be $97,200 [$2000 from School "a" invoice and $800 from School "b" invoice]
10. The idea is that the consultant needs to be able to see how much is left in the contract so that they are not doing work for which they will not get paid because it went over the contract amount left

That's basically how the entire thing would work. I didn't come up with the system, that's just how the consultant wants it to work. So, as I stated, everything is working correct as of right now, but if you have any insight into how to code things better or a more efficient system, I'm all ears. I'd love to learn more about efficient coding and better ways of doing things as some of ideas are simply workaround because I don't know the proper way to do things.

As I'm beginning to learn with programming, sometimes there's just a gremlin in the code somewhere the causes issues. I really am not sure why changing those things today worked and they didn't work yesterday, but I'll take it.

Thank you for the help.