07-22-13, 14:53 #1Registered User
- Join Date
- Jul 2013
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
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"
DoCmd.OpenForm "Gamedata", acNormal, "", "", , acNormal
07-23-13, 00:28 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
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
Docmd RunSQL SQL
that my 5 cents
Last edited by myle; 07-23-13 at 00:48. Reason: Spellinghope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
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