Solved

Need some help with Access to change a report

Posted on 2014-12-01
32
189 Views
Last Modified: 2014-12-02
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?
0
Comment
Question by:tsp2002
  • 20
  • 12
32 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40473362
Choose "Code anzeigen" to see the code.

Probably somewhere the Caption of the label is set:

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

/gustav
0
 

Author Comment

by:tsp2002
ID: 40473499
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40473511
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
0
 

Author Comment

by:tsp2002
ID: 40473521
all reports are write-protected when I see the code...how can I change that?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40473530
Did you locate the relevant code?

/gustav
0
 

Author Comment

by:tsp2002
ID: 40473534
I don´t know what you mean

Bzf_zuweisen

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

Expert Comment

by:Gustav Brock
ID: 40473546
> .. double-click the very last function:

It's the second last line.

/gustav
0
 

Author Comment

by:tsp2002
ID: 40473552
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
0
 

Author Comment

by:tsp2002
ID: 40473559
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40473577
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
0
 

Author Comment

by:tsp2002
ID: 40473592
I do not understand you???
0
 

Author Comment

by:tsp2002
ID: 40473598
where can I double click Bzf_zuweisen?
0
 

Author Comment

by:tsp2002
ID: 40473599
I do not know anything about access and this code !!!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40473605
The function name needs to be marked:
Mark.PNGThe press Shift+F2 to jump to the function.
Now study the code.

/gustav
0
 

Author Comment

by:tsp2002
ID: 40473619
okay double click and Shift +F2 I will get this window:
this is the new window
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40473638
Then my guess is that if you click Bezeichnungsfelder (left), then Bzf_zuweisen will show up right. Then double-click this.

/gustav
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:tsp2002
ID: 40473649
yes it will show up
0
 

Author Comment

by:tsp2002
ID: 40473658
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40473661
Well, can you find the name of the control holding the text "Zertifiziert nach ISO 9001" or the text string itself?

/gustav
0
 

Author Comment

by:tsp2002
ID: 40473675
did you mean that
0
 

Author Comment

by:tsp2002
ID: 40473688
will not find this anywhere
0
 

Author Comment

by:tsp2002
ID: 40473701
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?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40473735
Yes, it is the label named Info1Bzf to look for.
However, the function name to search is Bzf_zuweisen, not BZFzuweisen.

/gustav
0
 

Author Comment

by:tsp2002
ID: 40473741
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40473790
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
0
 

Author Comment

by:tsp2002
ID: 40475330
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 ?
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40475387
And:
> If you from design mode, run the preview of the report (the "Blitz" button), what caption is displayed?

The answer should be Yes.

But when you open the report the normal way, the caption reverts to the old text, not "Zertifiziert nach ISO 9001"?
If so, it must be changed by the code that opens the report the normal way.

/gustav
0
 

Author Comment

by:tsp2002
ID: 40475454
I can not run a preview of the page.... compiler error
0
 

Author Comment

by:tsp2002
ID: 40475464
If I preview the page ("Seitenansicht") I got an error
compiler error
This this normal?
0
 

Author Comment

by:tsp2002
ID: 40475495
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 ?
0
 

Author Closing Comment

by:tsp2002
ID: 40475527
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40475635
OK, thanks. That explains.

/gustav
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now