Solved

Search button in Master-Detail form

Posted on 2002-04-24
5
311 Views
Last Modified: 2012-08-13
Hello there. Here is the problem. I have made a Search button to locate records in a DB. It works fine in my 'Simple Form' project but I cannot do the same for the 'Master Detail Form'project (As you may recognize it is a VB6 wizard generated Form). Please help me!


**************Simple Form********************

Dim dbVideo As Database
Dim rsCustomers As Recordset

Private Sub Form_Load()
Set dbVideo = OpenDatabase("c:\Orders.mdb")
SQLQuery = "SELECT * FROM Customers"
Set rsCustomers = dbOrders.OpenRecordset(SQLQuery)
txtFields(0) = rsCustomers.Fields("CustID")
txtFields(1) = rsCustomers.Fields("Name")
txtFields(2)= rsCustomers.Fields("Phone")
End Sub


Private Sub CustomerSearch_Click()
SQLQuery = "SELECT * FROM Customers WHERE Name LIKE '*" & txtFields(1) & "*'"
Set rsCustomers = dbVideo.OpenRecordset(SQLQuery)
txtFields(0) = rsCustomers.Fields("CustID")
txtFields(1) = rsCustomers.Fields("Name")
txtFields(2)= rsCustomers.Fields("Phone")
End Sub



**************Master-Detail Form********************

Dim rsCustomers As Recordset

Private Sub Form_Load()
Dim db As Connection
  Set db = New Connection
  db.Open "Provider = MSDataShape;Data PROVIDER = Microsoft.Jet.OLEDB.4.0;Data

Source=C:\Orders.mdb;"
 
  Set rsCustomers = New Recordset
  rsCustomers.Open "SHAPE {select CustID,Name,Phone from Customers} AS ParentCMD APPEND

({select CustID,ProdID,Quantity,Total from Orders } AS ChildCMD RELATE CustID TO CustID) 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 = rsCustomers
  Next
  Set grdDataGrid.DataSource = rsCustomers("ChildCMD").UnderlyingValue
  mbDataChanged = False
End Sub


Private Sub CustomerSearch_Click()
???
End Sub
0
Comment
Question by:nickos_balabanis
5 Comments
 
LVL 2

Expert Comment

by:corvanderlinden
ID: 6965450
Why not use the Where clause in
"SHAPE {select CustID,Name,Phone from Customers} AS ParentCMD ..

"SHAPE {select CustID,Name,Phone from Customers WHERE Name LIKE '*" & txtFields(1) & "*'"} AS ParentCMD
0
 
LVL 3

Expert Comment

by:n_narayanan
ID: 6965507
Remove this coding,

Private Sub CustomerSearch_Click()
???
End Sub

And try,

If two procedure are same name, vb will always take the last one. In your case it would have executed the last procedure.

Narayanan
0
 

Accepted Solution

by:
Death-Speak earned 300 total points
ID: 6965626
I've used this before:

rsCustomers.MoveFirst
rsCustomers.Find " [some_field] = '" & strSomeStr & "'"

If its a date replace the 's with #s and if its a number remove the 's.

Hope this helps.
0
 

Author Comment

by:nickos_balabanis
ID: 6968852
Thanks. This is what I wanted! Actually this technique selects all records and points you to the one you search for. My technique selects only the records matching the search criteria.
I improved it a bit. Now it can search using part of the field:
adoPrimaryRS.Find " [some_field] Like '*" & strSomeStr & "*'"
0
 

Expert Comment

by:Death-Speak
ID: 6968877
Great! Glad it worked for you.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 VB code 9 115
VBA filters 2 59
Error when passing an argument to a vb6 that contains  '/' 7 36
Protecting vb6 & .Net code Obfuscation 18 121
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now