populate data from a a list to a table.

W

Will G

Hello, i hope someone has answer to this.

i have a list box on a form and from this i am creating a history table. the
list box has a row source a query that contains more column then what's
showing on the list. how can i append the rest of this column to this new
table of history, since i only have couple of items less on the list compared
to the query.

i could append what i have in the list using this:
Dim varItem As Variant
Dim prm_sched As Control
Dim DBS As Database
Dim rst As Recordset


Set prm_sched = Me![sched]

If prm_sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")

'run through all selected items
For Each varItem In prm_sched.ItemsSelected
rst.AddNew
rst!OrderNo = prm_sched.Column(0)
rst!RequiredDate = prm_sched.Column(1)
rst!CustomerID = prm_sched.Column(2)
rst!ModelNumber = prm_sched.Column(3)
rst!Description = prm_sched.Column(4)
rst!Comment = prm_sched.Column(6)
rst!OrderQty = prm_sched.Column(5)
rst.update
Next

but i want to append the rest of the columns in the query that list(sched)
row sources from. how can i do that
 
J

Jeff Boyce

Will

I'm not sure I'm following...

If you have a listbox with an ID field and several other columns, why would
you need to store anything besides the ID field? Storing all those other
columns would just create duplication, take up space, and risk the integrity
of your data. What happens if one of the records in the source of the
listbox changes, but the written-to-history record is NOT changed? Which
one is correct?!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
W

Will G

thanks for the response,
i am not an expert in this stuff, but what i really want to do, to be a
little more clear is to create a history of every PO or items selected from
the list. this way, if i go back, i should be able to reprint old reports. my
history table contains the first 5 items thats shown in the list, but the
other items that are not showing, how can i include that in this new table.
let me be clear that the list comes from a query that is a combination of two
table, one thats permanent and other one that changes constantly, and that is
why i have to create a table history that contain all fields from both table
for storage. but how can i go about that, i already have the history table,
and i have the code that i have in the previous post. i tried this and it
works, whatever columns i have listed there, is stored in the history table,
only that i dont get the other fields that are not listed. i hope this makes
what i want to do clear. thanks again for your help
--
need help


Jeff Boyce said:
Will

I'm not sure I'm following...

If you have a listbox with an ID field and several other columns, why would
you need to store anything besides the ID field? Storing all those other
columns would just create duplication, take up space, and risk the integrity
of your data. What happens if one of the records in the source of the
listbox changes, but the written-to-history record is NOT changed? Which
one is correct?!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Will G said:
Hello, i hope someone has answer to this.

i have a list box on a form and from this i am creating a history table.
the
list box has a row source a query that contains more column then what's
showing on the list. how can i append the rest of this column to this new
table of history, since i only have couple of items less on the list
compared
to the query.

i could append what i have in the list using this:
Dim varItem As Variant
Dim prm_sched As Control
Dim DBS As Database
Dim rst As Recordset


Set prm_sched = Me![sched]

If prm_sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")

'run through all selected items
For Each varItem In prm_sched.ItemsSelected
rst.AddNew
rst!OrderNo = prm_sched.Column(0)
rst!RequiredDate = prm_sched.Column(1)
rst!CustomerID = prm_sched.Column(2)
rst!ModelNumber = prm_sched.Column(3)
rst!Description = prm_sched.Column(4)
rst!Comment = prm_sched.Column(6)
rst!OrderQty = prm_sched.Column(5)
rst.update
Next

but i want to append the rest of the columns in the query that list(sched)
row sources from. how can i do that
Click to expand...
Click to expand...
 
J

Jeff Boyce

Again, I may be misunderstanding, but it sounds like you are describing
storing more fields in your history table than you may need to. Are the
data elements you use in creating a report available already in other
tables? If so, you probably do not need to store them a second time in your
history table. Storing a pointer (an ID field value) that lets you requery
those values should be sufficient.

Or am I totally missing your point?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
W

Will G

the list box is made of a query that combines two tables....table1....brings
in field1, field2, field3 and table2....brings in field4, field5 and
field6...this are all different fields.......history table has...field1,
field2, field3, field4, field5, field6........and note that table2 is a table
that is always changing.........can explain how can i direct the pointer to
this...with the said above...thanks again.....i am new to this....
--
need help


Jeff Boyce said:
Again, I may be misunderstanding, but it sounds like you are describing
storing more fields in your history table than you may need to. Are the
data elements you use in creating a report available already in other
tables? If so, you probably do not need to store them a second time in your
history table. Storing a pointer (an ID field value) that lets you requery
those values should be sufficient.

Or am I totally missing your point?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
J

Jeff Boyce

I guess it would help if you provided a more concrete example. I'm having
trouble visualizing what you are describing as "field1, field2, ..." Can
you give example data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Will G said:
the list box is made of a query that combines two
tables....table1....brings
in field1, field2, field3 and table2....brings in field4, field5 and
field6...this are all different fields.......history table has...field1,
field2, field3, field4, field5, field6........and note that table2 is a
table
that is always changing.........can explain how can i direct the pointer
to
this...with the said above...thanks again.....i am new to this....
Click to expand...
 
W

Will G

table: linkedTable
fields: OrderNo | RequiredDate | ProductID | CustomerID | Desc
46601 4/4/07 A331500M LSM ANY

table: MainTable
fields: ModelNumber | Spring | Border

query: qrMainquery
fields: OrderNo RequiredDate ProductID CustomerID Desc ModelNumber Spring
Border
 
J

Jeff Boyce

Will

Thanks, the data example helped.

I don't see anything in common between the two tables. How is the MainTable
supposed to connect to the linkedTable?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
W

Will G

thanks for your help, but i got it. i guess i just include it in the list
with width=0 for those that i dont want to show in the list...and it works
fine...thanks again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

populate data from a list to a new table 2
no duplicate record 6
Combo Box with a event procedure 1
Append Crosstab Query to Existing Table 0
Problem writing Outlook data to Access table 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Trying to tableize emails from user folders in outlook 1

Top