Selecting record from unbound box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a box in a form getting its information from a query. When I try to
select a record in the box, I always get the first record in the box.

Private Sub Arrived_Click()
Dim varitm As Variant
Dim db As Database
Dim rs1 As Recordset
Dim PersonID As Integer
Dim JobID As Integer
Dim first As String
Dim Last As String
Dim sql1 As String
Dim OTJ As Boolean
Dim Active_Jobs As String
Dim Current As Date
Dim ok As Integer
Active_Jobs = "jobs_active"
Set db = CurrentDb
Current = Now()
ok = 0


MsgBox Me.ListCurrent.ItemData(varitm)
MsgBox varitm

If IsNull(Me.ListCurrent.ItemData(varitm)) = False Then
PersonID = Me.ListCurrent.ItemData(varitm)
sql1 = " SELECT " & Active_Jobs & ".* " _
& " FROM " & Active_Jobs & " " _
& " WHERE " & Active_Jobs & ".PersonID = " & PersonID & " ;"
Set rs1 = db.OpenRecordset(sql1, dbOpenDynaset) 'active jobs
If Not rs1.BOF And Not rs1.EOF Then
rs1.Edit
Last = rs1!LastName
ok = MsgBox("Has " & Last & " arrived on the job?",
vbYesNo)
If ok = 6 Then
rs1!TravelTime = Current
rs1!OTJ = "True"
rs1.update
End If ' ok
rs1.close
End If 'eof bof
End If 'isnull

Me.ListCurrent.Requery

End Sub
 
You never assign a value to varItm, so its defaulting to the 1st row (with
an index of zero).

Try:
For Each varItm In Me.ListCurrent.ItemsSelected
MsgBox Me.ListCurrent.ItemData(varitm) ..............
End If 'isnull
Click to expand...
Next varItm

Note: If your list box isn't Multiselect then you don't want to use
ItemsSelected (which only exists if the list is Multiselect). Just use Value
and ListIndex:
MsgBox Me.ListCurrent.ItemData(Me.ListCurrent.ListIndex)
MsgBox Me.ListCurrent.Value.
 
You are the man! You have no idea how long I have been trying to get this to
work and the different variations I tried. I will rate this response
EXTREMELY high.
Thanks,
George
 
Back
Top