Numbering Records on a Form 2

[shortamericano]

Hello there. This seems like an easy task but I've been searching forums for two days and have not found a solution...

I have an Access form with an embedded subform. The subform returns multiple records based on the main form. I want to number these records returned in a sequential fashion:

1. [Field]
2. [Field]
3. [Field]
4. [Field]
5. [Field] etc...

I've tried different things to a text box but nothing seems to work. I know this feature exists on reports, but I can't find it for forms. Can anybody help?

A big thank you in advance!

 

[keun]

Do you have an autonumber field?

Did you try out DSum?

 

[shortamericano]

Hi keun,

There is an Autonumber field on the table that the subform stores its data, however, it does not correspond to the subform.

I could not get DSum to do what I wanted. Can you help?

Thanks!

 

[CautionMP]

shortamericano,
One approach:
[ol][li]Form named Form4 with the Default view set to Continuoss Forms.[/li]
[li]The form recordsource is set to a query that contains a field Key sorted in Ascending order.[/li]
[li]Create a TextBox named [tt]txtKey[/tt] with the control source of Key in the Detail section of the form.[/li]
[li]Another text box in the Detail section called [tt]txtNumber[/tt] with the control source set to [tt]=DCount("Key",[Forms].[Form4].RecordSource,"Key<=" & [txtKey])[/tt][/li][/ol]

Hope this makes sense and helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[shortamericano]

Thanks CMP. I'm still not seeing the sequential numbering. Questions:

1. Can the field Key be a date field?
2. Can you explain the different parts of the DCount expression? Maybe I'm misunderstanding something there. Here is what I have:

- Form name is "frmSurgery".
- Form Default View is set to Continuous Form.
- Form RecordSource has a query with DateOfSurgery in ascending order.
- TextBox field created and named txtNumber with control source set to =DCount("DateOfSurgery",[Forms].[frmSurgery].RecordSource,"[DateOfSurgery]<=" & [txtDateOfSurgery])


Thanks!

 

[keun]

Here is how I do it. I got this off of the internet years ago and it has never failed!

To begin with, you need an autonumber field in the table. I call my autonumber field auto.

On your form, create a text box. Make the Control Souce of the textbox

[b]=GetLineNumber([Form],"auto",[auto])[/b]

. I usually make the text box so there is no border and transparent background so it looks like the line number is just sitting on the form.

Then you need to make a new module. Call the module whatever you want.

Paste this into the module, save, and close:

[b]'============================================================
' The following function is used by the subLineNumber form
'============================================================

Function GetLineNumber(F As Form, KeyName As String, KeyValue)
   Dim rs As Object
   Dim CountLines As Integer

   On Error GoTo Err_GetLineNumber

   Set rs = F.Recordset.Clone

   ' Find the current record.
   Select Case rs.Fields(KeyName).Type
      ' Find using numeric data type key value?
      Case adSmallInt, adTinyInt, adBigInt, adInteger, adDecimal, adNumeric, adCurrency, adSingle, adDouble
         rs.FindFirst "[" & KeyName & "] = " & KeyValue
      ' Find using date data type key value?
      Case adDate
         rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
      ' Find using text data type key value?
      Case adChar, adVarChar
         rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
      Case Else
         MsgBox "ERROR: Invalid key field data type!"
         Exit Function
   End Select

   ' Loop backward, counting the lines.
   Do Until rs.BOF
      CountLines = CountLines + 1
      rs.MovePrevious
   Loop

Bye_GetLineNumber:
         ' Return the result.
         GetLineNumber = CountLines

         Exit Function

Err_GetLineNumber:
         CountLines = 0
         Resume Bye_GetLineNumber

End Function[/b]

 

[CautionMP]

shortamericano,
Looks right, if your not seeing the number what is showing in [tt]txtNumber[/tt]? #Error? Null? 0?

Since it's a date you could try:
[tt]=DCount("DateOfSurgery",[Forms].[frmSurgery].RecordSource,"[DateOfSurgery]<=#" & [txtDateOfSurgery]&"#")
[/tt]

Yes it will work with a date field, just remember that if your key is not unique you will end up with two records that show the same number in the form.

DCount function Syntax: DCount(expr, domain[, criteria]) for the full details of each argument check out the Help file.

CMP


Keun,
If I were going to go that route in a Form, and I have, it's much faster to use a [tt]RecordsetClone[/tt], [tt]Bookmark[/tt], and [tt]AbsolutePosition[/tt] to find the record number. [small]CMP[/small]

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[shortamericano]

Thank you both for your help! I really appreciate you taking the time to assist.

CMP: Unforunately, I kept getting "#Error?". I even tried creating a form and table from scratch and continued to get the error. Very weird.

Keun: Your suggestion worked! Thank you!

 

[keun]

Your welcome! Glad to help.

 

[ioshee]

CautionMP: your method worked great for me in a test, but I can't get it to work with my application.

It seems that the difference is that my "key" field is type:text instead of a type:number.

It is a unique field in the query. Any thoughts?

 

[PHV]

my "key" field is type:text
So, use single quotes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ioshee]

PHV, Do you mean instead of:
=DCount("Key",[Forms].[Form4].RecordSource,"Key<=" & [txtKey])

I would use:
=DCount('Key',[Forms].[Form4].RecordSource,'Key<=' & [txtKey])

?

That didn't seem to work. I am still getting #Error? in my txtKey control. Thank you though.

 

[PHV]

=DCount("Key","name of table/query","Key<='" & [txtKey] & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ioshee]

That worked beautifully. Thank you so much PH.