Get List box values...

L

Lissa

Registered User.
Local time
Today, 02:56
Joined
Apr 27, 2007
Messages
114
I thought I had good solution but it's not so good after all..
I have a main form that the allows users to select a Purchase Order Number from a drop down list box. That PO Number selection will pop up another form that displays all the line items from that PO.
The line items of that PO are displayed in a list box. The user selects a line item and the line item information populates fields on the main form.
This was way cool until I noticed that if the user selects/highlights the second item from the list box - it's still populating the main form with the values of the first record. :confused:

The list box on the second form is unbound...and it's source is a query of which 6 values are displayed- bound to column 1.
Currently in the double click event of the list box I had following code which successfully inserted values into a table that is used by my main form.

Private Sub LineItems_DblClick(Cancel As Integer)
Dim strSQL As String
Dim item As String
Dim PO As String
Dim PartNum As String
Dim Nomen As String
Dim DwgNum As String
Dim Qty As Integer
Dim Due As Date
Dim key As Integer

PO = Me.PONumber
PartNum = Me.PartNumber
DwgNum = Me.DrawingNumber
Nomen = Me.Nomenclature
Qty = Me.Quantity
Due = Me.DueDate

'strSQL = "UPDATE tblINCOMING_INSPECT_LOG SET PONum = '" & PO & "', PartNumber = '" & PartNum & "', Nomenclature = '" & Nomen & "', DrawingNumber = '" & DwgNum & "', QtyOrdered = " & Qty & ", DateShpDue = # " & Due & " # WHERE IncmgInspectLogID = " & updKey & " ;"
'CurrentDb.Execute strSQL, dbFailOnError
DoCmd.Close
End Sub


Am I not getting the value correctly by doing an assignment like PO = Me.PONumber?
 

Attachments

  • pic.JPG
    pic.JPG
    57.8 KB · Views: 191
Are you saying that the "Incoming Inspection Log" form you attached is a ListBox? It looks like a form in Continuous Form mode.
 
In my attachment, the "dialog box" titled "Incoming Inspection Log" with the question "Which incoming item are you about to inspect?" displays a List Box. The List Box displays 6 columns - bound to the first column...
 
The code you posted will not work with a ListBox. As a matter of fact, it should throw an error when it compiles.
 
I was working... but the point is that I have a design flaw that I'm trying to find a solution to. I like using a list box to display the options but I would like to be able to get the value of any record the user selects. Currently it only works with the selection of the first record. I am probably not retrieving the selection correctly in my code that's why I asked the question if it was wrong to use an assignment such as PO = Me.PONum
 
Lissa,
I'm not trying to be a jerk, I'm trying to understand what you have based on your picture. As I said, the code should throw an error if it is on the form you pictured and that is a ListBox. That is not the way to retrieve values from a ListBox. All of the referencing has to go through the ListBox control. That is why I think that is not a ListBox. That and the fact that the column headings do not line up with the columns.
 
Oh no I appreciate you trying to help me- it's hard to explain without posting up my db which I would do but it's too big.
I'm attaching a view of the subform with the List box. The only difference is that the listbox is called List0 since it's from my "production" copy of the database.
I'm searching for info on list boxes and it may be a freak thing that it was actually working with assignments like PO = Me.PONum...

The labels on the list box aren't aligned correctly since I just stuck them in one label... I can fix that later.

But I'm wondering if I shouldn't be using a List Box at all to accomplish what I'm doing - but the list box displays everything so neatly...:o
 

Attachments

  • ListBox.jpg
    ListBox.jpg
    96.3 KB · Views: 218
Well I'll admit that definitely looks like a ListBox. Is the code you posted in your 1st post on this form? If so then where are all of the controls that are referenced? It should throw an error if you compile.
 
This is the code to be exact... when I run it - I can't see any error since it actually adds the values into the table like I tell it... as for the other controls... that's all I have on that form... freakish, huh? The main form has those controls but I'm not calling them from the subform....

but I just read another example on list boxes and maybe I should be accessing the values like this instead??? - Me.List0.Column(1),Me.List0.Column(2)...etc


Private Sub List0_Click()
Dim strSQL As String
Dim PO As String
Dim PartNum As String
Dim Nomen As String
Dim DwgNum As String
Dim Qty As Integer
Dim Due As Date
Dim key As Integer
PO = Me.PONumber
PartNum = Me.PartNumber
DwgNum = Me.DrawingNumber
Nomen = Me.Nomenclature
Qty = Me.Quantity
Due = Me.DueDate

'Update the Incmg Inspection Log with the selected information
strSQL = "UPDATE tblINCOMING_INSPECT_LOG SET PONum = '" & PO & "', PartNumber = '" & PartNum & "', Nomenclature = '" & Nomen & "', DrawingNumber = '" & DwgNum & "', QtyOrdered = " & Qty & ", DateShpDue = # " & Due & " # WHERE IncmgInspectLogID = " & updKey & " ;"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.Close
End Sub
 
I would think that these lines of code:
Code: 
PO = Me.PONumber
PartNum = Me.PartNumber
DwgNum = Me.DrawingNumber
Nomen = Me.Nomenclature
Qty = Me.Quantity
Due = Me.DueDate
...would throw an error since they reference 6 different controls and none are the ListBox. Are you hiding controls on that form somewhere?
...maybe I should be accessing the values like this instead??? - Me.List0.Column(1),Me.List0.Column(2)...etc
Click to expand...
YES, that is the method to reference data within a ListBox. Here's a link that you might find helpful.
 
I don't know how it's getting the 6 values other than that list box is displaying the values from the underlying query. I thought I was accessing the list box values with the "Me."
I'll read that link and I'll try the method and see what happens :)
 
I've read over your post...and I think I got this down...

You have a form that has a drop down box that list the POs. You select one, and a second form opens up. This second form contains a List box with the line items to that PO. By selecting a line item, it updates a table with that data, which is then displayed on the form with the dropdown box? Is that correct?

If so, is there a reason why you just dont put the List box on the form with the PO, and when you double click on the line item, you populate fields based on that data instead of updating a table? Seems a bit counter productive.

My appoligies if I got the scenario wrong.

btw, to access data on a list box, it would look like this: me!Form_Name!Listbox.column(0) would grab the first column of data from the query that is the source of the list box.
 
BTW, there are free file hosting sites where you could post your db if you wanted to. Google is your friend.
 
Hi Scooterbug - you are correct. Why I don't put the List Box within the PO?- my requirement was to develop an entry form that looks exactly like an Incoming Inspection Log form - a form we use to inspect hardware parts we order from vendors to make our product.... We only inspect one piece of incoming hardware at a time... so a PO can have multiple line items so I have to select one piece of hardware from that PO....
 
Let me try the accessing the values using the list box method and if doesn't work I'll google for a free hosting site and post it up. Thanks! :-)
 
So, are you storing the line items twice in two separate tables? If so, there really is no need to. You could simply add a yes/no field to the line items table and put the yes/no control on the PO Form.

As for the list box issue, here is how I would do it. I would build a subform on the PO form using the fields from the Line Items table. Have the bound column on the list box be the primary key for the line item entry and have the record source for the subform have a WHERE clause where lineItemID equal to the listbox. Once the item has been inspected, simply have the user check the Inspected field to Yes.
 
Finally... accessing the values this way works! :)

PO = Me.LineItems.Column(0)
Due = Me.LineItems.Column(1)
Qty = Me.LineItems.Column(2)
Nomen = Me.LineItems.Column(3)
PartNum = Me.LineItems.Column(4)
DwgNum = Me.LineItems.Column(5)
 
Learning something new everyday ;)
Thanks for your help!
 
You must log in or register to reply here.

Similar threads

C
Getting a write conflict
Replies
6
Views
280
ChrisLeicester
C
M
Solved Type mismatch
Replies
16
Views
1,261
DickyP
DickyP
J
Refreshing List Box
Replies
8
Views
540
JamesJoey
J
E
Search Button Not Finding Record and populating the text boxes on form.
Replies
13
Views
1,312
Pat Hartman
P
A
Solved Adding Items to Combobox, but They aren't showing up
2
Replies
21
Views
904
AngelSpeaks
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom