We help IT Professionals succeed at work.

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

Dustin Stanley
on
141 Views
Last Modified: 2017-04-05
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!
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
Dustin StanleyEntrepreneur

Author

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.
Dustin StanleyEntrepreneur

Author

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
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
So you want to write out each record from the Item query?

Jim.
Dustin StanleyEntrepreneur

Author

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!
Dustin StanleyEntrepreneur

Author

Commented:
So you want to write out each record from the Item query?

I believe yes. IF we are on the same page.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
Dustin StanleyEntrepreneur

Author

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)
Dustin StanleyEntrepreneur

Author

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!
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
Dustin StanleyEntrepreneur

Author

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!
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dustin StanleyEntrepreneur

Author

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.
Dustin StanleyEntrepreneur

Author

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.
Dustin StanleyEntrepreneur

Author

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.........
Dustin StanleyEntrepreneur

Author

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

CERTIFIED EXPERT

Commented:
Set rstItem=db.OpenRecordset(strSQL)
Dustin StanleyEntrepreneur

Author

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

CERTIFIED EXPERT

Commented:
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.
Dustin StanleyEntrepreneur

Author

Commented:
That's how it is currently setup Line 9
Luke ChungPresident
CERTIFIED EXPERT

Commented:
Doesn't Access have an export to XML feature?
Dustin StanleyEntrepreneur

Author

Commented:
yes but it doesn't do exactly as I need. If this code works it should do everything plus more.
Luke ChungPresident
CERTIFIED EXPERT

Commented:
What was it not doing? Maybe a query would solve that problem.
Dustin StanleyEntrepreneur

Author

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.
CERTIFIED EXPERT

Commented:
Can you show corrected code?
Luke ChungPresident
CERTIFIED EXPERT

Commented:
What are you trying to import our into and how is the built in export failing?
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
and when you get this working, I'll show you how to use that "AddToString" Function.

Jim.
Dustin StanleyEntrepreneur

Author

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.
Dustin StanleyEntrepreneur

Author

Commented:
Thanks!