Originally Posted by jom1918
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 . . .
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")
if not .eof then
GetNextCode = !Code
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.