Works on Single click not double

[TallMan]

Hello All -

Have any of you ever had an issue where your code will work on a double click Event, but not a single click event?

The code listed below is doing this to me and for the life of me I cannot figure it out! The error that I am getting is "Object Variable or With Block Variable Not Set" I have placed (ERROR OCCURS HERE) where the debugger brings me to.

I appreciate any feedback you can give.

Dim db As DAO.Database
Set db = CurrentDb
Dim sql As String
Dim strschCode As String
Dim I As Integer
Dim rs As DAO.Recordset
Dim strBegin As String
Dim strEnd As String
Dim strRate As String
Me.lstFeeSchedule.RowSource = ""
For I = 0 To Me.lstDiscFeeSchedules.ListCount - 1
 
     If Me.lstDiscFeeSchedules.Selected(I) Then
                strschCode = Me.lstDiscFeeSchedules.VALUE
 
 sql = "SELECT tblOSM_DiscSchedule.Disc_Sch_Code, tblOSM_DiscSchedule.Seq_id, tblOSM_DiscSchedule.Begin_Range, tblOSM_DiscSchedule.End_Range, tblOSM_DiscSchedule.Rate " & _
       "FROM tblOSM_DiscSchedule " & _
       "WHERE (((tblOSM_DiscSchedule.Disc_Sch_Code)='" & strschCode & "')) order by tblOSM_DiscSchedule.Seq_id asc;"
        Set rs = db.OpenRecordset(sql)
    End If
Next I
 
While Not rs.EOF (ERROR OCCURS HERE)
strBegin = ""
strEnd = ""
strRate = ""
strBegin = FormatCurrency(rs.Fields("Begin_Range"), "0")
strEnd = FormatCurrency(rs.Fields("end_Range"), "0")
strRate = FormatPercent(rs.Fields("rate"))
Me.lstSchedule.AddItem ("'" & strBegin & "';'" & strEnd & "';'" & strRate & "'")
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

 

[bob fitz]

The line of code that "Sets" the recordset is within a If/Then statement. Try putting a breakpoint on the "Set rs" line check that the code actually gets inside the If/Then statement.

 

[gemma-the-husky]

maytbe the loop to declare the recordset never runs

incidentally - why run that multiple times anyway?

 

[jdraw]

Do some elementary debugging -- add a few debug.print "I am here..." in the code to make sure it actually executes.

Try some Step processing.
Open a locals window to see if RS gets a value.

And as Pat asked show your procedure headers so we know exactly what event we are discussing.

 

[TallMan]

Okay Guys -

First off, thanks for the help.

I have added the procedure heading to the code so you can see which event I am in. I also moved the SQL query out of the For Loop, but now my list box is not recognizing that there is a record selected and it is skipping setting my variable, strschCode, to the value that is selected in the list box. I have the list box set with a Row Source Type of "Value List".

When I execute the code the Msgbox "Test" is populating in the Else section of the IF statement. Any ideas?

Private Sub lstSchIds_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim sql As String
Dim strschCode As String
Dim I As Integer
Dim rs As DAO.Recordset
Dim strBegin As String
Dim strEnd As String
Dim strRate As String
Me.lstSchedule.RowSource = ""
strschCode = ""
For I = 0 To Me.lstSchIds.ListCount - 1
 
     If Me.lstSchIds.Selected(I) Then
        strschCode = Me.lstSchIds.VALUE
      Else
      MsgBox "test"
 
     End If
Next I
 sql = "SELECT tblOSM_DiscSchedule.Disc_Sch_Code, tblOSM_DiscSchedule.Seq_id, tblOSM_DiscSchedule.Begin_Range, tblOSM_DiscSchedule.End_Range, tblOSM_DiscSchedule.Rate " & _
       "FROM tblOSM_DiscSchedule " & _
       "WHERE (((tblOSM_DiscSchedule.Disc_Sch_Code)='" & strschCode & "')) order by tblOSM_DiscSchedule.Seq_id asc;"
        Set rs = db.OpenRecordset(sql)
 
While Not rs.EOF
        strBegin = ""
        strEnd = ""
        strRate = ""
 
        strBegin = FormatCurrency(rs.Fields("Begin_Range"), "0")
        strEnd = FormatCurrency(rs.Fields("end_Range"), "0")
        strRate = FormatPercent(rs.Fields("rate"))
 
        Me.lstSchedule.AddItem ("'" & strBegin & "';'" & strEnd & "';'" & strRate & "'")
        rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub

 

[jdraw]

Sounds like nothing is in the list, or possibly the first Item wasn't selected???

Right after For I = 0 To Me.lstSchIds.ListCount - 1 put

Debug.Print "Listcount is " & Me.lstSchIds.ListCount
Debug.Print "Value of I is " & I

Could you also describe in your words what the For Next loop is doing?
It seems to me that you are checking all the Selected values then getting out of the For Loop normally then running sql.

First thought is that you aren't using all the Selected values, but I haven't done anything thorough to check it.

Good luck

 

[TallMan]

Hey jdraw -

Thanks for the assitance. What I am trying to do is click on one record at a time in the me.lstSchIds listbox. (I have it set as a "Simple" listbox)

I would like the value or string value of the record selected set to a variable ( strSSchCode) This strSchCode variable is supposed to be part of a where clause in the SQL query to help find all of the records associated with the value of strSchCode. Then the results of that query are supposed to loop through and populate another listbox. (me.lstSchedule)

Does this help clear things up at all?

The results from the debug are:

Listcount is 1
Value of I is 0

Thank you,

TallMan

 

[gemma-the-husky]

has your list got a header row by any chance

i think the header row is counted as a row, although it has no data.

 

[TallMan]

:banghead:Column Heads = No :banghead:

 

[jdraw]

What version of Access are you running? I have 2003 and can not read accdb (2007 or 2010). I'd be willing to look at it --remove any confidential stuff... and save as mdb if possible.


What are the Values in your ValueList? You should be able to copy and paste it here.
1. Do you know about stepping through the code (F8)??? You could try and see what actually executes.

2.You might try using the lstSchIds_AfteUpdate event.
Copy all of the code below your current header and place it in the AfterUpdate event

Put a new name or prefix on the current lstSchIds_Click procedure header.

I don't see why it doesn't process the whole For-Next???? Or Does it?

 

[TallMan]

Jdraw -

I really appreciate your help. I got the procedure to work on a double click event. For some reason it is still not working on the "on click" event.

I am going to call it a day on this and use the double click.

Thanks for your help,

Tallman