delicate problem 6

[christerhaard]

Hmm this will take time to explain. . . ok I have a database where i have a uniqe counter field which is the order number for lets say a computer. The counter is also the computers serial number. But a customer might want to buy 5 computers so instead of makin 5 orders for the same product I have a field that explains to the user that the customer want's 5 pcs of this product. But the problem is that all 5 computers will now get the same serial number both on the sticker in the back of the computer and in the database. Not so good if the customers wants a uniqe individual serial number.

So what I would like to achive is that based on the number of products in the "number of pcs field" I would like the serial number field in the report to get an extention and print ser# 521458-1 and ser# 521458-2 and ser# 521458-3 and so on. The number of print out's of the order sheet is the same as the value in the "number of pcs field".

I have tryed to find a page count field or such but I cant get it right. Please help.

/ Chris

By the way does anyone know how i can make the "number of pcs field" automatically print the same number of copies as the value of the "number of pcs field" based on the code below

DoCmd.OpenReport DocName, acViewPreview, "computer order filter"

 

[PaulBricker]

I'm getting a lot of miles out of this technique that CosmoKramer posted the other day. This should get you close. Start with this thread,
Thread703-371336

and then scroll down to CosmoKramers post and look for the thread he sends you to from there.

It's easy to follow and should do what you need.

Paul

 

[christerhaard]

Thanx Paul,

This does not help me because Im printing a entire page not a lable. It worked fine but the result is that it prints the order fields the number of times of the qty value. Not the entire page. . . . But thank you very much for trying to help me anyway.

Or can I use this command in an other way ???

Thanx again. . . .

/ Chris

 

[PaulBricker]

Maybe a little more detail on the structure of your Forms and Reports will help. Are you using a MainForm/Subform or MainReport/SubReport? That could make a difference.
You said:
So what I would like to achive is that based on the number of products in the "number of pcs field" I would like the serial number field in the report to get an extention and print ser# 521458-1 and ser# 521458-2 and ser# 521458-3 and so on.

One thing is enables you to do is append a suffix to the end of your Ser# very easily. Right below CosmoKramers' post, we worked out a method to add a suffix. We used A,B C..etc. You would just change it to 1, 2, 3...etc.

The number of print out's of the order sheet is the same as the value in the "number of pcs field".

It is the second sentence that I may be getting confused.
You may be able to pass the value in the "number of pcs field" to the PrintOut command but I'm still not clear on exactly what the final product should look like.

Paul

 

[christerhaard]

Hi Paul,

Thanks for all the help again. . .

The order form is a main form with the customer information (from the customer table) and a subform with unlimited article rows (this is where we build the computer out of parts) we get this from the article table. We can then print the "build sheet" make invoice, quotes and so on from this form.

I print out the form with:
DoCmd.OpenReport DocName, acNormal, "computer order filter"

The report then gets its data from the same tables and the Build sheet has all the parts specified for that specific computer order. We print this on a lable sheet so we get the serial number lable and other marking lables directly of the build sheet.

I will read more carefully what you wrote in the thread you sent me, and try to implement it.

Been developing this system on and of for almost 10 years. The latest work iv'e done has put us on the web with direct link to our database. Check out our web at

http://www.netstore.se

log in with username: MISC password: CSIM

I will get back to you when iv'e tried it. . .

/ Chris

 

[christerhaard]

Hi again Paul,

Cosmo and you post the code below, where do I impliment it?

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

I understand the principle but not where and how, my brain is not working properly at the moment I guess, he he eh. . .

/ chris

 

[PaulBricker]

Post it in a new field in the underlying query. The whole line goes on the Field line of the query.

MyModifiedSer#:IIf([Qty] > 1,[Ser#] & "-" & Chr(48 + [UnjoinedNumberField]),[Ser#])

I've modified it a little to reflect what you need. Chr(48) is 0 so if there are more than 1 similar part, it appends 1,2,3...n onto the Ser#.

Very nice website. This is where I work. http:\\

http://www.williston.com

Paul

 

[christerhaard]

Thanks for your time paul!!!

But I get an err when I try to implement it:

"you might have a operand whith out an operator" free translation from swedish version of access.

I pasted the code in the field like you said and changed the Ser# and Qty and UnjoinedNumberField to my field names. and I still get the err but not on the qty declaration but on the ser# field.

Sorry to bug you. . .

/Chris

 

[PaulBricker]

Chris, post the expression you entered.

Paul

 

[PaulBricker]

I realize it's probably not English, but post it exactly as you have it.

Paul

 

[christerhaard]

Hi again Paul,

MyModifiedOffertId:IIf([antal_produkter] > 1,[OffertId] & "-" & Chr(48 + [qty]),[offertid])

Just so we get it right, I have put this expression in the control source query of the report directly in the field box.

In SQL it looks like this (without the expression):

SELECT DISTINCTROW OFFERTER.[KUND-ID], KUNDER.[FÖRETAG / NAMN], KUNDER.PERSON, OFFERTSPECIFIKATION.OFFERTID, OFFERTER.OFFERT_NAMN, OFFERTSPECIFIKATION.ARTIKELID, ARTIKELREGISTER.PRODUKT_NAMN, OFFERTSPECIFIKATION.ANTAL, OFFERTER.UT_PRIS, OFFERTER.OFFERT_INFO, ARTIKELREGISTER.PRODUKT_BESKRIVNING, OFFERTER.ANTAL_PRODUKTER, OFFERTER.LEVERANS_DATUM, OFFERTER.GARANTI, OFFERTER.PPS, OFFERTER.SERIENUMMER, KATEGORI.ORDNING, OFFERTER.MÄRKNING, ANTAL.QTY
FROM ANTAL, KATEGORI INNER JOIN ((KUNDER INNER JOIN OFFERTER ON KUNDER.[KUND-ID] = OFFERTER.[KUND-ID]) INNER JOIN (ARTIKELREGISTER INNER JOIN OFFERTSPECIFIKATION ON ARTIKELREGISTER.ARTIKELID = OFFERTSPECIFIKATION.ARTIKELID) ON OFFERTER.OFFERTID = OFFERTSPECIFIKATION.OFFERTID) ON KATEGORI.KATEGORI_ID = ARTIKELREGISTER.KATEGORI_ID
ORDER BY KATEGORI.ORDNING;


Thanx again

/ Chris

 

[PaulBricker]

Chris, I haven't looked at all of it yet, but the first thing I see is you have OFFERTID in two tables, OFFERTSPECIFIKATION.OFFFERTID and OFFERTER.OFFERTID. What you may need to do is specify in the expression which Table to look in for OFFERTID.
If the Table is OFFERTER then it would look like this.

MyModifiedOffertId:IIf([antal_produkter] > 1,[OFFERTER.OffertId] & "-" & Chr(48 + [qty]),[OFFERTER.offertid])


Try that and see how it goes.

Paul

 

[christerhaard]

Hi Paul,

OK found it instead of ,[OFFERTER.OffertId]
I used ;[OFFERTER.OffertId]
The I got no err in the query but now it prints the order lines on the build sheet the same amount of times as there are values in the QTY table (same happend with cosmos expression) I entered 1-30 in the table. So If I have 10 products that are to be a computer it will print every item 30 times in a row. And nothing happens to the serial number at all.

Sorry again for my incompetence. . .

/ Chris

 

[christerhaard]

Hi Paul,

OK me big nerd. . . I figured out the serialnumber my misstake please FORGET IT. . . it displays 1234-c dont know why but never mind. Other problem still there.

/ Chris

 

[PaulBricker]

OK, I think what we might want to do is pass the value of
antal_produkter to the DoCmd.PrintOut Method.

Try this

DoCmd.OpenReport DocName, acViewPreview, "computer order filter"
Dim i as Integer
i = Forms!FormName!antal_produkter
DoCmd.PrintOut acPrintAll,,,,i

This will print the Report i number of times (I think that is what you want). Not sure I got the syntax for i correct. It might be & i


Try it and let me know if I'm even working on the right problem.

Paul

 

[christerhaard]

Hi Paul,

OK this does not work. . . I will try to explain. the serial number does what it's supposed to do. The print out does almost. The value i have in "antal_produkter" for ex. 4 will print 4 build sheets (four papers) but the rows of articles (products) are * 4 on the same paper.

What I would like is to have it print 4 copys of buildsheet and ser# 1234-1 / 1234-2 and so on But the serial number bit probably works fine.

This problem comes from the query and I'm starting to doubt that we can use this techniq to print entire documents. Maybe it only works on lables. When you make a lable report you put the lable design in the detail portion of the report. The same place I'm listing the articles (products).

Any suggestions ?! Thanx alot for the help. . . .

/ Chris

 

[CosmoKramer]

Chris,

Add a group header and footer to your report for your serial number field. In the Force New Page property of your group footer, set it to After Section.

This should cause each serial number to print on its own page.....

Let me know if this helps.....

 

[christerhaard]

Hi Cosmo,

The serial number is not the problem, it works fine.

The problem is like I explain above that I'm not printing lables. Simplified I need the field "Antal_Produkter" value to set the number of pages to print not lables.

Thanx for all the help HELP HELP !!!! . . . . .

/ Chris

 

[CosmoKramer]

Chris,

I understand that the serial number part is working fine. Let me make sure I understand what your problem is.

Say you have a quantity of 4 of serial number 1234. Do you want four pages to print for this one part (1234-1, 1234-2, 1234-3, 1234-4)??

If so, then doing what I said above will accomplish this. The grouping on serial number (which will be a distinct value) and forcing a new page after each one will give you one serial number per page.

Let me know if this isn't what you're looking for.....

 

[christerhaard]

Hi Cosmo,

No one page for every serial number like:

page 1 = 1234-1
page 2 = 1234-2
page 3 = 1234-3

and so on. . .

I didn't understand what you ment with the reply before. The problem for me is that I have a swedish version of access installed (stupid of me) so I could not figure out what you ment with "group header" & "footer" I have a "page head" and a "page foot" in my report now.

Please clearify for me where I add a group header and footer then I should be able to figure it out.

Thanks / Chris