AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of Thomas Stockbruegger
Thomas Stockbruegger
Flag for Germany asked on

Need some help with Access to change a report

Hi,
I just want to make a small change in an report. When I change a form I see the result when I start the programm, but this is not the case with reports. The developer of this access programm is not accessible any more.
Can you help me... total NOVICE with Access.
500 points with a solution.
Best regards,
Thomas
Report_Lieferschein14 I would like to make a small changeReport_Lieferschein14 I would like to make a small changeReport_Lieferschein14 I would like to changeI would like to change the Zertified text
In the last hardcopy here you see  a part of the report:
Zertifiziert nach ISO 9001
I can change this text but when I start the programm I will not see any changes.
Do I need to compile the programm to see changes in reports?
Microsoft AccessVisual Basic Classic
Avatar of undefined
Last Comment
Gustav Brock
8/22/2022 - Mon
Gustav Brock
Choose "Code anzeigen" to see the code.

Probably somewhere the Caption of the label is set:

    Me!LabelSomeName.Caption = "Zertifiziert nach ISO 9001"

/gustav
Thomas Stockbruegger
ASKER
Option Compare Database
Option Explicit

Private Sub Bzf_zuweisen()

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    Dim SichtbarÜbergabe As Variant, RandOben As Variant, Zeilenabstand As Variant

    ' Öffnen der Verbindung ohne Data Source Name (DSN).
    Set cnn = New ADODB.Connection
    With cnn
        .ConnectionString = "driver={SQL Server};server=" & Servername & ";;;database=" & ServerSQLDB 'uid=sa;pwd=pwd
        .ConnectionTimeout = 30
        .Open
    End With
    
    strSQL = "SELECT * FROM [Lieferscheine]"
    strSQL = strSQL & " WHERE [LieferscheinNr] = " & Chr(39) & Forms![Menü]![FilterIDNrÜbergabe] & Chr(39)
    
    Set rst = New ADODB.Recordset
    With rst
        .ActiveConnection = cnn
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open Source:=strSQL
    End With
    
    rst.Find "[LieferscheinNr] <> " & Chr(39) & Chr(39)
    
    If rst.RecordCount >= 1 Then

        If rst![Geschäftspapieraktiv] = True Then
            
            'SichtbarÜbergabe = DLookup("[FirmenkopfSichtbar]", "[Grundeinstellung]", "[GrundeinstellungIDNr] = 1")
            'If SichtbarÜbergabe = False Then
            '    Me![GrundeinstellungAdresskopf].Visible = False
            'End If
            
            'SichtbarÜbergabe = DLookup("[FirmenAdresseSichtbar]", "[Grundeinstellung]", "[GrundeinstellungIDNr] = 1")
            'If SichtbarÜbergabe = False Then
            '    Me![GrundeinstellungAdresse].Visible = False
            'End If
            
            SichtbarÜbergabe = DLookup("[FirmenfußSichtbar]", "[Grundeinstellung]", "[GrundeinstellungIDNr] = 1")
            If SichtbarÜbergabe = False Then
                Me![GrundeinstellungAdressfuß].Visible = False
            End If
            
            SichtbarÜbergabe = DLookup("[FirmenabsenderzeileSichtbar]", "[Grundeinstellung]", "[GrundeinstellungIDNr] = 1")
            If SichtbarÜbergabe = False Then
                Me![GrundeinstellungAbsenderzeile].Visible = False
            End If
            
            RandOben = DLookup("[FirmenKopfFolgeSeitenRandOben]", "[Grundeinstellung]", "[GrundeinstellungIDNr] = 1")
    
            If RandOben > 0 Then
                
                [Seitenkopf].Height = 567 * (1.4 + RandOben) '(567 Twips = 1cm)
                [KundenAnzeigeBzf].Top = 567 * (0 + RandOben)
                [DuplikatBzf2].Top = 567 * (0 + RandOben)
                [DokBzf2].Top = 567 * (0 + RandOben)
                [Datum2].Top = 567 * (0.614 + RandOben)
                [Seitenkopflinie].Top = 567 * (1.249 + RandOben)
                                
            End If
            
        End If
        
        [Datum2].Caption = Format$(rst![Lieferscheindatum], "d. mmmm yyyy")
                
        'If rst![ProjektNr] <> "" Then
        '    ProjektNr.Caption = BZFzuweisen("ProjektNrBzf", rst![SpracheNr]) & " : " & rst![ProjektNr]
        'Else
        '    ProjektNr.Caption = ""
        'End If
        
        Zeilenabstand = DLookup("[InfofeldZeilenabstand]", "[Grundeinstellung]", "[GrundeinstellungIDNr] = 1")
        If Zeilenabstand > 0 Then
            Infofeld1.LineSpacing = 567 * Zeilenabstand / 10 'mm/10 = cm * Twips
            Infofeld2.LineSpacing = 567 * Zeilenabstand / 10 'mm/10 = cm * Twips
        End If
        
        'If rst![BriefAnrede] <> "" And rst![Lieferscheinvortext] <> "" Then
        '    Me![Vortextgesamt].Visible = True
        'Else
        '    Me![Vortextgesamt].Visible = False
        'End If
                
        If rst![Dokumentbezeichnung] <> "" Then
            DokBzf.Caption = rst![Dokumentbezeichnung] '& " " & rst![LieferscheinNr]
            DokBzf2.Caption = DokBzf.Caption
        Else
            DokBzf.Caption = BZFzuweisen("FertigmeldungBzf", rst![SpracheNr]) '& " " & rst![LieferscheinNr]
            DokBzf2.Caption = DokBzf.Caption
        End If
        
        If rst![Name1] <> "" And rst![Name2] <> "" Then
            KundenAnzeigeBzf.Caption = rst![Name1] & Chr(13) & Chr(10) & rst![Name2] & Chr(13) & Chr(10) & rst![Postleitzahl] & " " & rst![Ort]
        Else
            KundenAnzeigeBzf.Caption = rst![Name1] & Chr(13) & Chr(10) & rst![Postleitzahl] & " " & rst![Ort]
        End If
                    
        If rst![Seitenumbruchaktiv] = True Then
            Me![Seitenumbruch].Visible = True
        Else
            Me![Seitenumbruch].Visible = False
        End If
                    
        If Forms![Menü]![DuplikatAktiv] = True Then
            DuplikatBzf.Caption = BZFzuweisen("DuplikatBzf", rst![SpracheNr])
            Me![DuplikatBzf].Visible = True
            DuplikatBzf2.Caption = DuplikatBzf.Caption
            Me![DuplikatBzf2].Visible = True
        End If

    End If
    
    rst.Close
    cnn.Close

End Sub

Private Sub Detailbereich_Print_ALT(Cancel As Integer, PrintCount As Integer)

    Dim cnn As New ADODB.Connection
    Dim rstL As New ADODB.Recordset, rstA As New ADODB.Recordset
    Dim strSQLL As String, strSQLA As String

    ' Öffnen der Verbindung ohne Data Source Name (DSN).
    Set cnn = New ADODB.Connection
    With cnn
        .ConnectionString = "driver={SQL Server};server=" & Servername & ";;;database=" & ServerSQLDB 'uid=sa;pwd=pwd
        .ConnectionTimeout = 30
        .Open
    End With
    
    strSQLL = "SELECT * FROM [Lieferscheinpositionen]"
    strSQLL = strSQLL & " WHERE [LieferscheinNr] = " & Chr(39) & Forms![Menü]![FilterIDNrÜbergabe] & Chr(39)
    strSQLL = strSQLL & " AND [Einheitbez8wert] > 0" ' & Chr(39) & Chr(39)
    
    Set rstL = New ADODB.Recordset
    With rstL
        .ActiveConnection = cnn
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open Source:=strSQLL
    End With
    
    rstL.Find "[LieferscheinNr] <> " & Chr(39) & Chr(39)
    
    If rstL.RecordCount >= 1 Then

        While Not rstL.EOF
        
            strSQLA = "SELECT * FROM [Auftragsbestätigungen]"
            strSQLA = strSQLA & " WHERE [ABNr] = " & Chr(39) & rstL![Einheitbez8wert] & Chr(39)
            
            Set rstA = New ADODB.Recordset
            With rstA
                .ActiveConnection = cnn
                .CursorType = adOpenKeyset
                .LockType = adLockOptimistic
                .Open Source:=strSQLA
            End With
            
            rstA.Find "[ABNr] <> " & Chr(39) & Chr(39)
            
            If rstA.RecordCount >= 1 Then
        
                If InStr(1, rstA![StatusMeldungen], rstL![LieferscheinNr], 1) = 0 Then
                
                    If rstA![StatusMeldungen] <> "" Then
                        rstA![StatusMeldungen] = rstA![StatusMeldungen] & Chr(13) & Chr(10) & " Fertigmeldung " & rstL![LieferscheinNr] & " / " & Date
                    Else
                        rstA![StatusMeldungen] = " Fertigmeldung " & rstL![LieferscheinNr] & " / " & Date
                    End If
                    
                    rstA.Update
                
                End If
                
            End If
            
            rstA.Close
            
            rstL.MoveNext
            
        Wend

    End If
    
    rstL.Close
    cnn.Close
    
    Forms![Menü]![Seitenanzahl] = Me.Pages
    
End Sub

Private Sub Report_Open(Cancel As Integer)

    Call Bzf_zuweisen
    
End Sub

Open in new window


Hi Gustav,
this is the code to Report_Lieferschein14
Gustav Brock
OK. The caption is set via code or a lookup.

Now find the name of the control holding the text "Zertifiziert nach ISO 9001".
If you can't find that name in the code you listed, then double-click the very last function:

    Bzf_zuweisen

and press Shift+F2. That will open the function for you to study.

/gustav
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Thomas Stockbruegger
ASKER
all reports are write-protected when I see the code...how can I change that?
Gustav Brock
Did you locate the relevant code?

/gustav
Thomas Stockbruegger
ASKER
I don´t know what you mean

Bzf_zuweisen

and press Shift+F2. That will open the function for you to study.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock
> .. double-click the very last function:

It's the second last line.

/gustav
Thomas Stockbruegger
ASKER
I can not click on that in the code

Private Sub Report_Open(Cancel As Integer)

    Call Bzf_zuweisen              <----- click here ??? no result
   
End Sub
Thomas Stockbruegger
ASKER
There is nothing in the code wiht this text "Zertifiziert nach ISO 9001" , the text is only in the report
The report is also write-protected
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Gustav Brock
It's not a click.

.. double-click the very last function:

     Bzf_zuweisen

and press Shift+F2. That will open the function for you to study.

/gustav
Thomas Stockbruegger
ASKER
I do not understand you???
Thomas Stockbruegger
ASKER
where can I double click Bzf_zuweisen?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Thomas Stockbruegger
ASKER
I do not know anything about access and this code !!!
Gustav Brock
The function name needs to be marked:
Mark.PNGThe press Shift+F2 to jump to the function.
Now study the code.

/gustav
Thomas Stockbruegger
ASKER
okay double click and Shift +F2 I will get this window:
this is the new window
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Gustav Brock
Then my guess is that if you click Bezeichnungsfelder (left), then Bzf_zuweisen will show up right. Then double-click this.

/gustav
Thomas Stockbruegger
ASKER
yes it will show up
Thomas Stockbruegger
ASKER
Option Compare Database
Option Explicit

Function BZFzuweisen(FeldName As String, Sprache As Integer)

    Dim cnn As New ADODB.Connection
    Dim rstA As New ADODB.Recordset
    Dim strSQLA As String, strSQLANr As String
    
    Screen.MousePointer = 11
    
    ' Öffnen der Verbindung ohne Data Source Name (DSN).
    Set cnn = New ADODB.Connection
    With cnn
        .ConnectionString = "driver={SQL Server};server=" & Servername & ";;;database=" & ServerSQLDB 'uid=sa;pwd=pwd
        .ConnectionTimeout = 30
        .Open
    End With
    
    strSQLA = "SELECT * FROM Bezeichnungsfelder "
    strSQLA = strSQLA & " WHERE [BezeichnungsFeld] = " & Chr(39) & FeldName & Chr(39)
    
    Set rstA = New ADODB.Recordset
    With rstA
        .ActiveConnection = cnn
        .CursorType = adOpenKeyset
        .LockType = adLockPessimistic
        .Open Source:=strSQLA
    End With
    
    rstA.Find "[BezeichnungsFeld] <> " & Chr(39) & Chr(39)
    
    If rstA.RecordCount >= 1 Then
        
        rstA.MoveFirst
        
        BZFzuweisen = rstA.Fields(Sprache).Value
    
    End If
    
    rstA.Close
    cnn.Close
    
    Screen.MousePointer = 0
    
End Function

Function Berichtinfozuweisen(Sprache As Integer) As String

    Dim cnn As New ADODB.Connection
    Dim rstA As New ADODB.Recordset
    Dim strSQLA As String
    
    Screen.MousePointer = 11
    
    ' Öffnen der Verbindung ohne Data Source Name (DSN).
    Set cnn = New ADODB.Connection
    With cnn
        .ConnectionString = "driver={SQL Server};server=" & Servername & ";;;database=" & ServerSQLDB 'uid=sa;pwd=pwd
        .ConnectionTimeout = 30
        .Open
    End With
    
    strSQLA = "SELECT * FROM Grundeinstellung "
    'strSQLA = strSQLA & "WHERE [Berichtinfoaktiv] = 1;"
    
    Set rstA = New ADODB.Recordset
    With rstA
        .ActiveConnection = cnn
        .CursorType = adOpenKeyset
        .LockType = adLockPessimistic
        .Open Source:=strSQLA
    End With
    
    rstA.Find "[Berichtinfoaktiv] = 1" '& Chr(39) & Chr(39)
    
    If rstA.RecordCount >= 1 Then
        
        rstA.MoveFirst
        
        If Sprache = 1 Then
            Berichtinfozuweisen = rstA![BerichtinfoD]
        End If
        
        If Sprache = 2 Then
            Berichtinfozuweisen = rstA![BerichtinfoE]
        End If
        
        If Sprache = 3 Then
            Berichtinfozuweisen = rstA![BerichtinfoF]
        End If
        
        If Sprache = 4 Then
            Berichtinfozuweisen = rstA![BerichtinfoI]
        End If
        
        If Sprache = 5 Then
            Berichtinfozuweisen = rstA![BerichtinfoS]
        End If
        
        If Sprache = 6 Then
            Berichtinfozuweisen = rstA![BerichtinfoN]
        End If
        
        If Sprache = 7 Then
            Berichtinfozuweisen = rstA![BerichtinfoSp1]
        End If
        
        If Sprache = 8 Then
            Berichtinfozuweisen = rstA![BerichtinfoSp2]
        End If
        
        If Sprache = 9 Then
            Berichtinfozuweisen = rstA![BerichtinfoSp3]
        End If
    
    Else
    
        Berichtinfozuweisen = ""
        
    End If
    
    rstA.Close
    cnn.Close
    
    Screen.MousePointer = 0
    
End Function

Open in new window



This is the new code
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock
Well, can you find the name of the control holding the text "Zertifiziert nach ISO 9001" or the text string itself?

/gustav
Thomas Stockbruegger
ASKER
did you mean that
Thomas Stockbruegger
ASKER
will not find this anywhere
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Thomas Stockbruegger
ASKER
all reports are write-protected when I see the code...how can I change that?
Is this the reason? I can change any Forms and see the result in the programm but no reports.
Is it normal to see the changes in reports also? Or do I have to compile or do anything else for this?
Gustav Brock
Yes, it is the label named Info1Bzf to look for.
However, the function name to search is Bzf_zuweisen, not BZFzuweisen.

/gustav
Thomas Stockbruegger
ASKER
but look at the first code that I have posted .... this is Bzf_zuweisen


Option Compare Database
Option Explicit

Private Sub Bzf_zuweisen() <--------------------------------------------------

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    Dim SichtbarÜbergabe As Variant, RandOben As Variant, Zeilenabstand As Variant

    ' Öffnen der Verbindung ohne Data Source Name (DSN).
    Set cnn = New ADODB.Connection
    With cnn
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock
Yes, you are right.

If you have tried to edit the caption of Info1Bzf but it is changed back when running the report, it must be set from somewhere else but I can see that Info1Bzf is found nowhere.

If you edit the caption, save the report, and open it again, is the caption the old or the edited?
If you from design mode, run the preview of the report (the "Blitz" button), what caption is displayed?

/gustav
Thomas Stockbruegger
ASKER
If you edit the caption, save the report, and open it again, is the caption the old or the edited?
The report will show the changed text correct.
what button do you mean ?
ASKER CERTIFIED SOLUTION
Gustav Brock
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Thomas Stockbruegger
ASKER
I can not run a preview of the page.... compiler error
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Thomas Stockbruegger
ASKER
If I preview the page ("Seitenansicht") I got an error
compiler error
This this normal?
Thomas Stockbruegger
ASKER
Private Sub Seitenansicht_Click()

    On Error GoTo Bericht_Err
    
    Dim FormularauswahlÜbergabe As String
    Dim Übergabe As Variant
    
    FormularauswahlÜbergabe = [Formularauswahl]
    
    Forms![Menü]![FilterIDNrÜbergabe] = DLookup("[ABNr]", "[Auftragsauflistung]", "[PCLizenzNr] = " & Chr(39) & Forms![Menü]![PCLizenzNr] & Chr(39))

    If FormularauswahlÜbergabe = "Auftragsbestätigung" Then
        'Call Daten_in_Auftrag_speichern("Auftragsbestätigung")
        Call Daten_in_Auftrag_speichern(FormularauswahlÜbergabe)
        Call Auftragswerte_auf_Auftragsbestätigungspositionen_übertragen
    Else
        
        If [OPübertragen] = False Then
        '    If MsgBox("Soll die Auflistung jetzt in die offene Paket-Liste übertragen werden?", 48 + 4, "O.M.S.") = 6 Then
                Call Wiegekarten_in_AuftragsauflistungOP_übertragen
                [OPübertragen] = True
        '    End If
        End If
        
        'Call Daten_in_Auftrag_speichern("Auftragsbestätigung")
        Call Wiegekarten_auf_Auftragsbestätigungspositionen_übertragen
    End If
    
    Call FaxNrübertragen([AdressNr], "", "", "")
        
    DoCmd.Close
        
    If FormularauswahlÜbergabe = "Auftragsbestätigung" Then
        Übergabe = DLookup("[ABNr]", "[Auftragsauflistung]", "[PCLizenzNr] = " & Chr(39) & Forms![Menü]![PCLizenzNr] & Chr(39))
        BerichtSeitenansicht_BF "Auftragsbestätigung14 Auflistung", "[ABNr] = " & Chr(39) & Übergabe & Chr(39)
    Else
        Übergabe = DLookup("[ABNr]", "[Auftragsauflistung]", "[PCLizenzNr] = " & Chr(39) & Forms![Menü]![PCLizenzNr] & Chr(39))
        BerichtSeitenansicht_BF "Fertigmeldung14", "[ABNr] = " & Chr(39) & Übergabe & Chr(39)
    End If
    
    DoCmd.RunCommand acCmdPreviewOnePage
    DoCmd.RunCommand acCmdZoom100
    DoCmd.ShowToolbar "DABUS_O.M.S._Bericht", acToolbarYes
    
    If IsLoaded("Menü") Then
        Forms![Menü].Visible = False
    End If
    
    Forms![Menü]![FilterIDNrÜbergabe] = ""
    
Bericht_Exit:
    Exit Sub

Bericht_Err:
    MsgBox Error$
    Resume Bericht_Exit

End Sub

Open in new window



Gustav, I think I found something.
This is the code when the user  will press the button to see the Fertigmeldung page.
BerichtSeitenansicht_BF "Fertigmeldung14" <-------- how can I find this ?
Thomas Stockbruegger
ASKER
Hi Gustav,
I found the error. It was my mistake. There were 2 Reports almost with the same name from the developer. I found the right report now. So the changes did not work because I have always changed the wrong report. Thanks for your help. Best regards, Thomas
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock
OK, thanks. That explains.

/gustav
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent