MS Access VBA Need Help Looping Code Through Query Records To Generate a Long Text File

I am in need of some help please. This is a major part of my project I am trying to finish.  I have a query named Item and in that query are my records I need.

I need my code to Loop through the String "AddFixedPriceItem2" for each record in my query APPENDING the AddFixedPriceItem2 Strings to each other and then Call "SaveStringAsTextFile"

Here is my coding so far:
Public Function PrepSaveStringAsTextFile()
Dim AddFixedPriceItem2 As String
Dim Title As String
    Dim Description As String
    Dim Price As String
    Dim MPN As String
    Dim Brand As String
    Dim UPC As String
    Dim Quantity As Integer
    Dim SKU As String
    
    Title = DLookup("Title", "Item", "")
    Description = DLookup("Description", "Item", "")
    Price = DLookup("Price", "Item", "")
    MPN = DLookup("MPN", "Item", "")
    Brand = DLookup("Brand", "Item", "")
    UPC = DLookup("UPC", "Item", "")
    Quantity = DLookup("Quantity", "Item", "")
    SKU = DLookup("SKU", "Item", "")
    
    AddFixedPriceItem2 = "<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel><Item><Title>" & Title & "</Title> <Description>" & Description & "</Description><PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory><StartPrice>" & Price & "</StartPrice><InventoryTrackingMethod>SKU</InventoryTrackingMethod><SKU>" & SKU & "</SKU><CategoryMappingAllowed>true</CategoryMappingAllowed><ConditionID>3000</ConditionID><Country>US</Country><Currency>USD</Currency><DispatchTimeMax>3</DispatchTimeMax><ListingDuration>Days_7</ListingDuration><ListingType>FixedPriceItem</ListingType><PaymentMethods>PayPal</PaymentMethods><!--Enter yo" _
& "ur Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress><PostalCode>92078</PostalCode><ProductListingDetails><BrandMPN> BrandMPNType<Brand>" & Brand & "</Brand><M" _
& "PN>" & MPN & "</MPN></BrandMPN><UPC>" & UPC & "</UPC></ProductListingDetails><Quantity>" & Quantity & "</Quantity><ReturnPolicy><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><RefundOption>MoneyBack</RefundOption><ReturnsWithinOption>Days_30</ReturnsWithinOption><Description>If you arenot satisfied, return the item for refund.</Description><ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption></ReturnPolicy><ShippingDetails><ShippingType>Flat</ShippingType><ShippingServiceOptions><ShippingServicePriority>1</ShippingServicePriority><ShippingService>UPSGround</ShippingService><FreeShipping>true</FreeShipping><ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost></ShippingServiceOptions></ShippingDetails><Site>US</Site><!-- If the sel" _
& "ler is subscribed to Business Policies, use the <SellerProfiles> Container instead of the <ShippingDetails>, <PaymentMethods> and <ReturnPolicy> containers. For help, see the API Reference for Business Policies:http://developer.ebay.com/Devzo" _
& "n business-policies/CallRef/index.html --><!--<SellerProfiles><SellerShippingProfile><ShippingProfileID>5001287000</ShippingProfileID></SellerShippingProfile><SellerReturnProfile><ReturnProfileID>5001288000</ReturnProfileID></SellerReturnProfile><SellerPaymentProfile><PaymentProfileID>5001286000</PaymentProfileID></SellerPaymentProfile></SellerProfiles> --></Item></AddFixedPriceItemRequest>"
    
   '<PictureDetails><GalleryType>Gallery</GalleryType><PictureURL> anyURI </PictureURL></PictureDetails> 'Place right after PaypalEmailAddress
   
Call SaveStringAsTextFile("C:\Users\Station\Documents\Access XML Save Files\Test14.xml", AddFixedPriceItem2)
End Function

Public Sub SaveStringAsTextFile(psPathFile As String, psFileContents)
'160730 strive4peace
   Dim iFile As Integer
   
   iFile = FreeFile
   Open psPathFile For Output As iFile
   Print #iFile, psFileContents
   Close iFile

End Sub

Open in new window



Expectations In My Exported Text File:
<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel><Item><Title>" & Title </Title> <Description>" & Description </Description><PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory><StartPrice>" & Price </StartPrice><InventoryTrackingMethod>SKU</InventoryTrackingMethod><SKU>" & SKU </SKU><CategoryMappingAllowed>true</CategoryMappingAllowed><ConditionID>3000</ConditionID><Country>US</Country><Currency>USD</Currency><DispatchTimeMax>3</DispatchTimeMax><ListingDuration>Days_7</ListingDuration><ListingType>FixedPriceItem</ListingType><PaymentMethods>PayPal</PaymentMethods><!--Enter your Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress><PostalCode>92078</PostalCode><ProductListingDetails>
<BrandMPN> BrandMPNType<Brand>" & Brand </Brand><MPN>" & MPN </MPN></BrandMPN><UPC>" & UPC </UPC></ProductListingDetails><Quantity>" & Quantity </Quantity><ReturnPolicy><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><RefundOption>MoneyBack</RefundOption><ReturnsWithinOption>Days_30</ReturnsWithinOption><Description>If you arenot satisfied, return the item for refund.</Description><ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption></ReturnPolicy><ShippingDetails><ShippingType>Flat</ShippingType><ShippingServiceOptions><ShippingServicePriority>1</ShippingServicePriority><ShippingService>UPSGround</ShippingService><FreeShipping>true</FreeShipping><ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost>
</ShippingServiceOptions></ShippingDetails><Site>US</Site><!-- If the seller is subscribed to Business Policies, use the <SellerProfiles> Container instead of the <ShippingDetails>, <PaymentMethods> and <ReturnPolicy> containers. For help, see the API Reference for Business Policies:http://developer.ebay.com/Devzon business-policies/CallRef/index.html --><!--<SellerProfiles><SellerShippingProfile><ShippingProfileID>5001287000</ShippingProfileID></SellerShippingProfile><SellerReturnProfile><ReturnProfileID>5001288000</ReturnProfileID></SellerReturnProfile><SellerPaymentProfile><PaymentProfileID>5001286000</PaymentProfileID></SellerPaymentProfile></SellerProfiles> --></Item></AddFixedPriceItemRequest>
<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel><Item><Title>" & Title </Title> <Description>" & Description </Description><PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory><StartPrice>" & Price </StartPrice><InventoryTrackingMethod>SKU</InventoryTrackingMethod><SKU>" & SKU </SKU><CategoryMappingAllowed>true</CategoryMappingAllowed><ConditionID>3000</ConditionID><Country>US</Country><Currency>USD</Currency><DispatchTimeMax>3</DispatchTimeMax><ListingDuration>Days_7</ListingDuration><ListingType>FixedPriceItem</ListingType><PaymentMethods>PayPal</PaymentMethods><!--Enter your Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress><PostalCode>92078</PostalCode><ProductListingDetails>
<BrandMPN> BrandMPNType<Brand>" & Brand </Brand><MPN>" & MPN </MPN></BrandMPN><UPC>" & UPC </UPC></ProductListingDetails><Quantity>" & Quantity </Quantity><ReturnPolicy><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><RefundOption>MoneyBack</RefundOption><ReturnsWithinOption>Days_30</ReturnsWithinOption><Description>If you arenot satisfied, return the item for refund.</Description><ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption></ReturnPolicy><ShippingDetails><ShippingType>Flat</ShippingType><ShippingServiceOptions><ShippingServicePriority>1</ShippingServicePriority><ShippingService>UPSGround</ShippingService><FreeShipping>true</FreeShipping><ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost>
</ShippingServiceOptions></ShippingDetails><Site>US</Site><!-- If the seller is subscribed to Business Policies, use the <SellerProfiles> Container instead of the <ShippingDetails>, <PaymentMethods> and <ReturnPolicy> containers. For help, see the API Reference for Business Policies:http://developer.ebay.com/Devzon business-policies/CallRef/index.html --><!--<SellerProfiles><SellerShippingProfile><ShippingProfileID>5001287000</ShippingProfileID></SellerShippingProfile><SellerReturnProfile><ReturnProfileID>5001288000</ReturnProfileID></SellerReturnProfile><SellerPaymentProfile><PaymentProfileID>5001286000</PaymentProfileID></SellerPaymentProfile></SellerProfiles> --></Item></AddFixedPriceItemRequest>
<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel><Item><Title>" & Title </Title> <Description>" & Description </Description><PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory><StartPrice>" & Price </StartPrice><InventoryTrackingMethod>SKU</InventoryTrackingMethod><SKU>" & SKU </SKU><CategoryMappingAllowed>true</CategoryMappingAllowed><ConditionID>3000</ConditionID><Country>US</Country><Currency>USD</Currency><DispatchTimeMax>3</DispatchTimeMax><ListingDuration>Days_7</ListingDuration><ListingType>FixedPriceItem</ListingType><PaymentMethods>PayPal</PaymentMethods><!--Enter your Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress><PostalCode>92078</PostalCode><ProductListingDetails>
<BrandMPN> BrandMPNType<Brand>" & Brand </Brand><MPN>" & MPN </MPN></BrandMPN><UPC>" & UPC </UPC></ProductListingDetails><Quantity>" & Quantity </Quantity><ReturnPolicy><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><RefundOption>MoneyBack</RefundOption><ReturnsWithinOption>Days_30</ReturnsWithinOption><Description>If you arenot satisfied, return the item for refund.</Description><ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption></ReturnPolicy><ShippingDetails><ShippingType>Flat</ShippingType><ShippingServiceOptions><ShippingServicePriority>1</ShippingServicePriority><ShippingService>UPSGround</ShippingService><FreeShipping>true</FreeShipping><ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost>
</ShippingServiceOptions></ShippingDetails><Site>US</Site><!-- If the seller is subscribed to Business Policies, use the <SellerProfiles> Container instead of the <ShippingDetails>, <PaymentMethods> and <ReturnPolicy> containers. For help, see the API Reference for Business Policies:http://developer.ebay.com/Devzon business-policies/CallRef/index.html --><!--<SellerProfiles><SellerShippingProfile><ShippingProfileID>5001287000</ShippingProfileID></SellerShippingProfile><SellerReturnProfile><ReturnProfileID>5001288000</ReturnProfileID></SellerReturnProfile><SellerPaymentProfile><PaymentProfileID>5001286000</PaymentProfileID></SellerPaymentProfile></SellerProfiles> --></Item></AddFixedPriceItemRequest>

Open in new window



Etc....

Etc....

Until all records have been appended.

Thank you for the help!
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
a.  This:

    Title = DLookup("Title", "Item", "")
    Description = DLookup("Description", "Item", "")
    Price = DLookup("Price", "Item", "")
    MPN = DLookup("MPN", "Item", "")
    Brand = DLookup("Brand", "Item", "")
    UPC = DLookup("UPC", "Item", "")
    Quantity = DLookup("Quantity", "Item", "")
    SKU = DLookup("SKU", "Item", "")

 Is a slow way to do things.  Open a recordset, find the record you want, then have access to all the fields at once.   With a Dlookup(), your opening a recordset and finding the record each time.   The find part will be fast because the page will be in memory, but you have the overhead of opening a recordset on each call.

b. Checkout this article:

https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

 Drop the function in, call it, and it will hand you back a string of all the values for a specific column.

Jim.
0
Dustin StanleyEntrepreneurAuthor Commented:
Thank you Jim. You suggested this article to me awhile back ago and I have used it to Concatenating Values before. It gathered all the Picture Urls for a single item and it worked great. Thanks.

But I am confused at how this will work in this exact situation here.  I have not the clue on how to apply the method you suggested to my situation now.???? Thanks for the help Jim.
0
Dustin StanleyEntrepreneurAuthor Commented:
The Dlookup is found in the middle of my String  "AddFixedPriceItem2 ":
Public Function PrepSaveStringAsTextFile()
Dim AddFixedPriceItem2 As String
Dim Title As String
    Dim Description As String
    Dim Price As String
    Dim MPN As String
    Dim Brand As String
    Dim UPC As String
    Dim Quantity As Integer
    Dim SKU As String
   
    Title = DLookup("Title", "Item", "")
    Description = DLookup("Description", "Item", "")
    Price = DLookup("Price", "Item", "")
    MPN = DLookup("MPN", "Item", "")
    Brand = DLookup("Brand", "Item", "")
    UPC = DLookup("UPC", "Item", "")
    Quantity = DLookup("Quantity", "Item", "")
    SKU = DLookup("SKU", "Item", "")

   
    AddFixedPriceItem2 = "<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel><Item><Title>" & Title & "</Title> <Description>" & Description & "</Description><PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory><StartPrice>" & Price & "</StartPrice><InventoryTrackingMethod>SKU</InventoryTrackingMethod><SKU>" & SKU & "</SKU><CategoryMappingAllowed>true</CategoryMappingAllowed><ConditionID>3000</ConditionID><Country>US</Country><Currency>USD</Currency><DispatchTimeMax>3</DispatchTimeMax><ListingDuration>Days_7</ListingDuration><ListingType>FixedPriceItem</ListingType><PaymentMethods>PayPal</PaymentMethods><!--Enter yo" _
& "ur Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress><PostalCode>92078</PostalCode><ProductListingDetails><BrandMPN> BrandMPNType<Brand>" & Brand & "</Brand><M" _
& "PN>" & MPN & "</MPN></BrandMPN><UPC>" & UPC & "</UPC></ProductListingDetails><Quantity>" & Quantity & "</Quantity><ReturnPolicy><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><RefundOption>MoneyBack</RefundOption><ReturnsWithinOption>Days_30</ReturnsWithinOption><Description>If you arenot satisfied, return the item for refund.</Description><ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption></ReturnPolicy><ShippingDetails><ShippingType>Flat</ShippingType><ShippingServiceOptions><ShippingServicePriority>1</ShippingServicePriority><ShippingService>UPSGround</ShippingService><FreeShipping>true</FreeShipping><ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost></ShippingServiceOptions></ShippingDetails><Site>US</Site><!-- If the sel" _
& "ler is subscribed to Business Policies, use the <SellerProfiles> Container instead of the <ShippingDetails>, <PaymentMethods> and <ReturnPolicy> containers. For help, see the API Reference for Business Policies:http://developer.ebay.com/Devzo" _
& "n business-policies/CallRef/index.html --><!--<SellerProfiles><SellerShippingProfile><ShippingProfileID>5001287000</ShippingProfileID></SellerShippingProfile><SellerReturnProfile><ReturnProfileID>5001288000</ReturnProfileID></SellerReturnProfile><SellerPaymentProfile><PaymentProfileID>5001286000</PaymentProfileID></SellerPaymentProfile></SellerProfiles> --></Item></AddFixedPriceItemRequest>"
   
   '<PictureDetails><GalleryType>Gallery</GalleryType><PictureURL> anyURI </PictureURL></PictureDetails> 'Place right after PaypalEmailAddress
   
Call SaveStringAsTextFile("C:\Users\Station\Documents\Access XML Save Files\Test14.xml", AddFixedPriceItem2)
End Function

Public Sub SaveStringAsTextFile(psPathFile As String, psFileContents)
'160730 strive4peace
   Dim iFile As Integer
   
   iFile = FreeFile
   Open psPathFile For Output As iFile
   Print #iFile, psFileContents
   Close iFile

End Sub

I made the areas BOLD where they are found. Thanks
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
So you want to write out each record from the Item query?

Jim.
0
Dustin StanleyEntrepreneurAuthor Commented:
AddFixedPriceItem2  is a String I have created. I don't know if you seen my comment above. I was posting as you were. But AddFixedPriceItem2  is basically a template. The Dlookup inserts what I need into AddFixedPriceItem2  the template. I hope that makes sense.

Thanks for the help!
0
Dustin StanleyEntrepreneurAuthor Commented:
So you want to write out each record from the Item query?

I believe yes. IF we are on the same page.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You need to be structured differently.   The logic would be:

1. Open a recordset on item
2. Open a file for append.
3. Write any header to the file

4. For each record in recordset
       a. write a line out to the file
       b. loop

5. Write any trailer to the file
6. Close the file.

 All your items will end up in one file then....does that make sense?

Jim.
0
Dustin StanleyEntrepreneurAuthor Commented:
Well Jim as we know I am still a beginner here. So I kinda work with what I know ;) So your saying:

1. Open a Record
2. Open a File To Append (Txt or etc...)
3. Write any header to file  ( I am not sure what you mean there unless if it is a XML header???)

4.For each record in recordset
       a. write a line out to the file  (Write the record to the txt etc... File)
       b. loop (Loop to next record and do step A.)

I need that template String AddFixedPriceItem2  to be in every record. With the inserted fields from my query. Maybe I'm not getting it 100%. Pretty sure i'm not...

5. Write any trailer to the file (Not sure here either.???)
6. Close the file. (self explanatory)
0
Dustin StanleyEntrepreneurAuthor Commented:
Sorry if I am confusing but what I mean by "I need that template String AddFixedPriceItem2  to be in every record. " is I need it in the file line as in Step 4.A

Step 4 is what I am needing.

Thanks!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, #3 is any XML header, or in other words, anything that needs to appear at the start of the file before the data for the individual records.   If you were writing a CSV file, this would be something like the column headings.

#5 is more or less the same thing; anything that should appear at the end of the file after the detail.  Something like a total or a record count.

Hang on a minute and I'll post an outline of what you need.

Jim.
0
Dustin StanleyEntrepreneurAuthor Commented:
I really apprecaite it Jim. Take your time. I am leaving now to my daughter's Softball practice for a couple of hours. Thanks for help!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK this is the outline.   I've stripped out a lot of things you didn't need.  Note that this uses an ADO recordset, but you can use DAO as well.  ie.

Dim db as DAO.Database
Dim rstItem as DAO.Recordset

Set db = CurrentDB()
Set rstitem = db.OpenRecordset("item")

and note in the code below, AddToString is this:

Private Function AddToString(psOutput As String, psString As String)

10        psOutput = psOutput & psString

End Function

Open in new window



 which is a bit of a short-hand of saying:

     
strInventoryData = strInventoryData & "something"



and since you now have a recordset, you can refer to the fields directly in AddFixedPriceItem2.  ie.:

  AddFixedPriceItem2   = AddFixedPriceItem2  & "<Description>" & rstItems![Description]

 in the code.   No need for additional variables, unless you need to work with them in some way before placing them in the string.

Jim.


Public Sub SendStaplesComInventoryStatus()

    ' Sends an CSV inventory advice file via FTP to Staples Exchange

    ' 1.0.0.0 - 04/02/15 - OCS/JRD - Initial write.
    ' 1.1.0.0 - 07/07/15 - OCS/JRD - Added over rides for certain SKUs to always send 200 as QOH.
    ' 1.2.0.0 - 02/27/17 - OCS/JRD - Bug fix on QOH, QOO, and committed calculation.

    Const RoutineName = "SendStaplesComInventoryStatus"
    Const Version = "1.2.0.0"

    Dim strFileOutputPath As String
    Dim strFileName As String
    Dim strFile As String
    Dim strMailMsg As String

    Dim rstInvInfo As ADODB.Recordset
    Dim rstInvQuantities As ADODB.Recordset

    Dim dteNextAvailable As Date
    Dim intItemCount As Integer
    Dim lngQtyOH As Long
    Dim lngQtyOO As Long

    Dim strSQL As String
    Dim strInventoryData As String
    Dim intInventoryFileNum As Integer
    Dim intRet As Integer

    On Error GoTo Error_Procedure


    strFileName = "123456_inventory_" & Format$(Now(), "YYYYMMDD_HHMM") & ".dat"
    strFile = strFileOutputPath & strFileName
    intInventoryFileNum = FreeFile

   
    Open strFile For Append As #intInventoryFileNum

    ' Get the items - this would be your item query
    strSQL = "SELECT Tbl_LabelAlias.AliasID, Tbl_LabelAlias.AliasDescr, Tbl_LabelAlias.UserID, " & _
             "Tbl_LabelAlias.ItemID, tblInItem.Descr, tblInItemUom.UPCcode, tblInItem.UomBase, tblInItem.ItemStatus " & _
             "FROM (Tbl_LabelAlias INNER JOIN tblInItem ON Tbl_LabelAlias.ItemID = tblInItem.ItemId) " & _
             "INNER JOIN tblInItemUom ON (tblInItem.UomBase = tblInItemUom.Uom) AND (tblInItem.ItemId = tblInItemUom.ItemId) " & _
             "WHERE Tbl_LabelAlias.UserID='STAPLESCOM';"

    OpenADORecordset rstInvInfo, strSQL
    intItemCount = 0

    With rstInvInfo
        Do Until .EOF
            intItemCount = intItemCount + 1

             strInventoryData = ""
            AddToString strInventoryData, "61141,"
            AddToString strInventoryData, ![ItemID] & ","
            AddToString strInventoryData, ![AliasID] & ","
            AddToString strInventoryData, ","
            AddToString strInventoryData, ","
            AddToString strInventoryData, "NEW,"
            AddToString strInventoryData, Format$(lngQtyOH, "0") & ","

            ' Next avail qty and date
            If lngQtyOH = 0 Then
                AddToString strInventoryData, Format$(lngQtyOO, "0") & ","
                AddToString strInventoryData, Format$(dteNextAvailable, "YYYY-MM-DD") & ","
            Else
                AddToString strInventoryData, ","
                AddToString strInventoryData, ","
            End If

            ' Lead times
            AddToString strInventoryData, "2,"
            AddToString strInventoryData, "2,"

            ' Avail End Date
            If rstInvInfo![ItemStatus] = 1 Then
                AddToString strInventoryData, ","
                If lngQtyOH = 0 Then
                    AddToString strInventoryData, "Out of Stock,"
                Else
                    AddToString strInventoryData, "Available,"
                End If
            Else
                AddToString strInventoryData, "2029-12-31,"
                AddToString strInventoryData, "Discontinued,"
            End If

            AddToString strInventoryData, "EA"

            ' Write to file
            Print #intInventoryFileNum, strInventoryData

            .MoveNext
        Loop
    End With

    ' Write any trailer here - this file didn't need any.

Exit_Procedure:
    On Error Resume Next

    Close #intInventoryFileNum

    rstInvInfo.Close
    Set rstInvInfo = Nothing

    Exit Sub

Error_Procedure:
    ' error handler here.
    Resume Exit_Procedure

Open in new window

0
Dustin StanleyEntrepreneurAuthor Commented:
Thank you Jim for the help. This is throwing me for a loop.  I am not sure on how to use this exactly. I have stripped even more code to just make it bare to what I think I can use for now. Just to make it more understandable for me and no luck....

Option Compare Database
Option Explicit

Public Sub SendStaplesComInventoryStatus()

    Dim strFileOutputPath As String
    Dim strFileName As String
    Dim strFile As String

    Dim rstInvInfo As ADODB.Recordset

    Dim intItemCount As Integer
    Dim lngQtyOH As Long

    Dim strSQL As String
    Dim strInventoryData As String
    Dim intInventoryFileNum As Integer

    On Error GoTo Error_Procedure


    strFileName = "123456_inventory_" & Format$(Now(), "YYYYMMDD_HHMM") & ".dat"
    strFile = strFileOutputPath & strFileName
    intInventoryFileNum = FreeFile

   
    Open strFile For Append As #intInventoryFileNum

    ' Get the items - this would be your item query
    strSQL = "SELECT SKUs.SKU, SKUs.SkuNm AS Title, SKUs.SkuMPN AS MPN, SKUs.UPC, SKUs.SkuDescr AS Description, Products.Price, Manufactures.ManuNm AS Brand, ProdLocations.QtyLoc AS Quantity, IIf([CondCode]=""NSOP"",""1000"",IIf([CondCode]=""NSSP"",""1500"",IIf([CondCode]=""USOP"",""3000"",IIf([CondCode]=""USSP"",""3000"")))) AS Condition" _
& "FROM (Conditions RIGHT JOIN (Manufactures LEFT JOIN (SKUs LEFT JOIN Products ON SKUs.SkuID = Products.SkuID) ON Manufactures.ManuID = SKUs.ManuID) ON Conditions.ConditionID = Products.ConditionID) LEFT JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID;"


    OpenADORecordset rstInvInfo, strSQL
    intItemCount = 0

    With rstInvInfo
        Do Until .EOF
            intItemCount = intItemCount + 1

             strInventoryData = ""
            AddToString strInventoryData, "61141,"
            AddToString strInventoryData, ![ItemID] & ","
            AddToString strInventoryData, ![AliasID] & ","
            AddToString strInventoryData, ","
            AddToString strInventoryData, ","
            AddToString strInventoryData, "NEW,"
            AddToString strInventoryData, Format$(lngQtyOH, "0") & ","

            
            ' Write to file
            Print #intInventoryFileNum, strInventoryData

            .MoveNext
        Loop
    End With

    ' Write any trailer here - this file didn't need any.

Exit_Procedure:
    On Error Resume Next

    Close #intInventoryFileNum

    rstInvInfo.Close
    Set rstInvInfo = Nothing

    Exit Sub

Error_Procedure:
    ' error handler here.
    Resume Exit_Procedure
    
    Private Function AddToString(psOutput As String, psString As String)

10        psOutput = psOutput & psString

End Function

Open in new window


I added my SQL Query. and attached the Private Function at the bottom of the module.


You said:
and since you now have a recordset, you can refer to the fields directly in AddFixedPriceItem2.  ie.:

  AddFixedPriceItem2   = AddFixedPriceItem2  & "<Description>" & rstItems![Description]

 in the code.


But I do not understand where I am supposed to  do this or how exactly.  Like I said I know the ways I do things are not the best. I just try to do them the way I understand. Thanks.
0
Dustin StanleyEntrepreneurAuthor Commented:
What I need is a text file that will have all my records in it. But the text has to be just like this.

<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel><Item><Title>YELLOW TOY TRUCK</Title> <Description>PLASTIC AND YELLOW</Description><PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory><StartPrice>9.95</StartPrice><InventoryTrackingMethod>SKU</InventoryTrackingMethod><SKU>GAHYWT53JKL</SKU><CategoryMappingAllowed>true</CategoryMappingAllowed><ConditionID>3000</ConditionID><Country>US</Country><Currency>USD</Currency><DispatchTimeMax>3</DispatchTimeMax><ListingDuration>Days_7</ListingDuration><ListingType>FixedPriceItem</ListingType><PaymentMethods>PayPal</PaymentMethods><!--Enter your Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress><PostalCode>92078</PostalCode><ProductListingDetails>
<BrandMPN> BrandMPNType<Brand>BRANDYAY</Brand><MPN>YEL987</MPN></BrandMPN><UPC>765456787654</UPC></ProductListingDetails><Quantity>7</Quantity><ReturnPolicy><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><RefundOption>MoneyBack</RefundOption><ReturnsWithinOption>Days_30</ReturnsWithinOption><Description>If you arenot satisfied, return the item for refund.</Description><ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption></ReturnPolicy><ShippingDetails><ShippingType>Flat</ShippingType><ShippingServiceOptions><ShippingServicePriority>1</ShippingServicePriority><ShippingService>UPSGround</ShippingService><FreeShipping>true</FreeShipping><ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost>
<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel><Item><Title>BLUE TOY TRUCK</Title> <Description>PLASTIC AND BLUE</Description><PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory><StartPrice>24.50</StartPrice><InventoryTrackingMethod>SKU</InventoryTrackingMethod><SKU>HDFS45AREWT</SKU><CategoryMappingAllowed>true</CategoryMappingAllowed><ConditionID>3000</ConditionID><Country>US</Country><Currency>USD</Currency><DispatchTimeMax>3</DispatchTimeMax><ListingDuration>Days_7</ListingDuration><ListingType>FixedPriceItem</ListingType><PaymentMethods>PayPal</PaymentMethods><!--Enter your Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress><PostalCode>92078</PostalCode><ProductListingDetails>
<BrandMPN> BrandMPNType<Brand>YOLIUJ</Brand><MPN>BLU765</MPN></BrandMPN><UPC>789686958692</UPC></ProductListingDetails><Quantity>7</Quantity><ReturnPolicy><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><RefundOption>MoneyBack</RefundOption><ReturnsWithinOption>Days_30</ReturnsWithinOption><Description>If you arenot satisfied, return the item for refund.</Description><ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption></ReturnPolicy><ShippingDetails><ShippingType>Flat</ShippingType><ShippingServiceOptions><ShippingServicePriority>1</ShippingServicePriority><ShippingService>UPSGround</ShippingService><FreeShipping>true</FreeShipping><ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost>


*********************************************************
That was two different products above. One right after the other.  So basically I have the template that looks like this:
<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel><Item><Title>" & Title & "</Title> <Description>" & Description & "</Description><PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory><StartPrice>" & Price </StartPrice><InventoryTrackingMethod>SKU</InventoryTrackingMethod><SKU>" & SKU & "</SKU><CategoryMappingAllowed>true</CategoryMappingAllowed><ConditionID>3000</ConditionID><Country>US</Country><Currency>USD</Currency><DispatchTimeMax>3</DispatchTimeMax><ListingDuration>Days_7</ListingDuration><ListingType>FixedPriceItem</ListingType><PaymentMethods>PayPal</PaymentMethods><!--Enter your Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress><PostalCode>92078</PostalCode><ProductListingDetails>
<BrandMPN> BrandMPNType<Brand>" & Brand & "</Brand><MPN>" & MPN & "</MPN></BrandMPN><UPC>" & UPC & "</UPC></ProductListingDetails><Quantity>" & Quantity </Quantity><ReturnPolicy><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><RefundOption>MoneyBack</RefundOption><ReturnsWithinOption>Days_30</ReturnsWithinOption><Description>If you arenot satisfied, return the item for refund.</Description><ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption></ReturnPolicy><ShippingDetails><ShippingType>Flat</ShippingType><ShippingServiceOptions><ShippingServicePriority>1</ShippingServicePriority><ShippingService>UPSGround</ShippingService><FreeShipping>true</FreeShipping><ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost>
</ShippingServiceOptions></ShippingDetails><Site>US</Site><!-- If the seller is subscribed to Business Policies, use the <SellerProfiles> Container instead of the <ShippingDetails>, <PaymentMethods> and <ReturnPolicy> containers. For help, see the API Reference for Business Policies:http://developer.ebay.com/Devzon business-policies/CallRef/index.html --><!--<SellerProfiles><SellerShippingProfile><ShippingProfileID>5001287000</ShippingProfileID></SellerShippingProfile><SellerReturnProfile><ReturnProfileID>5001288000</ReturnProfileID></SellerReturnProfile><SellerPaymentProfile><PaymentProfileID>5001286000</PaymentProfileID></SellerPaymentProfile></SellerProfiles> --></Item></AddFixedPriceItemRequest>

Open in new window


And what I have currently the Dlookup just places in the proper value. ie..
" & SKU &" would be "HDFS45AREWT" and "GAHYWT53JKL"

SKU = DLookup("SKU", "Item", "")

I am sorry if I am repeating myself. I just want to make sure we are on the same page. I really appreciate the help and I know your one of the best. Thanks Jim.
0
Dustin StanleyEntrepreneurAuthor Commented:
Ok i finally got it to respond and send back files. Please let me know what you think. Thank you so much for the help.

Public Sub SendStaplesComInventoryStatus()
    Dim db As DAO.Database
    Dim rstItem As DAO.Recordset

    Set db = CurrentDb()
    Set rstItem = db.OpenRecordset("item")

    Dim strFileOutputPath As String
    Dim strFileName As String
    Dim strFile As String
    Dim strMailMsg As String

    Dim dteNextAvailable As Date
    Dim intItemCount As Integer
    Dim lngQtyOH As Long
    Dim lngQtyOO As Long

    Dim strSQL As String
    Dim intInventoryFileNum As Integer
    Dim intRet As Integer
    Dim AddFixedPriceItem2 As String

    On Error GoTo Error_Procedure
    
    

    strFileOutputPath = ("C:\Users\Station\Documents\Access XML Save Files\Test14.txt")
    'strFileName = "123456_inventory_" & Format$(Now(), "YYYYMMDD_HHMM") & ".dat"
    strFile = strFileOutputPath ' & strFileName
    intInventoryFileNum = FreeFile

   
    Open strFile For Append As #intInventoryFileNum

    ' Get the items - this would be your item query
    strSQL = "SELECT SKUs.SkuID, SKUs.SKU, SKUs.SkuNm AS Title, SKUs.SkuMPN AS MPN, SKUs.UPC, SKUs.SkuDescr AS Description, Products.Price, Manufactures.ManuNm AS Brand, ProdLocations.QtyLoc AS Quantity, IIf([CondCode]=""NSOP"",""1000"",IIf([CondCode]=""NSSP"",""1500"",IIf([CondCode]=""USOP"",""3000"",IIf([CondCode]=""USSP"",""3000"")))) AS Condition" _
& "FROM (Conditions RIGHT JOIN (Manufactures LEFT JOIN (SKUs LEFT JOIN Products ON SKUs.SkuID = Products.SkuID) ON Manufactures.ManuID = SKUs.ManuID) ON Conditions.ConditionID = Products.ConditionID) LEFT JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID" _
& "WHERE (((SKUs.SkuID)<15));"

   rstItem.OpenRecordset strSQL ' Opens RecordSet
    intItemCount = 0

    With rstItem
        Do Until .EOF
            intItemCount = intItemCount + 1

           AddFixedPriceItem2 = "<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel><Item><Title>" & rstItem![Title] & " </Title><Description>" & rstItem![Description] & "</Description><PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory><StartPrice>" & rstItem![Price] & "</StartPrice><InventoryTrackingMethod>SKU</InventoryTrackingMethod><SKU>" & rstItem![SKU] & "</SKU>" _
& "<CategoryMappingAllowed>true</CategoryMappingAllowed><ConditionID>" & rstItem![Condition] & "</ConditionID><Country>US</Country><Currency>USD</Currency><DispatchTimeMax>3</DispatchTimeMax><ListingDuration>Days_7</ListingDuration><ListingType>FixedPriceItem</ListingType><PaymentMethods>PayPal</PaymentMethods><!--Enter your Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress><PostalCode>92078</PostalCode><ProductListingDetails><BrandMPN> BrandMPNType<Brand>" & rstItem![Brand] & "</Brand>" _
& "<MPN>" & rstItem![MPN] & "</MPN></BrandMPN><UPC>" & rstItem![UPC] & "</UPC></ProductListingDetails><Quantity>7</Quantity><ReturnPolicy><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><RefundOption>MoneyBack</RefundOption><ReturnsWithinOption>Days_30</ReturnsWithinOption><Description>If you arenot satisfied, return the item for refund.</Description><ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption></ReturnPolicy><ShippingDetails><ShippingType>Flat</ShippingType><ShippingServiceOptions><ShippingServicePriority>1</ShippingServicePriority><ShippingService>UPSGround</ShippingService><FreeShipping>true</FreeShipping><ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost>"

            ' Write to file
            Print #intInventoryFileNum, AddFixedPriceItem2

            .MoveNext
        Loop
    End With

    ' Write any trailer here - this file didn't need any.

Exit_Procedure:
    On Error Resume Next

    Close #intInventoryFileNum

    rstItem.Close
    Set rstItem = Nothing

    Exit Sub

Error_Procedure:
    ' error handler here.
    Resume Exit_Procedure
    End Sub

Open in new window



I honestly have not a clue if this is even close or WAY OFF! Thanks for the help. ****UPDATE**** Out of no where it just started sending blanks. The text files are blank. I didn't change a thing.........
0
Dustin StanleyEntrepreneurAuthor Commented:
UPDATE******

Here is what I have:
Option Compare Database
Option Explicit

Public Sub SendStaplesComInventoryStatus()
    Dim db As DAO.Database
    Dim rstItem As DAO.Recordset

    Set db = CurrentDb()
    Set rstItem = db.OpenRecordset("item")

    Dim strFile As String
    Dim intItemCount As Integer
    Dim strSQL As String
    Dim intInventoryFileNum As Integer
    Dim AddFixedPriceItem2 As String

    On Error GoTo Error_Procedure
    
    strFile = ("C:\Users\Station\Documents\Access XML Save Files\Test14.txt")
    intInventoryFileNum = FreeFile

   
    Open strFile For Append As #intInventoryFileNum

    ' Get the items - this would be your item query
    strSQL = "SELECT SKUs.SkuID, SKUs.SKU, SKUs.SkuNm AS Title, SKUs.SkuMPN AS MPN, SKUs.UPC, SKUs.SkuDescr AS Description, Products.Price, Manufactures.ManuNm AS Brand, ProdLocations.QtyLoc AS Quantity, IIf([CondCode]=""NSOP"",""1000"",IIf([CondCode]=""NSSP"",""1500"",IIf([CondCode]=""USOP"",""3000"",IIf([CondCode]=""USSP"",""3000"")))) AS Condition" _
& "FROM (Conditions RIGHT JOIN (Manufactures LEFT JOIN (SKUs LEFT JOIN Products ON SKUs.SkuID = Products.SkuID) ON Manufactures.ManuID = SKUs.ManuID) ON Conditions.ConditionID = Products.ConditionID) LEFT JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID" _
& "WHERE (((SKUs.SkuID)<15));"


   rstItem.OpenRecordset strSQL ' Opens RecordSet
    intItemCount = 0

    With rstItem
        Do Until .EOF
            intItemCount = intItemCount + 1

           AddFixedPriceItem2 = "<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel><Item><Title>" & rstItem![Title] & " </Title><Description>" & rstItem![Description] & "</Description><PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory><StartPrice>" & rstItem![Price] & "</StartPrice><InventoryTrackingMethod>SKU</InventoryTrackingMethod><SKU>" & rstItem![SKU] & "</SKU>" _
& "<CategoryMappingAllowed>true</CategoryMappingAllowed><ConditionID>" & rstItem![Condition] & "</ConditionID><Country>US</Country><Currency>USD</Currency><DispatchTimeMax>3</DispatchTimeMax><ListingDuration>Days_7</ListingDuration><ListingType>FixedPriceItem</ListingType><PaymentMethods>PayPal</PaymentMethods><!--Enter your Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress><PostalCode>92078</PostalCode><ProductListingDetails><BrandMPN> BrandMPNType<Brand>" & rstItem![Brand] & "</Brand>" _
& "<MPN>" & rstItem![MPN] & "</MPN></BrandMPN><UPC>" & rstItem![UPC] & "</UPC></ProductListingDetails><Quantity>7</Quantity><ReturnPolicy><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><RefundOption>MoneyBack</RefundOption><ReturnsWithinOption>Days_30</ReturnsWithinOption><Description>If you arenot satisfied, return the item for refund.</Description><ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption></ReturnPolicy><ShippingDetails><ShippingType>Flat</ShippingType><ShippingServiceOptions><ShippingServicePriority>1</ShippingServicePriority><ShippingService>UPSGround</ShippingService><FreeShipping>true</FreeShipping><ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost>"

            ' Write to file
            Print #intInventoryFileNum, AddFixedPriceItem2

            .MoveNext
        Loop
    End With
    
    ' Write any trailer here - this file didn't need any.

Exit_Procedure:
    On Error Resume Next

    Close #intInventoryFileNum

    rstItem.Close
    Set rstItem = Nothing

    Exit Sub

Error_Procedure:
    ' error handler here.
    Resume Exit_Procedure
    End Sub

Open in new window


Line 31 If I have it like this then it gives me a blank document
rstItem.OpenRecordset strSQL

Open in new window


but if I have it like
rstItem.OpenRecordset

Open in new window


Then the file has some text but isn't right!

******************************************

I added message boxes to the error handlers and it is sending errors. With the strSQL it goes to error Box 2 which then says "Error 3421 Data type Conversion Error" and then to error1 message boxes

Exit_Procedure:
    On Error Resume Next
MsgBox "Error 1  " & Err.Description, , _
     "ERROR  " & Err.Number
    Close #intInventoryFileNum

    rstItem.Close
    Set rstItem = Nothing

    Exit Sub

Error_Procedure:
    ' error handler here.
    MsgBox "Error 2  " & Err.Description, , _
     "ERROR  " & Err.Number
    Resume Exit_Procedure
    End Sub

Open in new window

0
als315Commented:
Set rstItem=db.OpenRecordset(strSQL)
0
Dustin StanleyEntrepreneurAuthor Commented:
Set rstItem=db.OpenRecordset(strSQL)

Returns a error that access can't find the Query or Table "" But if I quote strSQL


Set rstItem=db.OpenRecordset("strSQL")

Open in new window


 then it returns an error that access cant find the table or query strSQL.

Thanks for the help!


It's something with
rstItem.OpenRecordset (strSQL) ' Opens RecordSet

Open in new window

0
als315Commented:
Comment line in the beginning of your code:
Set rstItem = db.OpenRecordset("item")
May be there is something wrong in StrSQL. Add debug.print strSQL before open it and try to run result (from immediate window)  from query designer.
0
Dustin StanleyEntrepreneurAuthor Commented:
That's how it is currently setup Line 9
0
Luke ChungPresidentCommented:
Doesn't Access have an export to XML feature?
0
Dustin StanleyEntrepreneurAuthor Commented:
yes but it doesn't do exactly as I need. If this code works it should do everything plus more.
0
Luke ChungPresidentCommented:
What was it not doing? Maybe a query would solve that problem.
0
Dustin StanleyEntrepreneurAuthor Commented:
I was trying to export a query. But the way Access lays out the XML file is completely wrong for where I have to import it.

I would have to change schemas and map it out different ways after I exported out of Access.

This code Jim supplied if I can get it to work properly will let me export anyway I need. I just create a template and insert what is needed.

I know it is something small probably overlooked but I can't figure it out.

Hopefully Jim can reply with a solution soon.

Thanks.
0
als315Commented:
Can you show corrected code?
0
Luke ChungPresidentCommented:
What are you trying to import our into and how is the built in export failing?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Dustin,

 The code I gave was an example of the outline I had given you:


1. Open a recordset on item
2. Open a file for append.
3. Write any header to the file

4. For each record in recordset
       a. write a line out to the file
       b. loop

5. Write any trailer to the file
6. Close the file.

  It wasn't the actual code that you needed to use and I see that you've made a pretty good attempt at it.   However it's still a bit off.   Below is the code very close to what you need.   Check the XML....I found a couple places where tag pairs didn't match.

  This logic is the outline you would need to do any type of Export.   As the others have mentioned, there are built-in commands to do exports (and imports), but if you want full control over the process and hence are doing it in VBA, then this is what you'd use.

Jim.

Option Compare Database
Option Explicit

Public Sub PrepSaveStringAsTextFile()

    Dim db As DAO.Database
    Dim rstItem As DAO.Recordset

    Dim strFile As String
    Dim intItemCount As Integer
    Dim intInventoryFileNum As Integer
    Dim AddFixedPriceItem2 As String

    On Error GoTo Error_Procedure
    
    ' 1 - Open a recordset on item
    Set db = CurrentDb()
    Set rstItem = db.OpenRecordset("item")

    ' 2 - Open a file for append

    strFile = ("C:\Users\Station\Documents\Access XML Save Files\Test14.txt")

    ' note that you might want to KILL the file in case it already exists first.
    ' On Error Resume Next
    ' Kill strFile
    ' On Error Goto Error_Procedure

    intInventoryFileNum = FreeFile
    Open strFile For Append As #intInventoryFileNum

    ' 3 - Write a file header if needed
    AddFixedPriceItem2 = "<?xml version=""1.0"" encoding=""utf-8""?><AddFixedPriceItemRequest xmlns=""urn:ebay:apis:eBLBaseComponents""><ErrorLanguage>en_US</ErrorLanguage><WarningLevel>Low</WarningLevel>"
    
    Print #intInventoryFileNum, AddFixedPriceItem2

    ' 4 - For each record in the recordset, write a line out.
    intItemCount = 0

    With rstItem
        Do Until .EOF
            intItemCount = intItemCount + 1

            ' When you have a big long string, it's better to break it up like this so you can make changes more easily.
            ' Same thing if you had a big SQL string.

            AddFixedPriceItem2  = "<Item><Title>" & rstItem![Title] & "</Title>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<Description>" & rstItem![Description] & </Description>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<PrimaryCategory><CategoryID>1267</CategoryID></PrimaryCategory>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<StartPrice>" & rstItem![Price] & "</StartPrice>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<InventoryTrackingMethod>SKU</InventoryTrackingMethod>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<SKU>" & rstItem![SKU] & "</SKU>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<CategoryMappingAllowed>true</CategoryMappingAllowed>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ConditionID>" & rstItem![Condition] & "</ConditionID>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<Country>US</Country>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<Currency>USD</Currency>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<DispatchTimeMax>3</DispatchTimeMax>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ListingDuration>Days_7</ListingDuration>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ListingType>FixedPriceItem</ListingType>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<PaymentMethods>PayPal</PaymentMethods>"

            ' This does not seem correct - elements are unbalanced - seems you should have <PayPalEmailAddress> at the start.
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<!--Enter your Paypal email address--><PayPalEmailAddress>PP@Gmail.com</PayPalEmailAddress>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<PostalCode>92078</PostalCode>"

            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ProductListingDetails>"
            ' Check this as well - doesn't look right
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<BrandMPN>BrandMPNType<Brand>" & rstItem![Brand] & "</Brand>" 
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<MPN>" & rstItem![MPN] & "</MPN></BrandMPN>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<UPC>" & rstItem![UPC] & "</UPC>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "</ProductListingDetails>"

            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<Quantity>7</Quantity>"

            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ReturnPolicy>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<RefundOption>MoneyBack</RefundOption>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ReturnsWithinOption>Days_30</ReturnsWithinOption>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<Description>If you arenot satisfied, return the item for refund.</Description>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ShippingCostPaidByOption>Buyer</ShippingCostPaidByOption>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "</ReturnPolicy>"

            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ShippingDetails>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ShippingType>Flat</ShippingType><ShippingServiceOptions>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ShippingServicePriority>1</ShippingServicePriority>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ShippingService>UPSGround</ShippingService>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<FreeShipping>true</FreeShipping>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "<ShippingServiceAdditionalCost currencyID=""USD"">0.00</ShippingServiceAdditionalCost>"
            AddFixedPriceItem2  =  AddFixedPriceItem2  & "</ShippingDetails>"

            ' Write to file.   You could breakpoint here to check the final result of AddFixedPriceItem2
            Print #intInventoryFileNum, AddFixedPriceItem2

            .MoveNext
        Loop
    End With
    
    ' 5. Write any trailer for the file here - this file didn't need any.

Exit_Procedure:
    On Error Resume Next

    Close #intInventoryFileNum

    rstItem.Close
    Set rstItem = Nothing

    Set db = nothing

    Exit Sub

Error_Procedure:
    ' error handler here.
    Resume Exit_Procedure
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and when you get this working, I'll show you how to use that "AddToString" Function.

Jim.
0
Dustin StanleyEntrepreneurAuthor Commented:
Thank you Jim for the help. It works good.  When you said
AddFixedPriceItem2  =  AddFixedPriceItem2  & "<Description>" & rstItem![Description]

I didn't get it but now it is clear. Thanks.
0
Dustin StanleyEntrepreneurAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.