troubleshooting Question

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

Avatar of Dustin Stanley
Dustin Stanley asked on
VBAMicrosoft Access
30 Comments2 Solutions148 ViewsLast Modified:
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


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>


Etc....

Etc....

Until all records have been appended.

Thank you for the help!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 30 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 30 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros