Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Search button in Master-Detail form

Posted on 2002-04-24
5
Medium Priority
?
318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1200 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

730 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