Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 28th, 2004, 02:09 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default Multi Select List Box Coding Errors...

:(

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



Reply With Quote
  #2 (permalink)  
Old June 29th, 2004, 03:45 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Belgium.
Posts: 44
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old June 29th, 2004, 01:03 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old June 29th, 2004, 02:04 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
Reply With Quote
  #5 (permalink)  
Old June 29th, 2004, 02:07 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

how would i do that?

Reply With Quote
  #6 (permalink)  
Old June 29th, 2004, 02:14 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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

anyway.. do something like this..

Code:
 
on error goto myerrors
your code here...
exit sub (or function)
myerrors:
stop 'this will stop the current execution so you can trace it
resume 'this will come to the same line that get's you the error
put that on the 2 subs that give's you the error so you can know where you have your problem...

HTH

Gonzalo
Reply With Quote
  #7 (permalink)  
Old June 29th, 2004, 02:16 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i'm doing vba for access 97.

Reply With Quote
  #8 (permalink)  
Old June 29th, 2004, 04:08 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #9 (permalink)  
Old June 30th, 2004, 08:20 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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



HTH

Gonzalo
Reply With Quote
  #10 (permalink)  
Old June 30th, 2004, 08:27 AM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Good point...

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

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo Box to Show Multi Column Info After Select Coby Excel VBA 0 August 14th, 2007 05:23 PM
multi select open box dialog yourfriendahsan Visual Basic 2005 Basics 1 July 19th, 2007 02:49 PM
multi-column list box values moved to 2nd list box sbmvr Access VBA 1 May 14th, 2007 01:58 PM
Multi-Select List Boxes and Calculations flrzeus BOOK: Expert One-on-One Access Application Development 0 December 5th, 2005 09:43 AM
Multi Select Combo Box acdsky VB How-To 2 March 1st, 2004 02:09 PM



All times are GMT -4. The time now is 10:46 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.