VBA Tipp: Liste per SQL aufbauen

Aus DBWiki
Wechseln zu: Navigation, Suche

Anforderung

  • Ich möchte in einem Formular- oder Berichtsfeld mehrere Werte aus einer Tabelle als Liste mit Trennzeichen darstellen.
  • Ich möchte die Datenquelle eines Kombinations- oder Listenfeldes als Wertliste flexibel aufbauen.

Lösung

Die folgende Funktion erlaubt es, derartige Listen mittels SQL-Anweisung in einer Auswahlabfrage flexibel zu erstellen.

Public Function SQLListe(SQL As String, _
                         Optional RowSeparator As String = ";", _
                         Optional FieldSeparator As String = ";", _
                         Optional IncludeNullValues As Boolean, _
                         Optional NullExpr As String) As Variant
 
   ' SQL:                SQL-Anweisung einer Auswahlabfrage, deren Feldliste
   '                     bei der Auswertung in gleicher Reihenfolge und Sor-
   '                     tierrichtung berücksichtigt wird.
   '
   ' RowSeparator:       Wird als Zeilentrennzeichen verwendet.
   '                     (Optional) Standardwert = ";"
   '
   ' FieldSeparator:     Wird als Feldtrennzeichen verwendet.
   '                     (Optional) Standardwert = ";"
   '
   ' IncludeNullValues:  Bei True werden Nullwerte in der Ausgabe berück-
   '                     sichtigt.
   '                     (Optional) Standardwert = False.
   '
   ' NullExpr:           Wird statt einer leeren Zeichenfolge für einen Null-
   '                     wert ausgegeben.
   '                     (Optional) Standardwert = "" (leere Zeichenfolge)
 
   'Quelle: http://www.dbwiki.net
 
   Dim row As String
   Dim rs  As DAO.Recordset
   Dim fld As DAO.Field
 
   'SQLListe = Null
   On Error GoTo Cleanup
   Set rs = DBEngine(0)(0).OpenRecordset(SQL, dbOpenForwardOnly, dbReadOnly)
 
   With rs
      Do Until .EOF
         row = vbNullString
 
         For Each fld In .Fields
            If Not IsNull(fld.Value) Then
               row = row & FieldSeparator & fld.Value
            ElseIf IncludeNullValues Then
               row = row & FieldSeparator & NullExpr
            End If
         Next
 
         If Len(row) > 0 Then
            SQLListe = SQLListe & RowSeparator & Mid$(row, Len(FieldSeparator) + 1)
         End If
 
         .MoveNext
      Loop
 
      If Len(SQLListe) > 0 Then
         SQLListe = Mid$(SQLListe, Len(RowSeparator) + 1)
      End If
   End With
 
Cleanup:
   If Err.Number Then SQLListe = CStr(CVErr(Err.Number))
   If Not rs Is Nothing Then rs.Close
End Function

Aufruf

1. Als Steuerelementinhalt eines berechneten Formular- oder Berichtsfeldes

Der Schlüsselwert abt_nr hat den Datentyp Zahl.
Keine Nullwerte anzeigen
=SQLListe("SELECT abt_nr FROM mitarbeiter WHERE abt_nr = " & [abt_nr];", ";", ")
Nullwerte als Leerstring anzeigen
=SQLListe("SELECT abt_nr FROM mitarbeiter WHERE abt_nr = " & [abt_nr];", ";", ";Wahr)
=SQLListe("SELECT abt_nr FROM mitarbeiter WHERE abt_nr = " & [abt_nr];";";";";Wahr)
Nullwerte als Ersatzwert "(Null)" anzeigen
=SQLListe("SELECT abt_nr FROM mitarbeiter WHERE abt_nr = " & [abt_nr];", ";", ";Wahr;"(Null)")


Der Schlüsselwert ProjektNr hat den Datentyp Text.
Keine Nullwerte anzeigen
=SQLListe("SELECT ProjektNr, Beschreibung FROM tblProjekt WHERE ProjektNr = '" & [ProjektNr] & "'";", ";": ")


weitere Hinweise
Im Ausdruck ... WHERE abt_nr = " & [abt_nr] ist abt_nr der Name des Tabellenfeldes, und [abt_nr] der Name (nicht Steuerelementinhalt) des Formular- oder Berichtsfeldes.
Es ist generell darauf zu achten, dass das Steuerelement einen gültigen Inhalt liefert, weil ansonsten der Abfragetext fehlerhaft wird und die Funktion einen Fehler zurückgibt. Ist man sich unsicher, sollte besser ein Ausdruck wie ... = " & Nz([abt_nr];0) verwendet werden.


2. Als Zuweisung der Datensatzherkunft eines Listenfeldes per VBA

Der Schlüsselwert FirmaID hat den Datentyp Zahl.
   'Einspaltiges Listenfeld, keine Nullwerte
   Me.EinListenFeld.RowSource = SQLListe("SELECT Nachname FROM tblPerson" & _
                                         " WHERE FirmaID = " & Me.FirmaID)
 
   'Zweispaltiges Listenfeld, Nullwerte als Leerstring anzeigen
   Me.EinListenFeld.RowSource = SQLListe("SELECT Nachname, Vorname FROM tblPerson" & _
                                         " WHERE FirmaID = " & Me.FirmaID, , , True)
Wiki hinweis.png

Hinweis zu Kombinations- und Listenfeldern:

  • Der Herkunftstyp des Kombinations- oder Listenfeldes muss auf Wertliste eingestellt sein.
  • Es ist empfehlenswert, bei mehrspaltigen Kombinations- oder Listenfeldern die Nullwerte mit anzeigen zu lassen, sonst werden die Werte, die nach einem Nullfeld kommen, in der falschen Spalte angezeigt.


Wikilinks


Wiki faq.png FAQ: Dieser Artikel gehört zu den "Häufig gestellten Fragen" (FAQ). Die Seite Access-FAQ bietet eine Übersicht aller FAQ-Artikel.