How do I auto populate several fields at once

[learmanj]

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.
Any advise?
thank you,
Jerome

[Dal Jeanis]

Okay, so you're going to write VBA. Here's the pseudocode for the routine

The person will enter game number, then enter (for example 23 and 1099)
Your form will have a field with the game number, and two fields that contain the endzoneview and sidezoneview file numbers.
Your VBA will read those fields and save them into variables.
It will start the play number at 1.
It will then enter a loop.
It will build the names of the first two files by making each number 4 digits, and adding VFD on the front and .WMV on the end.
It will check each folder for the file.
If neither file is found, it will stop the loop.
For each file that is found, it will build the path for the link.
If one file is not found, that link will be built empty, blank, or something.
The record will then be inserted into the table with game, play, and the two links.
Then loop to the next play number.

So, the question is, which part of this do you need examples for?

Google "insert records sql VBA" and you should find lots of good examples.
You'll also need the VBA to test for the existence of a file.

[learmanj]

Apparently I know less that I thought I knew. I took a class in access and they taught us how to do popup using the find function...

So need to know how to do that..
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.RunCommand acCmdDataEntry



End Function

Okay, so you're going to write VBA. Here's the pseudocode for the routine

The person will enter game number, then enter (for example 23 and 1099)
Your form will have a field with the game number, and two fields that contain the endzoneview and sidezoneview file numbers.
Your VBA will read those fields and save them into variables.
It will start the play number at 1.
It will then enter a loop.
It will build the names of the first two files by making each number 4 digits, and adding VFD on the front and .WMV on the end.
It will check each folder for the file.
If neither file is found, it will stop the loop.
For each file that is found, it will build the path for the link.
If one file is not found, that link will be built empty, blank, or something.
The record will then be inserted into the table with game, play, and the two links.
Then loop to the next play number.

So, the question is, which part of this do you need examples for?

Google "insert records sql VBA" and you should find lots of good examples.
You'll also need the VBA to test for the existence of a file.

[Dal Jeanis]

Okay, I don't do macros, so I can't comment on the syntax, but here are some notes.

1) Before starting the loop, I would verify each file existed, and prompt for a fix at that point.

2) After the loop starts, if the file doesn't exist, I would want to use Null rather than a nonexistent filename.

VALUES 
      (
      Gameno, 
      Play,
      IIF(FileExists(tblgames.filepath.Gameno"\"ezfilename),ezfilename, Null),
      IIF(FileExists(tblgames.filepath.Gameno"\"slfilename),slfilename, Null)
      )

3) I would tend to put the 4-digit serial number of the file into a variable and increment, rather than extracting and incrementing them each time, but that's just a personal preference.

Otherwise, your code/pseudocode should work when you get the syntax right.

[learmanj]

I like both of those ideas. Thank you.. It is a matter of getting the syntax right. I am hoping someone on here can help out as it doesn't seem like it should be that complex.
thanks again,
Jerome