incrementing numbers in subform 1

[barra47]

I have a form(Table1) with a subfrm(Table2)
Link by ID in each table
The main form generates the Purchase Order number - ID ( autonumber)
And Suppliers Name etc.
The sub form( tabular frm) contains LineNo, Qty, description and price. (LineNo is a number field)
I want the line number to start at 1 and increment by 1 for each item ordered
I have placed this in the before update of the ItemNo field

Me("ItemNo") = Nz(DMax("ItemNo","Table2"),0)+1

This works fine except the next time I create another Purchase Order
The ItemNo starts with the next number from the last Order instead of starting from 1 again.
Where am I going wrong

Thanks in Advance

 

[barra47]

Sorry
I placed the code on the before update of the form not the field

 

[TheAceMan1]

How are ya barra47 . . .

Can Do! . . .
[ol][li]1st. . . in the [blue]BeforeUpdate[/blue] event of the form, you detect if a new record was created (using the [blue]NewRecord[/blue] property) and set 1 as the [blue]Default[/blue] starting value in the subform:

[blue]If Me.NewRecord Then [[purple][b]subFormName[/b][/purple]]!ItemNo.DefaultValue = 1[/blue]

The [blue]DefaultValue[/blue] is used as it does'nt trigger edit mode and the subform record has to be edited in an alternate field to trigger saving (aka the [blue]Dirty[/blue] property).[/li]
[li]To finish . . . in the [blue]OnCurrent[/blue] event of the subform, you detect new record again and use DMax + 1 approriately:

[blue]   If Me.NewRecord Then Me!ItemNo = Nz(DMax("ItemNo","Table2"),0)+1[/blue]

[/li][/ol]
[purple]Your thoughts?[/purple]

See Ya! . . . . . .

 

[barra47]

Thanks Aceman

I entered the following code as you explained


Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then [Frm2]!ItemNo.DefaultValue = 1
End Sub

Private Sub Form_Current()
If Me.NewRecord Then Me!ItemNo = Nz(DMax("ItemNo", "Table2"), 0) + 1

End Sub

Im assuming these were to be placed in the subform, before update and on current

The sub form name is Frm2

when I run the form and enter the sub form I get an error come up saing - cant find the field "|" referred to in your expression

This part of the code is highlighted yellow
[Frm2]!ItemNo.DefaultValue = 1

 

[ssatech]

How bout something like:
Put code in your subform

Private Sub Form_AfterInsert()
Dim LineNo As Integer
Dim rstDetails As Recordset

Set rstDetails = Me.RecordsetClone

If rstDetails.RecordCount = 0 Then
LineNo = 0
Else
LineNo = rstDetails.RecordCount
End If

Me!LineNo

End Sub

 

[barra47]

Hi ssatech

I copy and pasted your code in the after insert as explained

the line - Me!LineNo

comes up as syntax error
Sorry Im not very conversant with coding

also do i take out all the other coding I placed in before update and oncurrent ?
Thanks

 

[ssatech]

Hey Barra,
Try this:

Me![LineNo] = LineNo

 

[TheAceMan1]

barra47 . . .

Sorry to get back so late (AceMan is single father of three, and as far as college is concerned . . . fighting for two now!)

My Idea was correct . . . just implemented improperly! The Idea is . . . [blue]per record in the main form . . . do records exist in the subform (New Record has no play here)! . . .[/blue] So the following code looks to see if records exist in the SubForm for the current record in the MainForm. [blue]If they do . . . DMax + 1 is performed, else LineNo is set to start at 1.[/blue] I believe you get the Idea!

The entire scenairo is triggered by the [blue]Dirty[/blue] property of the subform . . . [blue]which occurs whenever you edit a reccord in the subform![/blue]

So . . . in the [blue]Dirty[/blue] property of the subform, copy/paste the following:

[blue]   Dim db As DAO.Recordset, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT TOP 1 [LineNo] " & _
         "FROM [purple][b]Table2Name[/b][/purple] " & _
         "WHERE ([Id] = " & Forms!MainFormName!Id & ");"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      Me!LineNo = 1
   Else
      Me!LineNo = DMax("[LineNo]", "[purple][b]Table2Name[/b][/purple]", "[Id] = " & Forms!MainFormName!Id)
   End If[/blue]

This should do it . . .

To prvent editing of the LineNo . . . copy/paste the following to the [blue]OnGotFocus event of LineNo:

[blue]   Me![NextFieldInTabOrderName].SetFocus[/blue]

Let me know how ya make out! . . .

See Ya! . . . . . .

 

[barra47]

Thanks SSatech

It know works fine

Thanks to you also Aceman

I will have a play with your method on the week end
it does not hurt to have other methods, I also like the way you explain how it works, that helps me a lot


Cheers