Richard
asked on
access- sequential numbers
I have a database with groups such as charities etc.
I want to issue vouchers to them say in groups of 100 vouchers at a time. The vouchers would be printed by me presumably by Access reports as a batch.
Ideally whena voucher is redeemed I would be able to clear off the individual number of that voucher by a yes/no tickbox.
Ideally the vouchers would have a unique and sequenced ID.
I could do this with one group many vbouchers and have an Index for each voucher but would this mean I would have to create and print each voucher individually?
Sorry this is a little vague but any assistance welcome.
I want to issue vouchers to them say in groups of 100 vouchers at a time. The vouchers would be printed by me presumably by Access reports as a batch.
Ideally whena voucher is redeemed I would be able to clear off the individual number of that voucher by a yes/no tickbox.
Ideally the vouchers would have a unique and sequenced ID.
I could do this with one group many vbouchers and have an Index for each voucher but would this mean I would have to create and print each voucher individually?
Sorry this is a little vague but any assistance welcome.
ASKER
That sounds promising as regards printing.
However I am still at a theoretical stage as regards setting up the data.
OK, I have a group called "Local Charity" say. I can create a new voucher ID individually using Autonumber, say, but thats very long winded as I have to do each voucher manually.
However the advantage with giving each voucher its own record is that I can tick off when redeemed and whether its still valid and by whom redeemed etc.
However If I have to create 100 new records manually each time I have to print 100 vouchers this sounds like harder work than it should be.
Is there a batch way to create 100 unique new "voucher" records for each group and if so I would imagine there would be a chunk of VBA code behind it.
As you can see I am still trying to figure how to do this efficiently.
However I am still at a theoretical stage as regards setting up the data.
OK, I have a group called "Local Charity" say. I can create a new voucher ID individually using Autonumber, say, but thats very long winded as I have to do each voucher manually.
However the advantage with giving each voucher its own record is that I can tick off when redeemed and whether its still valid and by whom redeemed etc.
However If I have to create 100 new records manually each time I have to print 100 vouchers this sounds like harder work than it should be.
Is there a batch way to create 100 unique new "voucher" records for each group and if so I would imagine there would be a chunk of VBA code behind it.
As you can see I am still trying to figure how to do this efficiently.
ASKER
Ooooh, how about Append Table Query, would that do the trick to batch make records.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Looks v good.
Will test and report. Please allow a couple of days!
Thanks.
Will test and report. Please allow a couple of days!
Thanks.
ASKER
Private Sub Command397_Click()
Dim i As Integer
For i = 1 To 100
CurrentDb.Execute "INSERT INTO Clients(SeqNumber, ClientID) VALUES( " & i & "," & clientID & ")"
Next i
End Sub
Tried this and it doesnt recognize SeqNumber. Can you help?
Clients is my table for purpose of this test.
Dim i As Integer
For i = 1 To 100
CurrentDb.Execute "INSERT INTO Clients(SeqNumber, ClientID) VALUES( " & i & "," & clientID & ")"
Next i
End Sub
Tried this and it doesnt recognize SeqNumber. Can you help?
Clients is my table for purpose of this test.
The code Scott posted was just generic code.
You have to substitute your actual table and field names.
You have to substitute your actual table and field names.
ASKER
Private Sub Command397_Click()
Ok I have has success in inserting a value in the above where I have done
Dim i As Integer
"INSERT INTO Clients(ClientID)VALUES(36
this has inserted a record number 360 into Clients table
SOo then I tried:
CurrentDb.Execute "INSERT INTO Clients(ClientID)VALUES(i)
i = Me.clientOneMobile
(i is convenient textbox). i then put 360 into that textbox
error report :"too few parameters, expected 1."
any clues please?
Ok I have has success in inserting a value in the above where I have done
Dim i As Integer
"INSERT INTO Clients(ClientID)VALUES(36
this has inserted a record number 360 into Clients table
SOo then I tried:
CurrentDb.Execute "INSERT INTO Clients(ClientID)VALUES(i)
i = Me.clientOneMobile
(i is convenient textbox). i then put 360 into that textbox
error report :"too few parameters, expected 1."
any clues please?
ASKER
sorted with this: many thanks indeed
Private Sub Command397_Click()
Dim i As Integer
For i = Me.clientOneMobile To Me.clientTwoMobile
CurrentDb.Execute "INSERT INTO Clients(ClientID)VALUES(" & i & ")"
Next i
End Sub
Private Sub Command397_Click()
Dim i As Integer
For i = Me.clientOneMobile To Me.clientTwoMobile
CurrentDb.Execute "INSERT INTO Clients(ClientID)VALUES(" & i & ")"
Next i
End Sub
ASKER
inspirational and very motivating
You can print a report numerous times; if you want to print an distinct report for each "voucher", you could do something like this:
Dim i As Integer
For i =100 to 200
DoCmd.OpenReport "YourReport", , , "vouchernumber=" & i & " AND Customer=" & some_customer_value
Next
Of course, that is somewhat vague, but it would print the report for each "vouchernumber" for a specific customer.