Search specific record in Parent shape?

[theservant]

I have used the following code:
the question is how do i search a specific field in a Parent table. I have tried the where command, but get an error


Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=H:\VideoMecca\Data\Customer_List.mdb;"

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "SHAPE {select Celnr,Password,IDNr,Surname,Name,Street_Address,Suburb,City,Code,Allowed_User1,Allowed_User2,ContractAmount,Friend1_Surname,Friend1_Name,Friend1_Tel,Friend2_Surname,Friend2_Name,Friend2_Tel from Customer_Info} AS ParentCMD APPEND ({select ID_Nr,VideoCode,VideoName,Date from Customer_Videos Order by VideoCode } AS ChildCMD RELATE IDNr TO ID_Nr) AS ChildCMD", db, adOpenStatic, adLockOptimistic

Dim oText As TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtFields
Set oText.DataSource = adoPrimaryRS
Next

Set grdDataGrid.DataSource = adoPrimaryRS("ChildCMD").UnderlyingValue

mbDataChanged = False

 

[jebenson]

You should be able to put a WHERE clause in the parent select to get specific records. Try something like this:

If adoPrimaryRS.State = adStateOpen Then adoPrimaryRS.Close

If Searching Then

adoPrimaryRS.Open "SHAPE {select Celnr, Password, IDNr, Surname, Name, Street_Address, Suburb, City, Code, Allowed_User1, Allowed_User2, ContractAmount, Friend1_Surname, Friend1_Name, Friend1_Tel, Friend2_Surname, Friend2_Name, Friend2_Tel from Customer_Info [red]Where IDNr=" & txtSearchVal & "[/red]} AS ParentCMD APPEND ({select ID_Nr, VideoCode, VideoName, Date from Customer_Videos Order by VideoCode } AS ChildCMD RELATE IDNr TO ID_Nr) AS ChildCMD", db, adOpenStatic, adLockOptimistic

Elseif Not Searching Then

adoPrimaryRS.Open "SHAPE {select Celnr, Password, IDNr, Surname, Name, Street_Address, Suburb, City, Code, Allowed_User1, Allowed_User2, ContractAmount, Friend1_Surname, Friend1_Name, Friend1_Tel, Friend2_Surname, Friend2_Name, Friend2_Tel from Customer_Info} AS ParentCMD APPEND ({select ID_Nr, VideoCode, VideoName, Date from Customer_Videos Order by VideoCode } AS ChildCMD RELATE IDNr TO ID_Nr) AS ChildCMD", db, adOpenStatic, adLockOptimistic

End If

Note that there is a boolean variable - Searching - to indicate whether you are searching for a specific record or just getting all records. Also, I have indicated a text box (txtSearchVal) as the source of the value to find. You can of course use something else (list box, combo box, etc.) if you wish.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson