UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Openrecordset With Multiple Tables, Access 2016    
 
   
Adsens
post Nov 29 2017, 06:08 PM
Post#1



Posts: 6
Joined: 29-November 17



Hello all. I am getting an "Object variable or With block variable not set (Error 91)" error with the following code:

CODE
Dim db As Database
Dim rs As Recordset
'____________________________________________________________________

Private Sub Form_Load()

Dim mySQL As String

mySQL = "SELECT Tuteurs.ID_Tuteur, Tarifs_17_18.*, Paiements_17_18.* " & _
"FROM (Tuteurs INNER JOIN Tarifs_17_18 ON Tuteurs.ID_Tuteur = Tarifs_17_18.TuteurID_Trf) " & _
"INNER JOIN Paiements_17_18 ON Tuteurs.ID_Tuteur = Paiements_17_18.TuteurID_Pmt " & _
"WHERE ID_Tuteur =" & [Forms]![Eleves]![TuteurID_Elv]

Set db = CurrentDb
Set rs = db.OpenRecordset(mySQL, dbOpenDynaset, dbSeeChanges)

rs.MoveFirst

End Sub
'____________________________________________________________________

Private Sub btn_Enregistrer_Click()

Dim totIns As Integer
totIns = DSum("Montant", "Paiements_17_18", "[Mois_Regle]='Inscription'")

If totIns = rs!Tarif_Inscription Then
MsgBox "Yes" & totIns & " = " & rs!Tarif_Inscription
Else
MsgBox "No" & totIns & " # " & rs!Tarif_Inscription
End If
End Sub


totIns Is working very well. but
rs!Tarif_Inscription is the missing object variable.

[Tarif_Inscription] is a field in the [Tarifs_17_18] Table.
Any Help Please ??
Go to the top of the page
 
moke123
post Nov 29 2017, 06:25 PM
Post#2



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



could be your recordset is not in scope and your variables are declared outside your procedure.
not quite sure what you're trying to do opening a rs onload.

see if this does what you want
CODE
Private Sub WhateverName()

    Dim mySQL As String
    Dim db As Database
    Dim rs As Recordset
    Dim totIns As Integer

    mySQL = "SELECT Tuteurs.ID_Tuteur, Tarifs_17_18.*, Paiements_17_18.* " & _
            "FROM (Tuteurs INNER JOIN Tarifs_17_18 ON Tuteurs.ID_Tuteur = Tarifs_17_18.TuteurID_Trf) " & _
            "INNER JOIN Paiements_17_18 ON Tuteurs.ID_Tuteur = Paiements_17_18.TuteurID_Pmt " & _
            "WHERE ID_Tuteur =" & [Forms]![Eleves]![TuteurID_Elv]

    Set db = CurrentDb
    Set rs = db.OpenRecordset(mySQL, dbOpenDynaset, dbSeeChanges)

    rs.MoveFirst

    totIns = DSum("Montant", "Paiements_17_18", "[Mois_Regle]='Inscription'")


    If totIns = rs!Tarif_Inscription Then
        MsgBox "Yes" & totIns & " = " & rs!Tarif_Inscription
    Else
        MsgBox "No" & totIns & " # " & rs!Tarif_Inscription
    End If

    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub
'____________________________________________________________________

Private Sub btn_Enregistrer_Click()

    
  WhateverName


End Sub


is this a one record recordset?
Go to the top of the page
 
Adsens
post Nov 29 2017, 06:48 PM
Post#3



Posts: 6
Joined: 29-November 17



@moke123

You are a genius Thank you very much,
It works like a charm.
thumbup.gif

I'm really Happy

For your Question, No, it's a multiple records RECORDSET
Go to the top of the page
 
moke123
post Nov 29 2017, 07:55 PM
Post#4



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



QUOTE
For your Question, No, it's a multiple records RECORDSET

Are you sure your getting the result you want? You are only comparing one record in the recordset. Are you meaning to loop through the recordset?
Records in a recordset , i believe, are not necessarily returned in any particular order unless you explicitly state the order.
Go to the top of the page
 
Adsens
post Nov 30 2017, 09:27 PM
Post#5



Posts: 6
Joined: 29-November 17



Hi @Moke123
Sorry for my late response, I was traveling.
Yes I am satisfied with my comparison and I obtained the desired results.
I really appreciate your help.
Go to the top of the page
 
moke123
post Nov 30 2017, 09:29 PM
Post#6



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



yw.gif
Go to the top of the page
 
MadPiet
post Nov 30 2017, 09:48 PM
Post#7



Posts: 2,264
Joined: 27-February 09



What's with the dynamic recordset and dbSeeChanges? Looks like you could open a static read-only recordset and get the same results with a fraction of the overhead.
Go to the top of the page
 
Adsens
post Dec 1 2017, 09:11 AM
Post#8



Posts: 6
Joined: 29-November 17



@MadPiet
The code I showed is not complete, I have other lines of code to add other records to the recordset.
that's why I put the option dbOpenDynaset and dbSeeChanges
Go to the top of the page
 
MadPiet
post Dec 1 2017, 10:14 AM
Post#9



Posts: 2,264
Joined: 27-February 09



Oh, makes sense now!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 07:14 PM