Start Free Trial

Thomas Stockbruegger

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

User generated image

User generated image

User generated image

User generated image

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?

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

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

Thomas Stockbruegger

ASKER

all reports are write-protected when I see the code...how can I change that?

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.

> .. 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

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?

Thomas Stockbruegger

ASKER

I do not know anything about access and this code !!!

The function name needs to be marked:

User generated image

The 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:

User generated image

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

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

User generated image

Thomas Stockbruegger

ASKER

User generated image

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?

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

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.

User generated image

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Thomas Stockbruegger

ASKER

I can not run a preview of the page.... compiler error

Thomas Stockbruegger

ASKER

If I preview the page ("Seitenansicht") I got an error

User generated image

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

OK, thanks. That explains.

/gustav