Control Source from a sub form

[Groundrush]

Is there a way to create a text box with the control source based on a subform?

the subform is called "SubfrmCount and the control source I used in the text box was "=[SubfrmCount]"

the control source of the subform is a qry and everytime I try it all I get is an error message in the text box

Thanks

 

[Robert Dunstan]

Hi there,

A little more information is needed. What are you wanting to display in your text box? Is it a total of the subform?

If so then in your subform's footer create a hidden textbox and set its control source to =Sum([YourControl]). Give it a meaningful name e.g. txtTotal. Then in your main form create another textbox and sets its control source to =([frmYourSubformNameHere].[Form]![txtTotal])

HTH
Rob

 

[Groundrush]

What are you wanting to display in your text box? Is it a total of the subform?

Yes I am

I have just tried what you suggested, but It did not work, perhaps I am doing something wrong.

the reason why I need a text box to pick up the same values that is shown in the subform, is so I can activate a macro when the count reaches 2

I have tried before using the Subform, but I got this Message" A Property of the Automation object requires or returns a data type that isn't supported by visual basic.

so I thought if I created a text box that had the same data fed through my macro may work

 

[Robert Dunstan]

OK..

I think I misunderstood your post

Reading it again are you counting the number of records in the underlying recordset of your subform?

 

[Groundrush]

I think the answer to that is yes

the Subform is based on a qry, that totals up the alarm count
so I get an up to date total as you go along inputting data.

Please see a stripped down version of my database, if you have a free moment

thanks for your help

 

[Robert Dunstan]

Hi Groundrush

Ok I've had a look at your code for your. Presumably the bit you commented out in the On_Current event is where the problem lies?

Firstly when you want to reference a control on a subform you need to use the full syntax:

[Forms]![myMainForm]![mySubForm].[Form]![myControl]

Secondly running the code in the On_Current event will generate an error because no value has been assigned to SubfrmCount. So I placed the following code in the Got_Focus event of OperativeNo

Private Sub OperativeNo_GotFocus()

If [Forms]![frmAlarms]![SubfrmCount].[Form]![CountOfFalseAlarm] = 2 Then
    DoCmd.RunMacro "Action1", , ""
    MsgBox "YourMessageHere"
End If

If [Forms]![frmAlarms]![SubfrmCount].[Form]![CountOfFalseAlarm] > 3 Then
   DoCmd.RunMacro "Action2", , ""
    MsgBox "test"
End If

End Sub

That does work believe or not

Is that what you are looking for?

Rob

 

[Groundrush]

Thanks Robert

That has got me a step closer to what I need to do,

although it is very difficult not to complicate things, and I am trying to do one step at a time.

The Alarm Count is very important and I need to keep count on all the properties that we cover.

Basically what I am trying to do is:
so as soon as a property reaches count 2 it will activate the first part of the code to inform the user that the first action letter needs to be sent

then by clicking on a tick box or something to indicate that a letter has been produced, it will then disable the code for that property until the count reaches 4 or more
then the code will activate again to notify the user that the second action letter needs to be sent. And so on.

The tick boxes when ticked will print of the letters that I have created in the reports part of Access.

hope I have not scared you away

 

[Groundrush]

using your modified code in the Got Focus of the OperativeNo Field

I am getting a runtime error whenever I try to enter a new record that has a new property

error 2427, you entered an expression that has no value.

any ideas


see attached,

thanks

 

[Robert Dunstan]

Hi Goundrush,

Sorry for not coming back to you, I've been rather tied up on other things.

I did download your database the other day and generated the error 2427. The reason this is happening is that if you select a job that does not have any false alarms then you are trying to assign a null to an argument.

What is the latest, have you managed to sort it?

 

[Groundrush]

Hi Robert

I had another thread regarding a macro question that was related to this one and I have been getting help from IMO.

So far the database can log the false alarm activations and when the count reaches =2 and >3 (PER PROPERTY) it then fires up the message boxes to instruct the user to send out warning letters

The next step was to find a way to indicate that the letters had been sent and also disabeling the message boxes from activating everytime the form was opened or record selected.

We came up with the idea of having 2 toggle boxes that did just that, but unfortunatley it only works if all the entries with the same property name has the Toggle boxes checked, and it also activates whenever the same property is used again regardless of whether or not it's a false alarm

So I am trying to figure out how to have all the toggle boxes related to the property checked just by clicking on one of them

any thoughts?

 

[Robert Dunstan]

Hi Groundrush

OK any chance you can post an updated example of your db so I can have a look?

 

[Groundrush]

Thanks Robert

I think I am almost done, the sample attached is what I have so far.

I have tried to cover all eventualities of how the inputting may occur

2 properties are used as a test
both with 4 jobs raised each with a unique job no
first property has 2 false alarms with the first toggle box checked and the second property has 4 false alarms with all toggle boxes checked.

Now because the toggle boxes have been ticked the code will not activate,

it will activate when creating a new entry with the same property. which I am trying to avoid from happening

Cheers