Getting the ID of a search match in a loop.

[C.D]

Hello,
I'm wondering how I can get the ID of a matching record and use this ID in an AddNew loop.

Private Sub cmdAdd_Click()
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim x             As Long

  Set db = CurrentDb()

  Set rs = db.OpenRecordset("tblAnimeEpisode", dbOpenDynaset)

  For x = Me.txtStart To Me.txtEnd
    rs.AddNew
    rs!AnimID = Me.AnimeID
    rs!EpID = ?
    rs.Update
  Next x

  MsgBox "Process complete"
  
  Set rs = Nothing
  Set db = Nothing
End Sub

Thanks to pbaldy for supplying the base of the code

Basically I want to use the ID which matches the criteria "Episode " & x. Getting the fldEpisodeID where fldEpisodeName = "Episode " & x from tblEpisodeList - how can I do this?

Thanks in advance

 

[WayneRyan]

C.D.

For x = Me.txtStart To Me.txtEnd
   rs.AddNew               <-- rs!EpID has no value yet
   rs!AnimID = Me.AnimeID  <-- rs!EpID will be valid after this line
   rs!EpID = ?             <-- You don't assign anything to it, it now has a value
                               you can use; i.e. MsgBox("New ID = " & rs!EpID)
   rs.Update               <-- After this statement, rs@EpID is no longer valid
  Next x

Wayne

 

[C.D]

C.D.

For x = Me.txtStart To Me.txtEnd
   rs.AddNew               <-- rs!EpID has no value yet
   rs!AnimID = Me.AnimeID  <-- rs!EpID will be valid after this line
   rs!EpID = ?             <-- You don't assign anything to it, it now has a value
                               you can use; i.e. MsgBox("New ID = " & rs!EpID)
   rs.Update               <-- After this statement, rs@EpID is no longer valid
  Next x

Wayne

Thanks for replting, but I'm not sure if I described the situation correctly.
I have two tables, tblAnime and tbEpisodeList, and I'm using tblAnimeEpisode as a many-to-many conjunction. I need a quick way to add episodes to the anime series.

Here's the tblEpisodeList

EpisodeID	EpisodeName
1	Episode 1
2	Episode 2
3	Episode 3
4	Episode 4
5	Episode 5
6	Episode 6
7	Episode 7
8	Episode 8
9	Episode 9
10	Episode 10
....

I know that I could just set rs!EpID = x, as the fldEpisodeID equals fldEpisodeName - but I still need the functionality described in my initial post.

This is a querry of tblAnimeEpisode:

AnimeEpisodeID	AnimID	EpID	AnimeEpisodeName
917	Hellsing	Episode 10	Hellsing - Episode 10
918	Hellsing	Episode 11	Hellsing - Episode 11
919	Hellsing	Episode 12	Hellsing - Episode 12
920	Hellsing	Episode 13	Hellsing - Episode 13
909	Hellsing	Episode 2	Hellsing - Episode 2
910	Hellsing	Episode 3	Hellsing - Episode 3
911	Hellsing	Episode 4	Hellsing - Episode 4
912	Hellsing	Episode 5	Hellsing - Episode 5
913	Hellsing	Episode 6	Hellsing - Episode 6
914	Hellsing	Episode 7	Hellsing - Episode 7
915	Hellsing	Episode 8	Hellsing - Episode 8
916	Hellsing	Episode 9	Hellsing - Episode 9

This is where the data is added.
For x = Me.txtStart To Me.txtEnd
rs.AddNew
rs!AnimID = Me.AnimeID is taken from the main form.
rs!EpID = "Episode " & x doesn't work, since it's an ID field, how can I make it search the field EpisodeName in tblEpisodeList for the EpisodeID, and then use the result as a value for rs!EpID ?
rs.Update
Next x

 

[C.D]

Could DLookup work?
I tried, but I'm not sure how to concatenate it properly.
What is wrong with this sentence?
DLookUp("EpisodeID";"tblEpisodeList";[EpisodeName]="Episode" & " " & "10")

 

[WayneRyan]

CD,

I'm walking out the door now ... will look later.

DLookUp("[EpisodeID]","tblEpisodeList","[EpisodeName] = 'Episode 10')

Wayne

 

[C.D]

It gave me an error, and wouldn't show me the querry data - I'm just testing out in a querry builder.
DLookUp("[EpisodeID]";"tblEpisodeList";[EpisodeName]="Episode") works, and so does
DLookUp("[EpisodeID]";"tblEpisodeList";[EpisodeName]=("Episode" & " " & "10")), but in both cases, a popup asks for EpisodeName when I try to show the querry in data view. Both also returns the value 1 on all the records.

 

[stopher]

The syntax should be:

DLookUp("[EpisodeID]","tblEpisodeList","[EpisodeName]='Episode 10' ")

Note the use of single quotes inside double quotes.

If you are getting the 10 from a form as a parameter then do something like:

DLookUp("[EpisodeID]","tblEpisodeList","[EpisodeName]='Episode " & me.txtboxname & "'")

hth
Chris

 

[C.D]

Thanks a bunch, Chris - the code now works with name strings!

For x = 1 To Me.AnimeEpisodesTotal
         If DCount("AnimeEpisodeID", "tblAnimeEpisode", "AnimID=" & [AnimeID] & " AND EpID=" & (DLookup("[EpisodeID]", "tblEpisodeList", "[EpisodeName]='Episode " & x & "'"))) = 0 Then
            rs.AddNew
            rs!AnimID = Me.AnimeID
            rs!EpID = DLookup("[EpisodeID]", "tblEpisodeList", "[EpisodeName]='Episode " & x & "'")
            rs.Update
        End If
    Next x

But there's one more thing I can't seem to work out, which is also the main reason why I wanted to search the Name fields in the first place:
DLookUp("[AnimeEpisodeID]";"qryAnimeEpisode";"[AnimeEpisodeName]='[AnimeTitle]'&' - Episode '&'10'")

The criteria is supposed to be a concatenation of [AnimeTitle] - Episode X
The AnimeEpisodeNames are like the examples I have in the post above:
Hellsing - Episode 10
Hellsing - Episode 11
Hellsing - Episode 13

I don't get any errors, but the fields are empty.