How can I have text box report name get inside my form button? 2

[cimoli]

I would like to replace the Dlookup that appears in the On Click Events procedure.
The purpose was to lookup the report name for a given place.

Instead, I have figured out how to get the desired report name to appear automatically
in the same form in a Text Box using ..... =[cboReportNumber].[Column](1)

For example, the report name might be .... rptTicketIndCharter
So the new sReportNameSpl would use this rpt name. Thanks, Glen

Here is what I have presently.

Private Sub cmd1171_Click()

On Error GoTo Err_cmd1171_Click

Me.Refresh

Dim sReportNameSpl As String

sReportNameSpl = DLookup("Reportname", "tblreport", "[BusnID] = " & Me.BusnID _
& " And [resort#] = " & Me.ResortID & " And [ReportNum] = " & Me.cboReportNumber)

DoCmd.OpenReport sReportNameSpl, acViewPreview

DoCmd.Maximize

Exit_cmd1171_Click:
Exit Sub

Err_cmd1171_Click:
MsgBox Err.Description
Resume Exit_cmd1171_Click

End Sub

 

[dhookom]

Are you simply looking for something like:

sReportNameSpl = Me.[cboReportNumber].[Column](1)

It seems a bit odd to me that you would have many reports that might be the same but for different places.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

And, as far as "report name get inside my form button?"
Do you mean a [tt]Caption[/tt] of your command button:

...
sReportNameSpl = Me.[cboReportNumber].[Column](1) 
cmd1175.Caption = sReportNameSpl
...

BTW - do you have over 1100 command buttons on your Form?


---- Andy

There is a great need for a sarcasm font.

 

[cimoli]

Hi Duane - Lol. We have many resort place and they tend to request their report format. A pain in the butt.
So we have one qry that fits all but say a need to retrieve 40 different reports depending on the resort.

I use a number system for my work. The form buttons use the same button.

I will try your method. It is so appreciated. I see that Andrzejek has something similar.
I cannot try this till Weds. I will let you know how I made out.

Regards, Glen

 

[cimoli]

12/14/18 I get an error message.
It says "undefined function Dlookup in expression. glen


Here is what I have on the events button:


Private Sub cmd1171_Click()

On Error GoTo Err_cmd1171_Click

Me.Refresh

Dim sReportNameSpl As String

sReportNameSpl = Me.[cboReportNumber].[Column](2)

DoCmd.OpenReport sReportNameSpl, acViewPreview

DoCmd.Maximize

Exit_cmd1171_Click:
Exit Sub

Err_cmd1171_Click:
MsgBox Err.Description
Resume Exit_cmd1171_Click

End Sub

 

[Andrzejek]

I get an error message." and which line of your code is highlighted?

Do you use/have [tt][blue]Option Explicit[/blue][/tt] at the top of General Declaration section?

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I don't see any reference to DLookup().

Do you know how to debug with setting breakpoints to step through your code or using Debug.Print or Msgbox().

what is the value of sReportNameSpl?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[cimoli]

Sorry for being so dopey. It works. I forgot to pick something. Operator Error.

Thanks guys for helping me out. Cimoli Glen