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,424
    Provided Answers: 8
    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 01:48. Reason: Spelling
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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