Print record # of times based on # in a field 4

[CJTyo]

I'm developing my first really big DB that requires actual PROGRAMMING, so I'm having to learn a lot on the fly and the TEK-TIPS forums have been a lifesaver! So... I've searched high and low through the Report forum and can't quite find an answer to this, so here's my first question to the experts on TEK-TIPS.

I have a report that simply prints continuous records. No problem. One of the fields is a quantity field that is generally a 1. Every now and again it's a 2 - and this is where my question comes from. If that quantity field is 2 (or 3 or any number) I need to have the record print that many times AND, just so it's not too easy, I need to have the Account# get a -A or -B or -C based on whether it's the 1st, 2nd, 3rd, etc. instance of the record.

This is an example of what I have now:

Acct# 0001
ManID -- Qty
1 -- 1

Acct# 0002
ManID -- Qty
2 -- 2

What I need for it to do is PRINT like this (but keep the STORED QUANTITY for the Acct as a 2):

Acct# 0001
ManID -- Qty
1 -- 1

Acct# 0002-A
ManID -- Qty
1 -- 1

Acct# 0002-B
1 -- 1

I can't split the Acct into an "A" and "B" because they might receive 8 different manuals and only ONE of them twice.

I appreciate any thoughts.

Thanks,
C

 

[PaulBricker]

Well the only way to do this is to create a recordsource for your Report that already performs what you want. A report is just a mirror with very limited abilities. Here's what you could do. First you would have to create a temporary table, I'll call tempTable. tempTable will have all the same fields as your main Table. Then you would have to create Delete query with this SQL.

Delete tempTable.*
From tempTable;

In the Click event for a button on a form you could put this (I'll call your main table mainTable. Change names where appropriate). This assumes that Quantity is a number that we can use to do the looping.

DoCmd.OpenQuery "TheDeleteQuery"
Dim rst as DAO.Recordset, rst2 as DAO.Recordset
Dim i as Integer
Set rst = Currentdb.OpenRecordset("mainTable", dbOpenDyanset)
Set rst2 = Currentdb.OpenRecordset("tempTable", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
If rst!Quantity = 1 Then
rst2.AddNew
rst2!Acct# = rst!Acct#
rst2!Quantity = rst!Quantity
rst2.Update
End If
If rst!Quantity >1 Then
For i = 1 To rst!Quantity
rst2.AddNew
rst2!Acct# = rst!Acct# & Chr((64 + i )'this gives you A, B, etc.
rst2!Quantity = 1 - 1 (unsure about the value you want here)
Next i
rst.MoveNext
Loop
DoCmd.OpenReport "YourReportHere", acViewPreview

You would set your Recordsource for the report to tempTable. I wasn't entirely sure what you wanted for a quantity for each record if the quantity in the main table was greater than 1 but it shouldn't be hard to work out.

Look it over and see how it goes.

Paul

 

[PaulBricker]

I've cleaned up this code a little (it was getting late last night). This assumes that there will always be a quantity of at least 1. If that's not the case, that record will not be added to the temp table.

DoCmd.OpenQuery "TheDeleteQuery"
Dim rst as DAO.Recordset, rst2 as DAO.Recordset
Dim i as Integer
Set rst = Currentdb.OpenRecordset("mainTable", dbOpenDyanset)
Set rst2 = Currentdb.OpenRecordset("tempTable", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
If rst!Quantity = 1 Then
rst2.AddNew
rst2!Acct# = rst!Acct#
rst2!Quantity = rst!Quantity
rst2.Update
Else
For i = 1 To rst!Quantity
rst2.AddNew
rst2!Acct# = rst!Acct# & Chr((64 + i )'this gives you A, B, etc.
rst2!Quantity = 1 - 1 (unsure about the value you want here)
Next i
End If
rst.MoveNext
Loop
DoCmd.OpenReport "YourReportHere", acViewPreview

Paul

 

[CosmoKramer]

You should be able to do this without using a temporary table. In an earlier thread (thread703-364690), I detailed how to print labels per record based on a quantity field. If that quantity was 5, then 5 labels would be generated for that item. Combine that with Paul's method of appending a letter if the quantity is 2 or more, and this can be done in a query.

 

[PaulBricker]

Sweet. Then to append the suffix to the end of the account number you would use
MyAccountNumber:Acct# & Chr((64 + [UnjoinedNumberField]))

Paul

 

[CosmoKramer]

That's right.

And, like you said in your original post; if you only want the letters to appear for quantities of 2 or more, then try something like this:

MyAccountNumber:IIf([Qty] > 1,[Acct#] & Chr(64 + [UnjoinedNumberField]),[Acct#])

 

[CJTyo]

You guys are awesome! Thanks.

Cosmo, I'm already using your excellent label code. ;-) I'd been considering whether I could try to mess with it and use it for this same purpose... just hadn't actually tried that yet.

I really appreciate your time and input. This is by far the most helpful technical message board system I've found. With the most selfless, considerate, and dedicated techies around!

Thanks again,
C

 

[Russie]

PaulBricker

I'm about to start creating quite a few reports, the difference this time being that I'm writing my database exclusively without the use of Queries or Macros. I've previously based my reports on queries.

Is the only way to create a record set for a report to populate an ADO recordset and read the ADO recordset to a table or is there any other way that a report can be built on a purely coded recordset and called by the report when needed?

Thanks for anything

 

[PaulBricker]

Russie, I don't work with ADO much (actually only when I'm trying to learn it do I work with it)so I can't really speak to populating an ADO recordset for a Report. But as far as I know, you can use an SQL SELECT statement to create your Report. Now whether you can do that in ADO, I don't know. Someone else will have to answer to that.

Paul

 

[mikey69]

At the risk of being a bore, may I say that "Access 2002 Desktop Developers Handbook, by Litwin Getz and Gunderloy has been an invaluable source of info on ADO and DAO differences. It has paralel chapters describing how to use both environments. But like you, Paul, I've mainly avoided ADO. Guess I'll carry on like this as long as Access suports DAO - which 2002 does.