Need a bit of help with ADO

Now there may or may not be a much simpler way to do things, so I'll give a brief description of what I am trying to do.

I have a form for a purchasing application that I am helping on. The old way that this worked is the user would have to enter all of the information regarding a supplier every time. Now all of the supplier info is in a database (which is being accessed through ODBC).

What I want to do is have a DataCombo box with the various supplier names, and then have the specific supplier info fields populated based on that selection (contact person, adress, etc...)

I have set up 2 ADO data controls (data1 and data2 for reference). The first refers to the 'Name' field of the supplier table. The second refers to the entire Supplier Table. I have the DataCombo Box funtioning, but I somehow need to have it update data2 to whichever data1 is referring to when a selection is made. I have tried simply assigning the AbsolutePosition of data1 to data2 ( the expression I tried was data1.Recordset.AbsolutePosition = data2.Recordset.AbsolutePosition). That didn't work.

If anyone can offer any advice it would be MUCH appreciated. I either need a different way of approaching this, or I need to make data2 point to the same record as data1.

Hi Afroide. Can you explain in brief terms the structure of
your database? Specifically, I am wondering how many times
a supplier can appear in the Supplier table. If they can
appear multiple times, you should separate the table into
two tables - one table with a list of things that only need
to be entered once (like name, address, et cetera) and the
other with data like purchases, appointments, etc. where
there will be more than one. Then establish a one-to-many
relationship between the tables.

On the other hand, if the supplier can appear only once in
the table, there is a much easier way to search for a
record that what you are trying.

If the textboxes are bound to the data fields the easiest way to do this is:
to set up one recordset for supplier info and have all the textboxes bound to it then just filter it by the datacombo text. Then when a user selects something in the combo the whole form is updated and all the code is just in the Datacombo_click event.

Code:

---

Private Sub Form_Load()
  'Opens recordset
  SQLstr="SELECT * FROM SupplierInfo"
  rs.Open SQLstr,OtherCnnInfo,MoreInfo
  'sets form to blank
  rs.moveFirst
  rs.movePrevious
End Sub

Private Sub DataCombo_Click(Area as integer)
  'Makes sure the click was in the dropdown
  If Area=2 then
    rs.Filter="SupplierName='" & DataCombo.text & "'"
    'You'll need to add some error handling here for if no info matchs
  End if
End Sub

---

The code may not be exact becuase I didn't have a chance to test it but it gives you the idea.

If the textboxes aren't bound then after the filter just populate them:
txtSupAddress.text=rs!SupplierAddress

Each supplier can, in fact, only appear once. Any advice on a better way to do it would be much appreciated. I've done a fair bit of programming in the past (mostly c and Java), but I literally started VB/database late last week.

I assume that you have the other controls on the screen
bound to data2, something like:

Code:

---

Private Sub Form_Load()

data2.ConnectionString = "blah blah your connection route here"

Set txtPhone.DataSource = data2
txtPhone.DataField = "PHONE"

Set txtAddress.DataSource = data2
txtAddress.DataField = "ADDRESS"

Set txt  'yadda yadda yadda, you get the picture

End Sub()

---

I would suggest this:

1) get rid of the DataCombo and replace it with a textbox,
and bind the textbox to the "Name" field

2) get rid of the data1 because you will no longer need it

3) in data2's SQL, order the records by "Name"

4) add a button called "cmdSearch" or something similiar

5) add this code to the cmdSearch_Click() routine

Code:

---

Private Sub cmdSearch_Click()

Dim findString As String

'exit sub if recordset is empty
If data2.Recordset.RecordCount = 0 Then Exit Sub

'search for a specific record
findString = InputBox("Enter the first few letters of the company name...")

If findString = "" Then
    Exit Sub
Else
    data2.Recordset.Requery
    data2.MoveFirst
    data2.Find "NAME Like '" & findString & "*'"

    If data2.Recordset.EOF Then
        MsgBox "No matches were found.", vbInformation
        data2.Refresh
        data2.Recordset.Requery
    End If
End If

End Sub

---

This will search for a string with a wildcard in the "Name"
field, e.g. you enter "TR" and it will look for the first
supplier whose name begins with "TR."

you will have to add some error handling and other code to
that, it is just meant to get you started.

hope that helps ;-)