Autonumering in subform

[seal2b1]

I have created a form with a subform. There is a field (Receiver No.) in the form and it is tyed with another field name Receiver No. in the subform. I have the form field autnumbering. The subform then displays this same number in its receiver no field.

On the subform i also have an items field. I need this to autonumber also, however when I submit new record I need it to start the item numbering over. For instane if on receiver No (0001) i enter 10 items, i need the items field to autonumber each row 1-10 as I enter them. Then when I click new record everything clears and I can do the same thing over again instead of the items starting at number 11.

 

[straybullet]

Check out thread

http://tek-tips.com/viewthread.cfm?qid=1509604

I'm sure you could adapt the provided vba to accomplish what you're looking for.

Let them hate - so long as they fear... Lucius Accius

 

[TheAceMan1]

How are ya seal2b1 . . .

For starters, its a bad idea to have a period in your field names (Receiver No[red].[/red]). Also for easier reading of code its recommended to exclude spaces (ReceiverNo).

In the [blue]On Dirty[/blue] event of the subform, copy/paste the following (be sure to disable or remove any other code you've tried for this problem to prevent interaction):

[blue]   Dim Cri As String
      
   If Me.NewRecord Then
      Cri = "[Receiver No.] = " & Nz(Me.Parent![Receiver No.], 0)
      Me![Receiver No.]!Items = Nz(DMax("[Item]", "tblAcctType", Cri), 0) + 1
   End If[/blue]

Note that [blue]Items[/blue] doesn't update until you [blue]edit a record in the subform![/blue]

[blue]Your Thoughts?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Woops! . . .

Corrected code:

[blue]   Dim Cri As String
      
   If Me.NewRecord Then
      Cri = "[Receiver No.] = " & Nz(Me.Parent![Receiver No.], 0)
      Me!Items = Nz(DMax("[Items]", "[B][I]YourTableName[/I][/B]", Cri), 0) + 1
   End If[/blue]

Sorry about the mess!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[seal2b1]

thaks for the replies. Thanks TheAceMan1, you post worked exactly as i needed.

I have a couple more questions. I would like the column to auto format to width when the user enters information into the form, so when it is viewed and printed the information isnt cut off or you dont have to go and manually widen the column. Second, the tables that i am collecting the information in did have plus signs next to the rows before I entered in the code for my items numbering. When you clicked the plus sign it gave you the corresponding information from the other table below the row. It is gone, is this somethign i can get back. this would make viewing the table easier so it can consolodate the rows down to one instead of several rows listinf every items associated with the field.