Copy Table Fields 1

[trickshot1126]

Hello,

I have code that i have written that searches through a tables fields and copies that which is not null. It is working beautifully except for one thing. I do not, for any reason with this code, to copy fields 0 - 9. Those fields are only included in the form for future reporting purposes within the database.

The following is my code:

Sub copytoVehChgtoCCP()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qDef As DAO.TableDef
Dim fld As DAO.Field
Dim RowData As String
Dim x As Integer

Set db = CurrentDb()
Set qDef = db.TableDefs("tblTable1")
Set rs = qDef.OpenRecordset

RowData = ""
For Each fld In rs.Fields
If Not fld.Name = field1 Or field2 Then
If fld.Value <> "" Then
RowData = RowData & fld.Name & ": " & fld.Value & vbNewLine
End If
End If
Next fld

ClipBoard_SetText (RowData)
pasteContent (RowData)

End Sub

This code works up until I include the If Not fld.name = TS or NI1 then...

Ideas?

With out that specific if it works and copies all the fields in the table as needed.

Thanks for the help!

Rob

 

[dhookom]

Does your code compile?
Where do the values for field1 and field2 come from? Are these actual string values?

Maybe you can use:

For Each fld In rs.Fields
    If Not (fld.Name = "field1" Or fld.Name = "field2") Then
       If fld.Value <> "" Then
          RowData = RowData & fld.Name & ": " & fld.Value & vbNewLine
       End If
    End If
Next fld

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Not sure I understand what you are doing here

If Not fld.Name = field1 Or field2 Then

but

I do not, for any reason with this code, to copy fields 0 - 9[/quote}
I interpret this to mean do nothing with fields 0-9

...
dim i as integer
...
for i = 10 to rs.fields.count - 1
  set fld = rs.fields(i)
  'your code here
next i

 

[trickshot1126]

Thanks dhookom... It was not compiling and the parenthesis are what i was missing. But i think MajP's way is going to work much better.

Thanks also to MajP! I was trying to loop it but could not quite get the code to work. I will test it and report back!

 

[Andrzejek]

Please use "[blue]Great post? Star it![/blue]" link in the post(s) you’ve found helpful. It shows the appreciation for help given, and also marks the helpful posts for others to know which post(s) are valuable.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[trickshot1126]

So i have added the code MajP and it looks like this:

Sub copytoVehChgtoCCP()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qDef As DAO.TableDef
Dim fld As DAO.Field
Dim RowData As String
Dim x As Integer

Set db = CurrentDb()
Set qDef = db.TableDefs("tblvehchgT")
Set rs = qDef.OpenRecordset

RowData = ""
For Each fld In rs.Fields
    For x = 8 To rs.Fields.Count - 1
        Set fld = rs.Fields(x)
            If fld.Value <> "" Then
                RowData = RowData & fld.Name & ": " & fld.Value & "|" & vbNewLine
            End If
    Next x
Next fld

ClipBoard_SetText (RowData)
pasteContent (RowData)

It does not copy fields 0 - 8 which is perfect. However, the table i am copying only has one single row of data but the code is copying the data 10+ times. Any thoughts?

 

[MajP]

NO. You can either use a For Each, for iterate by the field count. Do not do both. Get rid of the outer for each.

 

[MajP]

Should read

You can either use a For Each, or iterate by the field

. In other words the following would not make any sense

for each Ctrl in me.controls
  for I = 0 to me.controls.count - 1
    do something
  next I
next ctrl

You would end up doing something N x N times instead of N times.

 

[trickshot1126]

Worked like a charm MajP... Thank you!