find data in one table and put in field of another table then update (1 Viewer)

jom1918

Registered User.
Local time
Today, 20:52
Joined
Apr 26, 2011
Messages
30
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.

I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".

Order Details Table Fields and conditions/criteria:
ID - primary key
DiscountID - only when the DiscountID = 92
Voucher - only populated when Discount ID = 92

Codes table Fields and conditions/criteria:
ID - primary key
code = text field with a code like "einstein01", "einstein02"
Allocated = False

Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.

Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked. Any help would really be appreciated.
 

MarkK

bit cruncher
Local time
Today, 02:52
Joined
Mar 17, 2004
Messages
8,178
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Yeah, absolutely, there's a way to do all that. What I would do first, is do the first thing, as a single subroutine, then do the next thing as a second subroutine, and so on. Debug each subroutine as you go, and then write a controlling subroutine that calls each of the sub-subroutines in the correct order, passing in the correct parameters.

So the first step is what? Find the next unallocated code. So . . .
Code:
Public Function GetNextCode as Long
[COLOR="Green"]'   This function returns the next code, and deletes it from the table
'   so it can't be re-used.[/COLOR]
   dim rst as dao.recordset
   set rst = currentdb.openrecordset("SELECT Code FROM Codes")
   with rst
      if not .eof then 
         GetNextCode = !Code
         .delete
      end if
      .close
   end with
end function
So now, debug that routine, which has a very simple, manageable purpose. And each step is like that, break it down to one or two simple jobs, and write a subroutine to solve it. Post back if you have more specific questions.

hth
 

Users who are viewing this thread

Top Bottom