Run-time error '3077'

[Anne]

I have a form with two option groups, a list box and a bunch of text boxes.
I have two AfterUpdate event procedures - one populates the listbox based on
the option groups - opt_Date_AfterUpdate - and one finds the record you pick
on the listbox -lst_Deviation_AfterUpdate. The option groups are meant to
filter for status of a deviation and for various date information.

All is well with the listbox procedure, until I run the option group
procedure, in which case, my listbox value (in the lst_Deviation_AfterUpdate
procedure) becomes one of the option group *description* values (not their
numerical value), and I get run-time error '3077'.

I appreciate any help you can provide. I'm really learning as I go, and my
knowledge is spotty at best. You may recognize code I swiped directly from
posts in this forum! Thanks in advance.

Private Sub lst_Deviation_AfterUpdate()

Dim rs As Object

Me.txt_DeviationCount.Value = Me.lst_Deviation.ListCount

Set rs = Me.RecordsetClone
With rs
.FindFirst "DevID = " & Me.lst_Deviation
If .NoMatch Then
MsgBox "Something is wrong! This should have worked"
Else
Me.Bookmark = rs.Bookmark
End If
End With
rs.Close
Set rs = Nothing

End Sub

----------------------------------------------------
Private Sub opt_Date_AfterUpdate()

Dim mySQL As String
Dim myStatus As String
Dim myDate As String
Dim myCriteria As String

mySQL = "SELECT DISTINCTROW tbl_Deviations.Status,
tbl_Deviations.DevNum, tbl_Deviations.DevRecDate, " & _
"tbl_Deviations.TrackAssignDate, tbl_Deviations.DevDate,
tbl_Deviations.CompletedDate, tbl_Deviations.FirstReviewDate, " & _
"tbl_Deviations.FinalReviewDate, tbl_Deviations.SOPNum,
tbl_SOPs.SOPTitle, tbl_Deviations.Section, tbl_Deviations.Version, " & _
"tbl_Deviations.Intended, tbl_Deviations.Owner,
tbl_Deviations.Global, tbl_Deviations.Study, tbl_Deviations.Description, " & _
"tbl_Deviations.RootCause, tbl_Deviations.CorrectiveAction,
tbl_Deviations.ActionDeadline, tbl_Deviations.SCReviewed, " & _
"tbl_Deviations.SCReviewDate, tbl_Deviations.ReviseSOP,
tbl_Deviations.BRC " & _
"FROM tbl_SOPs RIGHT JOIN tbl_Deviations ON tbl_SOPs.SOP =
tbl_Deviations.SOPNum "

Select Case opt_Status
Case 1 'All Deviations
myStatus = ""
Case 2 'Tracking Number Assigned
myStatus = "((tbl_Deviations.Status)=""Tracking Number
Assigned"")"
Case 3 'Authorized and Closed
myStatus = "((tbl_Deviations.Status)=""Authorized And Closed"")"
Case 4 'Authorized with Action Items
myStatus = "((tbl_Deviations.Status)=""Authorized with Action
Items"")"
Case 5 'Void
myStatus = "((tbl_Deviations.Status)=""Void"")"
End Select

Select Case opt_Date
Case 1 'All Dates
myDate = ""
Case 2 'Deviation Receive Date
myDate = "DevRecDate >= #" & Me.txt_StartDate & "# And
DevRecDate <= #" & Me.txt_EndDate & "#"
Case 3 'Tracking Number Assigned Date
myDate = "TrackAssignDate >= #" & Me.txt_StartDate & "# And
TrackAssignDate <= #" & Me.txt_EndDate & "#"
Case 4 'Deviation Occured Date
myDate = "DevDate >= #" & Me.txt_StartDate & "# And DevDate <=
#" & Me.txt_EndDate & "#"
Case 5 'Completion Date
myDate = "CompletedDate >= #" & Me.txt_StartDate & "# And
CompletedDate <= #" & Me.txt_EndDate & "#"
Case 6 'Action Item Estimated Date
myDate = "ActionDeadline >= #" & Me.txt_StartDate & "# And
ActionDeadline <= #" & Me.txt_EndDate & "#"
Case 7 'Steering Committee Review Date
myDate = "SCReviewDate >= #" & Me.txt_StartDate & "# And
SCReviewDate <= #" & Me.txt_EndDate & "#"
End Select

If opt_Status = 1 And opt_Date = 1 Then myCriteria = ""
If opt_Status = 1 And opt_Date <> 1 Then myCriteria = " WHERE " & myDate
If opt_Status <> 1 And opt_Date = 1 Then myCriteria = " WHERE " & myStatus
If opt_Status <> 1 And opt_Date <> 1 Then myCriteria = " WHERE " &
myStatus & " AND " & myDate

mySQL = mySQL & " " & myCriteria
mySQL = mySQL & " ORDER BY tbl_Deviations.DevNum DESC;"

Me.lst_Deviation.RowSource = mySQL
Me.txt_DeviationCount.Value = Me.lst_Deviation.ListCount


End Sub

 

[John Nurick]

Hi Anne,

It sounds as if something is wrong with the SQL statement you are
building.

You seem to have 24 columns in your listbox (I counted the fields listed
in MySQL). This is very, very unusual. Do you have code elsewhere in
this form that uses the values in all these columns?

If not, you can radically simplify things by including only the columns
that you do actually need in the list box. Typically there would be just
one or two:
1) a key that relates the listbox's contents to the primary key of
the underlying table
2) the value you want to display.

If the key value and the display value are the same, there'd just be one
column; if different, it is normal to hide the key value (by setting the
column width property of the listbox to 0). Either way, the Bound Column
of the listbox would be 1.

At a guess, tbl_Deviations.DevNum is the key value for the listbox, and
tbl_Deviations.Status is the value you're seeing unexpectedly. MySql
should probably be more like this:

"SELECT DISTINCTROW tbl_Deviations.DevNum, " _
& "tbl_Deviations.Status " _
& "FROM tbl_SOPs RIGHT JOIN tbl_Deviations " _
& "ON tbl_SOPs.SOP = tbl_Deviations.SOPNum "

I don't understand your data structure, of course, but maybe the FROM
clause should just be "FROM tbl_Deviations".

 

[Anne]

John,

Thanks! I was originally using the SQL for something else, so it had a lot
more than was necessary. Here's why I love this newsgroup - having someone
look at your work and say "why are you doing that?"! I changed the SQL
statement and that fixed the problem. You rock!