AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of Dustin Stanley
Dustin Stanley

asked on 

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!
VBAMicrosoft Access
Avatar of undefined
Last Comment
Dustin Stanley
8/22/2022 - Mon
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent