Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2014
    Posts
    2

    Unanswered: adding multiple records based on variable

    I have searched long and hard (as far as the 5-6th pages of google) and while this forum is a gold mine of knowledge it seems no one has tried this exact particular task I am trying to do.

    background
    for kicks i am cataloging my music collection (how i been teaching myself access):

    table album
    ID
    album title

    table song
    ID
    song title

    tablealbumsong
    id
    albumid
    songid
    track#

    What i am trying to do is from: formalbum input how many tracks are on the album, and on a subform create multiple records based on my input. However at the same time the records are being created I would need track# to be populated with the value i typed in (starting at 1).

    For what i have gather this can really only be done using VBA which is where I am weak at. I watched a lot of videos and read a lot of forums and articles. I have a decent understanding for the coding however, i cannot seem to put something together for what I am specifically trying to do. Would anyone be able to walk me through this?

    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Don't use reserved words or non-alphanumeric caracters in the name of the objects, so:
    Tbl_Albums: ID, AlbumTitle
    Tbl_Song: ID, SongTitle
    Tbl_AlbumSong: ID, AlbumID, SongID, TrackNumber

    2. With:
    a) Parent form: Frm_Albums (Text_ID, Text_AlbumTitle, Text_TrackCount, SF_AlbumsSongs)
    b) Child form: SF_AlbumsSongs (Text_ID, Text_AlbumID, Text_SongID, Text_TrackNumber)
    For SF_AlbumsSongs :
    - SourceObject: SF_AlbumsSongs
    - LinkChildFields: AlbumID
    - LinkMasterFields: ID

    3. In the AfterUpdate event handler of the TextBox Text_TrackCount, we use a For... Next loop to insert the rows in the junction table Tbl_AlbumsSongs, then we Requery the child form:
    Code:
    Private Sub Text_TrackCount_AfterUpdate()
    
        Const c_SQL As String = "INSERT INTO Tbl_AlbumsSongs ( AlbumID, TrackNumber ) VALUES ( @0, @1 );"
        
        Dim strSQL As String
        Dim i As Long
        
        If Val(Nz(Me.Text_TrackCount.Value, 0)) > 0 Then
            For i = 1 To Val(Me.Text_TrackCount.Value)
                strSQL = Replace(Replace(c_SQL, "@0", Me.Text_ID.Value), "@1", i)
                CurrentDb.Execute strSQL, dbFailOnError
            Next i
            Me.SF_AlbumsSongs.Requery
        End If
        
    End Sub
    Note: Keep in mind that this process won't work correctly if rows related to that album already exist in the junction table Tbl_AlbumsSongs. To handle such cases, you could test for the existence of those rows:
    Code:
    If DCount("*", "Tbl_AlbumsSongs", "AlbumID = " & Me.Text_ID.Value) = 0 Then
    
        ...
    
    End If
    Or you could elaborate a more complex procedure that would be able to add n rows to the already existing rows in Tbl_AlbumsSongs for that album:
    Code:
    Private Sub Text_TrackCount_AfterUpdate()
    
        Const c_SQL As String = "INSERT INTO Tbl_AlbumsSongs ( AlbumID, TrackNumber ) VALUES ( @0, @1 );"
        
        Dim strSQL As String
        Dim lngCount As Long
        Dim i As Long
        
        lngCount = DCount("*", "Tbl_AlbumsSongs", "AlbumID = " & Nz(Me.Text_ID.Value, 0))
        If Val(Nz(Me.Text_TrackCount.Value, 0)) > 0 Then
            For i = lngCount + 1 To Val(Me.Text_TrackCount.Value) + lngCount
                strSQL = Replace(Replace(c_SQL, "@0", Me.Text_ID.Value), "@1", i)
                CurrentDb.Execute strSQL, dbFailOnError
            Next i
            Me.SF_AlbumsSongs.Requery
        End If
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi
    Based on the following assumptions :-
    tablealbumsong
    id is Autonumber
    Primary key is a composite based on albumid and track#

    Mainform is based on table album
    Subform is based on/bound to tablealbumsong and has a Parent /Child relationship based on albumsong.id - > albumid
    Subform has a ComoBox based on [table song] to pick the song id and a textbox for the track#

    Based on the above I suggest the following code in the OnCurrent event of the sub form
    Code:
    Private Sub Form_Current()
        if Me.NewRecord Then
            Me.track# = Nz(Dmax(“track#”,” tablealbumsong”,” albumid = “ & Me.Parent.ID))+1
        End If
    End Sub
    This will automaticaly insert the next track number when a new record is started.
    Defining the composite key will ensure that you cannot have 2 songs in a given LP/CD with the same track number.

    Note this air code and may need tweeking!?

    HTH


    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •