thanks for your help.
this is the code i got to work....
strA = "Tab" & [gbl_CommandPointer]
'capture current order
strTabA = Forms![main].Form(strA)
'which point to a field called Tab1
so basically, depending on which command buttom i press, will give me a number (gbl_CommandPointer) which is used to recall the information held in field Tab1 or Tab2 or Tab3 etc
Running on the same lines I'm trying to change the caption on a control indirectly. PS. Is there somewhere i can look up addressing fields , controls etc? I've tried the code below but it throws run-time error 2465:-
strTabA = Forms![main].Command(strA).Caption
'strA hold a integer value. I have several buttons called Command1 Command2 etc. which i would like to change the caption to the string held in strTabA.
You cannot create an array of controls in VBA, contrarily to what can be done in VB, so: Command(strA) cannot work. If the names of the controls are Command1, Command2, etc., you can recompose the name from the value of strA like this:
ControlName = "Command" & strA
In such a case, you can address the control from the Controls collection only:
Forms!Main.Controls("Command" & strA)
Finally, if you want to change the caption (of a command button) to the string held in strTabA, it should be the other way around:
Personally I would not work like that. With your method you cannot change the captions several times, except if you memorize each previous value. I would store the command buttons into a Collection object and address them from there. Here's an example:
One form has 6 command buttons named Command1, Command1, ... etc. to Command6. The form also has 2 textboxes named Text_ButtonIndex and Text_ButtonCaption.
When you type something in Text_ButtonCaption, it changes the caption of the command button corresponding to the numeric value contained in Text_ButtonIndex, and when you change the numeric value of Text_ButtonIndex (between 1 and 6) it changes the caption of the corresponding button to what's contained in Text_ButtonCaption. Here's the code of the form module:
Option Compare Database
Private m_colCommandButtons As Collection
Private Sub Form_Open(Cancel As Integer)
Dim i As Integer
' Store the command buttons into the collection.
Set m_colCommandButtons = New Collection
' The form has 6 command buttons named "Command1" to "Command6"
For i = 1 To 6
m_colCommandButtons.Add Me.Controls("Command" & CStr(i)), CStr(i)
Private Sub Text_ButtonCaption_AfterUpdate()
Private Sub Text_ButtonIndex_AfterUpdate()
Private Sub ChangeCaption()
Dim strIndex As String
strIndex = Nz(Me.Text_ButtonIndex.Value, 0)
Select Case Val(strIndex)
Case 1 To 6: m_colCommandButtons.Item(strIndex).Caption = Nz(Me.Text_ButtonCaption.Value, "")
Case Else: MsgBox "The index must be between 1 and 6", vbInformation
Me.Text_ButtonIndex.Value = Null