VBA, Buttons, & Loops, oh my.

[kingc0rn]

Hey everyone! I have a couple years of experience with Java but that was about 6 years ago by now, and this VBA is a bit different.

I have a table with three Record Sets [ID], [caption], and [toRun]. The idea is to have a form change the captions of premade buttons to the contents of [caption] and then open a form or report listed in [toRun]. My current code is:

Private Sub Form_Current()
Dim buttonName As String
Dim i As Integer
i = 0

Do Until i = CurrentDb.TableDefs("MainMenu").RecordCount
    buttonName = "button" + i
    Me(buttonName).Caption = DLookup("Caption", "MainMenu", "ID=" + i)
    Me(buttonName).OnClick = DoCmd.OpenForm(DLookup("toRun", "MainMenu", "ID=" + i))
    i = i + 1
Loop



End Sub

I have a feeling the whole "buttonName" isn't working properly because it's trying to find a button named "buttonName" instead of the buttonName string... what am I overlooking there?

Apart from that this should work right?

 

[pbaldy]

Not sure I see where you're going, but try

Me(buttonName).Caption = DLookup("Caption", "MainMenu", "ID=" + i)

 

[kingc0rn]

Well thanks for the quick reply. Now it's freezing when I try to open the form...I did notice another error in my code so I changed it (with the additions you mentioned) in the original post.

 

[pbaldy]

I'm not sure the loop is doing what you think. Are you trying to do something so each record is different? Or do you have buttons for every record? Can you post the db here?

 

[kingc0rn]

Sure, my database is attached! So, I was using the loop to change the name of buttonName and advance the dslookup to pull records of [toRun] from the table MainMenu. That way whenever my form frmMainMenu is opened it will pull assign [caption] to button1.caption and open the form listed as the first record in [toRun] and do the same for button2, button3, button4, ....button(lastrecord).

 

[pbaldy]

What version of Access do you have? It looks like you're trying to manually create a switchboard. I never liked them, but they were a feature through 2007 (I think):

http://office.microsoft.com/en-us/access-help/create-and-use-a-switchboard-HA001213876.aspx

To do what you're doing, the buttons would have to already exist. You'd also have to account for the different types of objects you want to open. I don't think the code to set the click event will work either.

 

[kingc0rn]

I am using Access 2010 but also have access to Access 2013. . .
The buttons are already created I'm simply trying to change what they say and what they open dependent on the value in the MainMenu table. The assignment was to make this happen using VBA and it was heavily hinted to use DLookup instead of creating a query and referencing it. The loop was my solution.

 

[pbaldy]

The loop is fine, I just don't know about setting the click event that way. I've got an app where I dynamically assign code to labels, but I set it to a function. If you did this manually, you'd set the property of the button to "Event Procedure" and then put the DoCmd code in VBA. You can't put the DoCmd directly in the property, which is what you're doing. Unless I'm dense, this is not beginner stuff so I'm surprised it's a homework assignment, unless it's an advanced class.

 

[kingc0rn]

Ohhhh okay. Like I said I have a small background in Java and I'm pretty sure this can mostly be done using dlookup but I may be over thinking it. It's the 3rd access class of 3 but the first that requires VBA.

 

[pbaldy]

The DLookup is fine to get the name of the form/report, I just don't see how you can use it the way you are. I'd love to find out I'm wrong though, so please post back with your solution. I'm always looking to learn.

 

[vbaInet]

With your background in Java you should know that recordsets (or resultsets) exists, it's called recordsets in VBA.

I see what you're trying to do so here are the key points:

1. ".OnClick" refers to the On Click property on the Property Sheet, not the On Click event. This property can only be set to expressions or a call to a function/sub.
2. The caption of your buttons are too explicit. One would expect generic numbers like 1, 2, 3 and 4 and of course this should be reflected in your table.
3. You would need to create a function that opens a form where the form name is gotten from its parameter.
4. You will then need a recordset for looping through the records in the table, changing the caption of the button and constructing/setting the value of the button's OnClick property.
5. The OnClick property will call the function mentioned in step 3 and it should look something like this:

Me("button" & i).OnClick = "=[COLOR="blue"]FunctionName[/COLOR](" & Chr(34) & [COLOR="Blue"]"FormName"[/COLOR] & Chr(34) & ")"

... the FormName part will come from the recordset. A DLookup() will work as well but it's just too heavy for this sort of thing.

All of this will take place in the Open event of the form not the Current event.

 

[kingc0rn]

Okay, the assignment was due today; so since I was having trouble with my original solution I decided just to simplify it as much as possible. There was nothing saying the Main Menu table that I was using to populate the buttons had to dynamically updated if I changed the names of the reports or forms that I was opening. Once I removed some records in the Main Menu I remembered that the loop would of caused problems without additional coding to handle the null values returned. So my solution was very simple, thanks to vbaInet I changed the event to Open and applied the following.

Private Sub Form_Open(Cancel As Integer)
    frmBorrower_Button.Caption = DLookup("Caption", "MainMenu", "ID = 1")
    frmMedia_Button.Caption = DLookup("Caption", "MainMenu", "ID = 2")
    frmQuickSearchBorrower_Button.Caption = DLookup("Caption", "MainMenu", "ID = 3")
    frmQuickSearchMedia_Button.Caption = DLookup("Caption", "MainMenu", "ID = 4")
    rptBorrower_Button.Caption = DLookup("Caption", "MainMenu", "ID = 11")
    rptMediaList_Button.Caption = DLookup("Caption", "MainMenu", "ID = 12")
    rptPastDue_Button.Caption = DLookup("Caption", "MainMenu", "ID = 13")
End Sub

The onClicks were similar.

=DLookUp("[toRun]","MainMenu","[ID] = 1")

Thanks for the help gents.

 

[pbaldy]

The buttons were functional?

 

[kingc0rn]

Yes, they were.
As long as the table was kept up-to-date.

 

[pbaldy]

I'd love to see the finished db if you get a chance to post it here.

 

[vbaInet]

He's probably got a well-formed function call in his ToRun field.

"=[COLOR="blue"]FunctionName[/COLOR](" & Chr(34) & [COLOR="Blue"]"FormName"[/COLOR] & Chr(34) & ")"

... but then again I'd have thought you'll need Eval().

 

[kingc0rn]

Sure! I've attached it below.

 

[vbaInet]

Alright you're using a macro. I suppose the requirement doesn't forbid you to use macros then?

You could have still done it all in code as explained, but at least you found your own way. I would still advise that you look into recordsets. You should know resultsets in Java.

 

[kingc0rn]

Yeah, the instructions weren't exactly clear on that so I'm going to hope I won't be docked any points for it. I haven't used or learned about Java in about 7 years now and I don't work in an environment that affords me the opportunities to continue my studies in it, but yet after a quick Google I do remember ResultSets. I will probably attempt to add the code you suggested later today when I have a some spare time.

 

[vbaInet]

Think of recordsets as resultsets.