Results 1 to 13 of 13
  1. #1
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93

    Common ERROR: 438"Object Doesn't Support This Property or Method"

    Accessers,



    While trying to execute a code, I am getting an error:

    Run-time error '438': "Object Doesn't Support This Property or Method"


    I have looked around to find similar issue to the error I am getting on the code below:
    Code:
    Public Function AssignNextAM()    Dim i As Long
        
          With Screen.ActiveForm.[AMID]
          
                Dim stNextUser As String
                Dim MyDB As Database, RS As Recordset
                Set MyDB = DBEngine.Workspaces(0).Databases(0)
                
                stNextUser = ""
            
                Set RS = MyDB.OpenRecordset("select * from qryAIA_WorkAgn")
                lngRSCount = RS.RecordCount
                If lngRSCount <> 0 Then
                    RS.MoveFirst
                    
                    stNextUser = Trim(RS.Fields("Name").Value)
                    
                    RS.Edit
                    RS.Fields("LastAgn").Value = Now()
                    RS.Update
                    RS.Close
                    MyDB.Close
                    MsgBox "This section works."
                   
                Else
                    RS.Close
                    stMsg = MsgBox("An error has occured: Please check if at least one person is allowed to be assigned.", vbCritical)
                    MyDB.Close
                End If
        
          DoCmd.GoToRecord , , acNewRec
        
            For i = 0 To .ListCount  <---Gets highlighted in Debugger
                If .ItemData(i) = stNextUser Then
                    .Value = stNextUser
                    MsgBox "Person: " & stNextUser & " has been assigned to this activity and the log is updated.", vbApplicationModal
                End If
            Next i
        End With
    End Function
    How may we tweak this to make it function properly?

    Thanks!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not sure what you are counting.
    You put a record count in
    lngRSCount

    Is that what you want to use in the For Loop?

    The message, I believe, is saying that

    Screen.ActiveForm.[AMID] does not have a ListCount property.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    [AMID] is a listbox control?

    The syntax works for me. Can't replicate the issue. If you want to provide db for analysis, follow instructions at bottom of my post.

    Why set the With so soon in the code? It isn't needed until the For Next loop.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93
    June7,

    [AMID] is actually a combo box control.

    I have moved the With to the For Next loop section. Thanks for the tip.

    orange,
    What I am wanting the code to do is to assign the task to the next person with the earliest date of last task. So, the count should be able to find the record with the earliest date.

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I don't see anything wrong either, except that this might be one of those (totally unpredictable) times when Access expects a "!" instead of a ".", so that you need to use this:

    With Screen.ActiveForm![AMID]

    John

  6. #6
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93
    Still no success. Hmmm!

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Want to provide db?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93
    Unforunately, June7, the database is highly confidential. However, I am able to run the code without any issue now. BUT... it will NOT assign a person to the task nor will it update the recent date (LastAgn), instead, it will leave it blank and will not update the date field. It's like the code is there but will not do anything.

    Can you, June7, or someone explain what does each of the lines in the purple section of the code perform exactly?

    Code:
    Public Function AssignNextAM()    Dim i As Long
        Dim stNextUser As String
        Dim MyDB As Database, RS As Recordset
        Set MyDB = DBEngine.Workspaces(0).Databases(0)
        Set RS = MyDB.OpenRecordset("select * from qryAIA_WorkAgn")
        
        lngRSCount = RS.RecordCount
        stNextUser = ""
        
        If lngRSCount <> 0 Then
            RS.MoveFirst
            
            stNextUser = Trim(RS.Fields("Name"))
            
            RS.Edit
            RS.Fields("LastAgn").Value = Now()
            RS.Update
            RS.Close
            MyDB.Close
        Else
            RS.Close
            stMsg = MsgBox("An error has occured: Please check if at least one person is allowed to be assigned.", vbCritical)
            MyDB.Close
        End If
    
    
        DoCmd.GoToRecord , , acNewRec
        
        With Screen.ActiveForm.[AMID]
            For i = 0 To .ListCount
                If .ItemData(i) = stNextUser Then
                    .Value = stNextUser
                    MsgBox "Person:" & stNextUser & " has been assigned to this activity and the log is updated.", vbApplicationModal
                End If
            Next i
        End With
    End Function

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That appears to be looping through listbox items and if item matches the stNextUser content, it sets the listbox value to that content. I have no idea why you need to confirm the content is an item in the list nor why you need to set listbox value. Maybe you should explain exactly what you are trying to accomplish.

    Have you step debugged?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93
    June7,

    Yes, I have tried to step debug and it errors at the same line (For i = 0 To .ListCount).

    What I am trying to accomplish is to find a person in the [Name] field with the earliest date in the [LastAgn] field and then assign the current task to that person and update the [LastAgn] field with the current date/time.

    Hope I am more clear now.

    Thanks...

  11. #11
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    What you could try is to write the code without the With, and make all the references explicit, e.g. Screen.ActiveForm.[AMID].listcount. If you still get the error, then it's something else that's wrong.

    But I don't see the purpose of the loop - as far as I can tell, it sets the list item to the same value it already has, sort of like saying If x=2 then x=2.

    You check to see if the list item = stNextUser, then if it is, you set it to ... stNextUser.

    Am I missing simething here?

    John

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I tend to agree with John not really following your code.

    What I am trying to accomplish is to find a person in the [Name] field with the earliest date in the [LastAgn] field and then assign the current task to that person and update the [LastAgn] field with the current date/time.

    I would think code along the following lines should find the "Name" with the earliest LastAgn date.
    Once you have the record, you can then do the Update as you want.

    SELECT NAME,..other fields FROM YourTable
    WHERE LastAgn = (Select Min(LastAgn) from YourTable)

    You may be able to adjust to something like
    --totally air code untested--- and there could be multiple records with the same Min(LastAgn)

    Code:
    Update YourTable
    SET LastAgn = Now()
    , code to assign Task to this person
    Where RecordID = ( SELECT RecordId  FROM YourTable
                               WHERE LastAgn = (Select Min(LastAgn) from YourTable))

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As I said, I can't replicate the issue. The ListCount property works for me.

    If you can't provide sanitized db for analysis, don't think can help.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-27-2012, 02:16 AM
  2. Replies: 6
    Last Post: 11-18-2011, 03:46 PM
  3. Replies: 7
    Last Post: 02-03-2011, 07:13 AM
  4. Replies: 5
    Last Post: 08-05-2009, 04:07 PM
  5. Replies: 2
    Last Post: 02-28-2009, 03:31 PM

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