p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Line Item Numbers needed (http://p2p.wrox.com/showthread.php?t=42208)

Bob Hansen May 6th, 2006 01:19 AM

Line Item Numbers needed
Using a sub form to add line items to an order, want a line item number as a reference. The table for the subform has a field to store the value. Right now the line item is being entered manually for each line. How to assign this value automatically?

Hmmmm, maybe keep an invisible counter on the main form, start at 1, and increment every a record is saved in the subform? Then when new line is entered, get that value, add one and put result into field for line item, and update value on parent form? Would need to update Parent form only when sub form record is actually saved?

Yes, I think I may have just solved my own problem. Add a field to table for parent form that is LineItemCount.Put an invisible text box on parent that is bound to the LineItemCount. Will also need to find a way with handling deleting records in subform. Want to keep original line item numbers, so this should be OK, deleted records will leave gaps, but can still sort by LineItem number.

Thanks for listening.
I am still open to a known working method if you can share that with us.......

JAnthony May 16th, 2006 02:21 PM

Here is a way I've handled this in the past with this Function routine.....

    Dim strSS As String
    Dim ItmNum As Integer
    Dim db As Database
    Dim recS As Recordset

    strSS = "SELECT [" & strTable & "].LineNumber " & _
            "FROM [" & strTable & "] " & _
            "ORDER BY [" & strTable & "].LineNumber DESC"

    Set db = CurrentDb()
    Set recS = db.OpenRecordset(strSS, dbOpenDynaset)

    If recS.RecordCount > 0 Then
        With recS
            ItmNum = .Fields("LineNumber")
        End With
        ItmNum = 0
    End If

    ItmNum = ItmNum + 1

    LineNumbering = ItmNum


All times are GMT -4. The time now is 10:10 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.