Concatenate without string or convert string to variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 2003, and I want to be able to simplify a very repetitious
bit of code by calling a sub with two input numbers. This is for a
scoreboard program I am doing in Access, using VBA to calculate the new
totals. There are three teams and three players on each team, and I want to
be able to just do something like Score(1, 1) for the first player on the
first team.

Here is what my code is like at the moment. It works if I do it directly
rather than calling the sub and having it do the concatenating, but I can't
use it this way because in concatenating the parts, everything turns to
strings. Is there any way to fix that, or do I just have to work around it
another way?

Public Sub Right(Team As String, Player As String)
'First read the Team and Player to reference TeamX_PlayerXScore
'which is where the score is added to the player's total
ThisPlayer = "Team" & Team & "_Player" & Player & "Score"
'Now create a reference to the location where the team score is posted
'with the format TXScoreboard
ThisTeam = "T" & Team & "Scoreboard"
'Now create a reference to the field where the team total for the round is
saved
'with the format TXScore
ThisScore = "T" & Team & "Score"
'Using the references, attempt to make the calculations
'InputHere is the score for the current question, and is identified
'at the point when the input box loses focus
ThisScore = ThisScore + InputHere
ThisPlayer = ThisPlayer + InputHere
ThisTeam.Value = CStr(ThisScore)

'The problem is that of course it sees ThisPlayer, ThisTeam, and ThisScore
'as strings instead of as references to fields and text boxes.

End Sub

SSB
 
SSBSystemsClerk said:
I'm using Access 2003, and I want to be able to simplify a very repetitious bit of code
Click to expand...

A laudable aim!

by calling a sub with two input numbers.
Click to expand...

call MySub(11,22)
or:
MySub 11, 22

This is for a scoreboard program I am doing in Access, using VBA to calculate the new totals.
Click to expand...

Ok. Sorta. Understand that we do not know what totals you're referring
to, yet.

There are three teams and three players on each team,
Ok.

and I want to be able to just do something like Score(1, 1) for the first player on the first team.
Click to expand...

Sorry, your question falls apart here. What do you mean, "do something
like Score(1,1)" ? That really doesn't tell us what you're actually
trying to do.

Tell us what you're trying to achieve, from an end-use viewpoint.
Describe it in normal narrative text. We can not know, from lines of
code. If the code was an accurate representation of what you are trying
to do, you wouldn't be asking the question, would you! :-)

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
Oh dear, trying to explain these in the proper computerese is not my
strongpoint, obviously!

Let me see... I have a "+" and "-" button for each player. When I click the
"+" button, I want to run this code using the OnClick event for that button.
But rather than do the whole code for each button for each player, I want to
OnClick code to work by using Call Right(Team, Player) where Team is the
number of the team, and player is the number of the player--that is Right(1,
1), for player 1 team 1. The code I posted already is the code I'm using to
try to achieve that. Of course, when I try to do it, it doesn't work, so I
go into the debugger, and I can tell from that that my number one problem is
that why I try to add 200 (points) to ThisScore, it's (at best) coming up
with something like "Score1200", instead of recognizing that Score1 is a
reference to a field with the value 0 or 400 or whatever it happens to be at
the time.

But if I just put the code directly into the cmdAddT1P1_Click () code like
this:
Team1_Player1Score = Team1_Player1Score + InputHere
T1ScoreBoard = T1ScoreBoard + InputHere
T1Score = T1ScoreBoard

....it works fine. I just don't want to have to duplicate all that code 18
times if there is a more efficient way to do it.
 
I'm by no means sure I understand you, but maybe this will help.

If you have code in a Form's module (e.g. in a button's Click event
procedure, or in an ordinary Sub procedure in the module), you can refer
to the value of a textbox named txtXXX on the form using syntax like
this
Me.Controls("txtXXX").Value
Me.txtXXX.Value
or
Me.txtXXX
or this
Me!txtXXX
(these are not the only possibilities).

You can also use this syntax
Me.XXX.Value
to get the value of a field in the form's Recordsource. As you can see,
there's room for confusion if your textboxes have the same names as the
fields they are bound to.

If you find that 100 + 200 comes to 100200, you may also need to check
out the type conversion functions Val(), CDbl(), CLng() and so on.
 
Sorry, I thought I was getting notified about updates, but something went
wrong there. I finally went back to check again anyway, and there was your
post.

The good new is that yes, your solution worked!
"Me.Controls("txtXXX").Value" in particular was the missing key I needed.
Thanks a bunch! :-)
 
Back
Top