How do you create vb forms that take data from multiple db tables?

[shiatsu]

Please, please can anybody help me as I am really stuck? I’ve created an SQL Server 7 database and would like to create forms to enter the data. I have managed to create forms using the form wizard for data that is from one table but I do not know how to create forms whereby I use data from multiple tables. The form wizard does not seem to allow you to select specific fields from various tables.

Is there anyway I can create a form “manually” using fields from various tables and with the functionality to navigate, add, delete and refresh the records?


If anyone could help I will be incredibly grateful


Shiatsu

 

[caf]

Hi

I would suggest the "manual" way as you've phrased it.

Wizards are great but when you want power you have to do it yourself.

ADO <Insert music here>

That's the technology I'll be using in my demonstration
I have version 2.5 though (I will upgrade to 2.6 as soon as I get time) ;-)

The thing here is to control what comes back from the SQL Server db using SQL. You then execute the SQL using ADO <Insert music here> and fill up your controls from the ADO objects holding the data.

SQL Server <--> ADO <-->VB FrontEnd

I suggest you get a good book/resource on SQL syntax & Real World application of it.

Here's a little example

Option Explicit

Private mobj_ADOConnection As ADODB.Connection
Private mobj_ADORecordSet As ADODB.Recordset

Private Sub Form_Load()

Dim sSQL As String

Set mobj_ADOConnection = CreateObject(&quot;ADODB.Connection&quot

sSQL = &quot;Select * From Table1.Field1, Table1.Field2, Table1.Field&quot; & _
&quot; FROM Table1, Table2 WHERE Table2.Field1 = 'somevalue'&quot; & _
&quot; AND Table2.Field2 = 'someothervalue'&quot;

' FOR MORE FLEXIBILITY I SUGGEST LOOKING AT THE DIFFERNT TYPES OF JOINS YOU CAN PERFORM WITH SQL like UNIONS, INNERJOINS, OUTERJOINS etc

With mobj_ADOConnection
.CursorLocation = adUseClient
.Provider = &quot;SQL&quot; & &quot;OLEDB&quot; 'Prevent emoticon LOL
.Open &quot;Data Source=x0002160;Initial Catalog=pubs;&quot;, &quot;sa&quot;, &quot;&quot;

Set mobj_ADORecordSet = .Execute(sSQL)
End With

'Disconnect recordset (Force of habit)
Set mobj_ADORecordSet.ActiveConnection = Nothing

'........
'Do your stuff to the Data
'........

'If you want to fill text boxes one way you can achieve
'this is by adding a control array to your form (you do
'this by adding the first text box & copy/paste the rest
'when prompted if you'd like to create a control array
'choose Yes)
'Now you can loop easily through your fields filling up
'the text boxes. EG Let's assume you have three
'textboxes belonging to a control array & the first 3
'fields of the returned recordset is what you want to see

   'Text1(0)  Text1(1)  Text1(2)
   'Fields(0) Fields(1) Fields(2)

'For x = Text1.LBound To Text1.Ubound
' Text1(x).Text = mobj_RecordSet.Fields(x).Value
'Next x

'The main thing to remember here is you access your data
'through the fields collection of the RecordSet
'mobj_RecordSet.Fields(&quot;YourFieldName&quot
'mobj_RecordSet.Fields(8)
'mobj_RecordSet(&quot;SomeFieldName&quot
'mobj_RecordSet(16)
'mobj_RecordSet.Fields(&quot;SomeField&quot.Value
'mobj_RecordSet(&quot;SomeField&quot.Value
'You can use any f the above methods (I use the .Value)

'Reconnect with the datasource to issue any changes
Set mobj_ADORecordSet.ActiveConnection = mobj_ADOConnection

'........
'Execute batch processes
'........

'Cleanup
Set mobj_ADORecordSet = Nothing
Set mobj_ADOConnection = Nothing

End Sub

I hope I was clear enough


BTW: Since you working with SQL Server I'd suggest at some time looking into using Stored Procedure because they are significantly faster & easier to maintain. THere's nothing worse than looking for a syntax error in your select statements in VB code. Even on an Access db I called Queries rather than embed SQL syntax in my VB source.

Have fun
caf