Calculation on form with subform 2

[CarolOB]

I have a main form named new_mem (based on a table named members) and a subform named family1sub (based on a table named family1). There is a field called ID in the family1 table that joins with a field called MemberID in the members table.
One of the fields on the subform is called locker and it's a yes/no data type. What I want to do is count the number of family members where locker is true and multiply that number times $10.00 where the family1.ID matches the MemberID on the main form.
On my main form (new_mem), I have an unbound text box where I want to display the calculated value. This value should automatically update to the correct value if a change is made in the family1 subform (ie if 4 members of a family have lockers now and the value is $40.00, I'd like it to change to $30.00 if I change one of the family members to not having a locker).
I would very much appreciate any help you can offer me.

Thanks in advance for your help. CarolOB

 

[LittleSmudge]

Open a recordset and calculate it every time the main form changes record

In ADO ( Assecc 2000 & beyond )
Private Sub Form_Current()
Const ChargePerLocker = 10
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection

rst.Open "SELECT Count(Id) As CountOfIds From Family1 WHERE (Locker = True) AND (Id = " & ControlNameHoldingMemberId & &quot"

If rst.EOF Then
NameOfUnboundTextBox = 0
Else
NameOfUnboundTextBox = ChargePerLocker * rst!CountOfIds
End If
End Sub


I think that does what you're asking for.

G LS

 

[CosmoKramer]

CarolOB,

Check out my response in thread702-252741. This should be what you're looking for. If you requery the subform On Exit, the form's text box will be refreshed.....

 

[CarolOB]

Sorry I didn't respond sooner, but I've been trying both of your suggestions and I haven't had any luck yet.

Thank you for your suggestions - I'll keep fumbling around and see if I can get it to work. CarolOB

 

[rajeessh]

LittleSmudge suggestion seems to be ok
and it must work out

 

[CarolOB]

Good Morning,
I'm still not having any luck with this. I get a compile error: user defined type not defined on the line where it says Dim rst As ADODB.Recordset. After researching the help again, I changed ADODB to DAO and that seemed to remedy that problem. I then changed the next line from Set rst = New ADODB.Recordset to Me.Recordset as shown in the "help" example. This produced another compile error: Method or data member not found. I get this same message "Method or data member not found" on the line that reads rst.ActiveConnection = CurrentProject.Connection.

Can you offer any more help on this problem? I would really appreciate it because, as you can tell, I'm new to Access 2000.

Thank you. CarolOB

 

[LittleSmudge]

In the code window click on tools, references and go down the list to the
MicrosoftActiveX data Objects 2.1 library and add a tick to the box.

Then return to using ADODB

Conversion from DAO to ADO is not as simple as just changing the qualifier.
In DAO you need references to the CurrentDatabase and you use completely different constructs to open a record set.

If you have Access 2000 get ADO working - don't go back to an old technology and learn that.


G LS

 

[CosmoKramer]

CarolOB,

It really shouldn't be all that complicated. I've used my method several times. What's nice about it is that it is very easy and the calculated field will update as your data changes. Did you try my suggestion, and if so, what happened??

 

[CarolOB]

Sorry I took so long to respond, but it was a hectic day at work today.

I've made some progress. I seem to have the ADODB code working ok now, but I'm having trouble with the rst.Open statement. I've tried it several ways and have gotten everything from syntax errors to a message saying that I have one too many )'s (although it doesn't look like that to me). I used cut and paste to copy your statement so I wouldn't make a "typo" and only changed your wording from ControlNameHoldingMemberId to just Memberid as shown in the following:

rst.Open "SELECT Count(Id) As CountOfIds From Family1 WHERE (Locker = True) AND (Id = " & MemberId & &quot"

Any help would be greatly appreciated.

Thanks. CarolOB

 

[LittleSmudge]

The rst.Open looks okay as far as it goes "AS LONG AS" MemberId is a valid control name on the form.

Try this:
Put a break point in the code on the rst.Open line
When the code halts, hover your mouse pointer over the 'MemberId' term. A little yellow box will appear with the current value of MemberId in it ( Like a tool-tip ) Is this the value you are expecting?

Add a line below this
Msgbox rst!CountIfIds

Does this yield a sensible result?
OR What error message do you now get?



Graham

 

[CarolOB]

Good Morning,
Thanks for getting back to me. I put the break point in like you suggested, but it did not produce a yellow box with a tool tip in it when I hovered the mouse over the memberID term. Instead, the whole line was highlighted in yellow and there weren't any tool tips.
I'm running out of time to solve this so I think I'm going to have to "hardcode" the values instead of using the check boxes for now.
I would like to resolve this issue though because I think the check boxes are a "neater" way to go.
Thanks again for all your suggestions.
Sorry to be such a bother. CarolOB

 

[CosmoKramer]

I'll try this again.....

CarolOB,

Did you try my suggestion, and if so, what happened?? This method is simple and should accomplish what you're looking for.

 

[CarolOB]

Hi CosmoKramer,
I apologize, I thought I had replied to you. I've tried to modify your code to match what I need, but keep running into syntax errors and/or compile errors.
The following is the latest copy of the code I've tried:

Private Sub Form_Current()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT Count(LOCKER) As CountOfLOCKERS FROM family1 WHERE (LOCKER = True) AND ID =" & [family1 Subform].MemberID
LOCKERCOUNT = rst!CountOfLOCKERS
rst.Close
End Sub

I'm assuming that I have to use the square brackets around the subform name because the name contains a space. CarolOB

 

[CarolOB]

Good Morning, CosmoKramer,

I kept "fiddling" with my select statement and finally got the syntax down right. I guess I was having a "BRAIN CRAMP" or something.

It's working AWESOME now!

Thanks a million!!!!!!! CarolOB

 

[CosmoKramer]

Actually, LittleSmudge is the one who helped you with the recordset code. Here is the detail for my suggestion:

Add a text box to your subform's form footer. It won't be visible, it will just hold the count of your locker records that are "Yes". Set the control source of the text box to something like this:

=Abs(Sum([locker])

If [locker] is set to yes, it will contain the value -1. Adding them up will give you the count of boxes checked. The Abs function returns the positive value.

Add the following code to the After Update event of the [locker] field on your subform:

Me.Refresh

This will update the text box on your main form with the updated count.

Add the text box to your main form that you want to hold the dollar amount. Set its control source to something like this, changing to your subform and text box:

=[YourSubform subform].[Form]![txtLocker] * 10

If you format this text box as Currency, you should be all set.

 

[CarolOB]

Thanks for the follow-up message. I pretty much had proceeded with the way you said EXCEPT, I had changed the Yes/No data type to return True or False instead of -1 and 0, thus, I didn't need to use the ABS portion.

Thank you CosmoKramer and LittleSmudge.

I really appreciate the assistance. CarolOB