Opening form based on a Query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images Link Email

Opening form based on a Query

Kim Mittet

10 months ago

I have a form that is opened using VBA code - DoCmd.OpenForm "ManglendeBetalingM'nedF".
The form get the data from a query which i based on a table.
Before i open the form i populate the table (IkkBetaltePosterM'nedT) in the same sub which ends with the command - DoCmd.OpenForm "ManglendeBetalingM'nedF".
The code is triggered from a button -Private Sub ManglendeBetalingerM'ned_Click().
My problem is that when i from the form which contains the button, move to another form and change data that should change the data in the form "ManglendeBetalingM'nedF", it is not changed before i close and open the form which contains the button - i miss how to update after changing the data. My believe was that the query was triggered when i open the form - have i misunderstood that ?

	Adam Schwanz 10 months ago
May or may not be related to your question, but I would strongly advise not to use any symbols in your table/form/query/field names, especially quotes/double quotes. That's going to cause headaches eventually.

	Adam Schwanz 10 months ago
As for the question, can you post the entire sub? Or how does it "populate the table"? You may just need to use one of Refresh v Requery

	Kim Mittet OP 10 months ago
Are you refering to - "ManglendeBetalingM'nedF" ? - if yes, the quotes is just an attemp to highlight the name of the table/form

Kim Mittet OP

10 months ago

Details

Private Sub ManglendeBetalingerM'ned_Click()

    Dim db As DAO.Database

    Dim rstBudget As DAO.Recordset

    Dim rstFaktisk As DAO.Recordset

    Dim rstManglendeBetalinger As DAO.Recordset

    Dim rstBudgetoverf'rsler As DAO.Recordset

    Dim M'ned As String

    Dim Indev'rendeM'ned As String

    Dim 'rstal As String

    Dim UGID As Integer ' Undergruppe ID

    Dim PostType As Integer ' Angivelse af Indt'gt, Udgift eller opsparing

    Dim Budgetbel'b As Long ' Det budgetterede bel'b

    Dim Faktiskbel'b As Long ' Det faktiske bel'b

    Dim BdgPostId As Integer

    Dim Databasesti As String

    Dim BudgetOmr As Integer ' Budgetomr'de f.eks. Sommerhus, Lejlighed, Hus eller Andet

    Dim BudgetKntRel As Boolean ' V'rdien fra feltet Budgetkontorelateret fra tabellen FaktiskBudgetposterT

    

    

    

    Databasesti = DLookup("[Databasesti]", "[Initialiseringdata]")

    

    Set db = OpenDatabase(Databasesti)

    Set rstBudget = db.OpenRecordset("BudgetposterT", dbOpenSnapshot)

    Set rstFaktisk = db.OpenRecordset("FaktiskBudgetposterT", dbOpenSnapshot)

    Set rstManglendeBetalinger = db.OpenRecordset("IkkeBetaltePosterM'nedT", dbOpenTable)

    

    

    

    'Initialiser variabler

    

    BdgPostId = 0

    M'ned = Month(Now())

    'rstal = Year(Now())

    Budgetbel'b = 0

    Faktiskbel'b = 0

    



'Her opsamles data til tabellen "Opf'lgningForrigeM'nedT

'Tabellen skal indholde oplysninger om budgetposter og faktiske poster som findes for forrige m'ned.

'Der beregnes i tabellen en eventuel difference mellem budgetteret og faktiske udgifter

   

   Select Case M'ned

        Case 1

            Indev'rendeM'ned = "Bel'bJan"

        Case 2

            Indev'rendeM'ned = "Bel'bFeb"

        Case 3

            Indev'rendeM'ned = "Bel'bMar"

        Case 4

            Indev'rendeM'ned = "Bel'bApr"

        Case 5

            Indev'rendeM'ned = "Bel'bMaj"

        Case 6

            Indev'rendeM'ned = "Bel'bJun"

        Case 7

            Indev'rendeM'ned = "Bel'bJul"

        Case 8

            Indev'rendeM'ned = "Bel'bAug"

        Case 9

            Indev'rendeM'ned = "Bel'bSep"

        Case 10

            Indev'rendeM'ned = "Bel'bOkt"

        Case 11

            Indev'rendeM'ned = "Bel'bNov"

        Case 12

            Indev'rendeM'ned = "Bel'bDec"

    End Select

    

    





'Slet alle poster i tabellen



    db.Execute "DELETE * from IkkeBetaltePosterM'nedT;"



'Fyld de nye data i tabellen

    

    rstBudget.MoveFirst

    Do Until rstBudget.EOF



' Her findes poster i BudgetposterT hvor der er budgetteret med et bel'b st'rre end nul.

' Herefter findes tilsvarende poster i FaktiskBudgetposterT udfra nedenst'ende kriterierog hvor der ikke er registreret et bel'b, det vil sige

' at der er en budgetpost for m'neden, men der er ikke registreret et faktisk bel'b for den samme post i FaktiskBudgetposterT.

' De fundne poster kopieres over i en ny tabel, IkkeBetaltePosterM'nedT





            If rstBudget!Budget'r = 'rstal And rstBudget(Indev'rendeM'ned) > 0 Then

                UGID = rstBudget!UndergruppeId ' UndergruppeID

                PostType = rstBudget!Budgetposttype ' Angivelse af Indt'gt, Udgift eller opsparing

                Budgetbel'b = rstBudget(Indev'rendeM'ned) ' Det budgetterede bel'b

                BudgetOmr = rstBudget!Budgetomr'de ' Budgetomr'de f.eks. Sommerhus, Lejlighed, Hus eller Andet

                

                    rstFaktisk.MoveFirst

                    Do Until rstFaktisk.EOF

                        If rstFaktisk!Budget'r = 'rstal And rstFaktisk!UndergruppeId = UGID And rstFaktisk(Indev'rendeM'ned) = 0 And _

                        rstFaktisk!Budgetomr'de = BudgetOmr Then

                        Faktiskbel'b = rstFaktisk(Indev'rendeM'ned)

                        BudgetKntRel = rstFaktisk!BudgetkontoRelateret

                            With rstManglendeBetalinger

                                .AddNew

                                .Fields("UndergruppeID") = UGID

                                .Fields("BudgetpostType") = PostType

                                .Fields("Budgetbel'b") = Budgetbel'b

                                .Fields("Faktiskbel'b") = Faktiskbel'b

                                .Fields("BdgKntRel") = BudgetKntRel

                                .Fields("Budgetomr'de") = BudgetOmr

                                .Update

                            End With

                        End If

                        

                        rstFaktisk.MoveNext

                     Loop

                



            End If

            

            

' Her findes poster i BudgetposterT som ikke har registreret et bel'b, men der er betalt et bel'b for m'neden.

' Disse kopieres til IkkeBetaltePosterM'nedT



            

           If rstBudget!Budget'r = 'rstal And rstBudget(Indev'rendeM'ned) = 0 Then

              UGID = rstBudget!UndergruppeId

              BudgetOmr = rstBudget!Budgetomr'de

              Budgetbel'b = rstBudget(Indev'rendeM'ned) ' Det budgetterede bel'b

                    rstFaktisk.MoveFirst

                    Do Until rstFaktisk.EOF

                        If rstFaktisk!Budget'r = 'rstal And rstFaktisk!UndergruppeId = UGID And rstFaktisk(Indev'rendeM'ned) <> 0 And _

                        rstFaktisk!Budgetomr'de = BudgetOmr Then

                        BdgPostId = rstFaktisk!BudgetpostId

                        Faktiskbel'b = rstFaktisk(Indev'rendeM'ned)

                            With rstManglendeBetalinger

                                .AddNew

                                .Fields("UndergruppeID") = UGID

                                .Fields("BudgetpostType") = PostType

                                .Fields("Budgetbel'b") = Budgetbel'b

                                .Fields("Faktiskbel'b") = Faktiskbel'b

                                .Fields("BdgKntRel") = BudgetKntRel

                                .Fields("Budgetomr'de") = BudgetOmr

                                .Update

                            End With

                        End If

                        

                    rstFaktisk.MoveNext

                    Loop

                

           End If

           

'           UGID = rstBudget!UndergruppeId

           rstBudget.MoveNext

            

    Loop

    

    

'Luk database og recordset

    

rstBudget.Close

Set rstBudget = Nothing



rstFaktisk.Close

Set rstFaktisk = Nothing



rstManglendeBetalinger.Close

Set rstManglendeBetalinger = Nothing



db.Close

Set db = Nothing



DoCmd.OpenForm "ManglendeBetalingM'nedF"



End Sub

	Richard Rost 10 months ago
You must have a foreign-language version that allows ' in field names.

	Ludwig Willems 9 months ago
Kim, ben jij toevallig ook uit Belgi', uit je code te zien merk ik Nederlands te herkennen.

	Kim Mittet OP 9 months ago
It is not allowed to use ' in field names, but when i copy the code in here it changes special danish characters to ' - not when i write the letters, but when i save the comment.

	Richard Rost 9 months ago
Ahhh... that makes sense. Might want to use screen shots then.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.