From a form to a new table

[rikklaney1]

Ok. So I have created a form that lists records in a specific order that the user can change. After the user has arranged these records I need to save them to a new table in the new order. There are 40 records on the page and 6 fields per record. Any suggestions as to how to best accomplish this?

 

[Frothingslosh]

In access, the order data is stored in a table literally does NOT matter, and Access does not include anything to do that. If it is absolutely imperetive that your data be displayed in a certain order and you don't already have something you can use for that purpose (like a Date/Time field), then you need to include a special field for sorting. Best bet would be a numeric field.

 

[rikklaney1]

Actually in this case it sort of does matter. The records are work order numbers that are imported in the order they are to be run. The supervisor pulls up a set of records based on what his first order of the day is. Sometimes he needs to change the order that things are run in and then save that changed order to another table. So the form is basically an interim step for him to see what he is supposed to do then change it to what he is actually going to do in another table.

 

[Frothingslosh]

Well, the fact remains that Access literally doesn't do that. Rather than constantly creating new tables (which creates an OBSCENE amount of bloat), you're far better off creating a 'processing order' field and working with that. You would just create a function for new orders that adds either a 1 (if it's the first order for a given day) or the highest number assigned for that day + 1 to the processing order.

For the supervisor's page, you just sort the displayed records by that processing order, and give him the ability to change the numbering. If you have him doing it graphically somehow, then use a function to renumber the field behind the scenes.

The problem is that there is NO guarantee that the records, when pulled from the underlying table, will be listed in the order in which they were entered. What you're trying to do is build data into the table structure itself, which you should never, ever do. This data needs to be included as a field.

 

[rikklaney1]

Hmmm. Let me think around the problem a bit. Maybe rather than to a new table just append a field to the original table with just a 1 through 30 to indicate what order he actually ran that day. Then sort that in a report to show the differences? Is that what you mean?

 

[rikklaney1]

So then I just need another field in the original table that stays blank until he updates. Now to figure out how to append 30 records on a button.

 

[Frothingslosh]

Hmmm. Let me think around the problem a bit. Maybe rather than to a new table just append a field to the original table with just a 1 through 30 to indicate what order he actually ran that day. Then sort that in a report to show the differences? Is that what you mean?

There are a number of ways you could do this, depending on what precisely you want done. The easiest would simply be either a number field if you want the supervisor to just enumerate what sequence he used, or a timestamp field if you want to have him actually update the database as each order is processed. (Scanning a barcode with the order number on it when the order is complete and using that to timestamp the order works too, assuming you have the appropriate toys.)

So then I just need another field in the original table that stays blank until he updates. Now to figure out how to append 30 records on a button.

While it's perfectly possible to fill out a field in each of a number of records in sequence with one button click, that doesn't sound what you really want to do here. It sounds like you want to track the order in which these orders are processed, and that will require some sort of user input.

 

[rikklaney1]

So here's what I've got so far. The table he pulls records from gets overwritten every couple days and he would like to keep a record of what he did each day. So back to the original idea of "table -> form -> new table" He opens a form, it asks for his first serial number. it then populates in the order listed in the orders table. He changes the order to how he is actually going to run for the day. And needs to save the records to a new table. I tried this....

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("select * from Daily")
rec.AddNew
rec("Line Position") = Me.Text522
rec("Model") = Me.Listrs1
rec("serial") = Me.Listrs2
rec("ctl") = Me.Listrs3
rec("Hvac") = Me.Listrs4
rec("Run Date") = Me.Text561
rec("first") = Me.Shift
rec.Update
Set rec = Nothing
Set db = Nothing

Set db = CurrentDb
Set rec = db.OpenRecordset("select * from Daily")
rec.AddNew
rec("Line Position") = Me.Text524
rec("Model") = Me.ListST71
rec("serial") = Me.ListST72
rec("ctl") = Me.ListST73
rec("Hvac") = Me.ListST74
rec("Run Date") = Me.Text561
rec("first") = Me.Shift
rec.Update
Set rec = Nothing
Set db = Nothing



Which partially works. The fields pulling from text boxes update correctly but the ones in red are list boxes and they are not updating. Oh and btw Frothingslosh I just realized that you are the same person who answered the first question I asked when I joined this forum. Thank you very much for your help! I might actually be learning something here. lol

 

[jdraw]

I think it might be helpful if you told us the whole story of those orders/workorders.
I agree with frothingslosh that you DO NOT WANT TO BE CREATING NEW TABLES.

When the records are imported, you could add a field for sequencing incrementally. And if the supervisor wants to change the sequence he could do so. Your sequencing field could be a double datatype and populated with sequential integers. The supervisor, using an appropriate form to review the schedule, could make changes. The double would allow decimals so 5.11 could now be sorted before 5.12 or 6 (decimals makes for easy inserts). But there may be more to the order/work order over the course of a week (or some time period). Things like who worked on the Order, for How long and when etc. Sorting is done via a Query (which could be the recordsource of a form).

Knowing the "day in the life of a work order" details could help putting the pieces together to match your business needs.

Good luck with your project.

 

[rikklaney1]

Let me try to explain. We get a list emailed to us about once a week fro what we are going to run. Each order has 4 relevant fields. Model, serial, CTL, and HVAC. We get this as an Excel spreadsheet laid out something like this..

Monday Tuesday Wednesday Thursday Friday
M,S,C,H M,S,C,H M,S,C,H M,S,C,H M,S,C,H
M,S,C,H M,S,C,H M,S,C,H M,S,C,H M,S,C,H


Monday Tuesday Wednesday Thursday Friday
M,S,C,H M,S,C,H M,S,C,H M,S,C,H M,S,C,H
M,S,C,H M,S,C,H M,S,C,H M,S,C,H M,S,C,H


There are 30 items per day so I made a query to import from Excel one section at a time and set an id number so that I can get them in order. My new table, "Schedule" looks like this.

ID M,S,C,H
ID M,S,C,H
ID M,S,C,H
ID M,S,C,H


So I now have table with 5 fields and many records rather than many fields and fewer records. Now the supervisor opens a form and it immediately asks him for the first order he will work on. That will be (S). The form looks something like this...

Station Model Serial Ctl Hvac ID
Station Model Serial Ctl Hvac ID
Station Model Serial Ctl Hvac ID

Station is just a text box with a value that will be added to the next step, Model,serial,CTL,and HVAC are listboxes and id is a text box. This all populates from a query that puts the id number in the first ID field. The supervisor can change any id field and the ones after it will repopulate (There's a whole lot of vba doing this but I'm kinda new at this and If I can find a way that works I use it). So now that I've explained all that I've forgotten my original question. LOL. My VBA of...

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("select * from Daily")
rec.AddNew
rec("Line Position") = Me.Text522
rec("Model") = Me.Listrs1
rec("serial") = Me.Listrs2
rec("ctl") = Me.Listrs3
rec("Hvac") = Me.Listrs4
rec("Run Date") = Me.Text561
rec("first") = Me.Shift
rec.Update
Set rec = Nothing
Set db = Nothing


updates my text boxes into the correct fields as new records in a table but my list boxes do not. The new table will be the "historical record" of what was run and when since all the other tables actually delete records before adding new ones.

 

[jdraw]

Hmm. Still not sure readers will understand your situation.

We get a list emailed to us about once a week fro what we are going to run.

My interpretation of this is you are going to build or service something.
My guess is you have a repeatable process to build or service that something.
Model, serial , ctl and HVAC must represent something.

I'm trying to get you to tell us in plain English what all this represents.

Line position makes me think of an assembly line, is that what it means in this post?
We really have to understand the problem/issue/opportunity before we can offer assistance/suggestions to database structure and vba coding.

Where do the listboxes fit in?
You do realize that textboxes and listboxes are controls on forms.

 

[rikklaney1]

I got it figured out. Thanks guys.

 

[Frothingslosh]

Wow, I go away for a single holiday and everything fixes itself (with much help from jdraw).

I need more holidays!

 

[jdraw]

Amazing!!???
Could you please post the solution so others may benefit?

 

[rikklaney1]

Changed from this
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("select * from Daily")
rec.AddNew
rec("Line Position") = Me.Text522
rec("Model") = Me.Listrs1
rec("serial") = Me.Listrs2
rec("ctl") = Me.Listrs3
rec("Hvac") = Me.Listrs4
rec("Run Date") = Me.Text561
rec("first") = Me.Shift
rec.Update
Set rec = Nothing
Set db = Nothing



to this

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("select * from Daily Where Serial ='" & Me.rs2.Value & "'")
If (rec.EOF) Then
rec.AddNew
Else
rec.Edit
End If
rec("Line Position") = Me.Text522
rec("Model") = Me.listrs1.ItemData(varitem)
rec("serial") = Me.listrs2.ItemData(varitem)
rec("ctl") = Me.listrs3.ItemData(varitem)
rec("Hvac") = Me.listrs4.ItemData(varitem)
rec("Run Date") = Me.Text561
rec("first") = Me.Shift
rec.Update
Set rec = Nothing
Set db = Nothing


This solved the problem of the listbox not updating to the table and also made it look fro duplicate serial numbers and just modify it if it already exists in case the supervisor has to change the order during the day.