By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

Problem using listbox filter if no record for field is present

P: 2
I am new to access and am having a problem with filtering. Here is a little bit of my setup: I have a main form that has a listbox so that users can choose one or many groups in which to display information about. The control name of the listbox is "grupos". The name of the field it is sorting is "Grupo_Nombre". From the user´s selection, they can open up differing forms using command buttons that display filtered information about either members of "grupos", activities of "grupos", or objectives of "grupos".

The filter is working well EXCEPT for when the user selects a field on the listbox where there is no data entered yet. For example, one field in "grupos" may have records for objectives but no records for activities added yet. When the user tries to open the form based on the selected field with no records, it is completely blank.

I have created a form where a user can enter a new record for an activity, is there any way to open this form automatically if there is no record present as to add one...maybe using a conditional recordcount function to open the form if records for a certain field = 0?

I think I am a being a little confusing, so I will copy the code that is currently in place for the open event of the form based on the listbox query:

Private Sub EditarActividades_Click()
Dim varItem As Variant
Dim strNombre_Grupo As String
Dim strFilter As String

If SysCmd(acSysCmdGetObjectState, acForm, "ActividadesNoModificar") <> acObjStateOpen Then
DoCmd.OpenForm "ActividadesNoModificar"
End If

For Each varItem In Me.grupos.ItemsSelected
strNombre_Grupo = strNombre_Grupo & ",'" & Me.grupos.ItemData(varItem) _ & "'" Next varItem

If Len(strNombre_Grupo) = 0 Then
strNombre_Grupo = "Like '*'"
strNombre_Grupo = Right(strNombre_Grupo, Len(strNombre_Grupo) - 1)
strNombre_Grupo = "IN(" & strNombre_Grupo & ")"
End If

strFilter = "[Nombre_Grupo] " & strNombre_Grupo

With Forms![ActividadesNoModificar]
.Filter = strFilter
.FilterOn = True
End With

End Sub
Nov 12 '08 #1
Share this Question
Share on Google+
2 Replies

P: 68

Yes, you can certainly open another form or do whatever else you want based on the result of a recordcount, i.e. recordcount = 0.

Nov 13 '08 #2

P: 2
Thanks for the reply, but as I am new to access I am not quite sure of the syntax and where to place the IF condition in my code so that it opens only when there are no records. Could anyone show me the appropriate syntax and where to place it in the code?
Nov 13 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.