Want a running count of record number on continuous form 1

[DanEaton]

I have a search form that matches control's values with an underlying query. The unbound search comboBoxes are in the form header, and there are continuous results in the 'detail' section. Some people at work have complained that they get confused about what record they are at when they scroll down. I have four fields that are displayed in each record, and they up quite a bit of space. I suggested that a sub-form in datasheet view might work better, but they were dissatisfied with the aesthetics. ANYWAY, what they would like is just a box that displays each record number beside the controls in each record. Like say a text box with a 1 in it for the first record, and so on...Is there a function in VBA that will accomplish this task?

They are also unhappy that I don't have anything that tells them when "there are no search results". Is there a function that can be used that would accomplish this task in an "if" statement, if there are no matching records?

Thanks. Dan Eaton
deaton@caemachinery.com

 

[SeeJane]

Hi,

Yikes! I think your job will get easier if you have the search criteria (your current header) start off as an individual pop-up form. Just copy the header directly into a new little form (this new form is your search form). The search form should have a cmdSearchNow button that the user hits to get the results.

Your results form should have text boxes in the header (where the combo boxes are now) - you can use the values from the combo boxes to fill them in so the user knows what was searched. Give each combo box a companion text box (so if you had cboName you would change it to txtName). You will also want to add a text box for record count. Give it a control source "=Count(*) & " records found"

OK, now for the programming...
try this in the OnClick event

Dim intTotRec As integer

intFoundRec = DCount("*","QueryName&quot

'Stops the search routine if no records found
If intFoundRec = 0
MsgBox "No matches", vbOKOnly, "No matches"
Exit Sub
End If

'Open the results form
DoCmd.OpenForm "YourForm"

'Fill in the search criteria boxes
Me.TextBoxThatusedToBeComboBox1.Value = Forms!SearchForm!ComboBox1.Value
'etc for each criteria

'Close the search form
DoCmd.Close Forms,"SearchForm",acSaveNo

End Sub

I hope this is what you were looking for...
Good luck and yell for more help if you need it,
Cj

 

[DanEaton]

Wasn't exactly what I meant, BUT, I used the counting text box (which is similar to what I wanted), works great, thanks.

By the way, what I meant was that on the continuous form I wanted a text box in each matching record that was the same as the little record selector indicator at the bottom of forms (the little box that displays what record you are on).
But I also wanted that box, thanks again! Dan Eaton
deaton@caemachinery.com

 

[gol4]

A simple but resource eating way of getting what you want is to create a text box and use dcount
count the records that are <= to a record

assume your data is sorted by customerid
234 1 record = 0 less then = 1
245 1 record = 1 less then = 2
354 1 record = 2 less then = 3
will work on sorted text also

dcount(&quot;sortedfield&quot;,&quot;table&quot;,&quot;table.sortedfield <= forms!formname!sortedfield)

aggrate functions take a toll but if you have a small environment or p4's go for it