Option Explicit
Private Sub cbzkAppellation_Change()
If cbzkAppellation.Value <> "" Then
Appellationladen = cbzkAppellation.Value
Inladenhuis
txtWijnnummer.Enabled = False
chbPrimeur.Enabled = False
Else
txtWijnnummer.Enabled = True
chbPrimeur.Enabled = True
End If
End Sub
Private Sub cbzkHuis_Change()
If cbzkHuis.Value <> "" Then
Huisladen = cbzkHuis.Value
txtWijnnummer.Enabled = False
chbPrimeur.Enabled = False
chbHuis.Enabled = True
Else
txtWijnnummer.Enabled = True
If cbzkAppellation.Value = "" And cbzkLand.Value = "" And cbzkStreek.Value = "" Then chbPrimeur.Enabled = True
chbHuis.Enabled = False
End If
End Sub
Private Sub cbzkLand_Change()
If cbzkLand.Value <> "" Then
Inladenstreken
Landlanden = cbzkLand.Value
txtWijnnummer.Enabled = False
chbPrimeur.Enabled = False
Else
txtWijnnummer.Enabled = True
chbPrimeur.Enabled = True
End If
End Sub
Private Sub cbzkStreek_Change()
If cbzkStreek.Value <> "" Then
InladenAppellation
Streekladen = cbzkStreek.Value
txtWijnnummer.Enabled = False
chbPrimeur.Enabled = False
Else
txtWijnnummer.Enabled = True
chbPrimeur.Enabled = True
End If
End Sub
Private Sub cmdclose_Click()
Unload frm03Zoeken
frm03Zoeken.Hide
Load frmMenu
frmMenu.Show
End Sub
Private Sub cmdZoeken_Click()
Huisladentype = False
If txtWijnnummer.Value <> "" Then
WeergaveType = 7
WijnweergaveID = txtWijnnummer.Value
Else
If chbPrimeur.Value = True Then WeergaveType = 6
If cbzkLand.Value <> "" And cbzkStreek.Value <> "" And cbzkAppellation.Value <> "" And cbzkHuis.Value <> "" Then
WeergaveType = 5
If chbHuis.Value = True Then Huisladentype = True
ElseIf cbzkLand.Value <> "" And cbzkStreek.Value <> "" And cbzkAppellation.Value <> "" Then
WeergaveType = 2
ElseIf cbzkLand.Value <> "" And cbzkStreek.Value <> "" Then
WeergaveType = 3
ElseIf cbzkLand.Value <> "" Then
WeergaveType = 4
End If
End If
If WeergaveType = 7 Then
Unload frm03Zoeken
frm03Zoeken.Hide
Load frm05Zkweergavedetail
frm05Zkweergavedetail.Show
Else
Noload = False
Unload frm03Zoeken
frm03Zoeken.Hide
Load frm04Zkweergave
frm04Zkweergave.Show
End If
End Sub
Private Sub txtzkHuis_Change()
Huisladen = txtzkHuis.Value
End Sub
Private Sub txtWijnnummer_AfterUpdate()
If txtWijnnummer.Value <> "" Then
If IsNumeric(txtWijnnummer) = False Then
MsgBox "Melding: geen geldig wijnnummer", vbCritical
txtWijnnummer.Value = ""
End If
End If
End Sub
Private Sub txtWijnnummer_Change()
If txtWijnnummer.Value <> "" Then
cbzkLand.Enabled = False
cbzkStreek.Enabled = False
cbzkAppellation.Enabled = False
cbzkHuis.Enabled = False
chbPrimeur.Enabled = False
chbHuis.Enabled = False
Else
cbzkLand.Enabled = True
cbzkStreek.Enabled = True
cbzkAppellation.Enabled = True
cbzkHuis.Enabled = True
chbPrimeur.Enabled = True
chbHuis.Enabled = False
End If
End Sub
Private Sub UserForm_Initialize()
chbHuis.Enabled = False
Inladenlanden
End Sub
Private Sub InladenAppellation()
Dim cnnlocatieladen As ADODB.Connection
Dim rstladen As ADODB.Recordset
Dim strCnn As String
Dim StrSQL As String
Dim strProvider As String
Dim i As Long, Statusload
Dim jaarnummer As Long
i = 0
StrSQL = "SELECT tbStreek.Streek, tbStreek.IDstreek, tbWijnaantal.Appellation, tbAppellation.appellationnaam FROM (tbStreek INNER JOIN tbAppellation ON tbStreek.IDstreek = tbAppellation.Streeknaam) INNER JOIN tbWijnaantal ON tbAppellation.IDApp = tbWijnaantal.Appellation GROUP BY tbStreek.Streek, tbStreek.IDstreek, tbWijnaantal.Appellation, tbAppellation.appellationnaam HAVING (((tbStreek.Streek)='" & cbzkStreek.Value & "'));"
strProvider = "Microsoft.Jet.OLEDB.4.0"
strCnn = Connectionstring & "\VermaatKelderboek.mdb"
Set cnnlocatieladen = New ADODB.Connection
cnnlocatieladen.Provider = strProvider
cnnlocatieladen.Open strCnn
Set rstladen = New ADODB.Recordset
rstladen.Open StrSQL, cnnlocatieladen, adOpenForwardOnly, adLockReadOnly
cbzkAppellation.Clear
Do While Not rstladen.EOF
cbzkAppellation.AddItem rstladen!appellationnaam
rstladen.MoveNext ' Exit conditie word hierdoor bereikt
i = i + 1
Loop
rstladen.Close
cnnlocatieladen.Close
Set rstladen = Nothing
Set cnnlocatieladen = Nothing
End Sub
Private Sub Inladenstreken()
Dim cnnlocatieladen As ADODB.Connection
Dim rstladen As ADODB.Recordset
Dim strCnn As String
Dim StrSQL As String
Dim strProvider As String
Dim i As Long, Statusload
Dim jaarnummer As Long
i = 0
StrSQL = "SELECT tbLand.Landnaam, tbStreek.Streek, Count(tbWijnaantal.Wijnnaam) AS AantalVanWijnnaam FROM ((tbLand INNER JOIN tbStreek ON tbLand.IDland = tbStreek.Land) INNER JOIN tbAppellation ON tbStreek.IDstreek = tbAppellation.Streeknaam) INNER JOIN tbWijnaantal ON tbAppellation.IDApp = tbWijnaantal.Appellation GROUP BY tbLand.Landnaam, tbStreek.Streek HAVING (((tbLand.Landnaam)='" & cbzkLand.Value & "'));"
strProvider = "Microsoft.Jet.OLEDB.4.0"
strCnn = Connectionstring & "\VermaatKelderboek.mdb"
Set cnnlocatieladen = New ADODB.Connection
cnnlocatieladen.Provider = strProvider
cnnlocatieladen.Open strCnn
Set rstladen = New ADODB.Recordset
rstladen.Open StrSQL, cnnlocatieladen, adOpenForwardOnly, adLockReadOnly
cbzkStreek.Clear
Do While Not rstladen.EOF
cbzkStreek.AddItem rstladen!streek
rstladen.MoveNext ' Exit conditie word hierdoor bereikt
i = i + 1
Loop
rstladen.Close
cnnlocatieladen.Close
Set rstladen = Nothing
Set cnnlocatieladen = Nothing
End Sub
Private Sub Inladenhuis()
Dim cnnlocatieladen As ADODB.Connection
Dim rstladen As ADODB.Recordset
Dim strCnn As String
Dim StrSQL As String
Dim strProvider As String
Dim i As Long, Statusload
Dim jaarnummer As Long
i = 0
StrSQL = "SELECT tbAppellation.appellationnaam, tbWijnaantal.Wijnnaam FROM tbAppellation INNER JOIN tbWijnaantal ON tbAppellation.IDApp = tbWijnaantal.Appellation GROUP BY tbAppellation.appellationnaam, tbWijnaantal.Wijnnaam HAVING (((tbAppellation.appellationnaam) ='" & cbzkAppellation.Value & "')) ORDER BY tbWijnaantal.Wijnnaam;"
strProvider = "Microsoft.Jet.OLEDB.4.0"
strCnn = Connectionstring & "\VermaatKelderboek.mdb"
Set cnnlocatieladen = New ADODB.Connection
cnnlocatieladen.Provider = strProvider
cnnlocatieladen.Open strCnn
Set rstladen = New ADODB.Recordset
rstladen.Open StrSQL, cnnlocatieladen, adOpenForwardOnly, adLockReadOnly
cbzkHuis.Clear
Do While Not rstladen.EOF
cbzkHuis.AddItem rstladen!Wijnnaam
rstladen.MoveNext ' Exit conditie word hierdoor bereikt
i = i + 1
Loop
rstladen.Close
cnnlocatieladen.Close
Set rstladen = Nothing
Set cnnlocatieladen = Nothing
End Sub
Private Sub Inladenlanden()
Dim cnnlocatieladen As ADODB.Connection
Dim rstladen As ADODB.Recordset
Dim strCnn As String
Dim StrSQL As String
Dim strProvider As String
Dim i As Long, Statusload
Dim jaarnummer As Long
i = 0
StrSQL = "SELECT tbLand.Landnaam FROM tbLand;"
strProvider = "Microsoft.Jet.OLEDB.4.0"
strCnn = Connectionstring & "\VermaatKelderboek.mdb"
Set cnnlocatieladen = New ADODB.Connection
cnnlocatieladen.Provider = strProvider
cnnlocatieladen.Open strCnn
Set rstladen = New ADODB.Recordset
rstladen.Open StrSQL, cnnlocatieladen, adOpenForwardOnly, adLockReadOnly
cbzkLand.Clear
Do While Not rstladen.EOF
cbzkLand.AddItem rstladen!Landnaam
rstladen.MoveNext ' Exit conditie word hierdoor bereikt
i = i + 1
Loop
rstladen.Close
cnnlocatieladen.Close
Set rstladen = Nothing
Set cnnlocatieladen = Nothing
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
End If
End Sub