Using a Where Clause in a Class Module - ADO

[Cranger]

I have something weird happening.

I have a class that has a statement like so (shortened for this purpose)

Private Sub Class_Initialize()
Dim sSQL As String
Dim db As Connection
Set db = New Connection


db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;dsn=" & DSN & ";uid=" & USERID & ";pwd=" & PASSWORD & ";"

Set adoPrimaryRS = New Recordset

sSQL = "SHAPE {select Company_Code,Product_Code,Insertion_Order_Number, from Order "

If frmBuys.txtfields(18).Text <> &quot;&quot; Then
sSQL = sSQL & &quot; where Product_Code = '&quot; & Trim (frmOrder.txtfields(18).Text) & &quot;' &quot;
End If

sSQL = sSQL & &quot; where Product_Code = 'CHIC'&quot;
sSQL = sSQL & &quot;} AS ParentCMD APPEND &quot;
sSQL = sSQL & &quot;({select Insertion_Order_Number, Run_Date etc.... from OrderDetail Order by Run_Date } AS ChildCMD RELATE Insertion_Order_Number TO Insertion_Order_Number) AS ChildCMD&quot;
adoPrimaryRS.Open sSQL, db, adOpenStatic, adLockOptimistic

DataMembers.Add &quot;Primary&quot;
DataMembers.Add &quot;Secondary&quot;



End Sub

Now in my main form I took this logic out of Form_Load and did the following

If vbKeyReturn = KeyAscii Then

Set PrimaryCLS = New clsBuys

Dim oText As TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtfields
oText.DataMember = &quot;Primary&quot;
Set oText.DataSource = PrimaryCLS
Next
grdDataGrid.DataMember = &quot;Secondary&quot;
Set grdDataGrid.DataSource = PrimaryCLS
Call LoadGrid

End If

So basically, I want the user to type something in the product_code field and bring back only those records that match.

Strange thing is:

1) First time I hit enter, the value doesn't seem to pass to the class. But on 2nd attempt it works.
2) First time I hit enter, it does actually retrieve the FULL recordset(disregards the select) and repaints a blank form over top. (if I close the form after the first enter, there is another instance of the form with the full recordset).

I hope this makes sense....Any ideas on what is going on???

 

[simonkue]

Could it be a timing/sync issue. If you single step, does it work?

 

[Cranger]

I step through, and maybe I am dense, but I can't figure out what is going on.

 

[Cranger]

OK, strike that....I see what it is doing, but now don't know what to do.

Class initialize is hit before anything else is available to the class (referenced/passed variables).

So I guess I need to move my select out of my Where clause.
Any help?

Also starting to think that using a class module and a standard header/detail form was a bad idea. Alternative suggestions? Is there anywhere with solid examples I can look at?

 

[simonkue]

Have you tried creating a class method to create the recordset?
This would be run after class initialize and give you more control. I often use a class to provide access to data tables and have methods for Open, Close, Validate etc. I rarely use the initialze event.
Incidentally, the Wrox book on VB6 Database development provides a generic table maintenance routine based on and routed through classes. I've used this a lot and it is very handy.

 

[Cranger]

Thanks for your suggestion. I took your advice and atleast for now am OK. I really need to read up on this more. I will check out that book. Thanks Again.