multiple serial numbers on one line

[signallt03]

I am trying to great a form that will print product names on one line then print all of the serial numbers for that product on the same line. then go to the next line and start with the next product.

Any ideas?

 

[scriverb]

This can be done by creating a Function that reads the secondary SEQ# table and creates a comma deliminated string of all the sequence numbers. Problem is there may be more sequence numbers than a line of printing can handle. Why the requirement to print everything on one line? How many sequence numbers could there possibly be for each product? Post back with the table and field names and I can give you some code examples to work with.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[signallt03]

Printing onto multiple lines is a big possibility since some products could have as many as 15 serial numbers. The reason I want them to display horrizontally is because they have to fit into a specific format. I had to create an army form in access so that it would fulfill the army requirments.

Table is tbl_LineNumber
Fields NSN (model number), SN, QTY, HR_Holder

thanks for the help

 

[scriverb]

Copy and paste this query SQL into a new queries SQL window. Save the query and name it. Update any table and field names as necessary.

Select A.[i]ProductID[/i], A.ProductName, SerialString() as Serial_Numbers FROM [i]yourProductsTableName[/i] as A;

Copy and paste this VBA function into a database module:

Public Function SerialString(vID As Long)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vSerialString As String
Set db = CurrentDb
Set rs = db.OpenRecordset("[i]tbl_LineNumber[/i]", dbOpenDynaset)
rs.FindFirst "[NSN] = " & vID
Do
    If Not rs.NoMatch Then
        vSerialString = rs("SN") & ","
    End If
    rs.FindNext "[NSN] = " & vID
Loop Until rs.EOF
SerialString = IIf(Not IsNull(vSerialString), Left(vSerialString, Len(vSerialString) - 1), "No Matching Serial Numbers")
rs.Close
db.Close
End Function

Run the query and you should get your serial string deliminated by commas.

Post back with any questions. I have not been able to test this code so post back any problems and we can fix them.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[CosmoKramer]

Check out faq703-3587 for another method.....

Hoc nomen meum verum non est.

 

[signallt03]

i got it to put the serial numbers in one box. I used CosmoKramer's post. Scriverb's kept saying "wrong number of arguments used with function in query expression 'SerialString()"
The problem i have now it if i have too many serial numbers they do not all show up. If i change cangrow to yes then it messes up the format of my form and the boxes I have. Is there some way to stop the string at a certain number and then have it start on another box?

thanks for your help
signallt03

 

[scriverb]

Sorry about that. I left out the ID from the function call:

Select A.ProductID, A.ProductName, SerialString([red]A.ProductID[/red]) as Serial_Numbers FROM yourProductsTableName as A;

As far as running out of room yes this is a problem when you start to string together fields horizontally in a big string and don't set a limit. As long as al of your controls on the report are on the same starting line as the control for this string, you can set the Can Grow and only this control will grow and the others will all be at the time of the Detail Section. This will cause the Detail section to have different size lines but you are going to have different size Detail Section rows anyway if you try using extra controls for the overflow.

I don't really see anything wrong with that look in a report if it in fact represents all of the products associated with the ID.

Post back with your thoughts.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[signallt03]

the problem is the Army has a specific form that should be used for this. It has to look a certain way in order for it to be okay to use. I can email a copy of the database if you would like so you can see what I am looking for.

 

[scriverb]

Sure, see my email in my profile. Please identify the report, query name, tables, etc.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

I have received your database and now see why you were having problems. You have tried to structure your Detail Section within vertical lines betweeen your controls. If the Detail section grows because the control is set to Can Grow then the formatting of your report looks really bad.

This is what I have done.

1. Modified the Function to string together Serial Numbers and put a counter of the number of Serial Numbers on the front of the string.(i.e. 21-12345, 23456, 33456, etc.) This example tells me in the report that there are 21 serial numbers in the string. I have also included a carriage return left after 12 serial numbers to control where the extra lines start.
2. The query is modified to group by all items and call the serial number function to return this string of serial numbers.
3. The Serial Number control on the report uses the Mid$ function to strip off the "21-" at the beginning of the string.
4. Created an additional control with the entire string in it but made it invisible so that through code I could pick up the value 21 from the front of the string.
5. In the Detail section OnFormat event procedure I used the number of serial numbers to determine the height that was necessary for the Detail Section and the vertical lines to accommodate the number of extra rows that would be displayed in the Serial Number control as it grew due to the large string being returned.

Function Code:

Public Function SerialString(vID As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vSerialString As String, vCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_SN", dbOpenDynaset)
rs.FindFirst "[NSN] = '" & vID & "'"
Do
    If Not rs.NoMatch Then
        vSerialString = vSerialString & rs("SERIAL_NUM") & ","
        vCount = vCount + 1
        If vCount Mod 12 = 0 Then
            vSerialString = vSerialString & vbCrLf
        End If
    End If
    rs.FindNext "[NSN] = '" & vID & "'"
Loop Until rs.EOF Or rs.NoMatch
SerialString = IIf(Not IsNull(vSerialString), vCount & "-" & Left(vSerialString, Len(vSerialString) - 1), "No Matching Serial Numbers")
rs.Close
db.Close
End Function

Detail Section OnFormat Event Procedure:

Dim x As Integer
x = Mid$([SNString], 1, InStr(1, [SNString], "-") - 1)
If Reports!rptBob_Example.Section(0).Height < 500 Then
    Reports!rptBob_Example.Section(0).Height = 480 + (Fix(x / 12) + IIf(x Mod 12 > 0, 1, 0)) * 90
    Me![Line75].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line79].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line80].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line81].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line82].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line83].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line84].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line85].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line86].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line87].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line88].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line89].Height = Reports!rptBob_Example.Section(0).Height
    Me![Line76].Height = Reports!rptBob_Example.Section(0).Height
End If

These modification will all for varying number of lines in a single control as it grows and shrinks. The detail section and its vertical lines will shrink and grow with it.

Good luck with your project.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SCFROMDC]

Probably not where it should be posted, sorry, but I have this particular ANNOYING issue....I inherited an EPO server, with sql 7, the database is 12G, and I have no clue how to size it down, its allocated 12G, and if I start from scratch, its gonna be a 2 week journey through hell. I have 10000+ machines on the network.....anybody know how I can shrink it??? Ive read all the articles about how to shrink it, and tried everything I could find, but Im thinking the allocation cant be changed????
Im a sql wannabe, or maybe a dont-wannabe...please HELP!!!!!!! ( I have 175 MEG left as of wednesday night )
EPO 2.5.1
SERVER 2000
SQL 7

 

[scriverb]

This is an ACCESS forum and if it was an ACCESS database you would use the Compacting utility. But, unfortunately I don't have a clue how to help you with your problem. You can search the forum list and see if you can find a forum that better matches your software.

My suspicions are that there must be a similar utility that can be executed that compacts and removes the dead space in your system. But, I don't know specifically how to direct you to find it.

Sorry.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[signallt03]

scriverb thanks for your help.
the database is working great

thanks again
signallt03