Solved

Selected From Listbox as Recordset

Posted on 2000-04-05
15
208 Views
Last Modified: 2006-11-17
I am trying to grab the selected records from a list box and open a recorset based on them.

I am able to grab the selected records and have them in a variable, but I need to modify their records now, and I don't seem to be able to do so. I have this feeling I will need to seperate the data from the variable. Let me give you an example:

User selects these stores from the listbox (06058 08542 12542 23854).

The variable will hold a value of "06058, 08542, 12542, 23854". That whole string is the value of the variable, so I can not search by the variable.

I am looking for suggestions on how I can get the results I need.

Current Code Posted

Thanks

Public Function TransferDelivery1(frm As Form) As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim strMessage As String
Dim bytChoice As Byte
Dim rst As Recordset
Dim dbs As Database
Dim intCount As Integer

Set ctlSource = frm!lstStores

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, intCurrentRow) & ", "
End If
Next intCurrentRow

strMessage = "You have selected the following Store Numbers:@" & strItems & "@With a Delivery Date of " & frm.DeliveryDate1

bytChoice = MsgBox(strMessage, vbOKCancel)

If bytChoice = vbCancel Then
Exit Function
End If

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strItems)
With rst
intCount = .RecordCount
MsgBox intCount

End With

End Function
0
Comment
Question by:berg1375
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 9

Accepted Solution

by:
BrianWren earned 35 total points
ID: 2687592
I believe that you will need to put together SQL like this:

     strSQL = _
           "Select * " & _
           "FROM tblStores " & _
           "WHERE tblStores.ID IN (" & _
           strItems & _
           ")"
     Set rst = dbs.OpenRecordset(strSQL)

which will result in

Select * FROM tblStores WHERE tblStores.ID IN (06058, 08542, 12542, 23854)

Brian
0
 
LVL 7

Expert Comment

by:Believer
ID: 2687607
In your code where you have "Set rst = dbs.OpenRecordset(strItems)"...
You really want to open a recordset where a field (the key ID field?) matches one of the values, like this "air code":

Dim strSQL as String
strSQL = "SELECT * FROM tblTable WHERE [strItems] IN (" & strItems & ");

this resolves to:
  SELECT * FROM tblTable WHERE [strItems] IN ("06058", "08542", "12542", "23854");
for character data, or
  SELECT * FROM tblTable WHERE [strItems] IN (06058, 08542, 12542, 23854);
for numeric data


You may need to write additional code to delimit each store number in quotes, but I think you get the idea...
0
 
LVL 7

Expert Comment

by:Believer
ID: 2687610
Agggghhhh!  I didn't type fast enough! LOL!
0
 
LVL 3

Expert Comment

by:davereynolds
ID: 2688143
Yeah, Brian types faster than a lot of us! Paasky too!
0
 
LVL 4

Author Comment

by:berg1375
ID: 2689448
Okay...........this is what I got. I had to change ("&strItems&") to ('&strItems&'). The first code would not go through. Will this make a difference?

strSQL = "Select * FROM tblBolStore WHERE tblBolStore.STORENUM IN ('&strItems&')"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
With rst
intCount = .RecordCount
MsgBox intCount

End With


It runs, and gives me a value of 0 (zero) in the .RecordCount msgbox. Even though I selected four store numbers. Can someone tell me what is going on?

Thanks
berg
0
 
LVL 4

Author Comment

by:berg1375
ID: 2689703
I also tried:

strSQL = "Select * FROM tblBolStore WHERE tblBolStore.STORENUM IN (' " & strItems & " ')"

But I still get a RecordCount of 0 (zero).

What do I have to do??????
0
 
LVL 7

Expert Comment

by:Believer
ID: 2689795
Debug.print your sql statement and look at it in the debug window... I'm willing to bet that your syntax doesn't look like one of the two sql statements I showed you.  Since you're looking for a string match (on STORENUM) instead of a numeric match (on STORENUMID?), look at the example I gave with quotes.
If strItems equals:
   "06058", "08542", "12542", "23854"
then you don't need the extra quotes in this line:
   "Select * FROM tblBolStore WHERE tblBolStore.STORENUM IN (' " & strItems & " ')"
so then it would look like this:
   "Select * FROM tblBolStore WHERE tblBolStore.STORENUM IN (" & strItems & ")"

strItems should have been built with code (in a loop) something like this:
   stritems=chr$(34) & Item1 & chr$(34) & ","
   stritems=chr$(34) & Item2 & chr$(34) & ","
   ...
   stritems=chr$(34) & ItemX & chr$(34)

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 4

Author Comment

by:berg1375
ID: 2689908
Okay..... I now get a data type mismatch error in the Set rst = ..........

Although.......it is recognizing the store numbers in the SQL statement now. It is just storing them as numbers.

Is there a way I can throw quotes around the store numbers in the SQL statement, or will I have to do this in the strItems code????
0
 
LVL 4

Author Comment

by:berg1375
ID: 2690312
Adjusted points from 50 to 75
0
 
LVL 4

Author Comment

by:berg1375
ID: 2690313
Okay I have modified my code, and get the results:

"12331" "12344" "64645" "34545"

But now it tells me I am missing an operator in the SQL statement.

Here is what I have now:

Public Function TransferDelivery1(frm As Form) As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim strItemsLen As Integer
Dim intCurrentRow As Integer
Dim strMessage As String
Dim bytChoice As Byte
Dim rst As Recordset
Dim dbs As Database
Dim intCount As Integer
Dim myStores As Collection
Dim strSQL As String

Set ctlSource = frm!lstStores

For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
        strItems = strItems & ctlSource.Column(0, intCurrentRow) & """ """
    End If
Next intCurrentRow

strItems = Chr$(34) & strItems

strItemsLen = Len(strItems)

strItems = Left(strItems, strItemsLen - 1)

strMessage = "You have selected the following Store Numbers:@" & strItems & "@With a Delivery Date of " & frm.DeliveryDate1

bytChoice = MsgBox(strMessage, vbOKCancel)

If bytChoice = vbCancel Then
    Exit Function
End If

strSQL = "Select * FROM tblBolStore WHERE tblBolStore.STORENUM IN (" & strItems & ")"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
With rst
intCount = .RecordCount
MsgBox intCount

End With

End Function
0
 
LVL 4

Author Comment

by:berg1375
ID: 2690628
Well, I solved the problem. I just had to play with the character types in the code. I ended up adding ' ' around each STORENUM, and then modifying the query a little, and BINGO!

It is running like clock work now.

I will be splitting the points between Beleiver and BrianWren.

Thanks
0
 
LVL 3

Expert Comment

by:darinw
ID: 2690755
Community Support has reduced points from 75 to 35
0
 
LVL 3

Expert Comment

by:darinw
ID: 2690756
Reducing points for split.

darinw
Customer Service
0
 
LVL 4

Author Comment

by:berg1375
ID: 2691022
Thank you

Believer:

I am posting a Q for your points seperately.
0
 
LVL 7

Expert Comment

by:Believer
ID: 2693360
Glad you got it to work... sounds like you ran into a strange glitch along the way.  Thanks for the pts!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now