Create a recordset in a VB module from a table

[jamessl]

First up I am a VB rookie.

I am writing a VB module which edits a group of records in a table based on some information entered on a form. I need to write a module as the manipulation/editing of the records is slightly complex.

My (simple) question is -

I need to select a group of records (within the module) from a table, based on Date criteria. I think the best way to do this is to create a new recordset within my module. Then I should be able to check values within the records and edit the records within this recordset easily.

My question is mainly to do with syntax -

What is the VB code for creating a recordset, which relies on criteria (eg a date) for selecting its records?

I think I should be able to use the OpenRecordset method and use an SQL statement to select particular fields and use criteria, but I am not entirely sure how to do this.

I would be selecting the records for the recordset from a table in the open database.

Help appreciated!

 

[BlackKnight]

Hi,
Try this inside of a command button called "cmdFind". Modify as needbe.

Private Sub cmdFind_Click()
Dim conn As Connection
Dim rs As Recordset

Set conn = CurrentProject.Connection
Set rs = conn.Execute("SELECT * FROM tblInvoice WHERE InvoiceDate=#" & txtdate & "#&quot
With rs
If RecordCount > 0 Then
MsgBox !InvoiceDate
Else
MsgBox "No matching records found"
End If
.Close
End With
Set rs = Nothing: conn.Close: Set conn = -Nothing
End Sub

Have a good one!
BK

 

[BlackKnight]

Hi again,

The above is using ADO 2.1 in your references.

BTW there should not be a "-" in front of the NOTHING keyword...it was a typo.

Have a good one!
BK

 

[jamessl]

Thanks for that.

Well I have entered that code, of course tailored to my situation as follows -

Dim conn As Connection
Dim rstBookingsSchema As Recordset

Set conn = CurrentProject.Connection
Set rstBookingsSchema = _
conn.Execute("SELECT * FROM BookingsSchema&quot

With rstBookingsSchema

If rstBookingsSchema.RecordCount > 0 Then
MsgBox !Date
Else
MsgBox "No matching records found"
End If

End With

and I am getting 'no matching records found' when I run the code, and there are records in the Bookings Schema table. Also other references to the recordset in my module (I have quite a lot of other code in the module to edit fields etc in the recordset) are saying that there is no current record or that I am at BOF or EOF, so basically looks like it is not selecting my records.

As you can see in this code here I removed the criteria but there are still no records returned!

Any ideas??

Also, does the Nothing keyword in your code reset the recordset?? By the way I did have that extra ".Close" and "Set rstbookingsSchema = Nothing: conn.Close: Set conn = Nothing" code in and the same thing happened ie no records returned.

thanks

 

[AWithers]

I have had trouble with the .recordcount property in the past and instead used the .eof (end of file) property

e.g.


If rstBookingsSchema.eof = false Then
MsgBox !Date
Else
MsgBox "No matching records found"
End If

Andy

 

[jamessl]

Tried that thanks, but still not finding in records - It must be something to do with the way I am retrieving records for the recordset. any ideas?

 

[AWithers]

Just a though but, n your with section, I think you may have to remove the rstBookingsSchema before .eof (altenatively remove the with section and put in the full reference)

e.g.

With rstBookingsSchema

If .eof = false Then
MsgBox !Date
Else
MsgBox "No matching records found"
End If

End With

Andy

 

[BlackKnight]

Hi,

Ok...I copied/pasted your code snippet you gave me into a command button on a form. I made a couple of changes to it and it works fine and dandy. Here it is...Notice the test for End-Of-File (EOF) and -Beginning-Of-File (BOF) to see if there are any records found. As AWithers stated the .recordcount property is always good to use I since it isn't supported by all DB providers but I did...bad BK...bad BK <g>

Private Sub Command0_Click()
Dim conn As Connection
Dim rstBookingsSchema As Recordset

Set conn = CurrentProject.Connection
Set rstBookingsSchema = conn.Execute(&quot;SELECT * FROM BookingsSchema&quot

With rstBookingsSchema
If .EOF And .BOF Then
MsgBox &quot;No matching records found&quot;
Else
MsgBox !Date
End If
End With
End Sub

Conn and rs are objects specifically references to objects which take up memory...setting them to nothing frees up the memory that has been allocated by the sysetem to them.

Have a good one!
BK

 

[BlackKnight]

Hi,

AWithers,

Using the with keyword like this has no negative effect on whether records are retrieved. It merely defeats the purpose in using the with keyword. <g>

with rstBookingSchema
if rstBookingSchema.eof then msgbox &quot;EOF&quot;
end with

Have a good one!
BK

 

[jamessl]

Thanks,have managed to select my records now.

one thing though, I have had to use the open method to create the recordset as follows

Set conn = CurrentProject.Connection
Set rstBookingsSchema = New ADODB.Recordset
rstBookingsSchema.CursorType = adOpenDynamic
rstBookingsSchema.LockType = adLockOptimistic
rstBookingsSchema.Open &quot;SELECT * FROM BookingsSchema ORDER BY Period&quot;, conn, 1, adLockBatchOptimistic

because the execute method provides a Read Only recordset.

thanks again,

James