[Access] met 1 keuzelijst meerdere velden updaten

[Verwijderd]

Ik zit met het volgende. Mn access-kennis is niet zo heel groot en VB is afgerond naar boven 0,0.

Het gaat om een ledenbeheerpakket voor een muziekvereniging. We hebben een tabel met alle instrumenten (tbl_instrumenten) en een tabel met alle leden (tbl_leden)

Elk lid heeft een volgnummer toegewezen door AutoNummering (volgnummer)
Elk instrument heeft een volgnummer toegewezen door AutoNummering (instrumentid)

Nu wil ik graag de volgende informatie weten:
-Welke instrumenten hebben geen gebruiker
-Welke leden hebben geen instrument

Daarvoor heb ik:
- in tbl_leden een veld aangemaakt welke de instrumentid bevat
- in tbl_instrumenten een veld aangemaakt welke de gebruikersid bevat

Door middel van queries kan ik in een formulier met uitschuiflijst een lid zonder instrument kiezen en daar in een subformulier een instrument zonder gebruiker kiezen.

Het probleem / de vraag:

Hoe schrijf ik vanuit de formulieren:
- instrumentid weg in [tbl_leden].[instrumentid]
- volgnummer weg in [tbl_instrumenten].[gebruikersid]

De eerlijkheid gebied me te bekennen, dat ik nog niet heb gezocht. Ik heb geen idee hoe dit heet waarop ik wil zoeken. Het liefst zou ik geen pasklare oplossing zien, want die ctrl-c / ctrl-v je en dan leer je er nog niks van. Ik ben eigenlijk op zoek naar zoekhints danwel aanwijzingen, zodat er voor mijzelf nog wat uit te zoeken valt zodat het blijft hangen.

[ Voor 6% gewijzigd door Verwijderd op 28-11-2005 11:04 ]

[Lustucru]

Sowieso is het niet goed om de relatie in twee tabellen te definieren. Zoek even op normalisatie en je krijgt een bulk informatie over je heen. Ook de Access help biedt wel informatie onder een op veel relaties.

Stel jezelf even twee vragen vragen:
- kan een lid meer dan een instrument hebben?
- kan een instrument door meer dan één lid in gebruik zijn?
Als a waar is, dan handhaaf je het veld gebruikersid in instrumenten.
Als b waar is, dan handhaaf je het veld instrument id in leden
Als geen van beide waar is dan maak het eigenlijk niet uit welke van de twee je handhaaft, maar gezien je vraag zou ik instrumentid bij leden handhaven.
Als alle twee waar zijn dan moet je een koppeltabel gaan gebruiken (lid_id, instrumentid).

Als je dit eenmaal hebt gedaan wijst het zichzelf eigenlijk verder.
Instrumenten die niet in gebruik zijn haal je dan op met een query met een outer join waarbij lidid 'null' is. Ook daarvan kun je voorbeelden in de help vinden (outer join).

[Verwijderd]

Hmm na heel veel prutsen samen met een collega is er het volgende uitgekomen:

-Veld gebruikersid in tabel tbl_instrumenten gehandhaafd
-Stukje VB code gebouwd om het Volgnummer van het lid in het veld gebruikersid te prutsen, met een stukje beveiliging zodat je een lid geen 2 instrumenten kunt toewijzen:

Visual Basic:

Private Sub invoervak22_Click()
    If DCount("*", "tbl_instrumenten", "gebruikersid= " & Volgnummer) = 0 Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE tbl_instrumenten " & _
                     "SET tbl_instrumenten.gebruikersid = " & Volgnummer & " " & _
                     "WHERE (((tbl_instrumenten.instrumentid)= " & invoervak22 & "));", 0
        DoCmd.SetWarnings True
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.Requery
        MsgBox "Instrument toegekend"
    Else
        MsgBox "Deze heb al een instrument !!!"
    End If
End Sub

[ Voor 9% gewijzigd door Verwijderd op 30-11-2005 09:08 ]

[Verwijderd]

Goed, nu hetzelfde geintje in tegenovergestelde richting.

Een lid stopt er mee, en levert het instrument weer in. Het lid wordt verwijderd uit de database en het instrument wordt weer vrijgegeven. Daarvoor heb ik de volgende code onder een knop zitten:

Visual Basic:

Private Sub knop_verwijder_lid_Click()
On Error GoTo Err_knop_verwijder_lid_Click
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tbl_instrumenten " & _
                 "INNER JOIN tbl_leden " & _
                 "ON tbl_instrumenten.gebruikersid = tbl_leden.Volgnummer " & _
                 "SET tbl_instrumenten.gebruikersid = 0 " & _
                 "WHERE (((tbl_instrumenten.gebruikersid)= " & keuzelijstlid & ")) " & _
                 "AND (((tbl_instrumenten.eigen_instrument)= false));", 0
                 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    DoCmd.SetWarnings True
    MsgBox "Lid verwijderd en instrument vrijgegeven"

Er zijn ook mensen met een eigen instrument. Om die 2 groepen instrumenten uit elkaar te houden, is er een true/false veld in de tbl_instrumenten. False voor een instrument van de vereniging, true voor een eigen instrument.

Nu heb ik al wel afgevangen, dat als het een eigen instrument is de gebruikersid niet gereset wordt, maar omdat het lid en daarmee het instrument binnen de vereniging verdwijnt zal het instrument ook uit de database moeten worden verwijderd. Wat is de truuk om dit aan te pakken ?

Je controleert op het veld eigen_instrument en als het antwoord true is blijft de gebruiker staan, dus access weet, dat de inhoud van het betreffende veld true is. Is er zoiets in te bouwen bij het opzoeken van het instrument dat

SQL:

SELECT *
FROM tbl_instrumenten
WHERE eigen_instrument = true
AND gebruikersid = "lidnummer dat niet meer bestaat"

Dat je dan de record verwijderd ?
Om het kort samen te vatten:

Ik wil nog graag een melding dat het instrument is verwijderd uit de database in plaats van dat het is vrijgegeven als het een eigen instrument betreft

En een schop in de goede richting voor het opbouwen van de actie die nodig is om het instrument te verwijderen

[ Voor 31% gewijzigd door Verwijderd op 30-11-2005 09:09 ]

[Verwijderd]

Visual Basic:

If DLookup("gebruikersid", "tbl_instrumenten", "eigen_instrument =  '& keuzelijstlid & '") And DLookup("eigen_instrument", "tbl_instrumenten", "eigen_instrument= 'False'") Then
         
         ' DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE tbl_instrumenten " & _
                     "INNER JOIN tbl_leden " & _
                     "ON tbl_instrumenten.gebruikersid = tbl_leden.Volgnummer " & _
                     "SET tbl_instrumenten.gebruikersid = 0 " & _
                     "WHERE (((tbl_instrumenten.gebruikersid)= " & keuzelijstlid & ")) " & _
                     "AND (((tbl_instrumenten.eigen_instrument)= false));", 0
                     
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        ' DoCmd.SetWarnings True
        MsgBox "Lid verwijderd en instrument vrijgegeven"
        
    Else
       ' DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE tbl_instrumenten.*" & _
                     "FROM tbl_instrumenten " & _
                     "WHERE (((tbl_instrumenten.eigen_instrument)=True) AND ((tbl_instrumenten.gebruikersid)= " & keuzelijstlid & "));", 0
                                         
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        ' DoCmd.SetWarnings True
        MsgBox "Lid samen met instrument richting noorderzon vertrokken"
        
    End If

Dit heb ik nu.

Het gaat fout in de dLookUp regel.

Waar moet de regel aan voldoen: hij moet in tbl_leden het instrument opzoeken dat het lid gebruikt. Daarvan moet gecontroleerd worden, of het een eigen instrument is of niet. Is het een eigen instrument, dan moet er naar "Else" gesprongen worden.

Is het een instrument van de vereniging dan moet de "if" uitgevoerd worden.

Laat ik de 2e DLookUp weg (tenslotte controleer ik ook nog een keer in het SQL-statement) dan springt ie steeds naar de Else.

Kan iemand mij vertellen wat er fout gaat ?

[ Voor 9% gewijzigd door Verwijderd op 30-11-2005 09:09 ]

[Lustucru]

Je bent vreselijk moeilijk aan het doen

Als ik het goed begrijp kan een lid maximaal één instrument hebben en een instrument is nooit door meer dan één lid tegelijk in gebruik.
Je primair object zijn je leden neem ik aan?

Dan hoort het instrument_id in de leden tabel. Daar maak je een unieke index van: zo kan een instrument nooit door meer dan één lid tegelijk in gebruik zijn. Het veld is in een formpje gekoppeld aan een simpele combobox die je vult met een query als:

code:

SELECT tblInstrumenten.id, tblInstrumenten.omschrijving,tblinstrumenten.prive
FROM tblInstrumenten
WHERE tblInstrumenten.id Not In (select instrumentid from tblleden);

of

code:

SELECT tblInstrumenten.id, tblInstrumenten.omschrijving,tblinstrumenten.prive
FROM tblInstrumenten LEFT JOIN tblLeden ON tblInstrumenten.id = tblLeden.instrumentID
WHERE tblLeden.id Is Null;

Beide varianten leveren alleen de instrumenten die niet in gebruik zijn.

De enige vba die je dan nodig hebt is om de combobox te verversen als er een keuze is gemaakt, en dat kan met

code:

Private sub ComboInstrument_afterupdate()
      me.comboInstrument.requery
End sub

Verder hoef je leden helemaal niet te verwijderen. Je zet er gewoon een flag bij dat ie niet meer meedoet. Bij het zetten van het vlaggetje moet je dan idd wel controleren of het een verenigingsinstrument is; en zo ja, dan zet je het instrumentID weer op leeg. Anders doe je helemaal niks: het instrument blijft immers van het -vertrokken- lid.

Bijvoorbeeld: als je het jezelf echt makkelijk hebt gemaakt en prive hebt opgenomen als verborgen kolom in de combobox; uitgegaan van één formuliertje gebaseerd op tblLeden:

Visual Basic:

Private sub cmdButtonLidBedankt_click()
        me.lidaf=true    
        If me.comboinstrument.column(2)=false OR IsNull(me.comboinstrument.column(2)) then
                  me.comboinstrument=null
        End if
End sub

Of nog makkelijker, vraag je af of het nodig is om priveinstrumenten op te nemen in je database. En als je er dan nog op staat kun je eens in de zoveel tijd een schoonmaakactie houden om wat records te verwijderen.

[ Voor 18% gewijzigd door Lustucru op 29-11-2005 17:26 ]

[Verwijderd]

Maar het gaat niet alleen over instrumentenbeheer dat is het formulier waar ik op vast loop, maar voor de rest worden er voortgangsdossiers over de leden bijgehouden en dat soort ongein. Het is nu 1x wat meer moeite doen voor dit formulier of een hele hoop andere forms en rapporten overhoop gaan gooien We hebben bijvoorbeeld ook meerdere categorieën contributie, jeugdleden, jeugdleden met muziekles, leden, ereleden enzovoort. Mensen die er niet meer zijn wil ik gewoon uit de database hebben, klaar

Stel jezelf even twee vragen vragen:
- kan een lid meer dan een instrument hebben?
- kan een instrument door meer dan één lid in gebruik zijn?
Als a waar is, dan handhaaf je het veld gebruikersid in instrumenten.

A is waar, zelf heb ik bijvoorbeeld 2 trombones, eentje van mezelf en eentje van de vereniging . Vandaar dat ik voor gebruikersid in instrumenten heb gekozen. 1 instrument kan idd maar aan 1 lid gekoppeld zijn.Privé-instrumenten willen we er wel bij in hebben. Bijvoorbeeld schade opgelopen aan priv's-instrumenten tijdens een optreden van de harmonie worden vergoed.

Maar het zal ongetwijfeld makkelijker kunnen, maar ik vind prutsen om er van te leren ook leuk. Het gaat me nu om de DLookUp, de situatie zonder prive-instrumenten in de database had ik vanmiddag voor elkaar. Het moet toch niet zo'n punt zijn om als ik een lid verwijder, er gekeken wordt of ie een privé-instrument heeft ? Zo ja dan moet ie zowel lid als instrument onder de kont schoppen zo nee dan alleen het lid en het instrument vrijgeven

[Lustucru]

Jouw feestje

Samengevat: in tblinstrumenten heb je een ja/nee veld [eigen_instrument], een veld gebruikersid dat naar ik mag hopen numeriek is, en een numeriek instrumentID en in je formulier staat een keuzelijstID die in de eerste kolom de numerieke gebruikersId heeft staan?

Dan: dlookup (Veld;tabel;criterium)
Alle parameters zijn strings en numerieke waarden in het criterium horen niet tussen single quotes

Visual Basic:

'ongeveer iets als
Dim InstrumentID as variant
InstrumentID=DlookUp("ID", "tblInstrumenten","Eigen_instrument = True And GebruikersID = " & keuzelijstlid)
If not isnull(instrumentid) then
     ' verwijder de zut
    docmd.runsql "Delete * from tblInstrumenten where id=" & instrumentid
    msgbox "Instrument verwijderd"
end if
InstrumentID=DlookUp("ID", "tblInstrumenten","GebruikersID = " & keuzelijstlid)
If not isnull(instrumentid) then
    ' zet gebruikersid op null
    msgbox "Instrument vrijgegeven"
end if

Omdat beide situaties kunnen voorkomen moet je ze dus ook voor de goede melding alle twee controleren.

Je hebt heel die ifjes alleen maar nodig om de juiste messageboxjes weer te geven; anders zou je gewoon die querys kunnen draaien. In mijn ervaring is gebruik van een query icm recordcount beduidend sneller dan DLookUp().

[Verwijderd]

Even om het compleet te houden de werkende oplossing voor mij:

Visual Basic:

Private Sub knop_verwijder_lid_Click()
On Error GoTo Err_knop_verwijder_lid_Click
 
        Dim InstrumentID As Variant
        InstrumentID = DLookup("gebruikersid", "tbl_instrumenten", "eigen_instrument = False And gebruikersid = " & keuzelijstlid)
    
    If Not IsNull(InstrumentID) Then
         
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE tbl_instrumenten " & _
                     "INNER JOIN tbl_leden " & _
                     "ON tbl_instrumenten.gebruikersid = tbl_leden.Volgnummer " & _
                     "SET tbl_instrumenten.gebruikersid = 0 " & _
                     "WHERE (((tbl_instrumenten.gebruikersid)= " & keuzelijstlid & ")) " & _
                     "AND (((tbl_instrumenten.eigen_instrument)= false));", 0
                     
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        DoCmd.SetWarnings True
        MsgBox "Lid verwijderd en instrument vrijgegeven"
    
    End If
        
       InstrumentID = DLookup("gebruikersid", "tbl_instrumenten", "eigen_instrument = True And gebruikersid = " & keuzelijstlid)
    
    If Not IsNull(InstrumentID) Then
    
        DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE tbl_instrumenten.*" & _
                     "FROM tbl_instrumenten " & _
                     "WHERE (((tbl_instrumenten.eigen_instrument)=True) AND ((tbl_instrumenten.gebruikersid)= " & keuzelijstlid & "));", 0
                                         
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        DoCmd.SetWarnings True
        MsgBox "Lid samen met instrument richting noorderzon vertrokken"
        
    End If

Exit_knop_verwijder_lid_Click:
    Exit Sub

Err_knop_verwijder_lid_Click:
    MsgBox Err.Description
    Resume Exit_knop_verwijder_lid_Click
    
End Sub

Niesje, bedankt

[ Voor 21% gewijzigd door Verwijderd op 30-11-2005 09:07 ]