Listbox: Retrieve Value from Hidden Field of Current Record

[Sorrells]

I looked through 250 threads on Listboxes at 26,800 baud before posting this. Perhaps the question is inane!

I have a form to re-sequence the order of rooms. In it is a listbox of the rooms based on a query that returns all the fields of the underlying table but only displays field 4 with the Room Name.

There is another field on the same record, whose value I need to obtain when the user selects a record. It is the primary key index value.

I am unsure how to go about this and my feeble attempt returned a type miss match.

Forms![frmPrioritize_Rooms]![txtHouse_Room_ID] = lstSelected_Rooms!House_Room_ID

Perhaps a more general question is "Can any field of a listbox underlying query be accessed for its value when the current record and how is this done?"

Does anyone have suggestions for me? Any advice is appreciated.

 

[Rich]

=Me!lstSelected_Rooms

 

[Sorrells]

Rich,

Your comment helped but was a little terse . I also spent some time searching the web Newsgroups and found a lot of information. Below is the successful result.

HMS Form Prioritize Rooms Solution

This form has a single list box that shows the rooms selected by the Client. Although there are six fields returned in the underlying query, only the name of the room (field Room_Custom) is displayed. The fields I want are column0 of the query(House_Room_ID) and column 3 (Room_Custom).

I had to insert code on the form's OnOpen Event and the listbox OnClick Event to obtain the values I wanted. Once obtained they were assigned to form texboxes(txt) and to global variables(gbl).Below is the essential code to perform this.

OnOpen Event (Form)
lstSelected_Rooms.Requery
Me.lstSelected_Rooms.SetFocus
DoCmd.GoToRecord , , acFirst
Forms![frmPrioritize_Rooms]![txtHouse_Room_ID] = Me.lstSelected_Rooms.Column(0, 0)
gblHouse_Room_ID = txtHouse_Room_ID
txtSelected_Room = Me.lstSelected_Rooms.Column(3, 0)
gblSelected_Room = txtSelected_Room

OnClick Event (lstSelected_Rooms)
Dim nIndex As Long
Dim value As Long
nIndex = lstSelected_Rooms.ListIndex
value = lstSelected_Rooms.Column(0, nIndex)'0 is first column
Forms![frmPrioritize_Rooms]![txtHouse_Room_ID] = Me.lstSelected_Rooms.Column(0, nIndex)
gblHouse_Room_ID = txtHouse_Room_ID
txtSelected_Room = Me.lstSelected_Rooms.Column(3, nIndex)
gblSelected_Room = txtSelected_Room

Between the two sets of code, I am obtaining the values of the fields desired from this listbox.

My sincere thanks for you reply and interest!

 

[Carlos Urbina]

The syntax I was looking for was Me.lstSelected_Rooms.Column(0, nIndex)
Thanks