count record

[jgeneie]

Hi all,

i have a search form that searches records from a table

1) how do i use VBA to count the number of records and display as "n of records found" in a pop up menu and then display the output after clicking ok.

2) and to have a pop up menu that says "no record found" when no records are being found in the table.

all using in vb

thanks

 

[Isadore]

Probably a number of ways to do this. I would try the following. Add a subform (MypopupSubForm) to a popupform (MypopupForm) and then do a count on open..

Open up your popup form and put your search code there.

'do your count first (on open of the form...)
Dim Ct As Variant
Ct = DCount("[MyCtField]","tblMyTable","[MyCtFied] = ....)
If Ct IsNull Then
MsgBox "No Records were found..."
DoCmd.Close, acForm, "MyPopUpForm"
Exit Sub
Else
Forms![MyPopUpForm].Form![MypopupSubForm].Visible = True
Exit Sub
End If

Display the records that are there in the subform, say, in datasheet view. You may want to put an "on click" event on the subform so that the user can then open up that record on the form you first opened your search form.

If you want to put the search code on the main form then just use the code above and if there are records then just open up your popupform with the resutls. Hope this helps.