Autofill a set of records, based on template

[C.D]

Hello,
I've been searching the forum for a macro or module, that lets me add a set of records, based on a filled template.
Let's say I want to add a number of episodes to a TV show. What I usually do, is launch excel, and type in "Epsiode 1" and just use autofill and drag down the anchor so I get to the desired number for episodes, and then copy+paste it into my DB. I'm getting abit tired of this, but I can't seem to find anything already posted.

Take a look at the picture I posted. Does anyone have an idea?
Thanks in advance!

 

[pbaldy]

Basically you want a For/Next loop getting the number(s) from your form. Open a recordset on the target table, and within the loop use the AddNew method of the recordset to add a record each time through, using the loop counter to populate your field ("Episode " & X).

 

[C.D]

Thanks for the response.
I've found a few posts, by using the keywords you supplied me with, and I think this is one of the closest. I don't have much experience on the coding, unfortunately, but I'll try to cook something up based on the info I've got so far.

So, basically, I need to make some sort of a function, which uses my input and loops through the range (x to x), and does a .AddNew for each instance of ("Episode " & X) ?
I'll try to cook something together and report back, thanks.

 

[pbaldy]

Yes, that's got the basics of what you need. Dive in and give it a shot, and if you get hung up, post what you've got so far and we'll sort it out.

 

[C.D]

I've searched this forum, other forums and googled, but can't find any specific info regarding this coding. I'm really nothing but a novice VBA coder.
I would greatly appriciate links to examples of code, or any help to write the code.
Thanks.

 

[pbaldy]

I'm not sure what you're asking for. You want examples of code; the link you found has exactly the code you need. If you're not sure about what any given line does, ask.

 

[C.D]

Thanks for pointing that out for me..

I've scrambled up some code and surprisingly, it doesn't work.

Private Sub Kommando12_Click()
Dim db As Database
Dim txtFrom As Integer
Dim txtTo As Integer
Dim x As Variant
Dim rsEpisodes As Recordset

Set db = CurrentDb
Set rsEpisodes = db.OpenRecordset("tblAnimeEpisode", dbOpenTable, dpAppendOnly)

txtFrom = Me.txtFrom
txtTo = Me.txtTo

For x = txtFrom To txtTo


With rsEpisodes
.AddNew
!EpId = "Episode" & " " & x
.Update
End With

Next x
End Sub

Firstly: This is not a loop (Don't know how loops work)
Secondly: The field "EpID" is a combo box. Is it possible to do a "lookup" in a loop? If I had to use numbers, the whole point would be gone - I.e EpID 10 = "Episode 20".'

Which of the 'main' features did I miss here?

 

[missinglinq]

Here's some code I've quickly adapted to your fields. You may need to adapt it to get exactly what you want, but I think it'll speed things up.

On a continuous form with text controls named

AnimeID
EpisodeID

Place two unbound text boxes named

StartEpisode
EndEpisode

And a command button named

GenerateEpisodeID

Now place this code behind your form:

Private Sub AnimeID_AfterUpdate()
 AnimeID.DefaultValue = """" & Me.AnimeID.Value & """"
End Sub

Private Sub EpisodeID_AfterUpdate()
 Me.EpisodeID = "Episode " & Format(Me.EpisodeID, "000")
End Sub

Private Sub GenerateEpisodeID_Click()
Dim mn As Long
Dim Rec As Double
Rec = (EndEpisode - StartEpisode) + 1
For EN = 1 To CInt(Rec)
DoCmd.GoToRecord , , acNewRec
Me.EpisodeID = "Episode " & Format(EN + (StartEpisode - 1), "000")
Next EN
End Sub

Now say you're entering records for an entirely new anime. Say you want to enter the first 20 episodes.

Enter an AnimeID and its EpisodeID (just enter the digit 1)

Now in the StartEpisode box enter 2

In the EndEpisode box enter 20

Click on the GenerateEpisodeID button

You now should have records for your anime, Episodes 1-20.

If you later want to add to the episodes, you'll once again need to enter the AnimeID and EpisodeID for the first one, then enter the start and end episode numers and click the button.

 

[C.D]

Thanks for the reply!
It doesn't work, though. I've tried to adapt it to my setup, but still get an error.

I use a form with a subform for registering new episodes - which pops up and searches for an ID to match the active AnimeID.


I've placed this code in the Popup_sub:

Option Compare Database

Private Sub AnimID_AfterUpdate()
 AnimID.DefaultValue = """" & Me.AnimID.Value & """"
End Sub

Private Sub EpID_AfterUpdate()
 Me.EpID = "Episode " & Format(Me.EpID, "0")
End Sub

- I've made alternate fieldnames for the ID's, to separate them from the original.

This code is in the main popup form:

Option Compare Database
Private Sub GenerateEpisodeID_Click()
Dim mn As Long
Dim Rec As Double
Rec = (EndEpisode - StartEpisode) + 1
For EN = 1 To CInt(Rec)
DoCmd.GoToRecord , , acNewRec
[COLOR="Red"][Forms]![frmAnime_Delskjema_EpReg_Delskjema]![EpID] = "Episode " & Format(EN + (StartEpisode - 1), "0"[/COLOR]
Next EN
End Sub

- I assumed the "000" format was for the number of digits - I only have one (Episode 1, Episode 10, etc).
- The error (which is marked above), says It can't find the form.

 

[pbaldy]

Firstly: This is not a loop (Don't know how loops work)
Secondly: The field "EpID" is a combo box. Is it possible to do a "lookup" in a loop? If I had to use numbers, the whole point would be gone - I.e EpID 10 = "Episode 20".'

Firstly, it IS a loop; a For/Next loop. I actually like your attempt better, because there should be no need to use the form to add records. The recordset is cleaner. You said it didn't work, but didn't say what the error was. The EpId in this line

!EpId = "Episode" & " " & x

is looking for a field in the table, not a control on the form. You can certainly look things up within the loop, but I'm not clear what it is you want to look up.

 

[C.D]

Okay, the referance method was wrong. It should be Me!Subform1.Form.RecordSource
Which makes Me!frmAnime_Delskjema_EpReg_Delskjema.frmAnime_Delskjema_EpReg.EpID ?
Still get an error when trying to add episodes: Me!frmAnime_Delskjema_EpReg_Delskjema.frmAnime_Delskjema_EpReg.EpID = "Episode " & Format(EN + (StartEpisode - 1), "0") - "Object doesn't support object or method"
-

Firstly, it IS a loop; a For/Next loop. I actually like your attempt better, because there should be no need to use the form to add records. The recordset is cleaner. You said it didn't work, but didn't say what the error was. The EpId in this line

!EpId = "Episode" & " " & x

is looking for a field in the table, not a control on the form. You can certainly look things up within the loop, but I'm not clear what it is you want to look up.

missinglinq made a set of code a couple of posts above.

 

[C.D]

BTW here's a screen of the form.

 

[pbaldy]

Yes, I saw the posted code. As mentioned, I prefer a method that doesn't manipulate the form, as that will be less efficient. There is also a goof/typo in there. If you want to go that way, maybe it's just the syntax of the form reference holding you back:

http://www.mvps.org/access/forms/frm0031.htm

 

[C.D]

I get this error: -2147352567 80020009
Me!sub.Form.EpID = "Episode " & -Format(EN + (StartEpisode - 1), "0")
I'm note sure why this error is occuring. It says something about the input not being valid for the field.

 

[pbaldy]

What is the data type of that field in the table? Perhaps a sample db would speed things up.

 

[C.D]

Certainly.
It's PW protected - not to be lame, but It's just a personal preference

I'm sending the PW to everyone who has helped me yet and to anyone who helps in the future..

Thanks!

BTW - frmAnime is the main form..

 

[pbaldy]

I get an error trying to extract it.

 

[C.D]

First you need to remove the .ZIP - since it's a .rar archive

 

[C.D]

I also forgot to say that you need to click the "Add episodes" above the episode list in frmAnime.

 

[C.D]

Did it work?