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
> SQL Issue, Access 2010    
 
   
per007
post Sep 18 2019, 04:04 AM
Post#1



Posts: 7
Joined: 12-September 18



Hi all,

Could you please take a look at this? I have a fairly long SQL statement that will give me the right results once I put this in the properties window of this continuous form. I just can't get this to work in VBA (name? - errors everywhere). Any thoughts why this form doesn't display any records? Thanks,

Per


CODE
Private Sub Form_Load()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim fldValues As String
    
    On Error GoTo errhandler

    Set db = CurrentDb

strSQL = "SELECT tblDialoogInd_Status.DialoogIndID, tblDialoogInd_Status.StatusDatum, tblStatus.Omschrijving AS Status, tblDialoogInd_Status.Toelichting, tblIndicator.Indicator, tblIndicator.Onderwerp, tblIndicator.Thema, tblBedrijf.BedrijfsNaam, tblEngagement.Omschrijving AS Engagement, tblEngagement.AanvangDatum, tblDialoogIndicators.DialoogID, tblDialoog.DialoogAfgesloten, tblBedrijf.BedrijfId " _
        & "FROM ((((((qryDialoogIndicatorStatusDatumMax_StatusId " _
        & "LEFT JOIN tblDialoogInd_Status ON qryDialoogIndicatorStatusDatumMax_StatusId.DialoogInd_StatusID = tblDialoogInd_Status.DialoogInd_StatusID) " _
        & "LEFT JOIN tblDialoogIndicators ON tblDialoogInd_Status.DialoogIndID = tblDialoogIndicators.DialoogIndID) " _
        & "LEFT JOIN tblDialoog ON tblDialoogIndicators.DialoogID = tblDialoog.DialoogID) " _
        & "LEFT JOIN tblStatus ON tblDialoogInd_Status.StatusID = tblStatus.StatusID) " _
        & "LEFT JOIN tblIndicator ON tblDialoogIndicators.IndID = tblIndicator.IndID) " _
        & "LEFT JOIN tblBedrijf ON tblDialoog.Bedrijf = tblBedrijf.BedrijfId) " _
        & "LEFT JOIN tblEngagement ON tblDialoog.Engagement = tblEngagement.EngagementID " _
        & "WHERE (((tblDialoog.DialoogAfgesloten) = No) And ((tblBedrijf.BedrijfId) = 3)) " _
        & "ORDER BY tblDialoogIndicators.DialoogID;"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    Exit Sub
    
errhandler:
    MsgBox "Error" & Err.Number & ": " & Err.Description
    
End Sub
Go to the top of the page
 
June7
post Sep 18 2019, 04:20 AM
Post#2



Posts: 891
Joined: 25-January 16



Setting a recordset object variable does not set RecordSource of a form.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Phil_cattivocara...
post Sep 18 2019, 04:37 AM
Post#3



Posts: 364
Joined: 2-April 18



If this is the only code in Load event, the form does not display anything because you never "bind" the recordset to the form
CODE
...
   "ORDER BY tblDialoogIndicators.DialoogID;"
  
  Me.RecordSource = strSQL
  Me.RecordSource = Me.RecordSource

Do not ask me what last line means but... if you do not write it most times form does not function.
(Sorry June7, my edit time was too long and I wrote your same suggestion)
This post has been edited by Phil_cattivocarattere: Sep 18 2019, 05:13 AM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
per007
post Sep 18 2019, 04:55 AM
Post#4



Posts: 7
Joined: 12-September 18



Thanks both for clearing this up!
Go to the top of the page
 
BruceM
post Sep 18 2019, 07:37 AM
Post#5


UtterAccess VIP
Posts: 7,971
Joined: 24-May 10
From: Downeast Maine


Hmm. I have never had to do Me.RecordSource = Me.RecordSource, although I often use VBA to set the record source.
Go to the top of the page
 
Daniel_Stokley
post Sep 18 2019, 07:51 AM
Post#6



Posts: 328
Joined: 22-December 14
From: Grand Junction, CO, USA


CODE
Me.RecordSource = Me.RecordSource
seems strange to me.

I think I would use
CODE
Me.Requery
after setting a record source.


Go to the top of the page
 
Phil_cattivocara...
post Sep 19 2019, 02:48 AM
Post#7



Posts: 364
Joined: 2-April 18



QUOTE (BruceM)
Hmm. I have never had to do Me.RecordSource = Me.RecordSource, although I often use VBA to set the record source.
and
QUOTE (Daniel_Stokley)
CODE
Me.RecordSource = Me.RecordSource
Seems strange to me.
I agree with you, both.

Perhaps I was too tragic when I wrote
QUOTE
... if you do not write it most times form does not function.

Normal behaviour is:
QUOTE
Changing the record source of an open form or report causes an automatic requery of the underlying data
(from: Form.RecordSource property (Access))
When SQL is a "particularly complex" query, "recent" Access versions do not "understand" the new RecordSource even if you do an explicit Requery, and the only solution is the (technically) inexplicable
CODE
Me.RecordSource = Me.RecordSource
For recent Access versions I mean... A200x or A201x... I do not know the exact version when it started.

But here is an example: Subform Requery Does Not Seem To Requery The Recordsource, Access 2016 #6
and Tina_t's answer at #8
QUOTE
@Phil, you're right, hon, that solves it. and i agree, it is ridiculous, but i'm ready to go with whatever works. no, A97 definitely doesn't have this problem; when you say Requery, it requeries.
I wrote this not beacause it was useful for Tina_t only but because I read it (quite) often.There are legends about the situations when this happens but nobody can remember it or refer official (Microsoft) documentation.
Perhaps it was not per007's case but I saw a "strong SQL" so... I added it for caution.
I would be very grateful to anybody who can refer anything sure about this.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd October 2019 - 07:44 AM