Solved

Search button in Master-Detail form

Posted on 2002-04-24
5
309 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Great! Glad it worked for you.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 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

13 Experts available now in Live!

Get 1:1 Help Now