list box send to history table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bigwillno2 - 24 Jul 2007 16:39 GMT

Hello, i hope someone could help me with this.

i have a multiselect list and as i select and click on a send button, i send
the items to a new table. this is my code. the if statement will do the IF
it already exist dont send to history table(ProdHistory), but somehow its not
working properly. it seems like it's only sending OrderNo which could be many
and if it already exist by the first selection, it's gonna flag. it seems
like it's not doing, OrderNo AND ModelNumber.....it's there anything wrong
with my code bellow. i also dont want it do nothing, if it flags that the
OrderNo AND ModelNumber exists.

strSQL = "(OrderNo=" & Me.sched.ItemData(itm) & ")"
strSQL = strSQL & " AND (ModelNumber = '" & Me.sched.Column(3, itm) & "')
"

Set db = CurrentDb
Set rst = db.OpenRecordset("ProdHistory")

'run through all selected items
For Each itm In Me.sched.ItemsSelected
If DCount("*", "ProdHistory", strSQL) = 0 Then
rst.AddNew
rst.Fields("OrderNo") = Me.sched.ItemData(itm)
rst.Fields("RequiredDate") = Me.sched.Column(1, itm)
rst.Fields("CustomerID") = Me.sched.Column(2, itm)
rst.Fields("ModelNumber") = Me.sched.Column(3, itm)
rst.Fields("Description") = Me.sched.Column(4, itm)
rst.Fields("OrderQty") = Me.sched.Column(5, itm)
rst.Fields("Comment") = Me.sched.Column(6, itm)
rst.Fields("Bfill") = Me.sched.Column(7, itm)
rst.Fields("Qfill") = Me.sched.Column(8, itm)
rst.Fields("Uphfill") = Me.sched.Column(9, itm)
rst.Fields("Ptfill") = Me.sched.Column(10, itm)
rst.Fields("Spring") = Me.sched.Column(11, itm)
rst.Fields("Label") = Me.sched.Column(12, itm)
rst.Fields("Size") = Me.sched.Column(13, itm)
rst.Fields("Needle") = Me.sched.Column(14, itm)
rst.Fields("Pattern") = Me.sched.Column(15, itm)
rst.Fields("Config") = Me.sched.Column(16, itm)
rst.Fields("Border") = Me.sched.Column(17, itm)
rst.Fields("FoamCore") = Me.sched.Column(19, itm)
rst.Fields("FoamEnc") = Me.sched.Column(20, itm)
rst.Fields("ProtoTypeNum") = Me.sched.Column(22, itm)
rst.Fields("ModelRoot") = Me.sched.Column(23, itm)
rst.Fields("Color") = Me.txtColor
rst.Fields("Type") = Me.cmbType
rst.Fields("ProductionDate") = Me.Combo68
rst.update

strSel = "'" & Me.sched.ItemData(itm) & "',"
Else
MsgBox "This bed has been scheduled with this Order, do want to
continue", vbYesNo
Me.Combo68.SetFocus
End If
Next

Reply to this Message

RBear3 - 24 Jul 2007 16:59 GMT

Why? The normal way to archive items in Access is to add a checkbox or a
date field that can be filled in to indicate and inactive item. Then, in
your forms, reports, and queries that should not see inactive items, you
simply filter out those items.

Rarely would you move records around from one table to another within the
same file in a properly designed relational database.

Hope that helps.

Signature

Hope that helps!

RBear3
.

> Hello, i hope someone could help me with this.
>
[quoted text clipped - 57 lines]
> End If
> Next

Reply to this Message

bigwillno2 - 24 Jul 2007 17:18 GMT

the situation is that i am doing something else with that list. it's primary
function is to create couple of report. and it needs to be in list form and
the part of sending it to a table works perfectly only that i dont want to
have duplicate records in case that the same Order is sent twice. and other
suggestion.
thanks for the quick answer.

>Why? The normal way to archive items in Access is to add a checkbox or a
>date field that can be filled in to indicate and inactive item. Then, in
[quoted text clipped - 11 lines]
>> End If
>> Next

Reply to this Message

RBear3 - 24 Jul 2007 17:36 GMT

Still not sure why it needs to be in another table. A report can list out
records any way you'd like.

Signature

Hope that helps!

RBear3
.

> the situation is that i am doing something else with that list. it's
> primary
[quoted text clipped - 21 lines]
>>> End If
>>> Next

Reply to this Message

bigwillno2 - 24 Jul 2007 18:04 GMT

i prob wasnt clear enough where the information is coming from...it's being
imported from an external table of different system. i am really comparing
what i have stored in a table with what's coming and from external data and
put the in a list. now, any selection that make from this list, i send to a
total different table called history and from here, i want to be able to
print similar reports. note that i get external data everyday....therefore,
data is never the same in this list. does that help.

>Still not sure why it needs to be in another table. A report can list out
>records any way you'd like.
[quoted text clipped - 4 lines]
>>>> End If
>>>> Next

Reply to this Message