Limiting records in query causes problem with form

[hmanvel]

After a few days of tearing my hair out, I'm reaching out for advice. And I'm guessing the solution is simple and stupid, but I just can't seem to make this work. I have a form, that uses a query for it's recordsource. VBA brings up the form, everything is beautiful, works fine. BUT I want to limit the number of records that populate the form, so I go to my query and change the output to 12 records. Query works great, only shows 12 lines. But the report is blank, it wont populate unless the query is set to all records. Any and all ideas would be greatly appreciated!

 

[pbaldy]

What code is used to open the report, what is its source, and does any code run when it opens?

 

[hmanvel]

This code opens the form, and there is no code that runs on opening or whatever, no events at all.

Private Sub Command76_Click()
On Error GoTo Err_Command76_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Water Test 12 Months Form"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command76_Click:
Exit Sub



Err_Command76_Click:
MsgBox Err.Description
Resume Exit_Command76_Click


End Sub

 

[pbaldy]

If that record is displayed on the form and is available in the report, I'd expect it to work. Can you attach the db here?

 

[hmanvel]

I dont think I can send a copy, it's linked to an offsite MYSQL server and all the linkage would be broken etc. It's a sprawling db and we don't even use half of it. Just for clarification; it's a form that I'm trying to fix, not a report. Not sure if that makes a difference or not.

 

[pbaldy]

Oh, you said "the report is blank". If the query returns all records the form works, but not if the query only returns 12? Same questions about the form I guess.

 

[hmanvel]

Sorry, you're right I did say report, it's a form but we tend to use it as a report. I actually began this journey designing a new report to do this but it didnt work either so I backed up to this form which is a copy of a known working form, that we can print out. Everything does what it should until I limit the records in the query.

 

[pbaldy]

The source of the form is the query, directly? No code running when the form loads? If the form is based on the query, it should show whatever the query does. How is it opened?

 

[hmanvel]

Yes to all of the questions. It is opened by a command button on a customer info form, the code that I posted earlier. Now I am wondering if there is another way to limit records, like in the form instead of in the query. Shouldn't be necessary but I'm willing to do whatever to get this one off my list.....

 

[pbaldy]

The way you're opening the form, it is only going to show records that match the criteria. Is that what you want? It/they would have to be one of the 12.

 

[hmanvel]

The criteria is the customer number. Some may have hundreds of records, some two or three. I'm just trying to limit the records to avoid pages of old data. I'm googling how to limit the form to 12 records...