VBA to Specify Entries in PCOM Emulator

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
97


Ok, this shouldbe the last thing I need help on.
Thanks to those who helped thus far, my project is pretty much done.

Here is my issue:
I have a form where users can enter multiple resorts and dates for which theywant to perform an action.
The form locks the values in a table called Log.
My database assigns an auto number to each entry which is fine for me as Iarchive them after they are run.

The first part of my code is as follows:
Code:
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]rst.Open"selectResort_Code,AS400SD,End_Date,Unit_Size,Flag,Return,Track_Code,Res_Line from Log", CurrentProject.Connection, adOpenStatic, adLockOptimistic
[/FONT][/COLOR]


This selects the variables to send to AS400.
However, it only works for the first entry in the table.
I want the user to be able to enter multiple scenarios and, by adding two moreform fields called First Entry and Last Entry, allow the user to run Entry 1 to1 or 1 to 5.

I want the script to understand to repeat the loop for each entry that satisfies the criteria.
I was thinking of something like this (which is just a snippet of my code, the rest works).

Code:
[COLOR=#222222][FONT=Verdana]If.Fields("Entry") >= Screen.ActiveForm!FirstEntry And.Fields("Entry") <= Screen.ActiveForm!LastEntry Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]CallsendToNa("PUT", "400", 21, 18, 0, 0) 'Go to Option 400[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]CallsendToNa("COM", "enter", 0, 0, 0, 0)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]CallsendToNa("PUT", .Fields("Resort_Code"), 6, 43, 0, 0) 'PassResort ID[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]CallsendToNa("PUT", .Fields("AS400SD"), 8, 43, 0, 0) 'PassStart Date[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If.Fields("Track_Code") = "ALL" Then CallsendToNa("PUT", "*", 10, 43, 0, 0):  Call sendToNa("COM","enter", 0, 0, 0, 0) _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Else: CallsendToNa("COM", "PF4", 0, 0, 0, 0) 'Prompt for Track Codeselection[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If.Fields("Track_Code") = "A" Then CallsendToNa("PUT", "X", 14, 37, 0, 0): CallsendToNa("COM", "enter", 0, 0, 0, 0) _[/FONT][/COLOR]


But I’m not sure how to work the Loop portion into it.
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I think I need something like Do .EOF but it just loops through my data running the first line over and over now.
 
Upvote 0
Generally it would be something like
Code:
Set db=CurrentDB()
Set rst = db.Openrecordset(yourSQLHere)
Do whiile not rst.EOF
   Your variable = rst!Fieldname
    rst.MoveNext
Loop

If it was me I would put the criteria in the SQL, to restrict the records being returned.? No point returning all records and then testing, that is what queries do best.
HTH
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top