Repost - Fill in value *Sandra Daigle List Box Edit

[CJ]

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.

 

[news.microsoft.com]

Change the code to:

strSql = "UPDATE tblWorkOrderDetails SET dtmDateIn = Date() Where_
[Work Order ID]=" & Me.txtWOID & " AND (" & strwhere & ");"

 

[CJ]

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!

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

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.

 

[Arvin Meyer [MVP]]

I'm not sure that I can help because I'm not really familiar with Sandra's
code. Kinda of hard to wrap my head around what you are trying to do. Would
you explain it more thoroughly? I'll give it a shot.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

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!

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

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.

 

[Steve Sanford]

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.)

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!

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

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.

 

[CJ]

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!

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.)

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!

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


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.

 

[Steve Sanford]

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.)

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!

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.)

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!
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


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.

 

[CJ]

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!

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.)

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!

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.)


:

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!
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


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.

 

[Steve Sanford]

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.)

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!

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.)

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
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.)


:

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!
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


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.

 

[CJ]

Absolutely fabulous!!!....it worked.

Thanks to both of you for helping me out with this.

Now, I should be able to sleep again.

--
Cheers!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

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.)

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!

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.)


:

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
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.)


:

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!
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


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.