Problem with Recordset

[OC2]

Hi
I have just finished making a search engine with access 2000
and will filter the records fine. But to allow a message box to tell the user no records were found i had to add a recordset etc, since adding the dim rst as record set, set rst = db.openrecordset etc, it said type mismatch 13.
So i then added the DAO 3.6 and made it priority.
ok this then didnt bring up a error message but completely ignored all my query strings and just showed all my records. heres the code, can someone help me correct it pls.
Private Sub Command63_Click()
DoCmd.OpenForm "amending"
With Forms!amending
Dim db As Database
Dim rst As Recordset

Select Case Dates
Case 1
strsql = "Select * From [OLSquery] Where " _
& " transactiondate like '*'"
Case 2
strsql = "select * from [olsquery] Where " _
& " transactiondate = #" & Date & " #"
Case 3
strsql = "select * from [olsquery] where" _
& " transactiondate between #" & Me!From & " # and #" & Me!To & " #"
End Select
Select Case Order_Number
Case 1
strsql = "select * from [olsquery] Where " _
& " PoNo like '*'"
Case 2
strsql = "select * from [olsquery] where " _
& " PoNo = '" & Me!Order & " '"
End Select
Select Case Product_Code
Case 1
strsql = "select * from [olsquery] where " _
& " ProductCode like '*'"
Case 2
strsql = "select * from [olsquery] where " _
& " productcode = '" & Me!Product & " '"
End Select
Select Case TransType
Case 1
strsql = "select * from [olsquery] where " _
& " transtype like '*'"
Case 2
strsql = "select * from [olsquery] where " _
& " transtype = '" & Me!trans & " '"
End Select

Set db = CurrentDb()

Set rst = db.OpenRecordset(strsql, dbOpenDynaset)
If rst.RecordCount < 1 Then
MsgBox &quot;No records found&quot;, vbOKOnly, &quot;Try Again&quot;
Else
Forms![amending].Form.RecordSource = strsql
DoCmd.Close acForm, &quot;olsamend&quot;

End If
End With

End Sub

I know there is a way of consolidating it, but i want it working first b4 i tart it up.

 

[marcseys]

Parently, the type of the fields and the syntaxis is not corresponding.
I don't have experience with Acc2000 but for Acc97 the syntaxis is for type
DATE 15/04/2001
STRING '15/04/2001'
NUMBER 21546

Hope that this will help you.

rgds
Marc

 

[RickSpr]

(Note: This should have been posted in the Microsoft: Access Forms forum. This forum is for developing with Visual Basic, the software development product, not with VBA included in Microsoft Office.)

Adding the recordset to your search form shouldn't, in and of itself, have caused this problem. You need to isolate whether the problem is occurring on your search form, or on the Amending form.

Use your search form once, and then switch the Amending form into Design View. Look at the Record Source. Does it have the appropriate SQL statement in it? If so, the problem is in the Amending form; otherwise it's in the search form.

If the problem is in the Amending form, reexamine your selection criteria against your underlying table. Copy and paste the SQL into a query and see if you aren't getting the right records. If it works right in a query, I can't imagine how it could fail in the form's RecordSource. Perhaps your criteria don't work quite the way you expect them to.

If the problem is in your search form, set a breakpoint on the first line, and step through the code to find out why the RecordSource in Amending isn't being set to what you expected.

One more thing: before you test rst.RecordCount < 1, you should execute rst.MoveLast. When you open a recordset, Access gives control back to your code before the recordset has been fully populated. There is a delay before the RecordCount property is updated. Executing the MoveLast forces you to wait until the recordset has been fully populated, and the RecordCount property is correct. (You can see this when you open a large table; the record count in the navigation bar takes a few moments to show up. In the same way that you see the table before the record count is known, your code sees the recordset before the record count is known.) If you don't do the MoveLast, you may conclude there are no records when there really are. Rick Sprague