Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-07-2013, 06:09 PM   #1
jom1918
Newly Registered User
 
Join Date: Apr 2011
Posts: 30
Thanks: 6
Thanked 0 Times in 0 Posts
jom1918 is on a distinguished road
find data in one table and put in field of another table then update

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.

jom1918 is offline   Reply With Quote
Old 11-07-2013, 06:44 PM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,287 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Quote:
Originally Posted by jom1918 View Post
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
'   This function returns the next code, and deletes it from the table
'   so it can't be re-used.
   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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Reply

Tags
code , event procedure , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update a field in one table based on the status of a field in another table Dozza Tables 7 08-05-2013 06:07 AM
Update a table with a value field in ANOTHER table, but selecting unique value skbpillai Queries 1 12-21-2012 12:40 PM
Enter Data in Table Field to Update Others karl009 Tables 10 03-03-2010 01:23 PM
Auto update fields from one table based on a field in another table mobaligh Forms 3 04-30-2007 01:15 PM
I need to update data in a table with information from a calculated field in a form boomdart Queries 4 10-12-2006 06:52 PM




All times are GMT -8. The time now is 06:33 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World