Concatenation of field name

[barboza]

I am trying to get a message box to show the contents in several fields in a Do loop by concatenating the letter "F" + a counter.
The following are values in fields in the form I am working with
Field Name Value in Field
F8 A
F9 B
F10 C

What is the code that will pop up a MsgBox to show the value (A) that is in field F8 of the form?

Example: MsgBox "F" & "8" to display A in the MsgBox.

All I have been able to get the message box to display is F8 or F9 or F10, not the values in the fields - A or B or C.

Thanks,

BB

 

[boblarson]

How are you trying to bring up the message box? Is it on an event on a form? If in the recordsource of the form then:

MsgBox "F" & Me!YourFieldName

 

[barboza]

VBA concatenation Problem

This is an event for a command button in a form.
As in original message - no problem making the "word" F8.
The problem is displaying the contents of the text box in the form.

Private Sub cmdContinue_Click()
'strA = "8"
Do While "Me.F" & strA <> ""
MsgBox strA
strA= strA +1
End

 

[barboza]

Correction to previous reply

Private Sub cmdContinue_Click()
strA = "8"
Do While "Me.F" & strA <> ""
MsgBox "Me.F" & strA
strA= strA +1
End

The MsgBox displays Me.F8 not A

thanks

 

[boblarson]

Private Sub cmdContinue_Click()
strA = "8"
Do While "Me.F" & strA <> ""
MsgBox "Me.F" & strA
strA= strA +1
End

The MsgBox displays Me.F8 not A

thanks

Of course it would. You are assigning the text number 8 to the string. You need to use the ACTUAL FIELD NAME. So, what is the field name? Is it really F8? If so, it would be

MsgBox "F" & Me!F8

Don't put quotes where I didn't put quotes because they are there for a purpose. That also goes for the ! operator.

 

[boblarson]

By the way, what are you actually trying to accomplish?

 

[barboza]

Concat cont'd

We are an environmental testing lab.
We enter samples into a table called Custody using a form - frmCustody.

The form has more than 20 text boxes. Each text box is from a field in the Custody table.

The form has text boxes F8 thru F28.
When using the form we enter lab tests (analyses to be performed) in several of the fields F8 thru F28.

Ex. Lead - Pb, Copper- Cu, Mercury- Hg, etc....

We want to validate the entries in fields F8 thru F28 to make sure that the valid test codes have been entered (no typos).

When the form is completed, we have a command button called Continue.

When continue has been clicked, we wish to validate the entries in fields F8 thru F28 against the list of tests we perform that are contained in a table called tests.

I want to loop thru fields F8 through F28 comparing what has been entered in each field with the list of valid test codes in our test table.

In vb I would normally concatenate the field names using a counter in a loop. I am new to Access.

To simplify I used the message box as an example.

I do not want to type:
MsgBox Me.F8 to display the test entered in F8
MsgBox Me.F9 to display the test entered in F9
MsgBox Me.F10 to display the test entered in F10

20 some odd times.

I'm trying to use a counter in a loop.


counter = 8
Do while Me.F & counter <> ""
MsgBox "Me.f" + counter
Counter = Counter + 1
Loop

I want the MsgBox to display the Contents of Me.F8 NOT the text ME.F8


Thanks again,

BB

 

[boblarson]

Okay, that makes more sense. Thanks for the explanation. You SHOULD be able to reference it this way:

MsgBox Me.Controls("F" & counter).Value

 

[barboza]

Concatenation Thank you

Hi Bob,

Thanks for the reply. I can't wait to get home and give it a shot. I have a feeling that the snippet you sent will do the job.

Thanks again.

BB