Calling Code

[vbaInet]

Errors with what message?

 

[Spyhunter]

same error using any of the above coding.

 

[vbaInet]

Then the error must be happening on another line.

Let's see your db.

 

[Spyhunter]

i don't actually understand why this is still erroring as it should set it to either 1 or 2;

Dim dayd
If IsNull([Forms]![Main]![SubTotals]![CurrentDay]) = True Then
    dayd = 1
    Else
        dayd = 2
End If

 

[vbaInet]

Post your db.

 

[Spyhunter]

here you go - i know some of the stuff is not as itshould be done (don't have a go at me) but I am learning and changing it all the time. By the way your colour changing code didn't work either

form vba it is testing on is "MainSub10Oct"
Run 'Main' to start it.

Thanks.

 

[DCrake]

I think you need to compact your accdb.

 

[Spyhunter]

what do you mean?

 

[Spyhunter]

i know my database is not perfect and slightly designed incorrect but I am working on one problem at a time and the problem I have is the above, just need to get that working then I am going to go through the back end of the database.

 

[vbaInet]

By the way your colour changing code didn't work either

Or you just didn't set it up properly

With regards your Null assignment problem, you've got so much redundant code there so I won't be able to scrutinise your code. But like I mentioned in post #23, the error is coming from another line so you need to fix it all. Examples:

SickOct = [Forms]![Main]![SubTotals]![SickOct]
ELOct = [Forms]![Main]![SubTotals]![ELOct]

This is not limited to those two lines. If a variable is declared as anything other than Variant and you try to assign a Null value to it (which would most likely come from a field), then it will throw an error. In your case SickOct and ELOct are Double so they don't accept Null. All those variables need to be wrapped in Nz().

 

[Spyhunter]

but the field in question is not set as a double. it's just a 'dd' format ([Forms]![Main]![SubTotals]![CurrentDay])?

Also to note that the CurrentDay value is set on the mouse over VBA code on each sub form, so it know what date you user is selecting with the mouse (not pressing button just move over)
The code to set it is straight forward:
[Forms]![Main]![SubTotals]![CurrentDay] = "2", etc

So my query is how do I get this standard value to be compatible with the sub form calculation.

 

[vbaInet]

Re-read my code, I mentioned the Variable is Double. You are assigning to a variable which get its value from a field.

 

[Spyhunter]

the field has a number that is used, i just want to be able to use that number in a formula in VBA. I don not fully understand why it is not working.

 

[vbaInet]

There are times it doesn't contain anything and you must trap it. I've explained this over and over again.

 

[Spyhunter]

ok, then surely :

DayD = Nz(Me.SelectedDay, 1)

should work as it would equal 1 if null?

 

[vbaInet]

Yes it will work for DayD, but I gave you two instances where the code failed when I ran it. I will repeat it again, ALL the variables (that are getting their values directly from fields or controls) must be covered because they are also run when the code loads.

 

[Spyhunter]

ok i believe i understand (and listening now) but this code actually now works;

Dim DayD
Dim d As String
Dim i As Integer
DayD = Nz(Me.SelectedDay, 1)

For i = DayD To 31
     d = "[" & "Day" & CStr(i) & "]"
....

I just had to refresh the total shown and it changes based on the mosue over day.



Thanks for your comments, help, screams and explainations, I really do appreciate it - honestly. Now to the rest of the tidying up

 

[vbaInet]

...
Thanks for your ... screams ...

There was hardly any

Remember, all those variables in the Current event of the Subtotals form will need to be wrapped in Nz(). The two lines in post #30 threw an error when I loaded your Main form. I suspect any of them could throw a Null assignment error too.

 

[Spyhunter]

thanks once again, i have actually learn't a lot

 

[vbaInet]

Happy to help