Trying to populate text box (1 Viewer)

[foxxrunning]

I have a form I am using just to learn some VBA. Is there a way to populate a textbox (properly named) on a form with the result of some VBA code that has performed a calculation. What I was looking for was something like
Forms![Formtest].[textboxname] = c I know this is not too informative but all I want is the correct method to pass calculated data back onto a textbox on my form. Thanks for any help.

 

[MajP]

Your notation looks correct. But in real world your form should not be tightly coupled. It should pull the data

 

[Pat Hartman]

If the calculation is simple, you can put it directly in the ControlSource:

=(Qty * Price)

If it is more complicated, you might create a function:

=CalcExtendedPrice()

Both of the first two options can be done in the Form's RecordSource query which is my personal choice, and then you just bind the calculated field to a control.

If it is based on some action so that you need to do it at a specific time, then you need to tell us wat causes you to want to recalculate and we'll tell you the best event to use. The code would be something like:

Me.ExtendedPrice = Me.Qty * Me.Price

When working within the class module of a form, always refer to controls on that form using Me.controlname. If you are referencing a control on a different form, only then would you use the Forms!someformname!somecontrolname

We don't have any context so tell us more about what causes the recalculation. The first examples always happen automatically whether the calculation is on the form or in the query. Access takes care of refreshing the calculated field when one of the component fields change. Using the Function method, you would have to know when to trigger the function.

 

[foxxrunning]

Your notation looks correct. But in real world your form should not be tightly coupled. It should pull the data

Wow, did not expect a reply so quickly. Thanks. Do not know what you mean by "pull the data"

 

[Pat Hartman]

My examples show "pulling the data"

 

[The_Doc_Man]

With regard to this: Forms![Formtest].[textboxname] = c

If this calculation is done from FormTest then you can use Me.textboxname in place of the longer reference. If the calculation is done from some other form, you can only make this work if you first verify that FormTest is actually open at the time.

The reference to "pulling" the data is this, and I will generalize with simple names, two forms A and B, and three text boxes TA (on A) and TB (on B) and TB1 (on B). If you want TA to contain data computed based on TB and TB1, then you can get it there two ways.

From some event on form A, if you do Me.TA = Forms!B.TB * Forms!B.TB1, you are PULLING the data from B, which has to be open at the time. Since you are doing it from A, A is known to be open.
From some event on form B, if you do Forms!A.[TA] = Me.TB * Me.TB1, you are PUSHING the data to A, which has to be open at the time. Since you are doing it from B, B is known to be open.

 

[MajP]

If you build a public procedure in a standard module any form or other procedure can call that procedure. If you want to get something back that procedure needs to be a function. The function does not need to hard code the location to send it.

A very simple example would be a function that returns a message.

Public Function GetMessageNoArguments()
  GetMessageNoArguments = "Today is: " & Format(Date, "Long Date") & " Time is: " & Format(Now, "HH:MM:SS") & vbCrLf & "User: " & Environ("Username")
End Function

Then any form could call that message and display it or pop open the message
Example
Me.txtBxTest = GetMessageNoArguments()
or
Msgbox GetMessageNoArguments(), Vbinformation

me.subformOne.Form.LabelOne.Caption = GetMessageNoArguments

So it is not tightly coupled, it is not tied to a specific form. Lots of code from different locations can call it and use it.

 

[foxxrunning]

If the calculation is simple, you can put it directly in the ControlSource:

=(Qty * Price)

If it is more complicated, you might create a function:

=CalcExtendedPrice()

Both of the first two options can be done in the Form's RecordSource query which is my personal choice, and then you just bind the calculated field to a control.

If it is based on some action so that you need to do it at a specific time, then you need to tell us wat causes you to want to recalculate and we'll tell you the best event to use. The code would be something like:

Me.ExtendedPrice = Me.Qty * Me.Price

When working within the class module of a form, always refer to controls on that form using Me.controlname. If you are referencing a control on a different form, only then would you use the Forms!someformname!somecontrolname

We don't have any context so tell us more about what causes the recalculation. The first examples always happen automatically whether the calculation is on the form or in the query. Access takes care of refreshing the calculated field when one of the component fields change. Using the Function method, you would have to know when to trigger the function.

My friend and I are trying to analyze some data from election returns in a state. What we want to know is the party of people in the state as of different years. We are checking to see if the party affiliation is the same over many years. Then if the party is the same for perhaps 3 of 5 (this is the calculation I was talking about) then we want to display this fact in a textbox on our form. The form contains two fields (as of now ) a state ID and the textbox that says they have been in a Party for 3 of 5 years. Not a complicated calculation, but we have to examine 5 fields (5 different years) and then determine if they meet the 3 of 5 years criteria. Once we have done the "calculation" we want to pass that "True" statement back onto the form in the textbox as T or Yes etc. We just need to know how to pass that data back to the textbox from the calculation we do in VBA as a sub procedure. Hope this is clearer. Thanks for your instantaneous response.

If you build a public procedure in a standard module any form or other procedure can call that procedure. If you want to get something back that procedure needs to be a function. The function does not need to hard code the location to send it.

A very simple example would be a function that returns a message.

Public Function GetMessageNoArguments()
  GetMessageNoArguments = "Today is: " & Format(Date, "Long Date") & " Time is: " & Format(Now, "HH:MM:SS") & vbCrLf & "User: " & Environ("Username")
End Function

Then any form could call that message and display it or pop open the message
Example
Me.txtBxTest = GetMessageNoArguments()
or
Msgbox GetMessageNoArguments(), Vbinformation

me.subformOne.Form.LabelOne.Caption = GetMessageNoArguments

So it is not tightly coupled, it is not tied to a specific form. Lots of code from different locations can call it and use it.

Lots to think about. Thank you for your full reply

 

[MajP]

My guess is that your data is not properly normalized and each year is a column, instead of a record for each year. Can you show your table fields? Either way you may be able to do all your calculations in a single query and pull your answer using a dlookup or dcount in a calcuated control.
You can first do a union query to but all the years into a single column. Then you can use an aggregate query to pull the last three years of data. From there you can do a dcount to show if the last 3 years are the same or match the current year.

Can you provide a little more data on your table fields and the rules for your calculation.

 

[foxxrunning]

My guess is that your data is not properly normalized and each year is a column, instead of a record for each year. Can you show your table fields? Either way you may be able to do all your calculations in a single query and pull your answer using a dlookup or dcount in a calcuated control.
You can first do a union query to but all the years into a single column. Then you can use an aggregate query to pull the last three years of data. From there you can do a dcount to show if the last 3 years are the same or match the current year.

Can you provide a little more data on your table fields and the rules for your calculation.

You are correct in that there is a column for each year's election. The data we have is from Pennsylvania's web site. There is a record for each voter ID with no name attached. So each voters id has columns for many different years of voting. Will check the dlookup & dcount functions to see if they will work. Again, thanks for your help.

 

[MajP]

You can do a normalization query which can help solve these questions.
Assume my data is this
Table1 Table1

VoterID	2016	2017	2018	2019	2020
1​	D	D	D	D	D
2​	R	R	R	R	R
3​	R	R	D	D	D
4​	D	R	D	R	D
5​	D	D	R	R	R
6​	D	D	R	R	D

You can write a Union Query to get this
qryVoting qryVoting

VoterID	Party	YearVoted
1​	D	2016
1​	D	2017
1​	D	2018
1​	D	2019
1​	D	2020
2​	R	2016
2​	R	2017
2​	R	2018
2​	R	2019
2​	R	2020
3​	R	2016
3​	R	2017
3​	D	2018
3​	D	2019
3​	D	2020
4​	D	2016
4​	R	2017
4​	D	2018
4​	R	2019
4​	D	2020
5​	D	2016
5​	D	2017
5​	R	2018
5​	R	2019
5​	R	2020
6​	D	2016
6​	D	2017
6​	R	2018
6​	R	2019
6​	D	2020

Then you can do an aggregate query from this to get the counts for the last three years by party
qryCountInLastThree qryCountInLastThree

VoterID	Party	CountOfVoterID
1​	D	3​
2​	R	3​
3​	D	3​
4​	D	2​
4​	R	1​
5​	R	3​
6​	D	1​
6​	R	2​

So voter 1 voted D for last 3 years, and voter 4 voted 2 D and 1R in last three years.

You can then filter that to show who voted the same way in last three years.
qrySameLast3 qrySameLast3

VoterID	Party	CountOfVoterID
1​	D	3​
2​	R	3​
3​	D	3​
5​	R	3​

So 1 and 3 voted D each of the last three and 2,5 voted R each of the last three.

 

[foxxrunning]

You can do a normalization query which can help solve these questions.
Assume my data is this
Table1 Table1

VoterID	2016	2017	2018	2019	2020
1​	D	D	D	D	D
2​	R	R	R	R	R
3​	R	R	D	D	D
4​	D	R	D	R	D
5​	D	D	R	R	R
6​	D	D	R	R	D

You can write a Union Query to get this
qryVoting qryVoting

VoterID	Party	YearVoted
1​	D	2016
1​	D	2017
1​	D	2018
1​	D	2019
1​	D	2020
2​	R	2016
2​	R	2017
2​	R	2018
2​	R	2019
2​	R	2020
3​	R	2016
3​	R	2017
3​	D	2018
3​	D	2019
3​	D	2020
4​	D	2016
4​	R	2017
4​	D	2018
4​	R	2019
4​	D	2020
5​	D	2016
5​	D	2017
5​	R	2018
5​	R	2019
5​	R	2020
6​	D	2016
6​	D	2017
6​	R	2018
6​	R	2019
6​	D	2020

Then you can do an aggregate query from this to get the counts for the last three years by party
qryCountInLastThree qryCountInLastThree

VoterID	Party	CountOfVoterID
1​	D	3​
2​	R	3​
3​	D	3​
4​	D	2​
4​	R	1​
5​	R	3​
6​	D	1​
6​	R	2​

So voter 1 voted D for last 3 years, and voter 4 voted 2 D and 1R in last three years.

You can then filter that to show who voted the same way in last three years.
qrySameLast3 qrySameLast3

VoterID	Party	CountOfVoterID
1​	D	3​
2​	R	3​
3​	D	3​
5​	R	3​

So 1 and 3 voted D each of the last three and 2,5 voted R each of the last three.

That is almost exactly what we wanted to do. Don't believe I have used anything other than the regular query but am eager to try the union and aggregate queries to see what they do. Thanks again for this suggestion.

 

[MajP]

Some of these queries like Unions cannot be made using the query builder. You have to go to SQL view and type it in. The following is a great source for all types of queries.

SQL UNION Operator

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

This is good for doing aggregate (group by)

All About Aggregate Queries Using Access 2016

An aggregate query lets you determine statistics for a set of values. For example, you can determine the sum, average, and count. An aggregate query is also known as a Summary Query or a Totals Query.

www.universalclass.com

SQL is amazingly fast and efficient. Always try to do in queries before doing it in code.

One thing I would highly caution you about. Since this is Pennsylvania data.
If you find any user data that by chance looks like this

2016 R
2017 R
2018 R
2019 R
2020 D

You better ignore all of that data and destroy it. You do not want to be considered some kind of conspiracy theorist or insurrectionst. Just saying.

 

[foxxrunning]

Some of these queries like Unions cannot be made using the query builder. You have to go to SQL view and type it in. The following is a great source for all types of queries.

SQL UNION Operator

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

This is good for doing aggregate (group by)

All About Aggregate Queries Using Access 2016

An aggregate query lets you determine statistics for a set of values. For example, you can determine the sum, average, and count. An aggregate query is also known as a Summary Query or a Totals Query.

www.universalclass.com

SQL is amazingly fast and efficient. Always try to do in queries before doing it in code.

One thing I would highly caution you about. Since this is Pennsylvania data.
If you find any user data that by chance looks like this

2016 R
2017 R
2018 R
2019 R
2020 D

You better ignore all of that data and destroy it. You do not want to be considered some kind of conspiracy theorist or insurrectionst. Just saying.

The data we obtained is available to the public to download. Not too fearful of being called a insurrectionist as I was far from DC and looked on aghast at what was happening. Thanks for the url. I'll investigate it.

 

[foxxrunning]

With regard to this: Forms![Formtest].[textboxname] = c

If this calculation is done from FormTest then you can use Me.textboxname in place of the longer reference. If the calculation is done from some other form, you can only make this work if you first verify that FormTest is actually open at the time.

The reference to "pulling" the data is this, and I will generalize with simple names, two forms A and B, and three text boxes TA (on A) and TB (on B) and TB1 (on B). If you want TA to contain data computed based on TB and TB1, then you can get it there two ways.

From some event on form A, if you do Me.TA = Forms!B.TB * Forms!B.TB1, you are PULLING the data from B, which has to be open at the time. Since you are doing it from A, A is known to be open.
From some event on form B, if you do Forms!A.[TA] = Me.TB * Me.TB1, you are PUSHING the data to A, which has to be open at the time. Since you are doing it from B, B is known to be open.

I understand now. Thank you for your explanation.