473,326 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Consecutive Numbering of Text Box Names or Control Properties in Access

Hi
Anyone know a quick and simple way of consecutively naming text box
names (eg RR1, RR2, RR3 etc) or control source calculations? For
example, the following calculation is for month 1:

=IIf([m1rr] And [m1dbed] And [m1vd]=-1,1,IIf([m1rr]=-1 And [m1dbed]=-1
And [m1vd]=0,2,IIf([m1rr]=-1 And [m1dbed]=0 And
[m1vd]=0,3,IIf([m1rr]=0 And [m1dbed]=-1 And [m1vd]=-1,4,IIf([m1rr]=0
And [m1dbed]=-1 And [m1vd]=0,5,IIf([m1rr]=0 And [m1dbed]=0 And
[m1vd]=0,6,IIf([m1rr]=-1 And [m1dbed]=0 And [m1vd]=-1,7)))))))

For month 2 I want to change m1rr for m2rr and so on - quite time
consuming if you have to go and do each one manually.

Thanks
John
Nov 13 '05 #1
4 1419
There are a couple of ways.

1. Create a template for the expression, and set the control source based on
replacing a special substring. For example...

Public Sub SetupMonthlyCalcs() ' Call this from Form_Open
Const cstrCalcTemplate = "=IIf([m~rr] And [m~dbed] And [m~vd]=-1" & _
",1,IIf([m~rr]=-1 And [m~dbed]=-1 And " & _
"[m~vd]=0,2,IIf([m~rr]=-1 And [m~dbed]=0 " & _
"And [m~vd]=0,3,IIf([m~rr]=0 And " & _
"[m~dbed]=-1 And [m~vd]=-1,4,IIf([m~rr]=0" & _
"And [m~dbed]=-1 And [m~vd]=0,5," & _
"IIf([m~rr]=0 And [m~dbed]=0 And " & _
"[m~vd]=0,6,IIf([m~rr]=-1 And [m~dbed]=0 " & _
"And [m~vd]=-1,7)))))))"
Dim lngMonthNum As Long
Dim strSetControlSource As String

For lngMonthNum = 1 To 12
strSetControlSource = Replace(cstrCalcTemplate, "~", 1)
Me.Controls("MonthCalc" & lngMonthNum).ControlSource = _
strSetControlSource
Next

End Sub

2. Convert this expression into a a user-defined function, and call that from
the control expression - something like this...

Public Function CalculationXyz(ByVal MonthNum As Long) As Variant
Dim lngRr As Long
Dim lngDbed As Long
Dim lngVd As Long
Dim lngCombinationCode As Long

lngRr = Me!Controls("m" & MonthNum & "rr")
lngDbed = Me!Controls("m" & MonthNum & "dbed")
lngVd = Me!Controls("m" & MonthNum & "vd")

' Makes conditional logic simpler (below)
lngCombinationCode = Abs(blnRr) * 100 + _
Abs(blnDbed) * 10) + _
Abs(lngVd)

Select Case lngCombinationCode ' (rr, dbed, vd)
Case 111: CalculationXyz = 1
Case 110: CalculationXyz = 2
Case 100: CalculationXyz = 3
Case 11: CalculationXyz = 4
Case 10: CalculationXyz = 5
Case 0: CalculationXyz = 6
Case 101: CalculationXyz = 7
Case Else: CalculationXyz = Null
End Select

End Function

On 28 Oct 2004 06:44:46 -0700, jo********@hotmail.com (John Smith) wrote:
Hi
Anyone know a quick and simple way of consecutively naming text box
names (eg RR1, RR2, RR3 etc) or control source calculations? For
example, the following calculation is for month 1:

=IIf([m1rr] And [m1dbed] And [m1vd]=-1,1,IIf([m1rr]=-1 And [m1dbed]=-1
And [m1vd]=0,2,IIf([m1rr]=-1 And [m1dbed]=0 And
[m1vd]=0,3,IIf([m1rr]=0 And [m1dbed]=-1 And [m1vd]=-1,4,IIf([m1rr]=0
And [m1dbed]=-1 And [m1vd]=0,5,IIf([m1rr]=0 And [m1dbed]=0 And
[m1vd]=0,6,IIf([m1rr]=-1 And [m1dbed]=0 And [m1vd]=-1,7)))))))

For month 2 I want to change m1rr for m2rr and so on - quite time
consuming if you have to go and do each one manually.

Thanks
John


Nov 13 '05 #2
Thanks for your help Steve.

I've taken a look at this but really don't have the understanding of
Access to take advantage of what you've provided. I'm afraid that I
simply don't have sufficient knowledge of VB code to intepret and use
it.

Thanks for your time anyway.
John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3
Oops - mistake in example #1. Should be ... = Replace(cstrCalcTemplate, "~",
lngMonthNum)

On Thu, 28 Oct 2004 14:22:21 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:
There are a couple of ways.

1. Create a template for the expression, and set the control source based on
replacing a special substring. For example...

Public Sub SetupMonthlyCalcs() ' Call this from Form_Open
Const cstrCalcTemplate = "=IIf([m~rr] And [m~dbed] And [m~vd]=-1" & _
",1,IIf([m~rr]=-1 And [m~dbed]=-1 And " & _
"[m~vd]=0,2,IIf([m~rr]=-1 And [m~dbed]=0 " & _
"And [m~vd]=0,3,IIf([m~rr]=0 And " & _
"[m~dbed]=-1 And [m~vd]=-1,4,IIf([m~rr]=0" & _
"And [m~dbed]=-1 And [m~vd]=0,5," & _
"IIf([m~rr]=0 And [m~dbed]=0 And " & _
"[m~vd]=0,6,IIf([m~rr]=-1 And [m~dbed]=0 " & _
"And [m~vd]=-1,7)))))))"
Dim lngMonthNum As Long
Dim strSetControlSource As String

For lngMonthNum = 1 To 12
strSetControlSource = Replace(cstrCalcTemplate, "~", 1)
Me.Controls("MonthCalc" & lngMonthNum).ControlSource = _
strSetControlSource
Next

End Sub

2. Convert this expression into a a user-defined function, and call that from
the control expression - something like this...

Public Function CalculationXyz(ByVal MonthNum As Long) As Variant
Dim lngRr As Long
Dim lngDbed As Long
Dim lngVd As Long
Dim lngCombinationCode As Long

lngRr = Me!Controls("m" & MonthNum & "rr")
lngDbed = Me!Controls("m" & MonthNum & "dbed")
lngVd = Me!Controls("m" & MonthNum & "vd")

' Makes conditional logic simpler (below)
lngCombinationCode = Abs(blnRr) * 100 + _
Abs(blnDbed) * 10) + _
Abs(lngVd)

Select Case lngCombinationCode ' (rr, dbed, vd)
Case 111: CalculationXyz = 1
Case 110: CalculationXyz = 2
Case 100: CalculationXyz = 3
Case 11: CalculationXyz = 4
Case 10: CalculationXyz = 5
Case 0: CalculationXyz = 6
Case 101: CalculationXyz = 7
Case Else: CalculationXyz = Null
End Select

End Function

On 28 Oct 2004 06:44:46 -0700, jo********@hotmail.com (John Smith) wrote:
Hi
Anyone know a quick and simple way of consecutively naming text box
names (eg RR1, RR2, RR3 etc) or control source calculations? For
example, the following calculation is for month 1:

=IIf([m1rr] And [m1dbed] And [m1vd]=-1,1,IIf([m1rr]=-1 And [m1dbed]=-1
And [m1vd]=0,2,IIf([m1rr]=-1 And [m1dbed]=0 And
[m1vd]=0,3,IIf([m1rr]=0 And [m1dbed]=-1 And [m1vd]=-1,4,IIf([m1rr]=0
And [m1dbed]=-1 And [m1vd]=0,5,IIf([m1rr]=0 And [m1dbed]=0 And
[m1vd]=0,6,IIf([m1rr]=-1 And [m1dbed]=0 And [m1vd]=-1,7)))))))

For month 2 I want to change m1rr for m2rr and so on - quite time
consuming if you have to go and do each one manually.

Thanks
John


Nov 13 '05 #4
Do you have any specific questions on this that we could help with?

It's not really that hard get to the VBA code module behind a from, and paste
some VBA code. Do note my other reply, though in which I found a typo in my
example.

On 30 Oct 2004 05:07:24 GMT, John Smith <jo********@hotmail.com> wrote:
Thanks for your help Steve.

I've taken a look at this but really don't have the understanding of
Access to take advantage of what you've provided. I'm afraid that I
simply don't have sufficient knowledge of VB code to intepret and use
it.

Thanks for your time anyway.
John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Bruce Rusk | last post by:
I'm using Stephen Lebans' RTF2 control in a report, and have discovered what may be a slight bug in it. I have a lot of non-Western language (Chinese) text in my RTF field, and such records get...
13
by: TC | last post by:
Folks Is there >>ANY<< way to get the actual text of an error that is trapped by the Form_Error event? I mean actual text like: "duplicate record in table XYZ", not template text like:...
2
by: Rob | last post by:
Hi, I have an access 2000 application.I generate a report with Query as the recordsource.This report is the analysis of data Vendorwise.The vendors are listed in ascending order...
3
by: RR | last post by:
We have cards that are numbered consecutively. These cards are given out to different people in different sized batches. One group might get 5, the next group might get 20. What is a good...
54
by: MLH | last post by:
I use A97 and do not always insert line numbers while writing procedures. I find it necessary to go back and add them later to aid in debugging. Nearly 3 years ago, something was mentioned in...
1
by: Simon | last post by:
Dear reader, Most of the reports have a page numbering as Page x of nn, starting at the first page 1 (one) to the last page nn. But know I need to start with Page 1 of nn for each...
6
by: Kevin Chambers | last post by:
Hi all-- In an attempt to commit an Access MDB to a versioning system (subversion), I'm trying to figure out how to convert a jet table's metadata to text, a la SaveAsText. The end goal is to...
4
by: Miro | last post by:
Sorry for all the posts today. Just not been a good day. I cant seem to figure out version numbering. I click on the MyProject and set up the Major, Minor, MajorRevision, MinorRevision as ...
4
by: Steven Simpson | last post by:
Stefan Ram wrote (in "More than one language in a page"): Is this a new trend of user-agent writers (Microformats, and now Google) staking claims on the @class namespace? I'm surely not the only...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.