looping to run vb code -help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

I am a Coding newbie......hopefully I can do this...

I have some vb code (basically a few SQL queries) that currently is run based on a command button set up on a Form so it only runs for the records on that form. To automate things a little bit I'd like the user to be able to somehow tell this 'code' to i.e. loop and perform the query functions based on a begin and end parameter
I don't know how to ask the user for this info and once I get it I don't know how to set the loop around my queries so it still performs the function one record at a time
An example
Right now based on the ORDER the user is on, a button is pressed which performs some queries for that ORDER number

What I'd like - a form or something for user to be able to enter a Starting ORDER Num and Ending ORDER Num - press cmd button and the queries would run still for one ORDER at a time BUT loop through each order based on the user input

Can I do this

Thanks for help in advance
Shawna
 
Thank you for your reply. I have not yet tried the code as I've been working on other items, but I wanted toa ask the question - will this still work if OrderNO is a text field? Also part of my 'code' is an update query - right now in the where clause I have Forms!!frmOrderNo!OrderNo but now I'll want it to compare to the lngCounter variable. How can I do this
I'm using SQL= "yaddayadda where yadda
DoCmd.RunSQL SQL
Can I reference a variable in here

Thank you so much for your help on this...
Shawna
 
The code will and won't still owrk with Text fields

What I do is make a copy of my table (without the data) and add an AutoNumber field, formatted as a LongInteger, named SortKey. Then I run an AppendQuery to add all my data to the new table ... which is now just the same as the original but with a nifty AutoNumber field I can use for looping through it. Just update the code to use the SortKey instead of the OrderNo

Within the loop, you can use the current SortKey or the OrderNumber matching the current SortKey as the variable in your WHERE statement

lngCounter = lngStar
For lngCounter = lngStart To lngEn
Dim i As Intege
' see if the SortKey exist
i = DCount("[SortKey ]","tblOrders","[SortKey ]=" & lngCounter
' if SortKey is in the table, execute the code (if it isn't, don't
If i > 0 The
' insert appropriate code her
' look up OrderN
Dim strOrder As Strin
strOrder = DLookUp("[OrderID]","tblOrders","[SortKey]=" & lngCounter
strSQL = "UPDATE tblOrders SET tblOrders.Field1='YaddaYaddaYadda "
& "WHERE tblOrders.OrderNo = '" & strOrder & "';
' Or use
& "WHERE tblOrders.SortKey = " & lngCounter & ";
' depending on whether your OrderNo's are uniqu
' or you could use a 'Where OrderNo = AND SortKey = statemen
DoCmd.RunSQL strSQ
End I
Nex

Hope this helps

Howard Brod



----- Shawna wrote: ----

Thank you for your reply. I have not yet tried the code as I've been working on other items, but I wanted toa ask the question - will this still work if OrderNO is a text field? Also part of my 'code' is an update query - right now in the where clause I have Forms!!frmOrderNo!OrderNo but now I'll want it to compare to the lngCounter variable. How can I do this
I'm using SQL= "yaddayadda where yadda
DoCmd.RunSQL SQL
Can I reference a variable in here

Thank you so much for your help on this...
Shawna
 
Thank you in advance for your help - i am going to try and apply this to my db when I can get some time to test

Shawna
 
Back
Top