confused with append method (1 Viewer)

[fredy]

here it goes,

i have tabcontrol with 3 page in it. every page consists of 5 text boxes
4 boxes are set visible to false, not until the first box meet the certain criteria and the second box will be visible, when the 2nd box meet the 2nd criteria the box will be visible as well and so on..
the second and third page are the same with page 1. they have 5 boxes each tabpage and same condition as page1.

now, my question is...

how can i append the records in a table in just 1 field without blanks..

let say, on page 1
textbox 1 = 10
textbox 2 = 15
textbox 3 = 20
textbox 4 not visible
textbox 5 not visible
on page 2
textbox 1 = 5
textbox 2 = 18
textbox 3 not visible
textbox 4 not visible
textbox 5 not visible
on page 3
textbox 1 = 3
textbox 2 not visible
textbox 3 not visible
textbox 4 not visible
textbox 5 not visible

the table will appears like this

Table sample
field 1
10
15
20
5
18
3

guys i really need your help.
any suggestions or comments are appreciated.
tnx so much

PS: attached sample code/program is better

 

[Galaxiom]

One way is to generate a dynamic append query in vba then run it. Use the Visible property to determine which controls are to be processed.

Air Code

Dim strSQl as String
Dim ctrl As Control
 
For Each ctrl in Me
   If ctrl.Visible Then
      strSQL = "INSERT INTO tablename (fieldname) VALUES(" & crtl.Value & ")"
      Currentdb.Execute strSQL
   End If
Next

It can also be done by opening a recordset on the table and appending the values in a the loop. That would be faster.

Of course you would also need to use a condition to exclude any other controls on the form. The Tag property is a good one to define which controls to process.

 

[fredy]

thanks to your immediate response.

i really appreciate your time and effort to help me and others as well.

thanks for the code. this will be a big help.

sure to repost as soon as the program works well.

again thanks a lot

 

[fredy]

i tried what you suggested but i think i missed something.

i create a function to loop for the textbox using controls.

i revised your code and instead using currentdb.execute 'coz it gives me error everytime i call it. but still it's not working.

i want to append only the me.acc1.values,me.acc2.values,me.acc3.values,....me.acc15.values.

here is my code:

Sub laAcc(ByVal iIndex As Integer)
    Me.Controls("acc" & iIndex).Value = Me.Controls("a" & iIndex).Value & 
    Me.Controls("b" & iIndex).Value & Me.Controls("c" & iIndex).Value & Me.Controls("d" & iIndex).Value & Me.Controls("e" & iIndex).Value
End Sub

Private Sub appendbutton_Click()
Dim x As Integer
Dim strSql As String
Dim ctrl As Control


'For x = 1 To 15
For Each ctrl In Me
   If Visible(Acc(x)) = True Then
         DoCmd.RunSQL "INSERT INTO HSR_val(txtSeries) values (" & ctrl & ") "
   End If
Next
Next
End Sub

i hope someone help me on this tnx.

 

[Galaxiom]

There are several ways to select the controls to be processed. A naming pattern is a good one. No need to specifically generate each name as you have with the function.

For Each ctrl in Me
If Left(ctrl.Name, 3) = "acc" and ctrl.Visible Then
etc

Note with boolean values there is no need to include the comparision.
If crtl.Visible
returns the same for a True value as
If ctrl.Visible = True

 

[fredy]

you're right, it works!

a bunch of thanks to all the effort and patience and for the tips i learned from you.

time to celebrate. cheers

 

[Galaxiom]

No worries. Not so long ago this site led me through the bewildering maze of VBA. It is nice to be able to pass it on.

 

[fredy]

one more thing,

what if the values that i want to append will be placed in just one record? every values of the control will be appended in every field.

let's say:
acc1 = a
acc2 = b
acc3 = c
.
.
.
acc15 = o

i want it to append in the table in every field, not in every record.
output:

Table1
field1 - field2 - field3   . . . . .   field15
   a    -  b    -   c      . . . . .     o

just wanted to know this.

again, tnx a lot.

 

[Galaxiom]

In that case I would consider using a bound form. The table (or a query on the table) is set as the Record Source of the form and the textboxes' Control Source set to the appropriate field.

However this would probably be considered a denormalized structure.

As sql it would be a single query with no need for the loop.

 "INSERT INTO tablename(field1, field2 etc) VALUES(" & Me.acc1 & ", " &  Me.acc2 etc

A bound subform can also be used to save the values in the original structure. Instead of multiple text boxes the subform would have just one textbox. The subform would be in continuous form or datasheet mode. This would allow as many related records to be entered as desired for the record on the main form.

 

[fredy]

thanks to your response. yes i know that.
a bound subform in a continous form or datasheet. i think that's better. you just gave me another option.

just to ease my mind, let's go back to the problem.

just wondering (like example on the first post)
we're now able to append the records on a table in a single field.
what if, i want it to append in every field?

if the textboxes 1 to 5 has value in it (let say: "a","b","C","D","E") and
textboxes 6 to 10 is null and
textboxes 11 to 15 also has a value ("Z","Y","X","W","V")

before update/append to the table this will simply eliminate or trim the null values.
so the output in the table must be:

Table1
Field1 to 5 = ("a","b","C","D","E")
Field6 to 10= ("Z","Y","X","W","V")
Field11 to 15 = null

thanks a lot and more power

 

[Galaxiom]

Dim ctrl As Control
Dim accValues(14) As String [COLOR=seagreen]'(This is an array with indices 0 to 14)[/COLOR]
Dim n As Integer
Dim m As Integer
Dim strSQL as String
 
   For Each ctrl in Me
 
      If Left(ctrl.Name, 3) = "acc" and ctrl.Visible Then
         accValues(n) = ctrl.Value
         n = n + 1
      End If
 
   Next
 
   strSQL = "INSERT INTO tablename VALUES("  [COLOR=seagreen]'without specifying the fields the values will be place in the default field order[/COLOR]
 
   Do
      strSQL = strSQL & accValues(m) & ", "
      m = m + 1
   Loop While m < n
 
   strSQL = Left(strSQL, Len(strSQL) - 2) & ")"
   CurrentDb.Execute strSQL

 

[fredy]

sorry for the late response, i evaluated the codes just now.

it's working, when all the textboxes were filled out or simply visible.
but if there is something not visible it gives me error :

"Number of query values and destination fields are not the same"

i think we have to put additional statement in SQL (if not visible then values = "" or null) to complete the process.

 

[Galaxiom]

"Number of query values and destination fields are not the same"

i think we have to put additional statement in SQL (if not visible then values = "" or null) to complete the process

Ah. Without the fields specified all values must be provided in the query.

You wanted to avoid the values being spread across the fields with Nulls in between so we can't have the Nulls getting included in the loop. You could add Nulls to the end to pad it out but it is probably best to specify the fieldnames.

You can generate the names in a loop using strings and a counter in a loop but another way is to check every field and test them for a naming pattern similar to the way the controls were tested.

Addressing the tables in VBA is a bit more long winded than the Me keyword we use on forms. Unfortunately table fields don't support For Each either so the loop has to be done to a count the fields.

Untested code

Dim tbldefs As Object
Dim mytable As TableDef
Dim FieldCount As Integer
Dim strFieldName As String
 
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim n As Integer
 
Dim ctrl As Control
Dim accValues(14) As String
Dim strSQL as String
 
[COLOR=seagreen]' Put values from visible Form controls into array[/COLOR]
 
   For Each ctrl in Me
 
      If Left(ctrl.Name, 3) = "acc" and ctrl.Visible Then
         accValues(n) = ctrl.Value
         n = n + 1    [COLOR=seagreen]' count visible controls[/COLOR]
      End If
 
   Next
 
[COLOR=seagreen]' Get fieldnames from table and add to SQL string[/COLOR]
 
Set tbldefs = CurrentDb.TableDefs
Set mytable = tbldefs("tablename")
 
   FieldCount = mytable.Fields.Count
 
   strSQL = "INSERT INTO tablename("
 
   For k = 0 To FieldCount - 1 
      strFieldName = mytable.Fields(k).Name 
 
      If Left(strFieldName, 5) = "Field" Then
         strSQL = strSQL & strFieldName & ", "
         j = j + 1   [COLOR=seagreen]' count fields matching name pattern[/COLOR]
      End If
 
[COLOR=seagreen]      ' Exit loop when enough fields have been added to SQL[/COLOR]
      If j = n Then
         Exit For
      End If
 
   Next
 
Set mytable = Nothing
Set tbldefs = Nothing
 
[COLOR=seagreen]   ' strip trailing comma and space[/COLOR]
 
   strSQL = Left(strSQL, Len(strSQL) - 2) & ") VALUES("
 
[COLOR=seagreen]   ' Add Values from Array to SQL string[/COLOR]
 
   Do
      strSQL = strSQL & accValues(m) & ", "
      m = m + 1
   Loop While m < n
 
   strSQL = Left(strSQL, Len(strSQL) - 2) & ")"
 
   CurrentDb.Execute strSQL

You should also capture the case of no controls being visible and include error trapping for the unexpected in the finished procedure.

 

[Galaxiom]

Although I have provided the code to do this I would remind you again that the structure of the database is denormalized.

The fact that the values in the controls on the form are in no way associated with the fields in the table leaves no doubt that those fields should be a single field in a related table.

While you may be tempted to use a denormalized structure for the sake of some immediate convenience, in the bigger picture it will probably come against you. By then the restructuring will be a lot more work.

Anyway I think we have covered a good variety of code strategies to put unbound control values into tables so hopefully the page will be useful.

 

[fredy]

thank you so much! i really appreciate it.

thanks to all the response and patience you had given. sure i learned a lot from you.

this thread is very useful, and has been solved.

again thanks a lot, that's all i can say...

anyway, i don't intend to use this in my program but learning this lesson is a big deal for me.

thanks again for the effort, support and advice.