For...each .value on selected columns (1 Viewer)

bulbisi · Jan 26, 2011

For...each .value on selected columns SOLVED

Good morning/afternoon, ... everyone, and happy Australia day to Australian people

Trying to make a self-made XML export
looks like I must amend this code without touching my database
this is just an extract of course, you don't need everything I guess:

Code:

   For Each fld In rs.Fields
Print #1, "<"; fld.Name; ">"; fld.value; "</"; fld.Name; ">"
   Next fld

I'm actually and with success getting :

<nameofthefield1>itsvalue</nameofthefield1>
<nameofthefield2>itsvalue</nameofthefield2>
...

the problem is that this method export ALL fields (=all columns) from the query/table to my final file.
I want to select only a few of them.
Why? to make a special layout that can look like this:

<nameofthefield1>itsvalue</nameofthefield1>
<othercodenotrelatedtothetable> ... </othercodenotrelatedtothetable>
<nameofthefield2>itsvalue</nameofthefield2>

has anyone able to show me a solution? This is not a popular question on the internet.

Many thanks in advance

Chris

jdraw · Jan 26, 2011

Why not start with a vanilla query to get only the fields you want? Then use that query as the basis for the recordset (the rs in your sample code).

SELECT fld1, fld4, fldx FROM MyTable WHERE conditions

bulbisi · Jan 26, 2011

actually, my fields are extracted with that query already
but my For Each...next does not Join the queries (I need information in another query or table)
So I wanted to make only one SQL with all the datas already joined but then I cannot incorporate that manual line I was showing in my previous post, as For Each Next display all fields in a sudden.

any idea how to make it

Result sample:

<Field1>content</Field1>
<Field2>content</Field2>
<manual>
<Field3>content</Field3>
<Field4>content</Field4>
</manual>
<Field5>content</Field5>

Field# being in that SQL
the problem is those "Manual" I cannot include

vbaInet · Jan 26, 2011

Like jdraw mentioned, create another query using the query wizard and select the fields you only need for the export. You can get the fields from your original query and other fields from other tables/queries if there is a join field. Use that in the recordset.

bulbisi · Jan 26, 2011

understood
but I obtain this:

<Field1>content</Field1>
<Field2>content</Field2>
<Field3>content</Field3>
<Field4>content</Field4>
<Field5>content</Field5>

so without the break that I need. The goal is to obtain that list cut with that <manual> and </manual> stuff that I cannot incorporate into my query :/
(it is a parent level of Field3 and Field 4)

Except if you have a solution with a "if then else " or so to include this <manual> at one specific place, but I am not able to make it

vbaInet · Jan 26, 2011

What is the condition that should cause it to break? After every two records?

bulbisi · Jan 26, 2011

after every "Field3.value" for example
there is no real condition, just a specific place where to input some characters

I thought about something else but I don't know how to use it:
at the moment I'm extracting using fld.name and fld.value from rs
is there a way I can extract from a specific field name?
I didn't try it yet but it might sound like that:

For Each fld In rs.Fields
Print #1, "<FieldName1>"; fld!FieldName1.value; "</FieldName1>"
Next fld

vbaInet · Jan 26, 2011

Code:

dim i as long

for i = 0 to rs.Fields.Count - 1
     if i = 3 then
           Print #1, "<manual>"
           Print #1, "<"; fld.Name; ">"; fld.value; "</"; fld.Name; ">"
     else if i = 4 then
           Print #1, "<"; fld.Name; ">"; fld.value; "</"; fld.Name; ">"
           Print #1, "</manual>"
     else
           Print #1, "<"; fld.Name; ">"; fld.value; "</"; fld.Name; ">"
     end if
next

bulbisi · Jan 27, 2011

interesting
this, and all kind of Array's that I never used are probably my solution. ... if at last I am able to use them

Code:

dim i as long

....
 
 
Print #1, Tab(3); "<"; lvl3; ">"
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
    If i = 3 Then
          Print #1, "<ADDRESS>"
          Print #1, "<"; fld.Name; ">"; fld.Value; "</"; fld.Name; ">"
    ElseIf i = 4 Then
          Print #1, "<"; fld.Name; ">"; fld.Value; "</"; fld.Name; ">"
          Print #1, "</ADDRESS>"
    Else
[COLOR=darkorange]          Print #1, "<"; fld.Name; ">"; fld.Value; "</"; fld.Name; ">"
[/COLOR]    End If
Next
rs.MoveNext
   Loop
Print #1, Tab(3); "</"; lvl3; ">"
...

considering my original code is perfectly working but without that specific question.
Here I get an error number 91 which is defined as "Object variable not set"
The Access help, as usual, is good enough I reckon for those who studied or managed programming, but not for guys like me who are learning 'on the road'. Thanks to internet from where I learnt everything.

To give you an idea, I got 9 variables from my SQL, and the result ideally would be:
FirstName
FamilyName
DateOfBirth
Address
StreetAndNumber
PostBox
PostCode
City
Amount
...

"Address" not being from SQL because empty of any value (=just a title)

I'm still searching on my side

thanks for your support anyway, any tip is appreciated

vbaInet · Jan 27, 2011

Notice in my code I'm not using For Each fld, so you can't refer to fld. Replace fld with rs.Fields(i)

bulbisi · Jan 27, 2011

This is just absolutely beautiful.
I close this thread as SOLVED
thank you so much (the scale button is awaiting for a click)

vbaInet · Jan 27, 2011

Happy to hear bulbisi!

For...each .value on selected columns (1 Viewer)

bulbisi

Registered User.

jdraw

Super Moderator

bulbisi

Registered User.

vbaInet

AWF VIP

bulbisi

Registered User.

vbaInet

AWF VIP

bulbisi

Registered User.

vbaInet

AWF VIP

bulbisi

Registered User.

vbaInet

AWF VIP

bulbisi

Registered User.

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread