:(

I'm trying to figure out this piece of code...

Here's what this piece is supposed to do. On my form, I have a multi select list box with Dates, named lstPeriod. the user is supposed to select whatever dates they need. Then if the particular month's information is already in the database, my message pops up, saying that the information i requested is already in the db. My issue is this: Before I changed from the list box, this particular control was a combo box, and the code that was called worked. Now that I've changed the combo box to a list box, nothing works.

Not sure what else to do. :( I think there's probabaly some code that I'm missing (you know, if the user decides to choose more than one date); but I just don't know how to code this.

Here's the Code:

Public Function PerImport()

'Code to define a recordset named rstPeriod

    Dim rstPeriod As Recordset
    Dim db As Database
    Set db = CurrentDb()
    Set rstPeriod = db.OpenRecordset("tblPeriod")


'Checks to see whether or not files have been imported. If so, a message box will
'appear notifying the user that that particular month's files are already in the database.
'if not, the program goes to the import process.

Do Until rstPeriod.EOF()
    If rstPeriod.Fields(1).Value <= Forms!frmMain.lstPeriod Then
    MsgBox "The data that you have requested is already in the database."
    Exit Function
    Else: rstPeriod.MoveNext
    End If

    Loop
End Function

What I do in your case is that I create a listbox with Checkboxes (It's a normal listbox but the style=checkbox). (makes it easier to select multiple)

Then when the user hits submit I do the following.
I loop through all rows in the listbox and check if it was selected.
When it was selected, I do an action.

Dim i as Integer

For i = 0 to lstBox.listcount - 1

  If lstBox.Selected(i) = True then

    'here the ListIndex i was selected in the lstBox.
    'Action here

  End if

Next

I hope this helps

Greetz

Tom.

Thanks Tom.

I'm still stuck....maybe you can help with this as well. :)
Here's what I have so far...and I'm getting this Runtime Error (91): Object Variable or With Block Variable Not Set.

Private Sub CheckPeriod()
'This function loops through the Period table and selects the user-selected dates.
'These dates are then run through the PerImport function.
    Dim MyDB As Database
    Dim qdf As QueryDef
    Dim i As Integer, strSQL As String
    Dim strWhere As String, strIN As String
    Dim lstPeriod As ListBox
    Dim flgAll As Boolean
    Set MyDB = CurrentDb()

    For i = 0 To lstPeriod.ListCount - 1

        If lstPeriod.Selected(i) = True Then
        Call PerImport

        End If

    Next i


(Below This is the Period Import function)

Public Function PerImport()

'Code to define a recordset named rstPeriod

    Dim rstPeriod As Recordset
    Dim db As Database
    Set db = CurrentDb()
    Set rstPeriod = db.OpenRecordset("tblPeriod")


'Checks to see whether or not files have been imported. If so, a message box will
'appear notifying the user that that particular month's files are already in the database.
'if not, the program goes to the import process.

Do Until rstPeriod.EOF()
    If rstPeriod.Fields(1).Value <= Forms!frmMain.lstPeriod Then
    MsgBox "The data that you have requested is already in the database."
    Exit Function
    Else: rstPeriod.MoveNext
    End If

    Loop
End Function

Thanks for your help!

Angel

hi there...

you can trap that error, probably the database is not open yet when you try to open the recordset..

try to see if every object you use is open before you use it...

HTH

Gonzalo

thanks. unfortunately my vba experience is little to none. you've got to explain that in laymen's terms... :)

how would i do that?

mmm.. are you programming in vb or a specific vba??

anyway.. do something like this..

put that on the 2 subs that give's you the error so you can know where you have your problem...

HTH

Gonzalo

i'm doing vba for access 97.

Ok..this thing is driving me NUTZ!

Two Issues: I can't make this into a listbox (with checkboxes) My form would look messy...I have way too many date ranges to check. I have to use this type of listbox.

Second Issue: I can't get this frickin thing past this line (first bold line). This is the error message I get: Compile Error: Method of data not found.

AND on top of that, i always get stuck with this line of code (second bold line)

HELP! :(

Private Sub CheckPeriod()
'This function loops through the Period table and selects the user-selected dates.
'These dates are then run through the PerImport function.
    Dim DB As Database
    Dim lstperiod As recordset
    Set lstperiod = DB.OpenRecordset("tblPeriod")
    Set DB = CurrentDb()
    'On Error GoTo myerrors
    Dim i As Integer

For i = 0 To lstperiod.ListCount - 1

If lstperiod.Selected(i) = True Then
  Call PerImports

    'here the ListIndex i was selected in the lstBox.
    'Action here

  End If

Next
End Sub

lstperiod is a listbox.. or a recordset?? can't be the two things at the same time...



HTH

Gonzalo

Good point...

but even when i changed the variable to dim lstperiod as a listbox, it still didn't work correctly.