Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2013
    Posts
    1

    Unanswered: How do I auto populate several fields at once

    I have a table that I am holding play data for a football game. Each play is a separate record. Each Record will have two video files associated with it, a sideline view and an endzone view. What I would like is for someone to click the button at the top of the form and for the button to give you three prompts:
    1) Game Number
    2) Name of first Endzone File
    3) Name of first Sideline File

    I would then want access to append to the end of the table and auto populate the following 4 fields
    1) Game Number (always the same)
    2) Play Number (start at 1 and Go up 1 each play)
    3) Endzoneview (a link to a file would start off VFD00023.wmv and go up 1 each play)
    3) Sidelineview (a link to a file would start off VFD01099.wmv and go up 1 each play)

    I would want it to do this until it ran out of files in the endzone and sideline video folders

    I was able to do this in excel, but after I create the button and tie a macro to it, I am lost.

    This is what I was able to come up with, but I know it is not correct.

    This is what I have and know most of it is not right.... any advice

    Function Macro1()


    Prompt for Gameno
    Promt for ezfilename
    Promt for slfilname
    Play = 1
    While FileExists(tblgames.filepath.Gameno"\"ezfilename) Or FileExists(tblgames.filepath.Gameno"\"slfilename)
    INSERT INTO tblGameData ([Game Number], [Play Number], [EZ View], [SL View])
    VALUES (Gameno, Play, ezfilename, slfilename)
    Play = Pkay + 1
    ezfilename = "vfd" + (cat(ezfilename, 4, 4) + 1) + "wmv"
    slfilename = "vfd" + (cat(slfilename, 4, 4) + 1) + "wmv"
    EndWhile
    DoCmd.OpenForm "Gamedata", acNormal, "", "", , acNormal
    DoCmd****nCommand acCmdDataEntry



    End Function


    Any advise?
    thank you,
    Jerome

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,468
    Provided Answers: 10
    first of all

    think u missing the the DOT ".wmv" or does the cat() function look after that

    and the INSERT should that be in a String so you can run it the in a docmd
    and we need to build the values up to put into the SQL


    SQL = "INSERT INTO tblGameData ([Game Number], [Play Number], [EZ View], [SL View]) VALUES ([G], [P], [e], [s])"

    I user the [] and the Replace() so the SQL is read easy

    debug.print "Before >>" & SQL 'look in the debug log

    SQL = replace(SQL ,"[G]",InputBox("Game Number"))
    SQL = replace(SQL ,"[P]",Play)
    SQL = replace(SQL ,"[e]",InputBox("ezfilename"))
    SQL = replace(SQL ,"[s]",InputBox("slfilename"))

    debug.print "AFTER >>" & SQL ' look in the debug log see the differance

    STOP

    Docmd RunSQL SQL

    that my 5 cents
    Last edited by myle; 07-23-13 at 00:48. Reason: Spelling
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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