Results 1 to 7 of 7
  1. #1
    Earthmover is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2012
    Posts
    8

    Inserting Data into fixed text

    Hi Everyone,



    I've only been using Access for a little while now, and until last week I had an Access guru sitting beside me who would just do anything that I struggled with (but didnt teach me anything).

    Now he's gone and I'm struggling

    Basically, I have a table with 2 columns which need to be inserted into the following script until it reaches the end of the list. This is a script for an AS400 mainframe system.

    FunctionKey (CLEAR)
    WaitFor (UNLOCK)
    WaitForCursorPos (12, 33)
    TypeString ("***CELL A1***")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (22, 20)
    TypeString ("479")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (8, 17)
    TypeString ("***CELL B1***")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (15, 46)
    FunctionKey (ENTER)
    WaitFor (UNLOCK)

    So, once it has done this, I would want it to do Cell A2 and Cell B2, and repeat itself until it reaches the end of the list.

    I wouldn't even know where to start searching on the forum (I have tried), so if someone could point me in the right direction I'd be eternally greatful.

    Cheers,
    Earthmover.
    Last edited by Earthmover; 02-08-2012 at 05:55 PM. Reason: used wrong cell numbers

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How does a script for an AS400 mainframe have anything to do with Access? You have an Access project with an ODBC link to AS400 file? Where is this script, in a VBA module (doesn't look like VBA code)? Access doesn't have cell referencing, that's spreadsheet jargon. The end of what 'list' - the records in table? What is purpose of this process, what are you really trying to accomplish?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Earthmover is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2012
    Posts
    8
    I guess I was just trying to put it in words that I understood...
    the table looks like this

    LOCATION NUMBER
    0346 708-2L-00280
    0346 708-7W-00130
    0346 ND949010-2530
    0310 01010-61495
    0310 01011-62425
    0310 02763-00326

    So basically I want it to create a scipt that goes something like this.

    FunctionKey (CLEAR)
    WaitFor (UNLOCK)
    WaitForCursorPos (12, 33)
    TypeString ("0346")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (22, 20)
    TypeString ("479")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (8, 17)
    TypeString ("708-2L-00280")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (15, 46)
    FunctionKey (ENTER)
    WaitFor (UNLOCK)

    FunctionKey (CLEAR)
    WaitFor (UNLOCK)
    WaitForCursorPos (12, 33)
    TypeString ("0346")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (22, 20)
    TypeString ("479")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (8, 17)
    TypeString ("708-7W-00130")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (15, 46)
    FunctionKey (ENTER)
    WaitFor (UNLOCK)

    and repete this the whole way down my list of about 1200 lines.
    Once I have this, I can export it as text and run it as a script file though AS400.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I expect a VBA procedure can be written to accomplish this. But I might have a solution that uses a series of queries. This requires the source table have a unique record identifier (an Autonumber field will serve).

    Query1
    SELECT ID, "FunctionKey (CLEAR)" AS A, "WaitFor (UNLOCK)" AS B, "WaitForCursorPos (12, 33)" AS C, "TypeString ('" & [Location] & "')" AS D, "FunctionKey (ENTER)" AS E, "WaitFor (UNLOCK)" AS F, "WaitForCursorPos (22, 20)" AS G, "TypeString ('479')" AS H, "FunctionKey (ENTER)" AS I, "WaitFor (UNLOCK)" AS J, "WaitForCursorPos (8, 17)" AS K, "TypeString ('" & [Number] & "')" AS L, "FunctionKey (ENTER)" AS M, "WaitFor (UNLOCK)" AS N, "WaitForCursorPos (15, 46)" AS O, "FuntionKey (ENTER)" AS P, "WaitFor (UNLOCK)" AS Q
    FROM Table1;

    Query2
    SELECT ALL ID, "A" As Seq, A As Action FROM Query1
    UNION SELECT ALL ID, "B", B FROM Query1
    UNION SELECT ALL ID, "C", C FROM Query1
    UNION SELECT ALL ID, "D", D FROM Query1
    UNION SELECT ALL ID, "E", E FROM Query1
    UNION SELECT ALL ID, "F", F FROM Query1
    UNION SELECT ALL ID, "G", G FROM Query1
    UNION SELECT ALL ID, "H", H FROM Query1
    UNION SELECT ALL ID, "I", I FROM Query1
    UNION SELECT ALL ID, "J", J FROM Query1
    UNION SELECT ALL ID, "K", K FROM Query1
    UNION SELECT ALL ID, "L", L FROM Query1
    UNION SELECT ALL ID, "M", M FROM Query1
    UNION SELECT ALL ID, "N", N FROM Query1
    UNION SELECT ALL ID, "O", O FROM Query1
    UNION SELECT ALL ID, "P", P FROM Query1
    UNION SELECT ALL ID, "Q", Q FROM Query1
    ORDER BY ID, SEQ;

    Export Query2 to a text file.

    There is no query designer or wizard for UNION, must type (or copy/paste) in the SQL View window of query designer.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Earthmover is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2012
    Posts
    8
    Thanks June7, thats a big help so far.

    The only thing i need now is for the following to appear in " " instead of ' '
    Any ideas?

    SELECT ID, "FunctionKey (CLEAR)" AS A, "" AS B, "WaitFor (UNLOCK)" AS C, "" AS D, "WaitForCursorPos (12, 33)" AS E, "TypeString (" & [Location] & ")" AS F, "FunctionKey (ENTER)" AS G, "" AS H, "WaitFor (UNLOCK)" AS I, "" AS J, "WaitForCursorPos (22, 20)" AS K, "TypeString (479)" AS L, "FunctionKey (ENTER)" AS M, "" AS N, "WaitFor (UNLOCK)" AS O, "" AS P, "WaitForCursorPos (8, 17)" AS Q, "TypeString ('" & [Number] & "')" AS R, "FunctionKey (ENTER)" AS S, "" AS T, "WaitFor (UNLOCK)" AS U, "" AS V, "WaitForCursorPos (15,46)" AS W, "FunctionKey (ENTER)" AS X, "" AS Y, "WaitFor (UNLOCK)" AS Z
    FROM Table1;

    So
    Query2
    ActionFunctionKey (CLEAR)
    WaitFor (UNLOCK)
    WaitForCursorPos (12, 33)
    TypeString (14)
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (22, 20)
    TypeString (479)
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (8, 17)
    TypeString ('20973R1')
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (15,46)FunctionKey (ENTER)

    Needs to be
    Query2
    ActionFunctionKey (CLEAR)
    WaitFor (UNLOCK)
    WaitForCursorPos (12, 33)
    TypeString (14)
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (22, 20)
    TypeString (479)
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (8, 17)
    TypeString ("20973R1")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (15,46)FunctionKey (ENTER)

    Thanks in advance,
    Earthmover.
    Last edited by Earthmover; 02-09-2012 at 08:27 PM. Reason: highlights

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Try this change in Query1:
    "TypeString "(""" & [Number] & """)"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    David04845 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    16
    Quote Originally Posted by Earthmover View Post
    Hi Everyone,

    I've only been using Access for a little while now, and until last week I had an Access guru sitting beside me who would just do anything that I struggled with (but didnt teach me anything).

    Now he's gone and I'm struggling

    Basically, I have a table with 2 columns which need to be inserted into the following script until it reaches the end of the list. This is a script for an AS400 mainframe system.

    FunctionKey (CLEAR)
    WaitFor (UNLOCK)
    WaitForCursorPos (12, 33)
    TypeString ("***CELL A1***")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (22, 20)
    TypeString ("479")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (8, 17)
    TypeString ("***CELL B1***")
    FunctionKey (ENTER)
    WaitFor (UNLOCK)
    WaitForCursorPos (15, 46)
    FunctionKey (ENTER)
    WaitFor (UNLOCK)

    So, once it has done this, I would want it to do Cell A2 and Cell B2, and repeat itself until it reaches the end of the list.

    I wouldn't even know where to start searching on the forum (I have tried), so if someone could point me in the right direction I'd be eternally greatful.

    Cheers,
    Earthmover.

    maybe i have this wrong but it looks like you are linking an excel sheet into access and it is telling you !Num because its formatted as a text in access but a number (formula) in excel

    if that is it.. it is because when you link a table into excel the format is disregarded unlike an import where it is used.

    To get over this you need to go to the first 10 rows under the header row and type RecordSet in all text fields and 123456789 in all number fields.. that way when access reads the linked page it uses the first 10 rows to determine the data type.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Inserting text from a function
    By Mclaren in forum Programming
    Replies: 3
    Last Post: 03-08-2011, 01:29 PM
  2. Replies: 5
    Last Post: 02-20-2011, 08:22 PM
  3. export query to fixed width text
    By eladz949 in forum Import/Export Data
    Replies: 1
    Last Post: 02-08-2011, 07:28 AM
  4. export query to fixed width text
    By eladz949 in forum Import/Export Data
    Replies: 5
    Last Post: 01-01-2011, 03:28 PM
  5. Convert number to fixed length text field
    By tpcervelo in forum Queries
    Replies: 1
    Last Post: 08-02-2010, 07:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums