Undefined Error, Combo Boxes Won't Select, Access 2007

[Elladan]

Greetings:

I think I win the cake for weirdest error and I hope someone out there has a solution or at least knows what's causing it. I've been programming in Access for about 10 years now and I've developed a front-end back-end system that in Access 2003 that works just great. It uses Access 2003 as the front-end and our SQL server as the back-end.

We've been using the system for about 5 years now and our company is insisting that we upgrade to Window 7, including Access 2007, so we've been going through extensive testing to see if we can find any errors. Found a few, but that's not that point.

So, I have this one form called Meetings. It has a lstbox on it called MeetingAttendees (attendees attend meetings are are held in separate tables).
All of my code is completely unbound and uses ADODB recordsets to update all the controls on the forms.

So, one of the things you can do on the lstbox is delete all the meeting attendees at once.
You do this with a Right-click, causing the MouseDown event to fire, and then answering the msgbox in the affirmative.
Assuming you answered in the affirmative, it then deletes all the attendees and updates the form (with another sub routine called SetRecordSource).

I've traced the code and the oddity (I'm hesitant to even call it an error since it's not marked as such) is that a black bar appears in the upper left and then I'm not able to use any combo boxes correctly - I can click on them and mouse over them, but I can't actually select anything in the combo box. The oddity appears to occur on the "Exit Sub" line of the MouseDown event.
If I exit the system and restart, everything is fine.

Anyone have any clue what's causing this? I'm attaching a screen shot to assist with any diagnosis...

Thanks!!!!

Matt

 

[boblarson]

Post the mousedown event code as well as the SetRecordSource code. Just asking as it may yield some possible answers.

 

[Elladan]

Here is the MouseDown Code:

Private Sub lstMeetingAttendees_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
On Error GoTo ErrMessage

Dim answer As VbMsgBoxResult
'Dim rsDelete As DAO.Recordset
Dim rsDelete As ADODB.Recordset
Dim i As Integer
If Button = 2 Then
    If Me.lstMeetingAttendees.ListCount > 1 Then 'only do this if there are records there.
        answer = MsgBox("Do you wish to delete ALL Attendees from this meeting? (cannot UNDO)", vbOKCancel)
        If answer = vbOK Then
            'delete value
            
            'Check to see if able to delete values based on Presence or Absence of DateProcessedforCheck in dbo_tblMeetingAttendees
            'for these records.  Added 12/15/08.  MB
            If fCheckDelete = False Then
                MsgBox "You cannot delete these records at this time because Cheques have been completed for some/all individuals.", vbOKOnly, "Cannot Delete!"
                Exit Sub
            End If
            'end check on ability to delete values
            
            'need to add AUDIT TRAIL CAPABILITY HERE
            Set rsDelete = New ADODB.Recordset
            rsDelete.Open Me.lstMeetingAttendees.RowSource, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
            rsDelete.MoveLast: rsDelete.MoveFirst
            DoCmd.SetWarnings (False)
            For i = 1 To rsDelete.RecordCount
                fAuditTrail "dbo_tblMeetingAttendees", "dbo_tblMeetingAttendeesAudit", rsDelete!AttendeeID, "Deleted"
                rsDelete.MoveNext
            Next i
            Set rsDelete = Nothing 'added this 12/15/08 to clear rsDelete. MB 12/15/08
            DoCmd.RunSQL "DELETE * FROM dbo_tblMeetingAttendees WHERE MeetingFK=" & Me.MeetingID & ";"
            DoCmd.SetWarnings (True)
            MsgBox "Attendees Deleted!"
            SetRecordSource
        ElseIf answer = vbCancel Then
            Exit Sub
        End If
    End If
Else
    generic_lst_mousedown x, y
End If
Exit Sub

ErrMessage:
MsgBox "Error#" & Err.Number & ":" & Err.Description, vbOKOnly, "Error"

End Sub

And the SetRecordSource Code:

Private Sub SetRecordSource()
On Error GoTo ErrMessage
If strMeetingArgs = "" Then
    'no openargs - new record
    Set rsMeetings = New ADODB.Recordset
    rsMeetings.Open "SELECT * from dbo_tblMeetings;", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    strRecordType = "NewRecord"
    Me.lstMeetingAttendees.Enabled = False: Me.lstMeetingAttendees.RowSource = ""
    Me.cmdAddAttendees.Enabled = False
    Me.cmdMeetingReport.Enabled = False
    'added consultant stuff 9/10/10.  MB 9/10/10
    Me.cboConsultants.Enabled = False
    Me.cmdAddConsultant.Enabled = False
    With rsMeetings 'don't really need this in "With" statement, but it has nice symmetry with "With" statement below.
        Me.MeetingID = Null
        Me.MeetingDate = Null
        Me.MeetingLength = Null
        Me.MeetingBoard = Null
        Me.MeetingType = Null
    End With
    Set rsMeetings = Nothing
Else
    'existing record
    Set rsMeetings = New ADODB.Recordset
    rsMeetings.Open "SELECT * from dbo_tblMeetings WHERE MeetingID=" & strMeetingArgs & ";", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    With rsMeetings
        Me.MeetingID = !MeetingID
        Me.MeetingDate = !MeetingDate
        Me.MeetingLength = !MeetingLength
        Me.MeetingBoard = !MeetingBoard
        Me.MeetingType = !MeetingType
    End With
    strRecordType = ""
    Me.cmdAddAttendees.Enabled = True
    Me.lstMeetingAttendees.Enabled = True
    Me.cmdMeetingReport.Enabled = True
    'added consultant stuff 9/10/10.  MB 9/10/10
    Me.cboConsultants.Enabled = True
    Me.cmdAddConsultant.Enabled = True
    Me.lstMeetingAttendees.RowSource = "SELECT dbo_tblMeetingAttendees.AttendeeID, dbo_tblMeetingAttendees.MeetingFK, " & _
        "dbo_tblMembers.[MemberFName] & ' ' & dbo_tblMembers.[MemberLName] AS Name, " & _
        "dbo_tblMeetingAttendees.WrittenReviewsAssigned AS [# Reviews Assigned], " & _
        "dbo_tblMeetingAttendees.WrittenReviewsCompleted AS [# Reviews Completed], " & _
        "iif(([dbo_tblMeetingAttendees.AttendedMeeting])=TRUE, 'Yes', 'No') AS [Attended?], " & _
        "iif(([dbo_tblMeetingAttendees.AttendedPartialMeeting])=TRUE, 'Yes', 'No') AS [Partial?], " & _
        "dbo_tblMeetingAttendees.TimeAbsent AS [Time Absent], dbo_tblMeetingAttendees.ReasonAbsent AS [Reason Absent], " & _
        "dbo_tblMeetingAttendees.DateProcessedforCheck AS [DateProcessCheque] " & _
        "FROM dbo_tblMeetingAttendees INNER JOIN dbo_tblMembers ON dbo_tblMeetingAttendees.MemberID = dbo_tblMembers.MemberID " & _
        "WHERE [MeetingFK]=" & rsMeetings!MeetingID & " ORDER BY [AttendeeID]; "
    Set rsMeetings = Nothing
    'added dbo_tblMeetingAttendees.DateProcessedforCheck to above so that can be used in fCheckDelete below.  MB 12/15/08
End If
Exit Sub

ErrMessage:
MsgBox "Error#" & Err.Number & ":" & Err.Description, vbOKOnly, "Error"
End Sub

Please note that when the SetRecordSource code runs at this point that the record is known and so that part of the IF statement fires.

I really appreciate the help!

Matt

 

[boblarson]

A couple of questions and some of this may not have any bearing on the actual problem but best to rule them out.

1. You have:
rsMeetings.Open "SELECT * from dbo_tblMeetings WHERE MeetingID=" & strMeetingArgs & ";", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

So, is MeetingID numeric and, if so, why are you using what appears to be a string variable instead of a numeric one? If not, then it needs to have quotes. And just an observation for you for the future - you don't need to add the semi-colon at the end.

2. In this code:

rsDelete.Open Me.lstMeetingAttendees.RowSource, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
            rsDelete.MoveLast: rsDelete.MoveFirst
            DoCmd.SetWarnings (False)
            For i = 1 To rsDelete.RecordCount
                fAuditTrail "dbo_tblMeetingAttendees", "dbo_tblMeetingAttendeesAudit", rsDelete!AttendeeID, "Deleted"
                rsDelete.MoveNext
            Next i

Is there a reason why you are using record count instead of just using

rsDelete.Open Me.lstMeetingAttendees.RowSource, CurrentProject.Connection, adOpenKeyset, adLockReadOnly


            [B][COLOR="Red"]Do Until rsDelete.EOF[/COLOR][/B]
                fAuditTrail "dbo_tblMeetingAttendees", "dbo_tblMeetingAttendeesAudit", rsDelete!AttendeeID, "Deleted"
                rsDelete.MoveNext
            [B][COLOR="red"]Loop[/COLOR][/B]

There really isn't a need to do a move last and move first then. You can, if you might end up with 0 records use an If rsDelete.RecordCount <> 0 Then which if it isn't zero then there is at least one record.


So, next, you might see if closing the recordsets first before setting them to nothing will help. I'm not sure that's necessary but something there could be hanging things up.

 

[Elladan]

Bob,

I'll try to address all the issues you raised - some are really good points, but keep in mind that this code functions perfectly under Access 2003 - it's only under Access 2007 that I'm seeing the issue, so I'm thinking it a compatibility issue of some sort.

1) strMeetingArgs can be various values depending on where it's being used and to what it was set in a previous sub. This value is defined Globally since it is sent from the frmSearchMeetings. You're right about it being an integer here, but the code does function correctly and sees it as a number, pulling up the right records normally. I'm not included to fix what ain't broken...

2) No real reason to use recordcount instead of a do...until loop, but sometimes I need recordcount and just implemented this cross platform to be consistent. I have found that when I use recordcount that without the movelast:movefirst, I sometimes get no records, so doing the move ensures that the entire recordset is seen before the recordcount is calculated. Again, I've used the project wide and included it for consistency. As far as closing the recordsets, I'm pretty sure that setting them to nothing eliminates them from memory, so no need to actually close them as they don't exist anymore.

Again, I'm looking for a compatibility issue between Access 2003 and Access 2007.

Thanks for the thoughts....

Matt

 

[boblarson]

As for the closing of the recordsets, it is something to try to see if it helps. Yes, it likely won't help but what do you have to lose to try?

 

[Elladan]

As for the closing of the recordsets, it is something to try to see if it helps. Yes, it likely won't help but what do you have to lose to try?

Hey Bob,

Thanks for the reminder to keep an open mind - sometimes that's needed as we go so sure that we're right, we forget we can be wrong. Unfortunately, in this case, I wasn't wrong... I went through and closed every single recordset in the form, before setting it to nothing, and still got the same oddity. Still looking for ideas.....

 

[boblarson]

OK, next you can try doing a DECOMPILE (I've found that a lot of the time when you have unexplained stuff that normal troubleshooting can't explain, sometimes this helps. Make sure to keep a backup copy first BEFORE doing it.

Instructions are here:
http://www.granite.ab.ca/access/decompile.htm
And then one more thing to try is to import everything into a new, blank database file. Sometimes that helps especially when moving from a lower version to a higher version.

 

[Elladan]

Ok, Bob.... I spent the whole day doing this.... I imported everything into an Access 2003 type database in Access 2007 and set the references over again, recompiled (also tried the decompile - that didn't work), converted some of my DAO recordsets over to ADODB (apparently I missed more than a few in the reports section) and tried it again. SAME RESULT.:banghead:.

Frustration NOT aimed at you...

Any other good suggestions? I'd share the database, but since it's hooked up to an SQL server, it wouldn't do you much good...

Matt

OK, next you can try doing a DECOMPILE (I've found that a lot of the time when you have unexplained stuff that normal troubleshooting can't explain, sometimes this helps. Make sure to keep a backup copy first BEFORE doing it.

Instructions are here:
http://www.granite.ab.ca/access/decompile.htm
And then one more thing to try is to import everything into a new, blank database file. Sometimes that helps especially when moving from a lower version to a higher version.

 

[Elladan]

BTW, I also tried converting the database from 2003 (the one I told you I corrected) to native 2007 and that didn't clear the oddity either.

 

[boblarson]

Any other good suggestions? I'd share the database, but since it's hooked up to an SQL server, it wouldn't do you much good...

Some more questions -

1. What version of SQL Server are you using?

2. What Windows Operating System are you using?

3. What ODBC driver are you using? Did you set up a DSN or are you connecting via a different method?

Yeah, I sure wish I could play around with it. Perhaps if you have access to the SQL Server you could script out the database (sans data) and then send me that script and a copy of the frontend. Then I can recreate the backend and link to it. And then we could see if that yields anything.

 

[Elladan]

Some more questions -

1. What version of SQL Server are you using?

2. What Windows Operating System are you using?

3. What ODBC driver are you using? Did you set up a DSN or are you connecting via a different method?

Yeah, I sure wish I could play around with it. Perhaps if you have access to the SQL Server you could script out the database (sans data) and then send me that script and a copy of the frontend. Then I can recreate the backend and link to it. And then we could see if that yields anything.

SQL Server 2005.

Windows 7

ODBC driver was setup as a file DSN using an SQL server driver.

As far as sending you the data system, it would be pretty complicated and I'd have to explain how to use it. There's much more to it than just this one function...it handles member information, meeting information, meeting attendees (from the list of members), status of members, addresses, honoraria paid for attending a meeting, issuing checks for honoraria for attending a meeting, reports (including stats reports on money spent on an individual or board basis), etc. It needs most of that information (such as members) before you can even start playing with the issue I'm having...

At this point, the oddity is more of an annoyance than a real problem. It only occurs if you perform a really rare function (deleting all the attendees from a meeting does not occur unless you forgot to update someone's member record) and even if it does, the data processes correctly, and you just need to restart the front-end to make the oddity go away.

I should also point out that this doesn't occur with Access 2007 in a Windows XP environment (we have some users with that), but only with Access 2007 in a Windows 7 environment. We are being forced to migrate to the Windows 7, so I was having the system validated in it's new native environment when we came upon this issue.

Thanks,

Matt

 

[boblarson]

Okay, so where is the frontend file actually located when you are running it? Is it a copy on your machine? If so, under what directory?

 

[Elladan]

Okay, so where is the frontend file actually located when you are running it? Is it a copy on your machine? If so, under what directory?

Yes, the Front-end is on each individual machine. It's located on the Desktop. User's open it and login to the front-end, connecting via the SQL server. All the front-ends use the same SQL username/password built into the system (they don't know this), but each user has their own username/password for the Front-End. The users cannot see the code, the tables nor use special keys to get to any of that. They also cannot use the "X" to close the app - they must use my "close" button.

 

[DrallocD]

I would try commenting out pieces of the code until the strange behavior goes away so that you can home in on the issue. I personally would start by commenting out:

Me.MeetingID = Null
Me.MeetingDate = Null
Me.MeetingLength = Null
Me.MeetingBoard = Null
Me.MeetingType = Null

There is a slight difference with how 2007 handles Me. and Me!

In SetRecordSource I would also use the same recordsource in both sides of the IF/ELSE/ENDIF and set the WHERE clause to 1=0 rather than set the whole thing to "".

In SetRecordSource I would get rid of the rsMeetings from the first part of the IF, you open all records only to close it again without using it.

 

[mdlueck]

Me.MeetingID = Null
Me.MeetingDate = Null
Me.MeetingLength = Null
Me.MeetingBoard = Null
Me.MeetingType = Null

I was taught to use vbNullString in VBA where I need to set something to NULL.

Since you mention trying to comment that area out, perhaps the type of null being used would make a beneficial difference.

 

[boblarson]

I was taught to use vbNullString in VBA where I need to set something to NULL.

Well, a NULL and vbNullString are two totally different things. A NULL is really a NULL whereas a vbNullString is actually an empty string.

 

[Elladan]

Hi Draco,

Thanks for the ideas. I did try to comment out what you suggested. The SetRecordSource code now looks like this:

Private Sub SetRecordSource()
On Error GoTo ErrMessage
If strMeetingArgs = "" Then
    'no openargs - new record
'MB 10/3/12
'    Set rsMeetings = New ADODB.Recordset
'    rsMeetings.Open "SELECT * from dbo_tblMeetings;", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    strRecordType = "NewRecord"
    Me.lstMeetingAttendees.Enabled = False: Me.lstMeetingAttendees.RowSource = ""
    Me.cmdAddAttendees.Enabled = False
    Me.cmdMeetingReport.Enabled = False
    'added consultant stuff 9/10/10.  MB 9/10/10
    Me.cboConsultants.Enabled = False
    Me.cmdAddConsultant.Enabled = False
'MB 10/3/12
'    With rsMeetings 'don't really need this in "With" statement, but it has nice symmetry with "With" statement below.
'        Me.MeetingID = Null
'        Me.MeetingDate = Null
'        Me.MeetingLength = Null
'        Me.MeetingBoard = Null
'        Me.MeetingType = Null
'    End With
'    rsMeetings.Close '10/3/12
'    Set rsMeetings = Nothing
Else
    'existing record
    Set rsMeetings = New ADODB.Recordset
    rsMeetings.Open "SELECT * from dbo_tblMeetings WHERE MeetingID=" & strMeetingArgs & ";", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    With rsMeetings
        Me.MeetingID = !MeetingID
        Me.MeetingDate = !MeetingDate
        Me.MeetingLength = !MeetingLength
        Me.MeetingBoard = !MeetingBoard
        Me.MeetingType = !MeetingType
    End With
    strRecordType = ""
    Me.cmdAddAttendees.Enabled = True
    Me.lstMeetingAttendees.Enabled = True
    Me.cmdMeetingReport.Enabled = True
    'added consultant stuff 9/10/10.  MB 9/10/10
    Me.cboConsultants.Enabled = True
    Me.cmdAddConsultant.Enabled = True
    Me.lstMeetingAttendees.RowSource = "SELECT dbo_tblMeetingAttendees.AttendeeID, dbo_tblMeetingAttendees.MeetingFK, " & _
        "dbo_tblMembers.[MemberFName] & ' ' & dbo_tblMembers.[MemberLName] AS Name, " & _
        "dbo_tblMeetingAttendees.WrittenReviewsAssigned AS [# Reviews Assigned], " & _
        "dbo_tblMeetingAttendees.WrittenReviewsCompleted AS [# Reviews Completed], " & _
        "iif(([dbo_tblMeetingAttendees.AttendedMeeting])=TRUE, 'Yes', 'No') AS [Attended?], " & _
        "iif(([dbo_tblMeetingAttendees.AttendedPartialMeeting])=TRUE, 'Yes', 'No') AS [Partial?], " & _
        "dbo_tblMeetingAttendees.TimeAbsent AS [Time Absent], dbo_tblMeetingAttendees.ReasonAbsent AS [Reason Absent], " & _
        "dbo_tblMeetingAttendees.DateProcessedforCheck AS [DateProcessCheque] " & _
        "FROM dbo_tblMeetingAttendees INNER JOIN dbo_tblMembers ON dbo_tblMeetingAttendees.MemberID = dbo_tblMembers.MemberID " & _
        "WHERE [MeetingFK]=" & rsMeetings!MeetingID & " ORDER BY [AttendeeID]; "
    'added dbo_tblMeetingAttendees.DateProcessedforCheck to above so that can be used in fCheckDelete below.  MB 12/15/08
    rsMeetings.Close '10/3/12
    Set rsMeetings = Nothing
End If
Exit Sub

ErrMessage:
MsgBox "Error#" & Err.Number & ":" & Err.Description, vbOKOnly, "Error"
End Sub

I didn't understand this part though...

In SetRecordSource I would also use the same recordsource in both sides of the IF/ELSE/ENDIF and set the WHERE clause to 1=0 rather than set the whole thing to "".

Different record sets are required for whether this is a new record or an existing record (adding or updating?). In any case, commenting out the first rsMeetings, although a valid point, didn't solve the problem.

 

[Elladan]

Well, a NULL and vbNullString are two totally different things. A NULL is really a NULL whereas a vbNullString is actually an empty string.

For the record, I tried UNcommenting the code I commented previously and using vbNullString instead of NULL. No difference. I'm beginning to think this is a Windows 7 issue. Further, I've discovered it isn't just the combobox that doesn't work, it's also the lstbox on the previous form. In fact, no combobox or lstbox works in the entire system after I run this code, unless I exit the system and come back in. Someone else, in another group a long time ago, said that they had issues when the AllowEdits property of the form was set to No, but mine is set to Yes....so that's not it...

 

[boblarson]

I'm just wondering if a process is getting locked due to the mouse down event. I know this isn't optimal but does the code work if you put it into a click event of a button (just for testing purposes)?