Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

access- sequential numbers

Posted on 2013-01-23
10
474 Views
Last Modified: 2013-01-23
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.
0
Comment
Question by:topUKlawyer
  • 7
  • 2
10 Comments
 
LVL 84
ID: 38809657
Are you having trouble generating the sequential numbers, or are you having trouble printing them?

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.
0
 

Author Comment

by:topUKlawyer
ID: 38809733
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.
0
 

Author Comment

by:topUKlawyer
ID: 38809761
Ooooh, how about Append Table Query, would that do the trick to batch make records.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38809777
You could create new records like this:

Dim i As Integer

For i = 1 to 100
  Currentdb.Execute "INSERT INTO YourVoucherTable(SeqNumber, CustomerID) VALUES( " & i & "," & YourCustomerID & ")"
Next i

You could set the value of "i" to a user-defined value (perhaps a textbox on your form) like this:

For i = Me.txVoucherStart To Me.txVoucherEnd
  <insert records here>
Next  i

So if the user enters "200" and "300" respectively, the code would add 100 records starting at number 200 and ending at number 300.
0
 

Author Comment

by:topUKlawyer
ID: 38809817
Looks v good.

Will test and report. Please allow a couple of days!

Thanks.
0
 

Author Comment

by:topUKlawyer
ID: 38809929
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.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38810024
The code Scott posted was just generic code.

You have to substitute your actual table and field names.
0
 

Author Comment

by:topUKlawyer
ID: 38810397
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(360)"
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?
0
 

Author Comment

by:topUKlawyer
ID: 38810580
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
0
 

Author Closing Comment

by:topUKlawyer
ID: 38810589
inspirational and very motivating
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question