Results 1 to 3 of 3

Thread: VBA to Specify Entries in PCOM Emulator
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2016
    Location
    Miami, Florida
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to Specify Entries in PCOM Emulator



    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
    Code:
    rst.Open"selectResort_Code,AS400SD,End_Date,Unit_Size,Flag,Return,Track_Code,Res_Line from Log", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    


    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:
    If.Fields("Entry") >= Screen.ActiveForm!FirstEntry And.Fields("Entry") <= Screen.ActiveForm!LastEntry Then
    CallsendToNa("PUT", "400", 21, 18, 0, 0) 'Go to Option 400
    CallsendToNa("COM", "enter", 0, 0, 0, 0)
    CallsendToNa("PUT", .Fields("Resort_Code"), 6, 43, 0, 0) 'PassResort ID
    CallsendToNa("PUT", .Fields("AS400SD"), 8, 43, 0, 0) 'PassStart Date
    If.Fields("Track_Code") = "ALL" Then CallsendToNa("PUT", "*", 10, 43, 0, 0):  Call sendToNa("COM","enter", 0, 0, 0, 0) _
    Else: CallsendToNa("COM", "PF4", 0, 0, 0, 0) 'Prompt for Track Codeselection
    If.Fields("Track_Code") = "A" Then CallsendToNa("PUT", "X", 14, 37, 0, 0): CallsendToNa("COM", "enter", 0, 0, 0, 0) _


    But I’m not sure how to work the Loop portion into it.
    Last edited by MCTampa; Apr 15th, 2019 at 04:09 PM.

  2. #2
    New Member
    Join Date
    Apr 2016
    Location
    Miami, Florida
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Specify Entries in PCOM Emulator

    I think I need something like Do .EOF but it just loops through my data running the first line over and over now.

  3. #3
    Board Regular
    Join Date
    May 2013
    Posts
    608
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Specify Entries in PCOM Emulator

    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 by welshgasman; Apr 16th, 2019 at 04:31 AM. Reason: Added query criteria sentence
    Office 2007
    Access novice. Sometimes trying to give something back

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •