Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Forms Programming / March 2007

Tip: Looking for answers? Try searching our database.

referring to different variable's values in a loop

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bob - 14 Mar 2007 09:12 GMT
Hi all,

I'm trying to refer to a different variable each time I loop through a
procedure, as below:

I'm trying to loop through the different Menu1ItemCount,
Menu2ItemCount etc variables so as to determine how many times to loop
through one part of the code.  So basically I want the variable
CurrMenu to be able to equal the variables Menu1ItemCount,
Menu2ItemCount etc. So I've tried to do that by "CurrMenu = "Menu" &
MenuNo & "ItemCount" where MenuNo being the 1, 2, 3 etc so then
CurrMenu will reflect the value of the correct variable - not the text
name of the variable!  What sort of variable does CurrMenu need to be
set to (I have it as an object but that doesn't work), or what line do
I need to tell the system that its not text, its actually supposed to
be the value of the variable??

Dim MenuNo, ItemNo As Integer
Dim Menu1ItemCount, Menu2ItemCount, Menu3ItemCount, Menu4ItemCount,
Menu5ItemCount, Menu6ItemCount, Menu7ItemCount As Integer
Dim Menu8ItemCount, Menu9ItemCount As Integer
Dim MenuCount As Integer
Dim CurrMenu As Object

'# of Menus
MenuCount = 5

'# of Items in each Menu
Menu1ItemCount = 3
Menu2ItemCount = 6
Menu3ItemCount = 8
Menu4ItemCount = 2
Menu5ItemCount = 3

ItemNo = 1
MenuNo = 1

Do Until MenuNo = MenuCount + 1

CurrMenu = "Menu" & MenuNo & "ItemCount"  'should equal the variable's
value not the variable's name!

   Do Until ItemNo = CurrMenu

       Form_MainMenu.Controls("Menu" & MenuNo & "Lbl" &
ItemNo).Visible = False

       ItemNo = ItemNo + 1

   Loop

   MenuNo = MenuNo + 1

Loop
Reply to this Message
Allen Browne - 14 Mar 2007 09:26 GMT
I'm not sure I've understood exactly what you want in the end, but it looks
like you want an array:

Dim aiMenu(1 to 5) As Integer
Dim i As Integer
Dim strName As String

aiMenu(1) = 3
aiMenu(2) = 6
aiMenu(3) = 8
aiMenu(4) = 2
aiMenu(5) = 3

For i = 1 to 5
   strName = "Menu" & i & "Lbl" & aiMenu(i)
   Me(strName).Visible = False
Next

BTW, Bob, you should also be aware that declarations are different in VBA
than in some other languages. The declaration:
   Dim MenuNo, ItemNo As Integer
does not give you 2 integers.
MenuNo has no type specified, so you get a variant (default type), and an
integer.
You probably intended:
   Dim MenuNo As Integer, ItemNo As Integer

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi all,
>
[quoted text clipped - 50 lines]
>
> Loop
Reply to this Message
Bob - 14 Mar 2007 23:39 GMT
> I'm not sure I've understood exactly what you want in the end, but it looks
> like you want an array:
[quoted text clipped - 83 lines]
>
> - Show quoted text -

Thanks Allen, you were right about the variables, I didn't realise
that was the case.

Also though, I want to have a variable number of elements in each
group... So I've got Menu1, Menu2, Menu3, Menu4, Menu5 for example
(although I might want to add a Menu6 and Menu7 later) and each has 2,
4, 4, 5 and 3 items respectively.  How do I loop through each Menu and
then inside that loop, loop through each menu item to make it
invisible, while still being able to add more Menu's and Items later
easily?
Reply to this Message
Allen Browne - 15 Mar 2007 02:05 GMT
How will your code know how many items there are in the end?
One you have determined that, you can ReDim the array to that many items,
e.g.:
   iHowMany = 9
   ReDim aiMenu(1 to iHowMany)

I'm not really clear what you are doing here, but I think you are trying to
use controls on the form to simluate a menu. If so, it might be easier to
put the menu items in a table rather than hard code them.

If you are actually referring to items on command bars, I have not
understood you correctly.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>> I'm not sure I've understood exactly what you want in the end, but it
>> looks
[quoted text clipped - 93 lines]
> invisible, while still being able to add more Menu's and Items later
> easily?
Reply to this Message
Bob - 15 Mar 2007 06:28 GMT
On Mar 15, 12:05 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
> How will your code know how many items there are in the end?
> One you have determined that, you can ReDim the array to that many items,
[quoted text clipped - 113 lines]
>
> - Show quoted text -

Hi Allen,

Yes I am doing a menu which is labels which pop out from another
label.  How would you suggest I add them to a table then bring them
back out and add graphics to them and also execute the correct
commands when clicked?
Reply to this Message
Allen Browne - 15 Mar 2007 07:03 GMT
I wouldn't even try to do that, Bob.

It sounds like the kind of thing people do on web pages with JavaScript,
rather than the kind of approach that would be productive in Access where
command bars give this kind of functionality.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> On Mar 15, 12:05 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
> wrote:
[quoted text clipped - 127 lines]
> back out and add graphics to them and also execute the correct
> commands when clicked?
Reply to this Message
Bob - 15 Mar 2007 23:31 GMT
> I wouldn't even try to do that, Bob.
>
[quoted text clipped - 144 lines]
>
> - Show quoted text -

Hi Allen,

I've already done the menus - I just put a bold label with no
background at the top of the page then labels with backgrounds under
them to look kinda like a web menu and just messed about with the
visibility of them in the mousemove action.  It works really well and
looks great but I wanted to clean it up a little - thats why I wanted
loops in there.  Its a lot of lines for something quite simple but it
took no time at all to do though.  I've put the code below.  Do you
have any suggestions on how I could clean it up?

Cheers,

Bob.

Const UnselectedBG As Long = 12632256
Const SelectedBG As Long = 11316396

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As
Single, Y As Single)

'Menu1
Menu1Lbl1.Visible = False
Menu1Lbl2.Visible = False
Menu1Lbl3.Visible = False

'Menu2
Menu2Lbl1.Visible = False
Menu2Lbl2.Visible = False
Menu2Lbl3.Visible = False
Menu2Lbl4.Visible = False
Menu2Lbl5.Visible = False
Menu2Lbl6.Visible = False

'Menu3
Menu3lbl1.Visible = False
Menu3lbl2.Visible = False
Menu3lbl3.Visible = False
Menu3lbl4.Visible = False
Menu3lbl5.Visible = False
Menu3lbl6.Visible = False
Menu3lbl7.Visible = False
Menu3lbl8.Visible = False

'Menu4
Menu4lbl1.Visible = False
Menu4lbl2.Visible = False

'Menu5
Menu5lbl1.Visible = False
Menu5lbl2.Visible = False
Menu5lbl3.Visible = False

'Menu6
Menu6Lbl1.Visible = False
Menu6lbl2.Visible = False
Menu6lbl3.Visible = False
Menu6lbl4.Visible = False

End Sub

Private Sub Menu1Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu1Lbl1.BackColor = SelectedBG '11316396
Menu1Lbl2.BackColor = UnselectedBG
Menu1Lbl3.BackColor = UnselectedBG

End Sub

Private Sub Menu1Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu1Lbl1.BackColor = UnselectedBG
Menu1Lbl2.BackColor = SelectedBG
Menu1Lbl3.BackColor = UnselectedBG

End Sub
Private Sub Menu1Lbl3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu1Lbl1.BackColor = UnselectedBG
Menu1Lbl2.BackColor = UnselectedBG
Menu1Lbl3.BackColor = SelectedBG

End Sub

Private Sub Menu2Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = SelectedBG
Menu2Lbl2.BackColor = UnselectedBG
Menu2Lbl3.BackColor = UnselectedBG
Menu2Lbl4.BackColor = UnselectedBG
Menu2Lbl5.BackColor = UnselectedBG
Menu2Lbl6.BackColor = UnselectedBG

End Sub
Private Sub Menu2Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = UnselectedBG
Menu2Lbl2.BackColor = SelectedBG
Menu2Lbl3.BackColor = UnselectedBG
Menu2Lbl4.BackColor = UnselectedBG
Menu2Lbl5.BackColor = UnselectedBG
Menu2Lbl6.BackColor = UnselectedBG

End Sub
Private Sub Menu2Lbl3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = UnselectedBG
Menu2Lbl2.BackColor = UnselectedBG
Menu2Lbl3.BackColor = SelectedBG
Menu2Lbl4.BackColor = UnselectedBG
Menu2Lbl5.BackColor = UnselectedBG
Menu2Lbl6.BackColor = UnselectedBG

End Sub
Private Sub Menu2Lbl4_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = UnselectedBG
Menu2Lbl2.BackColor = UnselectedBG
Menu2Lbl3.BackColor = UnselectedBG
Menu2Lbl4.BackColor = SelectedBG
Menu2Lbl5.BackColor = UnselectedBG
Menu2Lbl6.BackColor = UnselectedBG

End Sub
Private Sub Menu2Lbl5_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = UnselectedBG
Menu2Lbl2.BackColor = UnselectedBG
Menu2Lbl3.BackColor = UnselectedBG
Menu2Lbl4.BackColor = UnselectedBG
Menu2Lbl5.BackColor = SelectedBG
Menu2Lbl6.BackColor = UnselectedBG

End Sub
Private Sub Menu2Lbl6_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = UnselectedBG
Menu2Lbl2.BackColor = UnselectedBG
Menu2Lbl3.BackColor = UnselectedBG
Menu2Lbl4.BackColor = UnselectedBG
Menu2Lbl5.BackColor = UnselectedBG
Menu2Lbl6.BackColor = SelectedBG

End Sub

Private Sub Menu3Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = SelectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = SelectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = SelectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl4_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = SelectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl5_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = SelectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl6_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = SelectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl7_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = SelectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl8_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = SelectedBG

End Sub

Private Sub Menu4Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu4lbl1.BackColor = SelectedBG
Menu4lbl2.BackColor = UnselectedBG

End Sub

Private Sub Menu4Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu4lbl1.BackColor = UnselectedBG
Menu4lbl2.BackColor = SelectedBG

End Sub

Private Sub Menu5Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu5lbl1.BackColor = SelectedBG
Menu5lbl2.BackColor = UnselectedBG
Menu5lbl3.BackColor = UnselectedBG

End Sub

Private Sub Menu5Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu5lbl1.BackColor = UnselectedBG
Menu5lbl2.BackColor = SelectedBG
Menu5lbl3.BackColor = UnselectedBG

End Sub
Private Sub Menu5Lbl3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu5lbl1.BackColor = UnselectedBG
Menu5lbl2.BackColor = UnselectedBG
Menu5lbl3.BackColor = SelectedBG

End Sub
Private Sub Menu6Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu6Lbl1.BackColor = SelectedBG
Menu6lbl2.BackColor = UnselectedBG
Menu6lbl3.BackColor = UnselectedBG
Menu6lbl4.BackColor = UnselectedBG

End Sub

Private Sub Menu6Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu6Lbl1.BackColor = UnselectedBG
Menu6lbl2.BackColor = SelectedBG
Menu6lbl3.BackColor = UnselectedBG
Menu6lbl4.BackColor = UnselectedBG

End Sub
Private Sub Menu6Lbl3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu6Lbl1.BackColor = UnselectedBG
Menu6lbl2.BackColor = UnselectedBG
Menu6lbl3.BackColor = SelectedBG
Menu6lbl4.BackColor = UnselectedBG

End Sub
Private Sub Menu6Lbl4_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu6Lbl1.BackColor = UnselectedBG
Menu6lbl2.BackColor = UnselectedBG
Menu6lbl3.BackColor = UnselectedBG
Menu6lbl4.BackColor = SelectedBG

End Sub

Private Sub lblMenu1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu1Lbl1.Visible = True
Menu1Lbl2.Visible = True
Menu1Lbl3.Visible = True

End Sub
Private Sub lblMenu2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.Visible = True
Menu2Lbl2.Visible = True
Menu2Lbl3.Visible = True
Menu2Lbl4.Visible = True
Menu2Lbl5.Visible = True
Menu2Lbl6.Visible = True

End Sub

Private Sub lblMenu3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.Visible = True
Menu3lbl2.Visible = True
Menu3lbl3.Visible = True
Menu3lbl4.Visible = True
Menu3lbl5.Visible = True
Menu3lbl6.Visible = True
Menu3lbl7.Visible = True
Menu3lbl8.Visible = True

End Sub

Private Sub lblMenu4_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu4lbl1.Visible = True
Menu4lbl2.Visible = True

End Sub
Private Sub lblMenu5_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu5lbl1.Visible = True
Menu5lbl2.Visible = True
Menu5lbl3.Visible = True

End Sub
Private Sub lblMenu6_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu6Lbl1.Visible = True
Menu6lbl2.Visible = True
Menu6lbl3.Visible = True
Menu6lbl4.Visible = True

End Sub
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.