Assigning records from one table to another

[Design by Sue]

Database has a form to add a new item. This info is stored in the Item TBL. There is also a table that lists available UPC codes called UPC TBL. When a user enters a new item a dialog box will give them the choice to add a UPC code for that item. Clicking no - nothing changes and user return to the original form (no brainer) But if they click yes what I need to happen is:

1. The next available UPC code in the UPC TBL is entered into the Item TBL field for UPC.
2. The UPC TBL is updated to remove the assigned UPC, leaving it ready for the next assignment.
3. - and for another day - I need to create a message when the UPC table is empty because all UPC's have been assigned

Help - any thoughts on how to accomplish this??

Sue

 

[jdraw]

Here's a video tutorial that I have used to show or remove things.
It's explained well in the video, and may be helpful in removing a code from your available codes list. Whether or not you need 2 tables-- I'm not sure. Watch the video and see if iy applies. Also see if 1 table would do the job.

http://www.datapigtechnologies.com/flashfiles/listboxtrick.html

 

[Geotch]

Sue, not knowing Vba in my early days I did this using several queries and a macro.

My UPC code table would have the following fields. Id-auto number , UPC, used, date used.

First query. Get max Id number from above table where date is null.

2nd query match the first query Id field which is only one record back to UPC table to pick up the actual UPC code with the largest id and where used date is null. Use the make table query to make a temp table with the info.

Use that temp table to run an update into the table that needs the UPC code. Then run an update back to the UPC table to update date used and the used field.

Confused? If so I could send you a sample. Once you see it should make sense. It's also the non programmers version of a loop statement with a few changes.

 

[Design by Sue]

jdraw - great video - I can get some of what I need from that (ie using a column to show used - like your deleted column - that would make it possible for the user to "unassign" a UPC code in the event they made an error)
Geotch - thanks - I am a bit confused, but maybe with the help from the jdraw video and your post I hope I can get this done. If possible to send me a sample that would be great.

Sue

Edit: Geotch - I found http://office.microsoft.com/en-us/a...m-values-in-a-group-or-field-HA010096437.aspx which seems to explain your method - and I have gotten the first query to work - so I am on my way. I will post back if successful or I have questions. Thanks to both of you - both posts have been very helpful!

 

[Design by Sue]

Geotch - or any other - how do I create a temp table - I have been searching but can't figure out how to. How do I use the "make table" query results a temporary table?

 

[Design by Sue]

Making some headway on this - found that I don't need an actual temporary table as a created on will be overwritten the next time the query is run (or so I have read) I have completed the steps to where the table is created/updated by the 3rd query. This is the "temporary" table. But when I run the 3rd query from the button I created, it does not run the first 2 to get the info. (hope that makes sense) Query 3 is based on query 2 which is based on query 1 - how do I get them to run and give me the final results??

Thanks
Sue

 

[Design by Sue]

Got it worked out - could not use the method Geotch suggested because the all of the queries would not run from a button on the form. Instead I did a query that sorts on the autonumber field ascending, and used a return of 1 and a criteria of false - that gave the first record in the table not already assigned - the one I want and displays the 2 fields I need to update in my table. - posting this in case anyone else finds themselves in this quandary.

 

[Geotch]

Glad you got it to work. The make table query will create a table for you. Test it out, comes in handy every so often.