Dynamically assign a button caption/code based on a field in a table

[rscott1989]

Hello everyone,



I have been trying to figure this out for sometime now and cant seem to get my head around it. I am working with a table "MainMenu" and in that table are 3 fields. The RecordID field, the Caption field and the Form field. Each of my forms and reports are listed inside this table. The idea is to be able to use a DLookup command to lookup the value from that table and dynamically assign a caption and allow said form/report to be opened up.

The form I am trying to do this in is the HomePage_Form

Any and all help is greatly appreciated!

[ItsMe]

Maybe something like
Me.ControlName.Caption = Dlookup()
https://msdn.microsoft.com/en-us/lib.../ff834404.aspx

[rscott1989]

Thanks for your response. I am still getting an error when I use the DLookUp using VBA

this is the code I am using

The button is "LookUpCMD" field is "Caption" and the table is "MainMenu". There are 14 total records. "ID" 01-14

Private Sub LookUpCMD_Click()
Me.[LookUpCMD].[Caption] = DLookup("[Caption]", "[MainMenu]", "[ID]=01")
End Sub

[Micron]

You should post the error message and number if applicable. It might give clues as to what the reason is since we cannot see beyond the information you post. I am wondering if your ID data type is text or number. If text, "[ID]=01" needs to be "ID='01'". Also, if you never use spaces in names (which I don't see that you have), you'll probably never need square brackets and won't get confused by them. Nor should you used reserved words (which you have). Me.[LookUpCMD].[Caption] is not how I would write this since I believe Access may interpret [Caption] incorrectly - it is the same name as your field (not good) and is a reserved word. Try Me.LookupCMD.Caption.

[ItsMe]

As mentioned, you do not want to use Caption as a name for a field, object, or anything else. When I read the OP, I was imagining you wanted to edit the Caption property of a control in your form. Now, I am not sure what you are trying to accomplish. Are you trying to assign a value to a control, like a textbox control?

[rscott1989]

Thank you guys for replying so quick. Exactly what I am trying to do is use the code (DLookUP) to dynamically read the caption in the table MainMenu. I also want the code for the button to dynamically read the form or report name and run the form/report once the button is clicked.

Table "MainMenu" has the three fields "ID","Caption","Form". All three are Short Text fields
The button I am trying to assign these commands is "LookUpCMD". This is located on the form HomePage_Form.

So what on the onclick event for the button is: Me.LookUpCMD.Caption = DLookup("Caption", "MainMenu", "ID='01")

When I click the button I get the error "Run Time error 3075
Syntax error in string in query expression "ID='01".

I hope this clears up what i am trying to do here

Thanks for your help!

[ItsMe]

If the field named ID is of type text, the syntax error is because you are missing a single quote
Me.LookUpCMD.Caption = DLookup("Caption", "MainMenu", "ID='01'")

for Numeric
Me.LookUpCMD.Caption = DLookup("Caption", "MainMenu", "ID=1")

However, what this code is doing is changing the 'Caption' PROPERTY of the control named LookUpCMD. In other words doing somwethig like change 'Click Me' to 'TheValueOfCaption'.

What you are stating here is not making sense to me.

I also want the code for the button to dynamically read the form or report name and run the form/report once the button is clicked.

What do you want the button to do when the user clicks it? What is it going to do for the User? Is it going to open a form? Will it print a report? Will it make a sandwich?

[rscott1989]

Okay so i made a little change to this. I was able to figure out the missing single quote. So now when I click the button the caption of the corresponding ID populates.

I have inserted a combo box with a list of all the captions in the MainMenu table. So now what I want it to do is read the selection from the combo box and open that form or report when the button is clicked. Does this make any more since?

Thanks again for all your help!

[ItsMe]

Here is some code to open a form.

DoCmd.OpenForm "NameOfForm"

You can also apply criteria to the Form's Filter property. In the following example the value of the first column is compared to the field named ID.

DoCmd.OpenForm "NameOfForm", , , "[ID] =" & Me.NameOfCombo.Column(0)

[rscott1989]

So how would I change the caption of the button based on the combo box selection?

So if borrower Form is selected from the Combo Box then "Borrower Form" populates on the button caption and when it is clicked the borrower_Form is opened.

[ItsMe]

So how would I change the caption of the button based on the combo box selection?

So if borrower Form is selected from the Combo Box then "Borrower Form" populates on the button caption and when it is clicked the borrower_Form is opened.

What you are describing is that you want to accomplish a couple different things. You want to change the Caption property of the Command Button and you want to open a form.

You can edit the caption property like this
Me.ControlName.Caption = "My New Caption"

ComboBox Controls can have multiple column's. You might be able to get what you need using the Value property of a combo. Here is one test you can try to determine the current value of a Combo
msgbox me.ComboName.Value

You can also retrieve the value of other columns by specifying the columns index. The index starts with 0. This example goes after the second column.
msgbox me.ComboName.Column(1)

[rscott1989]

So do I use the DLookUp like this to pull the caption from the combobox? Me.ControlName.Caption = DLookup("Caption", "ComboBox", "ID=1")? I understand how to rename the control the way you said. However, I want it to be renamed automatically based on the combobox selection.

So I want the on click command to pull the combo box selection, place the caption to the button and open the selected object.

[ItsMe]

Why don't you start by choosing one thing and building that one thing? That is how I do it. Build a little and then test what I built.

There are examples in this thread to do several different things. Choose one and give it a try.