Rusty SQL Statement

[cid]

Private Sub DatabaseM_Click()
    Dim ADOCn       As ADODB.Connection
    Dim rsValues    As ADODB.Recordset
    Dim ConnString  As String
        ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & CommonDialog1.FileName & ";" & _
            "Persist Security Info=False"
        Set ADOCn = New ADODB.Connection
    
    ADOCn.ConnectionString = ConnString
    ADOCn.Open
    Set rsValues = New ADODB.Recordset
    rsValues.Open "SELECT * FROM Overview", ADOCn, adOpenKeyset, adLockOptimistic, adCmdText
    With rsValues
        .AddNew
        .Fields("ProjName").Value = ProjNameT.Text
        .Fields("OilComp").Value = OilCoT.Text
        .Fields("GeoCon").Value = GeoT.Text
        .Fields("SurveyCon").Value = SurvT.Text
        .Fields("DrillCon").Value = DrillT.Text
        .Fields("Total").Value = AcresT.Text
        .Fields("TodDate").Value = DateT.Text
        .Fields("StartDate").Value = StartT.Text
        .Update
 
End With
ProjNameT.Text = vbNullString
OilCoT.Text = vbNullString
GeoT.Text = vbNullString
SurvT.Text = vbNullString
DrillT.Text = vbNullString
AcresT.Text = vbNullString
DateT.Text = vbNullString
StartT.Text = vbNullString
rsValues.MoveFirst
End Sub

And also, Say that i have 70 textboxes instead of 9, is there anyway that i could use ADO without typing out all 70 textboxes and all 70 fields?

[Pino]

And also, Say that i have 70 textboxes instead of 9, is there anyway that i could use ADO without typing out all 70 textboxes and all 70 fields?

I dont think so, you'll just have to type away :-/

as for the code, remmber to set your connection and recordset = nothing when finished

Pino

[Mark Gambo]

And also, Say that i have 70 textboxes instead of 9, is there anyway that i could use ADO without typing out all 70 textboxes and all 70 fields?

Yes, assuming the fields in your recordset are in the same order of the Textbox Array:

Set rsValues = New ADODB.Recordset
    rsValues.Open "SELECT * FROM Overview", ADOCn, adOpenKeyset, adLockOptimistic, adCmdText
    With rsValues
        .AddNew
             With .Fields
                  For a = 1 To .Count - 1
                     .Value = Text1.Item(a).Text & ""
                  Next a
              End With        
        .Update
    End With

[Pino]

you didnt say array :grr:

Pino

[BillBoeBaggins]

Sure, loop through a control collection specifying that they be text boxes. Then if you either A) Name the text box as the field name in the Db, or B) set a property each control has in VB which can just store data you can use.. I forget the property name... then you can do...

Dim CTL as Control
For Each CTL in Controls
'Some test to make sure it is a textbox
rs.fields(CTL.Name).Value=CTL.Text
Next CTL