Results 1 to 14 of 14
  1. #1
    edm608 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    7

    Not working in Access 2016

    Greeting. No data is populated in the list box after updating to Access 2016. Any idea why?



    Sub Form_Current()
    Dim rs As ADODB.Recordset
    Dim strSQL As String


    If Err <> 0 Then
    GoTo Form_Current_Exit
    Else
    On Error GoTo Form_Current_Err

    strSQL = "SELECT dbo.SmallGroup.SmallGroupID, dbo.SmallGroup.TrainingDate, dbo.ModuleType.ModuleType, dbo.SmallGroup.Room, dbo.Applicant.LastName + ', ' + dbo.Applicant.FirstName AS [Clinical Facilitator], Applicant_1.LastName + ', ' + Applicant_1.FirstName AS [Psychosocial Facilitator]" & _
    " FROM dbo.Applicant AS Applicant_1 RIGHT OUTER JOIN dbo.SmallGroup_Training INNER JOIN dbo.ApplicantTraining ON dbo.SmallGroup_Training.ApplicantTrainingID = dbo.ApplicantTraining.ApplicantTrainingID RIGHT OUTER JOIN dbo.SmallGroup ON dbo.SmallGroup_Training.SmallGroupID = dbo.SmallGroup.SmallGroupID LEFT OUTER JOIN dbo.Applicant ON dbo.SmallGroup.ClinicalFacilitatorID = dbo.Applicant.ApplicantID ON Applicant_1.ApplicantID = dbo.SmallGroup.PsychosocialFacilitatorID LEFT OUTER JOIN dbo.ModuleType ON dbo.SmallGroup.ModuleTypeID = dbo.ModuleType.ModuleTypeID" & _
    " WHERE ApplicantTraining.ApplicantTrainingID ='" + ApplicantTrainingID + "' AND ApplicantTraining.ApplicantID ='" + ApplicantID + "'"

    Set rs = New ADODB.Recordset
    rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
    Set Me.Parent![lstSmallGroup].Recordset = rs
    Date.SelStart = 0
    End If


    Form_Current_Exit:
    Exit Sub


    Form_Current_Err:
    'MsgBox Error$
    Resume Form_Current_Exit


    End Sub

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Comment out the error handling and see if you get an error raised?
    Check if you have any missing references in the debug window.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    edm608 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    7
    Click image for larger version. 

Name:	Error1.png 
Views:	16 
Size:	36.8 KB 
ID:	33863

    I am getting this error and it's referring to sql statement.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    okay, after you create the SQL string add a Debug.Print strSQL and copy and paste it to the your note pad.

    See if it makes sense and as you are running this against a linked SQL server tables make sure all the names are correct.

    If you think they are all correct then do a simple "SELECT * FROM dbo.Applicant " to see if it is connecting correctly.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It may not solve your error but you should normally use '&' instead of '+' to concatenate text fields in your SQL statement.

    Also you need to replace RIGHT OUTER JOIN with RIGHT JOIN
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by ridders52 View Post
    It may not solve your error but you should normally use '&' instead of '+' to concatenate text fields in your SQL statement.
    I think the main reason being that "abc" & Null = "abc". "abc" + Null = Null.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    edm608 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    7
    I tried that. It's still doesn't work.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by edm608 View Post
    I tried that. It's still doesn't work.
    You tried what exactly - all the suggested changes?
    What does 'doesn't work' actually mean? No results? Error?
    Also the line below is correct if both TrainingID & ApplicationID are TEXT fields
    Code:
    " WHERE ApplicantTraining.ApplicantTrainingID ='" & ApplicantTrainingID & "' AND ApplicantTraining.ApplicantID ='" & ApplicantID & "'"
    If both are number fields, it should be:
    Code:
    " WHERE ApplicantTraining.ApplicantTrainingID =" & ApplicantTrainingID & " AND ApplicantTraining.ApplicantID =" & ApplicantID & ""
    EDIT: Did you also change LEFT OUTER JOIN to LEFT JOIN?

    If all of that doesn't fix it, please post revised SQL with an description of what happens
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    edm608 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    7
    Yes I tried all the suggested changes. Both ApplicantTrainingID and ApplicantID are integer in the table. When I click on the row of the sub form (see attach), The data should populate in the listbox. It worked in 2010. Click image for larger version. 

Name:	Form.png 
Views:	14 
Size:	33.7 KB 
ID:	33865

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You didn't answer my question - WHAT DOES HAPPEN?
    Do you still get an error on the SQL?
    If so which part does it highlight?

    Please post the modified SQL as requested
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    edm608 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    7
    it highlights the whole sql statement

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    it highlights the whole sql statement
    Really? Normally it highlights the first section with an error

    Third time of asking:
    a) what error is shown
    b) please post the revised SQL

    EDIT:
    I've just realised this is a duplicate of another thread: https://www.accessforums.net/showthread.php?t=71896
    Why have you posted it twice?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    edm608 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    7
    I changed the code in the beginning and I am still getting the same error "Run time error 2424". It's look like Microsoft change something in 2016 version. Please help!

    strCriteria = "Applicant.[ApplicantID] ='" & Forms![Applicant]![ApplicantID] & "'"

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by edm608 View Post
    I changed the code in the beginning and I am still getting the same error "Run time error 2424". It's look like Microsoft change something in 2016 version. Please help!

    strCriteria = "Applicant.[ApplicantID] ='" & Forms![Applicant]![ApplicantID] & "'"
    I've been trying to help since post #1 but it seems to be impossible to get you to answer fully (or have you done so in the other unnecessary duplicate thread?)
    You have now posted filter criteria but there is no apparent link between that & the SQL from post 1 which had several errors that you needed to correct.

    Unless you help us to help you, nobody can offer you a solution
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Kill statement not working in Access 2016
    By Sephaerius in forum Modules
    Replies: 18
    Last Post: 05-04-2018, 02:40 AM
  2. Replies: 0
    Last Post: 03-02-2018, 11:08 AM
  3. Automating Outlook 2016 from Access 2016
    By jcc285 in forum Programming
    Replies: 10
    Last Post: 09-30-2017, 01:53 PM
  4. Replies: 5
    Last Post: 09-20-2017, 10:07 AM
  5. Replies: 1
    Last Post: 02-23-2016, 11:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums