Option Compare Database Option Explicit Private Sub cmdCancel_Click() DoCmd.Close End Sub Private Sub cmdOK_Click() frm_Adresse_Suchen_ADR_ID = lstAnzeige.SelectedItem DoCmd.Close End Sub Private Sub cmdSuchen_Click() Dim mydb As Database Dim rstAdresse As Recordset Dim SQL As String Dim SQL_Where As String Dim myListitem As ListItem SQL_Where = "" '//Wherestring bilden If txtName <> "" Then SQL_Where = "where ADR_Name like '*" & txtName & "*'" Else If txtStrasse <> "" Then SQL_Where = "where ADR_Strasse like '*" & txtStrasse & "*'" Else If txtPlz <> "" Then SQL_Where = "where ADR_ORT_ID like '*" & txtPlz & "*'" Else SQL_Where = "where ORT_Name like '*" & txtOrt & "*'" End If End If End If SQL = "SELECT tbl_Adresse.ADR_ID, tbl_Adresse.ADR_NAME, tbl_Adresse.ADR_STRASSE, tbl_Adresse.ADR_LDC_ID, tbl_Adresse.ADR_ORT_ID, tbl_ORT.ORT_NAME" _ & " FROM tbl_ORT INNER JOIN tbl_Adresse ON (tbl_ORT.ORT_LDC_ID = tbl_Adresse.ADR_LDC_ID) AND (tbl_ORT.ORT_ID = tbl_Adresse.ADR_ORT_ID)" & SQL_Where Set mydb = CurrentDb Set rstAdresse = mydb.OpenRecordset(SQL, dbOpenDynaset) lstAnzeige.ListItems.Clear If rstAdresse.EOF = True Then MsgBox "Keine entsprechenden Datensätze vorhanden" Else Do While rstAdresse.EOF = False Set myListitem = lstAnzeige.ListItems.Add(, "k" + Format(rstAdresse!ADR_ID), rstAdresse!ADR_ID) myListitem.SubItems(1) = rstAdresse!ADR_NAME myListitem.SubItems(2) = rstAdresse!ADR_STRASSE myListitem.SubItems(3) = rstAdresse!ADR_LDC_ID myListitem.SubItems(4) = rstAdresse!ADR_ORT_ID myListitem.SubItems(5) = rstAdresse!ORT_NAME rstAdresse.MoveNext Loop End If rstAdresse.Close End Sub