Filling textboxes in the header of a report from a form with variables (1 Viewer)

Xilo

New member
Local time
Today, 16:32
Joined
May 12, 2018
Messages
4
Hello,


I want to set some textboxes of a report in its header from the selected thing in a combobox



So far I have this code to Open a premade Report and filling the detail part of the Report.



Code:
Private Sub openvorlage_Click()

Dim aktionsauswahl As String
    aktionsauswahl = Kombinationsfeld32.Value

Dim strNewRecord As String
    strNewRecord = "SELECT Hauptliste.* FROM Hauptliste " & "WHERE (([Hauptliste].[" & aktionsauswahl & "] Not In (0)))" _



DoCmd.SelectObject acReport, "Vorlage", True
DoCmd.CopyObject , "Bericht", 3, "Vorlage"
DoCmd.OpenReport "Bericht", 6
Reports!Bericht.RecordSource = strNewRecord


I would like to set the value of a textbox in the header of the form "Bericht"

I thought of something like:

Code:
Reports!Bericht!txtTitle.Value = [aktionsauswahl]
I know there is missing the part for addressing the header but I just cant figure out how to access the header I read a few diffrent things but non worked.

Also I cant realy use the OpenArgs because i access the report from diffrent forms.
Thanks for your help in advance
 

June7

AWF VIP
Local time
Today, 07:32
Joined
Mar 9, 2014
Messages
5,425
Can't set the Value property of controls on report with VBA.

Certainly can use OpenArgs even if report is opened from several forms.

Why aren't these fields part of the report RecordSource? A subform as the header or DLookup() are options.
 
Last edited:

Xilo

New member
Local time
Today, 16:32
Joined
May 12, 2018
Messages
4
The fields arent part of the recordsource because the header fields would be filled from a diffrent table then the detail fields. I tried DLookUp and can get the string i want but i am not able to put the string in to the textfield.



Do you have an example of how i could put the strings into a textfield in the header?


Other wise I will try to do it with a subform.
Thanks for anwser.
 

June7

AWF VIP
Local time
Today, 07:32
Joined
Mar 9, 2014
Messages
5,425
And why can't you join the tables in report RecordSource query?

DLookup() can be used in textbox ControlSource.
 

isladogs

MVP / VIP
Local time
Today, 15:32
Joined
Jan 14, 2017
Messages
18,186
Agree with June about joining the tables using a query.
However another possibility is to use label captions rather than textboxes for your headers.

Unlike textboxes, the captions can be set using vba e.g. in the report open event
 

Xilo

New member
Local time
Today, 16:32
Joined
May 12, 2018
Messages
4
Im sorry I am by far not an expert. So what exactly do you mean with joining them in the record source query?
My query so far is:
Code:
SELECT Hauptliste.* FROM Hauptliste " & "WHERE (([Hauptliste].[" & aktionsauswahl & "] Not In (0)))
I would need to add the Fields Aktion and Lehrer form the Table Aktionen. Can I just do something like

Code:
SELECT Hauptliste.* FROM Hauptliste " & "WHERE (([Hauptliste].[" & aktionsauswahl & "] Not In (0)))" & "SELECT Aktion, Lehrer From Aktionen WHERE ( Aktionen = " &  aktionsauswal & ""




Thanks for the help
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:32
Joined
May 7, 2009
Messages
19,175
of course you can passed an OpenArgs to the report.
passed the FormName/TextboxName.
then process the openargs on the Report's Load event.

Example: Form1 is open and we want to get the value from Text3 textbox:
Code:
Docmd.OpenReport reportname:="table3",view:=acViewPreview,openargs:="Form1/Text3"

on the report:
Code:
Private Sub Report_Load()

' openargs must be in format "FormName/TextBoxName"
'
' check if there is an argument passed
If Nz(Me.OpenArgs, vbNullString) <> vbNullString Then
    ' set the caption of the report
    Me.Auto_Header0.Caption = Forms(Split(Me.OpenArgs, "/")(0))(Split(Me.OpenArgs, "/")(1))
End If
End Sub
 

June7

AWF VIP
Local time
Today, 07:32
Joined
Mar 9, 2014
Messages
5,425
No, that is not a valid SQL statement.

Building queries that join related tables is basic Access functionality. Use the query designer.

You should do a little research and maybe even work through tutorials.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:32
Joined
May 7, 2009
Messages
19,175
there is much simpler solution though.
passing "FORMS!FormName!TextboxName" as openargs of the report.
then process the openargs on the Report's Load event.

Example: Form1 is open and we want to get the value from Text3 textbox:
Code:
Docmd.OpenReport reportname:="table3",view:=acViewPreview,openargs:="Forms!Form1!Text3"

on the report:
Code:
Private Sub Report_Load()

' openargs must be in format "Forms!FormName!TextBoxName"
'
' check if there is an argument passed
If Nz(Me.OpenArgs, vbNullString) <> vbNullString Then
    ' set the caption of the report
    Me.Auto_Header0.Caption = Eval(Me.OpenArgs) & ""
End If
End Sub
 

Xilo

New member
Local time
Today, 16:32
Joined
May 12, 2018
Messages
4
Thanks Guys!
I got it to work with OpenArgs just had to change some smaller things in the other forms that i befor tought werent possible to change,
 

Users who are viewing this thread

Top Bottom