Standaardwaarde formulierveld bepalen obv verschillende tabellen

[BartSt]

Hallo,

Hierbij (het relevante deel van) mijn tabellenstructuur: Toelichtingen daarbij:
- Baremas.Loon is een DateTime-veld.
- De twee tabellen met één veld lijken misschien vreemd, maar heb ik zo moeten maken omdat Access geen veel-op-veel relaties ondersteunt.

Ik heb een formulier per WN_Persoonsgegevens.Rijksregisternummer, met daarin een subformulier voor WN_Tewerkstellingen.

Op dat formulier wil ik dat het veld "Brutomaandloon" als standaardwaarde het Baremas.Loon geeft dat overeenkomt met:
- de recentste indexatie
- het barema dat hoort bij de functie
- EN de anciënniteit die wordt opgegeven in WN_Tewerkstellingen (en dus in het hierboven vermelde subformulier dat is gebaseerd op deze tabel.)

Ik heb een aantal zaken geprobeerd, waaronder Dlookup(). No cigar. Wie weet raad?

 

[Tardis]

Met afbeeldingen kunnen we nit veel.
Sluit even je database bij.

Paar opmerkingen, MS Access kan, naast andere databases, prima overweg met veel op veel relaties.
Alleen moet je die ombouwen naar 1 op veel relaties, wat je dus gedaan hebt.

Tabel ancienniteit kan weg, je ancienniteit kun je via je Functie tabel ophalen uit je Barma tabel.
Samengestelde primaire sleutel, theorie, in de praktijk ben je beter af met 1 primaire sleutel.
Die zet je dan op een ID kolom, gegevenstype Autonummering.
Om eventueel in te regelen dat een combinatie van gegevens uniek is gebruik je dan een samengestelde index.

Reden is dat gegevens veel sneller benaderd kunnen worden, zeker als je queries gaat maken.
Daarnaast, als je met queries gaat werken waarin je tabellen met samengestelde primaire sleutels gebruikt kan dat de performance aanzienlijk naar beneden halen.

Tardis

 

[BartSt]

Tardis,

Bedankt voor je advies! Dat kan op termijn inderdaad een verschil maken. Ik ben zelf geen fan van samengestelde sleutels, maar had een enkele primaire sleutel met een samengestelde index gewoonweg niet overwogen.

Ik kan helaas mijn DB niet uploaden, daar het over vertrouwelijke info gaat.

Wel kan ik de mislukte Dlookup() meegeven waarvan sprake in mijn eerste post. Hopelijk kan ik zo een idee geven van wat ik wil bereiken:

Dlookup("[loon]","baremas",[indexatie] in max([indexatie]) AND [baremas]![anciënniteit]=[wn_tewerkstellingen].[anciënniteit] AND [baremas]![barema]=[functies]![barema])

Dit had ik dus ingevuld in de standaardwaarde-eigenschap van het formulierveld "brutomaandloon".

 

[Tardis]

Maak een lege kopie van je DB, zet er testgegevens in en voeg deze bij als bijlage.

Tardis

 

[BartSt]

Het heeft even geduurd, maar hierbij een testversie van mijn DB. Let wel: in tegenstelling tot de echte versie zijn niet alle mogelijke combinaties van barema's en anciënniteit aanwezig in de tabel "Barema's". Ik heb één werknemer in dit bestand staan: zie personeelsfiche. In de opmerking daarvan staat wat er moet gebeuren. Daarbij wil ik dus graag dat het gepaste maandloon automatisch verschijnt.

Bekijk bijlage 2012-09-05 Testbestand tewerkstellingen_lonen.zip

 

[Tardis]

Kan je bestand niet openen.
Sla je bestand anders op als Access 2007 bestand en voeg dat toe.

Tardis

 

[BartSt]

Kan je bestand niet openen.
Sla je bestand anders op als Access 2007 bestand en voeg dat toe.

Tardis

Het was in 2007 indeling, dus ik vermoed dat je 2003 bedoelt. Dat zit in bijlage.

(Het lukte me niet mijn 2010 bestand op te slaan als 2003, dus heb ik een nieuw 2003 bestand moeten maken. Ik heb alle tabellen en formulieren gekopieerd en de relaties opnieuw gelegd. Dat zou moeten volstaan.)

Bekijk bijlage 2012-09-06 Test DB Tewerkstellingen_Lonen.zip

 

[Tardis]

Het was in 2007 indeling, dus ik vermoed dat je 2003 bedoelt.

Nee ik bedoel wel degelijk 2007, je bestand was niet in 2007 indeling.
Anyway, bestand is nu wel te openen.
Zal eens kijken zodra ik tijd heb.

Tardis

 

[Tardis]

Een oplossing is de volgende

- maak een nieuwe query in ontwerpweergave
- ga naar SQL weergave en plak de volgende SQL in het venster:

SELECT Functies.Functienaam, Lonen.Loon
FROM Lonen INNER JOIN Functies ON Lonen.Barema = Functies.Barema
WHERE Lonen.Anciënniteit = (SELECT Max( Lonen_1.Anciënniteit) FROM Lonen AS Lonen_1 WHERE Lonen.Barema =  Lonen_1.Barema);

- sla de query op, ik heb hem Query1 genoemd
- open je subformulier in ontwerpweergave
- zet de volgende code in de Na Bijwerken gebeurtenis van je functie veld:

[INDENT]Private Sub Functie_AfterUpdate()

Dim strBruto As String

strBruto = DLookup("Loon", "Query1", "Functienaam = '" & Me.Functie & "'")

Me.Brutomaandloon = strBruto

End Sub[/INDENT]

Sla het formulier op.

Na het kiezen van een functie wordt nu brutomaandsalaris ingevuld met het bedrag dat hoort bij de hoogste anciënniteit op basis van de barema die bij de gekozen functie hoort.

Tardis

 

[Tardis]

Geheel in VBA:

Private Sub Functie_AfterUpdate()

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

strSQL = "SELECT Lonen.Loon FROM Lonen INNER JOIN Functies ON Lonen.Barema = Functies.Barema " & _
" WHERE Functienaam = '" & Me.Functie & "' AND Lonen.Anciënniteit = (SELECT Max(Lonen_1.Anciënniteit) FROM Lonen AS Lonen_1 WHERE Lonen.Barema =  Lonen_1.Barema)"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

rst.MoveFirst
rst.MoveLast
If rst.RecordCount <> 0 Then
    If rst.RecordCount = 1 Then
    Me.Brutomaandloon = rst!Loon
    Else: MsgBox "Meer waarden gevonden"
    End If
Else: MsgBox "geen waarden gevonden"
End If

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

Tardis

 

[BartSt]

Tardis,

Bedankt, maar ik heb iets anders nodig. Met name het loon dat overeenstemt met:

• de recentste indexatie (niet de anciënniteit) die van kracht is
• het barema dat aan de functie is gekoppeld (WN_Tewerkstellingen => Functies => Baremanummers => Lonen)
• en de anciënniteit die ik ingeef in het subformulier WN_Tewerkstellingen ( => HLP_T_Anciënniteiten => Lonen)

Op basis van jouw query heb ik een variant gemaakt (naam: HQ_Lon_Lonen laatste indexatie) die alle lonen oplijst van de meest recente indexatie:

SELECT Functies.Functienaam, Lonen.Anciënniteit, Lonen.Loon
FROM Lonen INNER JOIN Functies ON Lonen.Barema = Functies.Barema
WHERE Lonen.Indexatie = (SELECT Max( Lonen_1.Indexatie) FROM Lonen AS Lonen_1 WHERE Lonen.Barema =  Lonen_1.Barema);

Dat is dus al een stap vooruit.

 

[BartSt]

Tardis,

Ik realiseer me net het volgende:

Eigenlijk moet er naar de startdatum van de tewerkstelling (WN_Tewerkstellingen) worden gekeken in plaats van de meest recente indexatie: welke indexatie was van kracht toen de tewerkstellingsperiode inging?

Als het loon dat overeenstemt met de meest recente indexatie altijd wordt ingevuld, wordt records in WN_Tewerkstellingen aanmaken/wijzigen in het verleden of de toekomst gevaarlijk. Als er bijvoorbeeld al een toekomstige indexatie werd ingegeven, en een record in WN_Tewerkstellingen wordt aangemaakt/gewijzigd waarvan de startdatum voor die van de toekomstige (en dus de recentste) indexatie valt, wordt het verkeerde loon ingevuld.

Ik realiseer me dit net zelf pas, en moet dus mijn initiële vraag bijsturen. Het loon dat moet verschijnen moet overeenstemmen met:

• de indexatie die van kracht is bij de startdatum van de tewerkstelling
• het barema dat aan de functie is gekoppeld
• en de anciënniteit die ik ingeef in het subformulier WN_Tewerkstellingen

Een hele boterham, ik weet het. Daarom vroeg ik dus hulp

Wegens verlof zal ik er overigens eventjes niet zijn. Het is maar dat je weet waarom ik tot volgende week dinsdag niet zal antwoorden.

 

[Tardis]

Maak van je veld anciënniteit een keuzelijst met waarden.
Zet daarna de volgende code in de Na Bijwerken gebeurtenis van je keuzelijst anciënniteit:

Private Sub Anciënniteit_AfterUpdate()

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
On Error Resume Next

If Len(Trim(Me.Startdatum & "")) = 0 Then
MsgBox "Eerst een startdatum invullen"
Me.Anciënniteit = Null
Me.Startdatum.SetFocus
GoTo 1:
End If

If Len(Trim(Me.Functie & "")) = 0 Then
MsgBox "Eerst een functie kiezen"
Me.Anciënniteit = Null
GoTo 1:
End If

strSQL = "SELECT Lonen.Loon FROM Lonen INNER JOIN Functies ON Lonen.Barema = Functies.Barema " & _
" WHERE Functienaam = '" & Me.Functie & "' AND Lonen.Indexatie = " & _
"(SELECT Min(Lonen_1.Indexatie) FROM Lonen AS Lonen_1 WHERE Lonen.Barema = Lonen_1.Barema " & _
" AND Lonen_1.Indexatie >= # " & Format(Me.Startdatum, "mm-dd-yyyy") & " # AND Lonen_1.Anciënniteit = " & Me.Anciënniteit & " AND Lonen_1.Anciënniteit = Lonen.Anciënniteit) "

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

rst.MoveFirst
rst.MoveLast
If rst.RecordCount <> 0 Then
    If rst.RecordCount = 1 Then
    Me.Brutomaandloon = rst!Loon
    Else: MsgBox "Meer waarden gevonden"
    End If
Else: MsgBox "geen waarden gevonden"
End If

rst.Close
Set rst = Nothing
Set db = Nothing

1:
Exit Sub

End Sub

Tardis

 

[BartSt]

Tardis,

Bedankt! Hoewel ik zelf VBA kan schrijven, begrijp ik de logica achter je code en ik denk dat die overeenstemt met wat ik zoek.

Wanneer ik een record toevoeg aan WN_Tewerkstellingen (via het gelijknamige subformulier) kom ik echter steeds uit bij de foutmelding "geen waarden gevonden". Doe ik iets verkeerd?

Moet ik misschien nog een verwijzing naar een library toevoegen? (Dat heb ik nog ooit moeten doen voor een recordset benadering.)

 

[Tardis]

Bart,

als ik die melding krijg is die bij mij logisch te verklaren.
De recordset vind dan namelijk geen enkel record.
Ik vermoed dat dat bij jou ook het geval is (en dat is dan op zich logisch).
Anders lijkt me dat je iets niet goed doet.

Met libraries heeft dit niets te maken.
Dan zou je foutmeldingen krijgen, deze melding die je nu krijgt is geen foutmelding.

Tardis

 

[BartSt]

Tardis,

Ik heb het gevonden. In het SQL gedeelte wordt de indexatiedatum vergeleken met de startdatum van de tewerkstelling. Ik heb dat aangepast naar indexatie <= startdatum.
Van min(indexatie) heb ik ook max(indexatie) gemaakt.

Heel hard bedankt voor al je hulp! Ik weet dat het geen evidente vraag was. Het resultaat mag er wezen! :thumb: