Add New Record via VBA

[Robecca]

I hope I explain this well.

I am designing a database for a summer bowling league. This league's teams change weekly based on a blind draw. So I check in each bowler each week.

I have a table Score to capture the games bowled. This table has a Primary Key ScoreID (auto numbered) and it has a Foreign Key (ChkInID) which is PK in CheckInBowler table. The Score table is designed so that there is one record per bowler per game.

So what I want to do is use VBA to create 3 new records in the Score table whenever a new record is created in the CheckInBowler table.

Since I am new to VBA, googling it was not helpful, I did not understand what I was seeing, nor do I know where to put it. I have a form to check in the bowlers, do I put the code there as an event? Maybe on afterupdate to the CheckInID field?

Thank you! Robecca

 

[jzwp22]

Using your check-in form would probably be the best. Is this check-in form bound to a table? How does the check-in take place? Do you manually enter a value in the check-in control or is it done via a combo box?

With respect to the code, it would be fairly simple in that you would execute an append query. Since you want to add 3 records, you would need to execute the query 3 times. The best way to handle that is to embed the append query within a loop. Is there anything that changes between the 3 records such as the game number? How do you distinguish the games of one day from the previous time?

If nothing changes between the 3 records being added, then the SQL text of the append query does not need to be within the loop. If the value for a field changes with each append, the the SQL text must be built within the loop.

Nothing changes between the 3 records being added:

Dim i as integer
Dim mySQL a string

mySQL="INSERT INTO Score ( ChkInID ) values (" & me.formcontrolnameholdingtheID & ")"


For i=1 to 3
currentdb.execute mySQL, dbfailonerror
i=i+1
Next i

Let's say that you have a field in the score table to designate the game # and you sequentially number the games for each player as 1, 2, 3. The code would look like this:

Dim i as integer
Dim mySQL a string




For i=1 to 3
mySQL="INSERT INTO Score ( ChkInID,gameno ) values (" & me.formcontrolnameholdingtheID & "," & i & ")"

currentdb.execute mySQL, dbfailonerror
i=i+1
Next i

 

[Robecca]

My check-in form is bound to a CheckInBowler table. This table has the ChkInID which is an autonumber, BowlerID, WkDate, PositionID and LaneID.

On my check-in form, I have the BowlerID field as a combo box, showing the bowler name. Once the bowler is chosen, the ChkInID is assigned.

I do have a GameID field, in which I capture "Game1", "Game2" and so forth. I thought I would handle what game it is during the entry of their scores, but can I incorporate it here if it is a text field?

My part of the above code for me.formcontrolnameholdingtheID would be me.ChkInID correct?

 

[jzwp22]

My part of the above code for me.formcontrolnameholdingtheID would be me.ChkInID correct?

That is correct.

I do have a GameID field, in which I capture "Game1", "Game2" and so forth. I thought I would handle what game it is during the entry of their scores, but can I incorporate it here if it is a text field?

Having it a numeric field is easier. You can always concatenate the word "game" when you want to display it as Game 1, Game 2 etc. It will save you a lot of typing.

For example, in a form you would still bring in the game number field into a control (but hide the control). You would then set up another control and set it's record source ="Game " & gamenumberfield

I would probably put the code in the after update event of the form. There is one catch to that. If you go back and edit an existing record (i.e. update it), it will add another 3 records. To take care of that, you need to check if records already exist for that check in if they do, do not do the append.

I did find an error in my code. The i=i+1 is not needed.

The attached database has a form that illustrates the code.

 

[Robecca]

Thank you so much!

I appreciate your time and that you built a quick database to show me how it works.

BTW, I did change my GameID from text to a number.

Robecca

 

[jzwp22]

You're welcome. Glad we could help out.