Office: Access - Listeneintrag per VBA einem Feldnamen zuweisen

Hallo,
ich möchte die Einträge eines Listenfelds mit einem bestimmten Feldnamen innerhalb einer VBA-Routine verbinden, stosse da aber programmiertechnisch an meine Grenzen...

Hintergrund: Für den Export nach Excel habe ich ein (Unter-)Formular erstellt, indem der User durch Auswahl von Einträgen in drei Listenfeldern die zu exportierenden Felder des Hauptformulars auswählen kann. Die Listenfelder sind dabei als Wertlisten angelegt; in den Wertlisten sind die jeweiligen Feldnamen der zugehörigen Tabelle hinterlegt. Das funktioniert soweit auch schon. Nun wäre es aber viel schöner, wenn nicht die Feldnamen in (zum Teil recht kryptischer) Kamelschreibweise in den Listenfeldern für den User sichtbar wären, sondern die Bezeichnungen der Felder, wie sie auch im Formular verwendet werden (Namen der Bezeichungsfelder).

Es müsste also imo innerhalb der For Each-Schleifen eine Zuweisung erfolgen etwa so:
"Für jeden Listeneintrag (=Name der Bezeichnungsfelder) verwende den zugehörigen Feldnamen". Nur wie sag ich das auf VBA?

Der Code sieht so aus:

'Taste zum Export der aktuell gewählten Felder im Datensatz nach Excel
Sub Export_Click()

Dim strSpalten As String, strIDs As String, itm As Variant, ID As Variant, strSql As String
Dim rs As DAO.Recordset, rst As DAO.Recordset
Dim xlApp As Object, xlWB As Object, xlSheet As Object, i As Integer

'Ausgewählte Einträge in Listenfeldern in strSpalten schreiben
strSpalten = ""

For Each itm In Me.FelderEinheit.ItemsSelected
strSpalten = strSpalten & Me.FelderEinheit.ItemData(itm) & ","
Next itm

For Each itm In Me.FelderLeitung.ItemsSelected
strSpalten = strSpalten & Me.FelderLeitung.ItemData(itm) & ","
Next itm

For Each itm In Me.FelderVerfahren.ItemsSelected
strSpalten = strSpalten & Me.FelderVerfahren.ItemData(itm) & ","
Next itm

If Len(strSpalten) = 0 Then
MsgBox "Keine Exportfelder gewählt!": Exit Sub
End If

strSpalten = Left(strSpalten, Len(strSpalten) - 1)

strIDs = ""
Set rst = [Forms]![frm_Einheitendaten_reg].RecordsetClone
rst.MoveFirst
Do Until rst.EOF
strIDs = strIDs & rst!DB_ID & ","
rst.MoveNext
Loop

strIDs = Left(strIDs, Len(strIDs) - 1)

If Len(strIDs) = 0 Then
MsgBox "Keine Datensätze gefiltert!": Exit Sub
End If

Debug.Print strSpalten
Debug.Print rst.RecordCount & ": " & strIDs


strSql = "SELECT " & strSpalten & " FROM tbl_Einheitendaten WHERE DB_ID IN ( " & strIDs & ")"

Set rs = CurrentDb.OpenRecordset(strSql)

'Excel starten
Set xlApp = CreateObject("Excel.application")
xlApp.Visible = True ' zum Testen
Set xlWB = xlApp.Workbooks.Add
Set xlSheet = xlWB.Sheets(1)

'Überschriften schreiben
For i = 0 To rs.Fields.Count - 1
xlSheet.Cells(1, i + 1) = rs.Fields(i).Name
Next

'Daten schreiben
xlSheet.Cells(2, 1).CopyFromRecordset rs

'Formatieren
xlSheet.Rows("1:1").Font.Bold = True
xlSheet.Cells.Columns.AutoFit

rs.Close
Set rs = Nothing
rst.Close
Set rst = Nothing

DoCmd.Close acForm, "ufrm_Feldauswahl_Einheitendaten_reg", acSaveNo

End Sub

 

Hallo,
schau Dir mal diesen Link zum Zusammenhang von Textfeldern und Bezeichnungsfeldern an.

Access: So ermitteln Sie die Bezeichnung für ein Textfeld (experto.de)

Beste Grüße
Andreas

 

Hallo Andreas,
danke für den Hinweis! Das ist es wohl, was ich hier brauche.

"Ermitteln des zu einem Bezeichnungsfeld gehörenden Steuerelementes (Textfeldes, Listenfeldes, Kombinationsfeldes usw.) in Access: strTextfeldName = Me.Bezeichnungsfeld.Parent.Name"

Leider bekomme ich die Eigenschaft "Parent.Name" noch nicht fehlerfrei in meine Schleife. Wenn ich so vorgehe:

For Each itm In Me.FelderEinheit.ItemsSelected
strSpalten = strSpalten & Me.FelderEinheit.ItemData(itm).Parent.Name & ","
Next itm

Erhalte ich einen Laufzeitfehler 424 'Objekt erforderlich'.

Noch eine Idee, wie das zusammengeht oder liege ich mit meinem Ansatz völlig falsch?

Beste Grüße
Stefan

 

Hallo Stefan,

das "itm" impliziert in der for each Schleife, dass es ein Controls-Objekt ist.
Das itm in der Klammer muss aber ein Integerindex beginnend mit 0. (siehe auch unter Begriff "Controlsaufzaehlung")

Gruß
Andreas

 

Hallo Andreas,
sorry für die späte Rückmeldung! Ich habe nach Deinem Hinweis mit viel Brüten über verschiedene Hilfeseiten und Blogeinträge zu Integerindex und Controlsaufzaehlung die Schleife folgendermaßen angepasst:

Dim List As Control
Dim itm As Variant
Dim itmCount As Integer

strSpalten = ""
Set List = Me.FelderEinheit
itmCount = Me.FelderEinheit.ItemsSelected.Count
For itm = 0 To itmCount - 1
strSpalten = strSpalten & [Forms]![frm_Einheitendaten_reg].ItemData(itm).Parent.Name & ","
Next itm

Da die Steuerelemente auf dem Formular Einheitendaten_reg liegen habe ich das noch angepasst. Leider erhalte ich jetzt aber einen Laufzeitfehler 2465 (Anwendungs- oder objektdefinierter Fehler). Denke das Parent.Name kommt nicht mit ItemData klar (?) Ersetzen von ItemData(itm) mit .column(0,itm) war auch nicht zielführend. Ich weiss nicht weiter.

Kannst Du mich nochmal in die richtige Richtung schubsen?

Gruß
Stefan

 

Hallo Stefan,
ich habe da noch eine, vielleicht einfachere, Funktion mit der man alle Bezeichnungsfeldertexte ermitteln kann.

Code:

Public Function GetLabels(strForm As String)
Dim ctl As Control

DoCmd.OpenForm strForm, , , , , acHidden

For Each ctl In Forms(strForm).Controls
    If ctl.ControlType = acLabel Then
       Debug.Print strForm & " " & ctl.Name & " " & ctl.Caption

    End If
Next

DoCmd.Close acForm, strForm, acSaveNo

End Function

Der Aufruf erfolgt durch

Code:

getlabels(MeinFormularname)       'z.B. getlabels("meinform")

Dabei ist es egal ob Du die Prozedur für das Haupt- oder das Unterformular aufrufst.
Falls ein (Unter)formular nicht einzeln im Hintergrund aufrufbar/zu öffnen ist, baue den Code in den jeweiligen Code des (Unter)Formulars ein und ersetze strForm durch Me!.

 

Hallo Andreas,
danke für Deine schnelle Rückmeldung! Hab deinen Code gerade getestet. Funktioniert soweit, aber...

mit ctl.Name erhalte ich nur den Namen des Bezeichnungsfelds - ich brauche aber den Feldnamen des zugehörigen Steuerelements (also Steuerelementinhalt).

Mit ctl.Parent.Name statt ctl.Name erhalte ich zwar den Namen des zugehörigen Steuerlements, aber immer noch nicht den Feldnamen (=Steuerelementinhalt / Spaltenname in der Tabelle).

Bei den Control-Objekt Eigenschaften hab ich nichts Passendes findes können. Dann bin ich auf ControlSource gestossen, aber Verwendung von ctl.Parent.ControlSource gibt mir nur einen Laufzeitfehler 438 (Objekt unterstützt diese Eigenschaft oder Methode nicht). Das geht also auch nicht zusammen.

Brauche ich dafür (Bezeichnungsfeldtext -> Feldname) vielleicht einen anderen Ansatz?

Gruß
Stefan

 

Ändere mal den Controltype auf acTextBox.

Dann filtert die Schleife nicht die Bezeichnungsfelder, sondern die eigentlichen Textboxen.

 

Hallo Andreas,
danke, aber mit acTextBox (statt acLabel) erhalte ich leider einen Laufzeitfehler 438. Setze ich wieder acLabel ein, läuft die Funktion durch (?).

Gruß
Stefan

 

Das Objekt Textbox hat keine Eigenschaft "Caption". deshalb der Fehler.

Code:

If ctl.ControlType = acTextBox Then
       Debug.Print strForm & " " & ctl.Name
 End If

 

Danke! Hab die Funktion noch etwas erweitert, um auch die Kontrollkästchen und Kombinationsfelder in meinem Formular zu erwischen und die zugehörigen Feldnamen auszulesen:

For Each ctl In Forms(strForm).Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox Then
Debug.Print strForm & " " & ctl.ControlSource
End If
Next

Das gibt mir nun alle Feldnamen im zugrundeliegenden Formular und bringt mich aber zurück zu meinem Kernproblem. Hast Du noch eine Idee, wie ich die in den Listenfeldern ausgewählten Bezeichnungsfeldtexte in die zugehörigen Feldnamen umwandeln und damit den String strSpalten für den Export befüllen kann?

Gruß
Stefan

 

Hast Du schon mal daran gedacht anstatt einer Werteliste für die Listenfelder eine Tabelle als Datenquelle zu nutzen, in der Du dann die Feldnamen referenzierst? Über die gebundene Spalte sollte das dann ja auch gehen.

 

Super Idee! Das probiere ich sobald wie möglich aus.

 

Hallo Andreas,
hab nun eine Lösung gefunden
Über ein Listenfeld mit gebundener Spalte ging leider nicht, da ich dann im Excel-Output immer den Originalfeldnamen der Tabellenspalten erhalten habe. Nach einigem Suchen im Netz habe ich es nun so gelöst, dass ich zunächst die Spalten der betreffenden Tabelle in einer Abfrage mit Aliasnamen versehe:

SELECT tbl_Einheitendaten.[Einheitenname_engl] AS [Unit Name engl],
tbl_Einheitendaten.[Einheitenname] AS [Unit Name ger],
tbl_Einheitendaten.[Einheitenkürzel] AS [Unit Abbreviation],
tbl_Einheitendaten.[Einheitentyp] AS [Unit Type],
...
FROM tbl_Einheitendaten;

Das Befüllen des Strings mit den Aliasnamen geschieht über Auswahl in einem Listenfeld (z.B. FieldsUnit), in dem alle Aliasnamen notiert sind, und sieht dann so aus:

For Each itm In Me.FieldsUnit.ItemsSelected
strSpalten = strSpalten & "[" & Me.FieldsUnit.ItemData(itm) & "],"
Next itm

Und der komplette Datensatz wird so erfasst, wobei jetzt die Abfrage mit den Aliasnamen als Quelle dient:

strSql = "SELECT " & strSpalten & " FROM qry_Einheitendaten_Export WHERE DB_ID IN ( " & strIDs & ")"

Erstaunlicherweise musste ich an der Exportsubroutine an der Stelle, an der die Überschriften erstellt werden, gar nichts mehr ändern:

For i = 0 To rs.Fields.Count - 1
xlSheet.Cells(1, i + 1) = rs.Fields(i).Name
Next

Hatte zunächst den Verdacht, dass "Name" wieder auf die eigentlichen Spaltennamen der Tabelle zurückgreift.

Besten Dank für Deine Hilfe! Hab viel gelernt.

Gruß
Stefan