> My bad..... I quit typing before I quit thinking :(
>
> You need to requery the list boxes. In the code for each button, you
> should
> add these two lines:
>
> lstTruckInventory.Requery
> lstOnSite.Requery
>
> They should be the last two lines above the "End Sub" line.
>
>
> HTH
> --
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
>
>
> "CJ" wrote:
>
>> Sorry, I think you might need a little more data
>>
>> This company rents equipment out to oilfield sites.The equipment is
>> assigned
>> from inventory to a truck and then from the truck to a site. That way, we
>> can keep track of where all of the equipment is.
>>
>> The list box on the left is the truck inventory, the one on the right is
>> the
>> site inventory.
>> So, when we remove the equipment from the site, I need it to return to
>> the
>> truck inventory and show up in the left list box.
>>
>> With the change to the code Arvin gave me, the date that the item is
>> picked
>> up from the site is being filled in but the item is not moving to the
>> left
>> (truck) list box, it is staying in the right (site) list box.
>>
>> When the code was:
>> strSql = "Delete * from tblWorkOrderDetails where [Work Order ID]=" &
>> Me.txtWOID & " AND (" & strwhere & ");"
>> the items moved between the list boxes but the item was removed from
>> tblWorkOrderDetails. Instead of it being deleted, I just want the date
>> filled in.
>>
>> I did change the dates as you suggested and I understand why that is
>> correct, but the equipment just is not moving from right to left. I have
>> not
>> changed any of the code on the buttons that move the items, just the list
>> box code.
>>
>> --
>> Thanks for taking the time!
>>
>> CJ
>> ---------------------------------------------------------
>> Know thyself, know thy limits....know thy newsgroups!
>> "Steve Sanford" <limbim53 at yahoo dot com> wrote in message
>> news:DE5A343F-5B66-4243-999C-DBCDC688A95E[ at ]microsoft.com...
>> >I don't understand the purpose of the list boxes, but if the date field
>> > determines which list box the record should be in, one list box would
>> > have
>> >
>> > [dtmDateIn] is NULL
>> >
>> > and the other list box would have
>> >
>> > [dtmDateIn] is NOT NULL
>> >
>> > in the WHERE clauses.
>> >
>> > HTH
>> > --
>> > Steve S
>> > --------------------------------
>> > "Veni, Vidi, Velcro"
>> > (I came; I saw; I stuck around.)
>> >
>> >
>> > "CJ" wrote:
>> >
>> >> OK, here you go Steve.
>> >>
>> >> This is the SQL of the list box on the left, lstTruckInventory
>> >>
>> >> SELECT qryTruckItems.[SKU Number], qryTruckItems.Item,
>> >> tblWorkOrderDetails.[SKU Number]
>> >> FROM qryTruckItems LEFT JOIN tblWorkOrderDetails ON qryTruckItems.[SKU
>> >> Number] = tblWorkOrderDetails.[SKU Number]
>> >> WHERE (((tblWorkOrderDetails.[SKU Number]) Is Null) AND
>> >> ((qryTruckItems.lngTruckID)=[Forms]![frmTruckToSite]![lngTruckID]))
>> >> ORDER BY qryTruckItems.[SKU Number];
>> >>
>> >> The SQL of the list box on the right, lstOnSite
>> >>
>> >> SELECT tblWorkOrderDetails.[SKU Number], [strMake] & " - " &
>> >> [strModel]_
>> >> AS Item
>> >> FROM tblMake INNER JOIN (tblModel INNER JOIN (tblInventory INNER JOIN_
>> >> tblWorkOrderDetails ON tblInventory.[SKU Number] =
>> >> tblWorkOrderDetails.[SKU
>> >> Number]) ON tblModel.lngModelID = tblInventory.Model) ON
>> >> tblMake.lngMakeID =
>> >> tblInventory.Make
>> >> WHERE (((tblWorkOrderDetails.[Work Order
>> >> ID])=[Forms]![frmTruckToSite]![txtWOID]))
>> >> ORDER BY tblWorkOrderDetails.[SKU Number];
>> >>
>> >> Since the left arrow now fills in the dtmDateIn field, I could filter
>> >> lstOnSite by adding criteria that says that dtmDateIn must Is Null.
>> >> If the date is not empty, then the item has been taken from the site.
>> >> So, I guess the code would need to requery the right list box and make
>> >> sure
>> >> the item
>> >> shows up in the left one.
>> >>
>> >> I hope this all makes sense to you.....
>> >>
>> >> --
>> >> Thanks for taking the time!
>> >>
>> >> CJ
>> >> ---------------------------------------------------------
>> >> Know thyself, know thy limits....know thy newsgroups!
>> >> "Steve Sanford" <limbim53 at yahoo dot com> wrote in message
>> >> news:DF8E09ED-E678-4286-9C55-DAAD3C49E158[ at ]microsoft.com...
>> >> > It depends on the RowSource of each list box.
>> >> >
>> >> > You have to have some way to limit the records returned. One way
>> >> > would
>> >> > be
>> >> > to
>> >> > filter by [dtmDateIn], ie theSQL of the rowsource for one list box
>> >> > would
>> >> > have
>> >> > a Where clause that looked something like ".... WHERE dtmDateIn >
>> >> > Date()"
>> >> > and the other list box rowsource would have a where clause of the
>> >> > other
>> >> > list
>> >> > box would be
>> >> > " WHERE dtmDateIn <= Date()"
>> >> >
>> >> > I used one table and two list boxes to select options in one of my
>> >> > databases. The table had a boolean field named "ynSelected". The
>> >> > where
>> >> > clause
>> >> > of one list box rowsource had "..WHERE ynSelected = TRUE" and the
>> >> > other
>> >> > had
>> >> > "..WHERE ynSelected = FALSE". To make an option "move" from one list
>> >> > box
>> >> > to
>> >> > the other, I had buttons that set/reset the boolean field, then
>> >> > requeried
>> >> > the
>> >> > list boxes.
>> >> >
>> >> >
>> >> > It would help if you wouold post the rowsource SQL of both of the
>> >> > list
>> >> > boxes.
>> >> >
>> >> > HTH
>> >> > --
>> >> > Steve S
>> >> > --------------------------------
>> >> > "Veni, Vidi, Velcro"
>> >> > (I came; I saw; I stuck around.)
>> >> >
>> >> >
>> >> > "CJ" wrote:
>> >> >
>> >> >> Hi Arvin
>> >> >>
>> >> >> Thanks so much for popping in!
>> >> >>
>> >> >> Your code worked and the date filled in beautifully but the
>> >> >> item still stays in the right hand list box.
>> >> >>
>> >> >> What would I need to do to make it "move" but still not
>> >> >> delete it from tblWorkOrderDetails?
>> >> >>
>> >> >> I am trying to track that the item was returned but is no
>> >> >> longer out at the field site.
>> >> >>
>> >> >> --
>> >> >> Thanks for taking the time!
>> >> >>
>> >> >> CJ
>> >> >> ---------------------------------------------------------
>> >> >> Know thyself, know thy limits....know thy newsgroups!
>> >> >> "news.microsoft.com" <arvinm[ at ]mvps.invalid> wrote in message
>> >> >> news:ORKHViUUJHA.4372[ at ]TK2MSFTNGP04.phx.gbl...
>> >> >> > Change the code to:
>> >> >> >
>> >> >> > strSql = "UPDATE tblWorkOrderDetails SET dtmDateIn = Date()
>> >> >> > Where_
>> >> >> > [Work Order ID]=" & Me.txtWOID & " AND (" & strwhere & ");"
>> >> >> > --
>> >> >> > Arvin Meyer, MCP, MVP
>> >> >> >
http://www.datastrat.com>> >> >> >
http://www.mvps.org/access>> >> >> >
http://www.accessmvp.com>> >> >> >
>> >> >> >
>> >> >> > "CJ" <private[ at ]newsgroups.com> wrote in message
>> >> >> > news:A45EE503-956C-4D11-A4CA-DC4542050027[ at ]microsoft.com...
>> >> >> >> Hi Groupies
>> >> >> >>
>> >> >> >> Sorry for the repost in less than 24 hours. I do realize that it
>> >> >> >> is
>> >> >> >> a
>> >> >> >> holiday
>> >> >> >> in the states and many MVP's are taking a very deserved rest.
>> >> >> >> I am way out of my element on this one and I can not progress
>> >> >> >> any
>> >> >> >> further
>> >> >> >> until it is solved.
>> >> >> >>
>> >> >> >> Instead of items selected in the right list box being deleted
>> >> >> >> when
>> >> >> >> you
>> >> >> >> push the left facing arrow, I would like the underlying table to
>> >> >> >> have
>> >> >> >> a date filled in instead. I do still want them to leave the list
>> >> >> >> box.
>> >> >> >>
>> >> >> >> Thanks!!
>> >> >> >>
>> >> >> >>
>> >> >> >> I am using the code for Sandra Daigle's list box transfer.
>> >> >> >>
>> >> >> >> I would like to change it so that when you remove an item
>> >> >> >> from the table on the right, it does not remove it from the
>> >> >> >> underlying table. Instead, I would like a field to be filled in
>> >> >> >> with
>> >> >> >> a date. I can not seem to get the code right.
>> >> >> >>
>> >> >> >> Here is a snip:
>> >> >> >>
>> >> >> >> <snip>
>> >> >> >> For Each varItem In Me.lstOnSite.ItemsSelected
>> >> >> >> strwhere = strwhere & "[SKU Number]='" &
>> >> >> >> Me.lstOnSite.ItemData(varItem)
>> >> >> >> & "' Or "
>> >> >> >> Next varItem
>> >> >> >> strwhere = Left(strwhere, Len(strwhere) - 4)
>> >> >> >>
>> >> >> >> ' I think this line below is the one that needs to change
>> >> >> >>
>> >> >> >> strSql = "Delete * from tblWorkOrderDetails where_
>> >> >> >> [Work Order ID]=" & Me.txtWOID & " AND (" & strwhere & ");"
>> >> >> >>
>> >> >> >> db.Execute strSql
>> >> >> >> Set db = CurrentDb
>> >> >> >> Set rst = Nothing
>> >> >> >> Set db = Nothing
>> >> >> >> Me.lstOnSite.Requery
>> >> >> >> Me.lstTruckInventory.Requery
>> >> >> >> <snip>
>> >> >> >>
>> >> >> >> So, instead of having the data deleted from tblWorkOrderDetails
>> >> >> >> I would like the field dtmDateIn to be filled in with the date
>> >> >> >> from this form, frmTruckSite and the field txtDate.
>> >> >> >>
>> >> >> >> I'm sure it can be done, I just have no clue how.
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >> Thanks for taking the time!
>> >> >> >>
>> >> >> >> CJ
>> >> >> >> ---------------------------------------------------------
>> >> >> >> Know thyself, know thy limits....know thy newsgroups!
>> >> >> >
>> >> >> >
>> >> >>
>> >>
>>