Need to trap blank new record on form

[leeg]

Hi all,

I am having trouble finding out how to stop a new record showing on my form when there are no records showing in my underlying query

Query criteria is populated from a form text box and then I use a cmd button to open the form.

cmd button on click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "F_SearchJobNoForm"

DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!F_SearchJobNoForm.RecordSource = "Q_SearchJobRefNo"


I have on Form_load to just maximise the form and that's it.

I can set the form to not allow additions but it just shows a blank form and the user has to shut it down using the x in the top right corner.

Best way to implement this and trap the blank record before the user sees the form?

I'm pants at access but willing to try very hard ;o)

Appreciate any help

[JKing]

Hi by adding a few more lines you can check to see if the supplied criteria will return any results in your query before opening the form.

Private Sub cmd_Click()
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stLinkCriteria as String
Dim stDoc as String
 
Set db = CurrentDB
Set rs = db.OpenRecordset ("qryYourQuery", dbOpenDynaset)
 
stDoc = "frmYourForm"
stLinkCriteria = "yourCriteria"
 
rs.FindFirst stLinkCriteria
 
If rs.NoMatch Then
    MsgBox "Search criteria returns 0 results.  Please try again.", vbOkOnly + vbInformation, "Sorry!"
Else
    Docmd.OpenForm stDoc,,,stLinkCriteria
End If
 
rs.Close
 
Set db = Nothing
Set rs = Nothing
End Sub
 

rs.NoMatch returns true if there are no records in the query that match your stLinkCriteria.

Be sure to replace "qryYourQuery" with the name of the underlying query of the form you want to open. Sames goes with frmYourForm and yourCriteria be sure to replace them with your own values.

[leeg]

Thank you.

Sorry for being a bit thick but I have tried your code and get an error

"runtime error '3061' Too few parameters. Expected 1."

When I debug it highlights (line 7.)
Set rs = db.OpenRecordset("Q_SearchJobRefNo", dbOpenDynaset)

Here's the code I put in and substituted the names as per your message

I have rem'd line 9 as I'm unsure what this variable is doing. The query field filter is populated using the text box on another form?

1. Private Sub comViewSearch_Click()

2. Dim db As DAO.Database
3. Dim rs As DAO.Recordset
4. Dim stLinkCriteria As String
5. Dim stDoc As String
6. Set db = CurrentDb
7. Set rs = db.OpenRecordset("Q_SearchJobRefNo", dbOpenDynaset)
8. stDoc = "F_SearchJobNoForm"
9. 'stLinkCriteria = "yourCriteria"
10. rs.FindFirst stLinkCriteria
11. If rs.NoMatch Then
12. MsgBox "Search criteria returns 0 results. Please try again.", vbOKOnly + 13. vbInformation, "Sorry!"
14. Else
15. DoCmd.OpenForm stDoc, , , stLinkCriteria
16. End If
17. rs.Close
18. Set db = Nothing
19. Set rs = Nothing
20. End Sub

Any more help would be appreciated

Graeme

[JKing]

I believe the problem is that your query includes parameters which were allowing you to previously filter the form. What I suggest is removing those parameters from the query.

This is where stLinkCriteria comes in. The purpose of the variable stLinkCriteria is to pass it to the openform command as the where statement for the underlying query of the form. So on your first form with your command button you should include a textbox for the user to type into. Then when they click the command button you build your criteria. For example:

stLinkCriteria = "[field] = " & Me.txtSearch
 

[field] would be the field in the query that you want to search on like and ID and Me.txtSearch would be the reference to the textbox on your search form.

Now when we pass this into the FindFirst method of the recordset it will search for the first occurence in the query that matches the user's input for that field. If there's no matches prompt the user and dont open the form. If there is a match open the form with that criteria.

If this seems confusing and you require a little more help with examples more specific to your own database then I'll need you to supply me with a few more things. I'll need to see the sql for the query behind your second form, the name of the field you want to search by and it's data type(i.e. text, number, date) and the name of any controls(i.e. textbox, combobox, listbox) on your search form that you're using to build your criteria.

[leeg]

Thanks very much. I use vb badly and access badly so between the two, confusion arises for me.

That makes sense what you're saying. I have to go now and will have a go at this tomorrow.

Appreciate your help!