Hey guys!
Usually I start writing out my problem that I'm stuck on when I get an epiphany towards the end of the post and solve it myself. I think sometimes all you really need to do is talk it out aloud to think of something new. But this has got me beat :/
I'm writing a database for a Counselling Clinic. My problem is in tallying up all the Session Numbers. Now, Session Number currently works on [Total Amount of Sessions Attended]+1 to get the number for the session you are about to book. In order to do this, I have a subform that calculates the Session Number.
The problem occurs when you are booking a new client their first session. As the subform runs a query that counts the total number of attended session from the TBL_Diary_Booking table, when you book a new client, this subform field remains blank. I have tried both the IIf() and NZ() functions to return a 0 value, but it will simply not happen. I have even tried pulling the data from the subform onto a hidden textbox on the main form and then performing the IIf and Nz functions, but I get a #Error! and #Size! values returning.
Here are the codes I have already tried on the main form. Please tell me if they are wrong :/
And on the subform query:
Is there anyway to either:
- Force the Subform query to return a 0 value OR
- Make a textbox copy the data from the subform and return it as a 0 if there is no value (record) of the client existing on the TBL_Diary_Booking form yet?
Usually I start writing out my problem that I'm stuck on when I get an epiphany towards the end of the post and solve it myself. I think sometimes all you really need to do is talk it out aloud to think of something new. But this has got me beat :/
I'm writing a database for a Counselling Clinic. My problem is in tallying up all the Session Numbers. Now, Session Number currently works on [Total Amount of Sessions Attended]+1 to get the number for the session you are about to book. In order to do this, I have a subform that calculates the Session Number.
The problem occurs when you are booking a new client their first session. As the subform runs a query that counts the total number of attended session from the TBL_Diary_Booking table, when you book a new client, this subform field remains blank. I have tried both the IIf() and NZ() functions to return a 0 value, but it will simply not happen. I have even tried pulling the data from the subform onto a hidden textbox on the main form and then performing the IIf and Nz functions, but I get a #Error! and #Size! values returning.
Here are the codes I have already tried on the main form. Please tell me if they are wrong :/
Code:
[frmDiarySessionNumber].[Form]![SessionNumber] (#Error! message)
Nz([NumberBox],0) (#Size! message)
IIf(IsNull([frmDiarySessionNumber].[Form]![SessionNumber]),0,[frmDiarySessionNumber].[Form]![SessionNumber])
Nz(DLookup("[SessionNumber]", "TBL_Diary_Booking", "ClientID = Me.ClientID"),0)And on the subform query:
Code:
SessionNumber: IIf(IsNull([Attended]),0,[Attended])Is there anyway to either:
- Force the Subform query to return a 0 value OR
- Make a textbox copy the data from the subform and return it as a 0 if there is no value (record) of the client existing on the TBL_Diary_Booking form yet?
