ADODB & Loops Gallor!

[cid]

Option Explicit
    Dim ADOCn As adodb.Connection
    Dim rsvalues As adodb.Recordset
    Dim ConnString As String
    Dim rsrecord As adodb.Record
    Dim i As Integer
    Dim c As Control

Private Sub DataConnect_Click()
    
 
   On Error GoTo DataConnect_Click_Error
 
CommonDialog1.Filter = "Access Database (*.mdb)|*.mdb|"
CommonDialog1.ShowOpen
  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 Permits", ADOCn, adOpenKeyset, adLockOptimistic, adCmdText
For i = 0 To rsvalues.Fields.Count - 1
For Each c In Me.Controls
 If c.Name = "txt" & rsvalues.Fields(i).Name Then
  c.Text = CStr(rsvalues.Fields(i).Value & "")
   Exit For
  End If
 Next c
rsvalues.MoveFirst
 On Error GoTo 0
 Exit Sub
Next
DataConnect_Click_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DataConnect_Click of Form Permit1"
 End Sub

Private Sub datanew_click()
        For i = 0 To rsvalues.Fields.Count - 1
          For Each c In Me.Controls
                  
          rsvalues.AddNew
      If c.Name = "txt" & rsvalues.Fields(i).Name Then
        Select Case rsvalues.Fields(i).Type
           Case adodb.DataTypeEnum.adVarChar, adodb.DataTypeEnum.adChar
                rsvalues.Fields(i).Value = c.Text
           Case adodb.DataTypeEnum.adDate
                rsvalues.Fields(i).Value = CDate(c.Text)
           Case adodb.DataTypeEnum.adInteger
                rsvalues.Fields(i).Value = CInt(c.Text)
           rsvalues.Update
           End Select
           End If
           Exit For
             Next c
             Next i
                
                
 
    For Each c In Me.Controls
     If TypeOf c Is TextBox Then
    c.Text = vbNullString
End If
Next
End Sub

Private Sub datadel_click()
Set rsrecord = New adodb.Record
rsrecord.DeleteRecord
rsvalues.Update
End Sub
 
Private Sub FillData_Click()
 
  For i = 0 To rsvalues.Fields.Count - 1
    For Each c In Me.Controls
      If c.Name = "txt" & rsvalues.Fields(i).Name Then
        Select Case rsvalues.Fields(i).Type
        Case adText, adChar
            rsvalues.Fields(i).Value = c.Text
        Case adDate
            rsvalues.Fields(i).Value = CDate(c.Text)
        Case adInteger
            rsvalues.Fields(i).Value = CInt(c.Text)
       End Select
      End If
      Exit For
    Next c
  Next i
 
  For Each c In Me.Controls
    If TypeOf c Is TextBox Then
      c.Text = vbNullString
    End If
  Next c
End Sub
 
Private Sub FillText_Click()
For i = 0 To rsvalues.Fields.Count - 1
  For Each c In Me.Controls
    If c.Name = "txt" & rsvalues.Fields(i).Name Then
      c.Text = CStr(rsvalues.Fields(i).Value & "")
      Exit For
    End If
  Next c
Next i
End Sub

Few problems, my DataNew doesnt save the information in the textboxes. And DataDel IS A FRICKING MESS!

[techgnome]

datanew:

move the .Update outside the for each control loop.

           For Each c In Me.Controls
                  
          rsvalues.AddNew
      If c.Name = "txt" & rsvalues.Fields(i).Name Then
        Select Case rsvalues.Fields(i).Type
           Case adodb.DataTypeEnum.adVarChar, adodb.DataTypeEnum.adChar
                rsvalues.Fields(i).Value = c.Text
           Case adodb.DataTypeEnum.adDate
                rsvalues.Fields(i).Value = CDate(c.Text)
           Case adodb.DataTypeEnum.adInteger
                rsvalues.Fields(i).Value = CInt(c.Text)
           End Select
           End If
           Exit For
             Next c
           rsvalues.Update

That way it only does the update AFTer all fields have been updated.

And yeah, DataDel doesn't do anything. Two ways to delete. 1) Find the record in the recorset, then do a rs.Delete on it. or 2) create a DELETE SQL command and execute that.

-tg

[cid]

I updated my code and it is still doing the same thing:

Private Sub datanew_click()
        For i = 0 To rsvalues.Fields.Count - 1
         For Each c In Me.Controls
                  
          rsvalues.AddNew
      If c.Name = "txt" & rsvalues.Fields(i).Name Then
        Select Case rsvalues.Fields(i).Type
           Case adodb.DataTypeEnum.adVarChar, adodb.DataTypeEnum.adChar
                rsvalues.Fields(i).Value = c.Text
           Case adodb.DataTypeEnum.adDate
                rsvalues.Fields(i).Value = CDate(c.Text)
           Case adodb.DataTypeEnum.adInteger
                rsvalues.Fields(i).Value = CInt(c.Text)
           End Select
           End If
           Exit For
             Next c
           rsvalues.Update
                
                
 
    For Each c In Me.Controls
     If TypeOf c Is TextBox Then
    c.Text = vbNullString
End If
Next
Next
rsvalues.MoveNext
End Sub

[cid]

...
Ok I just figured out that my AddNewRecord is not only creating 1 record, but everytime I hit the button it creates about 70!.

[techgnome]

Sorry about that. I can see now what you are doing......

Private Sub datanew_click()
 
  'Add a new record
  rsvalues.AddNew
 
  'Loop through each field
  For i = 0 To rsvalues.Fields.Count - 1
    'Loop through each control
    For Each c In Me.Controls
      'If the control name matched the field name
      If c.Name = "txt" & rsvalues.Fields(i).Name Then
        'Set it based on the data type
        Select Case rsvalues.Fields(i).Type
           Case adodb.DataTypeEnum.adVarChar, adodb.DataTypeEnum.adChar
                rsvalues.Fields(i).Value = c.Text
           Case adodb.DataTypeEnum.adDate
                rsvalues.Fields(i).Value = CDate(c.Text)
           Case adodb.DataTypeEnum.adInteger
                rsvalues.Fields(i).Value = CInt(c.Text)
        End Select
      End If 'end if field matches control
    Next c 'Get the next control
  Next i 'Get the next field
 
  'Update the recordset
  rsvalues.Update
 
  'Loop through the controls and blank each one out
  For Each c In Me.Controls
    If TypeOf c Is TextBox Then
      c.Text = vbNullString
    End If
  Next
 
  'Move to the first record
  rsvalues.MoveFirst
 
End Sub

See if that works better. Cleaned it up some, rearranged afew things and added comments

-tg

[cid]

type mismatch:

rsvalues.Fields(i).Value = CDate(c.Text)

It seems to actually be doing its job now, thank you for the code thus far. After I finish this adding problem, my project will almost be done

[techgnome]

yeah, if c.Text isn't a date compatible format or value, converting it to Date will fail. Probably should do a IsDate() check on it first, then set it if it's valid else set the field to NULL.

-tg

[cid]

how do i do a IsDate()? This is my second day on ADO

[techgnome]

IsDate() is a VB function.... has nothing to do with ado...

if IsDate(c.Text) Then
  rsvalues.Fields(i).Value = CDate(c.Text)
Else
  rsvalues.Fields(i).Value = Null 'Or what ever default value you want
End If

Stick that inside your select case where you are setting the date datatype.

-tg

[cid]

Thank you techgnome... Now it wont create 70 new records.
Last problem standing is that it will not append the data to the fields

'Add a new record
  rsvalues.AddNew
 
  'Loop through each field
  For i = 0 To rsvalues.Fields.Count - 1
    'Loop through each control
    For Each c In Me.Controls
      'If the control name matched the field name
      If c.Name = "txt" & rsvalues.Fields(i).Name Then
        'Set it based on the data type
        Select Case rsvalues.Fields(i).Type
           Case ADODB.DataTypeEnum.adVarChar, ADODB.DataTypeEnum.adChar
                rsvalues.Fields(i).Value = c.Text
          If IsDate(c.Text) Then
            rsvalues.Fields(i).Value = CDate(c.Text)
            Else
            rsvalues.Fields(i).Value = Null 'Or what ever default value you want
            End If
           Case ADODB.DataTypeEnum.adInteger
                rsvalues.Fields(i).Value = CInt(c.Text)
        End Select
      End If 'end if field matches control
    Next c 'Get the next control
     Next i 'Get the next field
 
  'Update the recordset
  rsvalues.Update
 
  'Loop through the controls and blank each one out
  For Each c In Me.Controls
    If TypeOf c Is TextBox Then
      c.Text = vbNullString
    End If
  Next
 
  'Move to the next record
  rsvalues.MoveNext

[si_the_geek]

How many threads have you created for the same question?? This is confusing and annoying - I have to read 3 threads to find out what the current situation is!

Last problem standing is that it will not append the data to the fields

What exactly do you mean by this?

If you mean that it is adding a new record when you want to update the current one, just remove/comment out the "rsvalues.AddNew" line.

[cid]

The only problem that remains is making the program bind the data to a field in the database. It will create new records, but it doesnt save the old ones.

Private Sub datanew_click()
   'Add a new record
  FillData_Click
  rsvalues.AddNew
 
  'Loop through each field
  For i = 0 To rsvalues.Fields.Count - 1
    'Loop through each control
    For Each c In Me.Controls
      'If the control name matched the field name
      If c.Name = "txt" & rsvalues.Fields(i).Name Then
        'Set it based on the data type
        Select Case rsvalues.Fields(i).Type
           Case adodb.DataTypeEnum.adVarChar, adodb.DataTypeEnum.adChar
                rsvalues.Fields(i).Value = c.Text
          If IsDate(c.Text) Then
            rsvalues.Fields(i).Value = CDate(c.Text)
            Else
            rsvalues.Fields(i).Value = Null 'Or what ever default value you want
            End If
           Case adodb.DataTypeEnum.adInteger
                rsvalues.Fields(i).Value = CInt(c.Text)
        End Select
      End If 'end if field matches control
    Next c 'Get the next control
     Next i 'Get the next field
 
  'Update the recordset
  rsvalues.Update
 
  'Loop through the controls and blank each one out
  For Each c In Me.Controls
    If TypeOf c Is TextBox Then
      c.Text = vbNullString
    End If
  Next
 
  'Move to the next record
  rsvalues.MoveNext
End Sub
 
 
Private Sub FillData_Click()
 
    For i = 0 To rsvalues.Fields.Count - 1
    'Loop through each control
    For Each c In Me.Controls
      'If the control name matched the field name
      If c.Name = "txt" & rsvalues.Fields(i).Name Then
        'Set it based on the data type
        Select Case rsvalues.Fields(i).Type
           Case adodb.DataTypeEnum.adVarChar, adodb.DataTypeEnum.adChar
                rsvalues.Fields(i).Value = c.Text
          If IsDate(c.Text) Then
            rsvalues.Fields(i).Value = CDate(c.Text)
            Else
            rsvalues.Fields(i).Value = Null 'Or what ever default value you want
            End If
           Case adodb.DataTypeEnum.adInteger
                rsvalues.Fields(i).Value = CInt(c.Text)
        End Select
      End If 'end if field matches control
    Next c 'Get the next control
     Next i 'Get the next field
 
  'Update the recordset
  rsvalues.Update
 
End Sub

See anything wrong here? It should be updating perfectly

[si_the_geek]

It looks to me as if the FillData_click sub should save the values in the textboxes to the current record. ADO has no method to initiate Editing (DAO had rs.edit, but ADO assumes as soon as you set a value) so the code you have should work.

What is it actually doing? Is it being saved anywhere in the database table?

[cid]

It looks to me as if the FillData_click sub should save the values in the textboxes to the current record. ADO has no method to initiate Editing (DAO had rs.edit, but ADO assumes as soon as you set a value) so the code you have should work.

What is it actually doing? Is it being saved anywhere in the database table?

No, all it does it create a new blank record and discards anything i type.

[si_the_geek]

"Filldata" does not have appropriate code to add a record, "datanew" does however.

I think you should upload your code file(s), so that we can see what issues you have - as something isn't right here!

[cid]

here is the form and the database in which the form operates on

:Check Attachments:

[si_the_geek]

After deciphering your dodgy naming (of controls & subs) and strange indenting, here's a few issues for you:

In "DataConnect_Click": rather than having a new loop to fill the values (which shouldn't have a .Movefirst !), just Call the approriate sub instead (fillText_Click).

Behind the button labeled * , your code is:

Label13.Caption = "Added New Record"
datadel_click

..do you see a problem here? the same for the button *.* :

Label13.Caption = "Deleted Record"
datanew_click

At the end of "FillData_Click", you seem to have removed the "rs.values.update", which is the part that actually writes to the DB

"datanew_click": you call FillData_Click, which saves the textbox values to the current record, then you immediately add a new record and save those same values to it, and then clear the boxes. What you should do instead is:

• start with "FillData_Click" if you want.
• initiate the Addnew
• clear the textboxes
• have another event (possibly a new button) which calls FillData_Click.

I would personally have a button for saving, which will cover both of these options - note that as things currently stand you do not do anything to save when you move to a different record (you could call FillData_Click before any move commands).