Find Button in Forms

[bagos]

So I have a one table as a backend. All my forms, reports and queries are in a front end. I'm creating multiple forms for users to input data of food items. Once a record is created a user can find it using different forms. In one of the forms the user can find the item by UPC. So far I've created this code On_Click as a Find that opens another form with the found information and closes the search one. Each record has a series of fields (8). On the find by UPC form the user must enter a UPC AND the Quarter for which the product was originally set. If information is missing from either field the a msgbox appears requiring the missing fields. If all fields are completed then the user clicks find and a new form opens up with the information of that product. If the UPC does not match the Quarter or if the UPC doesnt exist I would like a msgbox to say "Records dont exist". I know very little about Access but I'm battling through. Right now I'm having a type missmatch error and on the code Quarter is Highlighted. Current records in quarter are as such Q1-2014, Q2-2014... Any recommendations will be great on anything that can help me get this done.

Private Sub Command25_Click()


Dim dbs As Database
Dim rst As Recordset


Set dbs = OpenDatabase("Databaselabmine.mdb")
Set rst = dbs.OpenRecordset("SELECT [Universal Product Code] " _
        & "[Quarter] FROM Table1;")
       
If IsNull(Me![Universal Product Code]) Or IsNull(Me![Quarter]) Then
    MsgBox "Please fill all fields.", vbOKCancel, "Yep"
    ElseIf Me![Universal Product Code] And Me![Quarter] <> rst Then
        MsgBox "No records available.", vbOKCancel, "Yep"
            Else
                DoCmd.OpenForm "M_M_ELSUPC", acNormal, "", "", , acNormal
                DoCmd.Close acForm, "EDITupc"
                DoCmd.SetWarnings False 'ADDED
                DoCmd.OpenQuery "Table1 Query", acViewNormal, acEdit 'ADDED
End If
            
End Sub

[June7]

Try:

Private Sub Command25_Click()

Dim dbs As Database
Dim rst As Recordset

Set dbs = OpenDatabase("Databaselabmine.mdb")
Set rst = dbs.OpenRecordset("SELECT [Universal Product Code], [Quarter] FROM Table1;")
       
If IsNull(Me![Universal Product Code]) Or IsNull(Me![Quarter]) Then
        MsgBox "Please fill all fields.", vbOKCancel, "Yep"
ElseIf rst.RecordCount = 0 Then
        MsgBox "No records available.", vbOKCancel, "Yep"
Else
        DoCmd.OpenForm "M_M_ELSUPC", acNormal, , , , acNormal
        DoCmd.Close acForm, "EDITupc"
        DoCmd.SetWarnings False 'ADDED
        DoCmd.OpenQuery "Table1 Query", acViewNormal, acEdit 'ADDED
End If
            
End Sub

There is no filter criteria in the recordset SQL.

Why the SetWarnings and OpenQuery?

[bagos]

There is no filter criteria in the recordset SQL because I'm retrieving all the values that are stored in both fields on all records for a search. When I open a new form with the search criteria the database takes the two inputs that I previously put and creates a new record with all other values blank. In order for me to delete the record I'm opening a query that deletes the last record and thus I get a warning that I dont want to be displyed.
I liked your idea although there are already multiple records that are stored in the database. So If use RecordCount = 0 my guess is that it will always come up with "No records available" even though there are. I will give it a try.

[June7]

No, as long as there is one record in the table, the RecordCount would always be greater than 0 because there is no filter criteria and the "No records available" message will never show.

If you need to determine if the two values are in the recordset, options:

1. open the recordset with filter using those two values as parameters, if there is no match then the RecordCount will be 0
Set rst = dbs.OpenRecordset("SELECT [Universal Product Code], [Quarter] FROM Table1 WHERE [Universal Product Code] = '" & Me![Universal Product Code] & "' AND [Quarter]=" & Me![Quarter] & ")"

2. use FindFirst on the recordset
rst.FindFirst "[Universal Product Code] = '" & Me![Universal Product Code] & "' AND [Quarter]=" & Me![Quarter]
If rst.NoMatch Then

3. Instead of opening recordset, use DLookup()
If IsNull(DLookup("ID", "table1", "[Universal Product Code] = '" & Me![Universal Product Code] & "' AND [Quarter]=" & Me![Quarter])) Then

The above examples assume [Universal Product Code] is text field and Quarter is number field.

Table1 Query is a DELETE action? Why are you deleting record?