I have this interface:
When the add button is pressed, a UserControl with the same fields is added:
So, the question is: How do I save the main values and those that have been added? That is, if the user pressed the add button, how do I also save those values?
Currently, this is the entire interface code:
Option Explicit
Private oldPos As Integer
Dim indice As Integer
Dim indicee As Integer
Dim cnn As Connection
Dim rs As Recordset
Dim sCon As String
Dim sql As Command
Private Sub btnAñadir_Click()
indice = indice + 1
indicee = indicee + 0
Picture1.Visible = True
Load uc1(indice)
Set uc1(indice).Container = Picture1
uc1(indice).Visible = True
uc1(indice).Top = IIf(indice = 1, 0, uc1(indice - 1).Top + uc1(indice - 1).Height + 20)
Set lblTipo(indice).Container = uc1(indice)
lblTipo(indice).Visible = True
lblTipo(indice).Top = lblTipo(indice - 1).Top
Load cmbAddTipo(indice)
Set cmbAddTipo(indice).Container = uc1(indice)
cmbAddTipo(indice).Visible = True
cmbAddTipo(indice).Top = cmbAddTipo(indice - 1).Top
CargarTablaEnCombo "SELECT [tipo] FROM dbo.[tipo_Numero]", Me.cmbAddTipo(indice), "tipo"
Load lblAddPrefijo(indice)
Set lblAddPrefijo(indice).Container = uc1(indice)
lblAddPrefijo(indice).Visible = True
lblAddPrefijo(indice).Top = lblAddPrefijo(indice - 1).Top
Load txtAddPrefijo(indice)
Set txtAddPrefijo(indice).Container = uc1(indice)
txtAddPrefijo(indice).Visible = True
txtAddPrefijo(indice).Top = txtAddPrefijo(indice - 1).Top
Load lblAddNum(indice)
Set lblAddNum(indice).Container = uc1(indice)
lblAddNum(indice).Visible = True
lblAddNum(indice).Top = lblAddNum(indice - 1).Top
Load txtAddNumero(indice)
Set txtAddNumero(indice).Container = uc1(indice)
txtAddNumero(indice).Visible = True
txtAddNumero(indice).Top = txtAddNumero(indice - 1).Top
Load chkAddPrincipal(indice)
Set chkAddPrincipal(indice).Container = uc1(indice)
chkAddPrincipal(indice).Visible = True
chkAddPrincipal(indice).Top = chkAddPrincipal(indice - 1).Top
Load chkAddActual(indice)
Set chkAddActual(indice).Container = uc1(indice)
chkAddActual(indice).Visible = True
chkAddActual(indice).Top = chkAddActual(indice - 1).Top
Load lblAddVin(indice)
Set lblAddVin(indice).Container = uc1(indice)
lblAddVin(indice).Visible = True
lblAddVin(indice).Top = lblAddVin(indice - 1).Top
Load cmbAddVinculo(indice)
Set cmbAddVinculo(indice).Container = uc1(indice)
cmbAddVinculo(indice).Visible = True
cmbAddVinculo(indice).Top = cmbAddVinculo(indice - 1).Top
CargarTablaEnCombo "SELECT [tipoVinculo] FROM dbo.[tipo_Vinculo]", Me.cmbAddVinculo(1), "tipoVinculo"
If indice = 3 Then
indice = 0
Me.btnAñadir.Enabled = False
End If
End Sub
Private Sub btnGuardar_Click()
Dim sql As String
sql = "INSERT INTO ejemplo(nombre) VALUES("
sql = sql & "'" & cmbAddTipo.Count & "',"
sql = sql & "'" & txtAddPrefijo.Text & "',"
sql = sql & "'" & txtAddNumero.Text & "',"
sql = sql & "'" & chkAddPrincipal.Value & "',"
sql = sql & "'" & chkAddActual.Value & "',"
sql = sql & "'" & cmbAddVinculo.Count & "',"
sql = sql & "'" & txtTimer.Text & "')"
cnn.Execute sql
End Sub
Private Sub Form_Load()
Dim theDate As Date
theDate = Format(Now(), "short date")
TextBox1.Text = theDate
scrollAdd.Min = 0
scrollAdd.Max = 3000
scrollAdd.SmallChange = Screen.TwipsPerPixelX * 10
scrollAdd.LargeChange = scrollAdd.SmallChange
Call IniciarConexion
CargarTablaEnCombo "SELECT [tipo] FROM dbo.[tipo_Numero]", Me.cmbAddTipo(0), "tipo"
CargarTablaEnCombo "SELECT [tipoVinculo] FROM dbo.[tipo_Vinculo]", Me.cmbAddVinculo(0), "tipoVinculo"
End Sub
Sub CargarTablaEnCombo(ByVal sql As String, comboBox As Object, ByVal Campo As String)
Set rs = New ADODB.Recordset
rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
While Not rs.EOF
If Not IsNull(rs.Fields(Campo)) Then
comboBox.AddItem rs.Fields(Campo)
End If
rs.MoveNext
Wend
End Sub
Function cerrarRecordset(R As Recordset)
On Error Resume Next
If Not R Is Nothing Then
If R.State = adStateOpen Then
R.Close
Set R = Nothing
Else
Set R = Nothing
End If
End If
Err.Clear
On Error GoTo 0
End Function
Private Sub IniciarConexion()
Set cnn = New ADODB.Connection
With cnn
.CursorLocation = adUseClient
.Open "PROVIDER=MSDASQL;driver={SQL Server};server=server;uid=uid;pwd=password;database=database;"
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
On Local Error Resume Next
cnn.Close
Set cnn = Nothing
End Sub
Private Sub scrollAdd_Change()
ScrollControls
End Sub
Private Sub scrollAdd_Scroll()
ScrollControls
End Sub
Private Sub ScrollControls()
Dim c As Control
For Each c In Me.Controls
If c.Container.Name = "Picture1" And Not TypeOf c Is VScrollBar Then
c.Top = c.Top + (oldPos - scrollAdd.Value)
End If
Next
oldPos = scrollAdd.Value
End Sub
Advertisement
Answer
Your app allows the creation of any number of UserControls. At some point, you will need to save all this data. One approach is to to loop through those UserControl’s and build an Insert statement. As mentioned in the comments, it is wise to sanitize the input and to use parameters. Something like this:
Private Sub btnGuardar_Click()
Dim i As Integer
Dim CM As ADODB.Command
For i = 0 To NumberOfUserControls - 1
Set CM = New ADODB.Command
Set CM.ActiveConnection = cnn
CM.CommandType = adCmdText
CM.CommandText = "INSERT INTO ejemplo (nombre) VALUES (?)"
CM.Parameters.Append CM.CreateParameter("@nombre", adInteger, , , uc1(i).Nombre)
CM.Execute , , adExecuteNoRecords
Next
End Sub
For simplicity, this code assumes you know the number of UserControl’s. It also builds upon the ideas I presented in this answer. You will need to sanitize the data and add additional Parameters as needed.