Line number counter to always start with 1 per new quote? 2

[angelandgreg]

Hello,
i've seen similiar posts but most seem to be just to add 1 from some existing field, etc ...

i need help with setting a restart on counter whenever the QuoteID is unique. The counter is the LineNumber field. It is blank.


Basically a line number should exist and always restart with 1 for every unique QuoteID.
This is to make it easier to reference it when the Customer Service rep is talking to the customer.
They would just say on LineNumber10 of QuoteID 12345 this and that and the other ...

If they just use the current ItemID field, at some point it may say 789008 and that's just more difficult when trying to reference something while talking with a customer.

And it should have line numbers per order (quote). So I need help with what I have partially got thus far ...

I have an Event on a field called Item. When the information in this field is entered I would like the LineNumber to start "counting" from 1.


Private Sub Item_AfterUpdate()
If IsNull(LineItem) Then
LineItem = "' and [QuoteID]=#" & Me.QuoteID & "#"), 0)) + 1)
End If
End Sub


and of course it's erroring ... can someone help me clean this up?


For every QuoteID the LineItem should restart with 1 and increment until the next QuoteID which then it should restart the LineItem with 1 and continue on.


i hope i made sense. I have search various posts. Autonumber, counter, increment, etc ...

 

[PHV]

Have a look at the DCount function (or Nz and DMax)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[angelandgreg]

from what though?

LineNumber field is Null until the Item field is entered with data and the AfterUpdate Event on the Item field is what should update the LineNumber to 1 on the first entry of the Item per the QuoteID and so on and restart with 1 when there's another QuoteID.


??

 

[PHV]

A starting point:
Me!LineNumber = 1 + DCount("*", "yourTable", "QuoteID=" & Me!QuoteID)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[angelandgreg]

Well, ok, now I'm getting interesting numbers ...

It started with 2 as the LineNumber for QuoteID 56 instead of one for my 1st item.

Then I went to another quote, QuoteID 51 and instead of restarting with 1 it started with 4.
Then I went to the next Item of the same quote and it put 4 again and that doesn't work.


QuoteID ItemID LineNumber Item
56 70 2 W2002RDGL
51 66 W1001
51 65 W5010
51 64 4 W5010


it should be:
QuoteID ItemID LineNumber Item
56 70 1 W2002RDGL
51 66 3 W1001
51 65 2 W5010
51 64 1 W5010


how do i make it do that?

 

[PHV]

What is YOUR actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[angelandgreg]

The AfterUpdate Event is on the Item field and is as such:

Private Sub Item_AfterUpdate()
If IsNull(LineNumber) Then
Me!LineNumber = 1 + DCount("*", "CustomQuoteDetails", "QuoteID=" & Me!QuoteID)
End If
End Sub

 

[PHV]

What about replacing this:
If IsNull(LineNumber) Then
with this ?
If Me.NewRecord Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya angelandgreg . . .

How is QuoteID updated and whats its datatype?

See Ya! . . . . . .

 

[angelandgreg]

QuoteID is just an autonumber.

 

[angelandgreg]

struggling LOL

hi there to both of you ...


forget to greet. i'm really actually a very nice person ;p

just stressing. sorry

 

[angelandgreg]

There are 3 forms.

Main form, where the QuoteID is generated.
Then a subform that is linked by the QuoteID is the CustomQuoteDetails (of the items).

A quote can have multipe items.

then yet another subform of levels per item.

So the main form is opened with the QuoteID generated.
Then after filling out the "header" info the item details are entered.


The Item info is entered and then the Event is to update the LineNumber with 1 and increment but also restart when it's another Item. The unique identifier would be the ItemID as a Quote can have the same Item but with different features.

So say an Item called 12345 can be built with features/dimensions but for quoting purposes you start with the base Item number. Hence the ItemID per QuoteID.


Anyway, the LineNumber per ItemID is the issue here. To restart the counter per ItemID per QuoteID.

 

[PHV]

What is the SQL code of the RecordSource property of the CustomQuoteDetails subform ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

angelandgreg . . .

[blue]PHV's[/blue] reference to [blue]New Record[/blue] was on target, so perhaps the following (double check all names!):

[blue]Private Sub Item_AfterUpdate()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   If Me.NewRecord Then
      Set db = CurrentDb
      SQL = "SELECT TOP 1 [purple][b][i]LineItem[/i][/b][/purple] " & _
            "FROM [purple][b][i]TableName[/i][/b][/purple] " & _
            "WHERE ([QuoteID] = " & Me.QuoteID & ") " & _
            "ORDER BY LineItem DESC;"
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      
      If rst.BOF Then [green]'Unique QuoteID[/green]
         Me![purple][b][i]LineItem[/i][/b][/purple] = 1
      Else            [green]'Quote ID Exists[/green]
         Me![purple][b][i]LineItem[/i][/b][/purple] = rst![purple][b][i]LineItem[/i][/b][/purple] + 1
      End If
   End If

   Set rst = Nothing
   Set db = Nothing

End Sub[/blue]

See Ya! . . . . . .

 

[angelandgreg]

It is working!

at least for new ones being entered.


Thank you both very very much!!