Open A Form To Show ONly Records I Specify Otherwise Do Not Open

[Grumpdaddy]

I have a form (MAIN) with two combo boxes (SELECTPROD and SELECTPRI) and a command button (GO). I want to use the combo boxes to select a PROJECT and PRIORITY (since we have numerous projects with different priority levels), and then click the button to open the form (PROJECTS) but only show records matching the combo box selections. The fields referenced on the PROJECTS form are PRIORITY and PRODUCT. The code I used for this is:

Private Sub GO_Click()
 
Dim strWhere As String
 
strWhere = "1 = 1"
 
If Not IsNull(Me.SELECTPRI) Then
 
  strWhere = strWhere & " AND [PRIORITY] = " & Me.SELECTPRI
       
End If
 
If Not IsNull(Me.SELECTPROD) Then
 
  strWhere = strWhere & " AND [PRODUCT] = """ & Me.SELECTPRODe & """ "
 
End If
 
DoCmd.OpenForm "PROJECTS", , , strWhere
 
 
End Sub

This works just fine. But if there are no records that match both fields, then it opens a blank form which I do not want to do. I used a function on earlier version of this database that accomplished this where I only used one criterion (PRIORITY) with this code:

Private Sub Go_Click()
 
On Error GoTo Err_GO_Click
 
    Dim stDocName As String
 
    Dim stLinkCriteria As String
   
    Dim intHolder As Integer
 
    intHolder = DCount("PRIORITY", "PROJECTS")
 
    If intHolder > 0 Then
 
        stDocName = "PROJECTS"
 
        DoCmd.OpenForm stDocName, , , stLinkCriteria
   
    Else: MsgBox "You Do Not Have Any Projects With This Priority Level. Please Verify Selection Or Enter A New Project."
 
End If
 
Exit_GO_Click:
 
Exit Sub
 
Err_GO_Click:
 
      Resume Exit_GO_Click
 
End Sub

This worked fine, too but now I want to combine these in my new database so that only the selected criteria from the combo boxes are used and not open a blank form. I apologize if this has been asked before, but I am a newbie to this forum and know enough VBA to break everything so, please help. Thanks for taking the time to look this over.

Grumpy

[pbaldy]

You should be able to do the same thing, using strWhere as the criteria of the DCount().

[Grumpdaddy]

Thanks, Paul:

I am not sure how to go about this. I have tried to guess where things should go but now it's frustrating. I am sure this is easier than I am making it out to be. Could you please provide an example of what you mean? Thanks so much for your time and patience.

- The Grumpy One

[pbaldy]

intHolder = DCount("PRIORITY", "PROJECTS", strWhere)

[Grumpdaddy]

Thanks, Paul for your response. Still not getting how to integrate both functions. I tried this:

<code>

Dim strWhere As String

Dim stLinkCriteria As String

Dim intHolder As Integer

strWhere = "1 = 1" & " AND [PRIORITY] = " & Me.PRIORITYPICK & " AND [PRODUCT] = """ & Me.PRODUCTPICK & """ " /this is the criteria I need to work

intHolder = DCount("PRIORITY", "PROJECTS", strWhere) /this just links the priority fields, I need it to link priority and product fields but get "argument" error

If intHolder > 0 Then

If Not IsNull(Me.PRIORITYPICK) And Not IsNull(Me.PRODUCTPICK) Then

DoCmd.OpenForm "PROJECTS", , , stLinkCriteria

Else: MsgBox "There Are No Records Matching Your Search Criteria. Please Verify Your Selection Or Create A New Project Task"



End If

End If

</code>

It sort of works but still shows all records, not just the ones I want filtered. Selections that do not match any record do not return error and cancel open (just sits there and does nothing without prompting reason why)

I tried to add "PRODUCT", "PROJECTS" to DCount (as noted in code above) but I get an error saying "Wrong number of arguments or invalid property assignment". I just want to open the form with PRODUCT and PRIORITY to match BOTH PRODUCTPICK AND PRIORITYPICK, otherwise return an error and cancel open. I hope I'm making sense. Please let me know if there is anything specific you need from me. And thanks again.

-The Grumpy One

[pbaldy]

It sort of works but still shows all records, not just the ones I want filtered.

You're using the unpopulated stLinkCriteria in the OpenForm instead of strWhere.

[Grumpdaddy]

This is why I am a mere novice and you, sir are the expert.

Ok...so filter works! Now, why do I not get the error and cancel open when criteria is not met?

And another, not so related question but...how do I keep the user form creating new blank records every time the next record button is clicked after the last actual record (so I have 10 records in the data base but if I keep clicking next record, it just adds more beyond the original 10 and when viewing all records or the source table, I have all these empty record entries)?

Thanks again for all your help!!!!!!!!!!!!!

- The (not so) Grumpy One

[pbaldy]

Can you post the db here?

[Grumpdaddy]

Paul:

Attached is a copy of my DB (specifics omitted, of course...company secrets). I would like to see if I can use the CREATE NEW TASK button to automatically fill in product and priority based on the two combo boxes, as well. Thanks for looking this over. ProjectTrackerExample.zip

- Grumps

[pbaldy]

Like this?

DoCmd.OpenForm stDocName, , , "Product = '" & Me.PRODUCTPICK & "' AND Priority = " & Me.PRIORITYPICK

[Grumpdaddy]

Hi, Paul:

I tried that but it doesn't open a new form with the desired fields automatically populated with the PRODUCTPICK and PRIORITYPICK selections. It just opens a blank form with task priority 5, which is how it was set up in the first place (OpenForm properties). But that's not a big issue, just a thought. Going back to my original post, the idea that it should open the form with just the records that match the combobox selections is what I really need, and thanks to you it pretty much works except for the not getting my msgbox and cancel open. That's where I thought just clicking another button with selections already made should open a new record with those fields already filled. Plus the fact that it keeps adding new records even after I've "forwarded" through existing ones.

I take it you were able to view my DB? If so, please let me know what I broke in it. I'm a big boy and can take it. I would rather have a more streamlined and efficient DB over one that is bloated with unneeded stuff (you should have seen the first one...I had forms for each priority and product before I figured out that I could filter stuff...which of course led me here!)

Thanks for taking time from your busy day to help me out. Web site is cool BTW... I like the painting

- Grumpy