UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Populate Table From Two String Variables, Access 2016    
 
   
jokeme71
post Sep 13 2018, 07:38 PM
Post#1



Posts: 918
Joined: 7-June 02
From: Maryland


I have a datasheet form that my users can select a checkbox to indicate which records they would like to do a batch update to. After the records are selected they hit a command button that opens a popup form where they can select keywords to apply to the records they have checked. I need to have all of the selected keywords associate with each selected record. I have captured all selected record PKs into a string and all of the keyword PKs into another string. There is where I am stuck. I have a table that is set up to accept the association as it contains two fields; one for the selected record PK and the other for the keyword PK. Was thinking an array possibly to populate the table but the sizes of the two arrays would not match? Any help is greatly appreciated.
Go to the top of the page
 
theDBguy
post Sep 13 2018, 08:26 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,236
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Can you show us an example of what you got and what you need to get from it? Thanks!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jokeme71
post Sep 14 2018, 10:47 AM
Post#3



Posts: 918
Joined: 7-June 02
From: Maryland


I figured it out. The array was the way to go. Feedback is welcome if anyone sees problems with this approach.

CODE
Sub proUpdKeyword(strRec As String, strKW As String)
On Error GoTo Err_proUpdKeyword

Dim arrSelRec() As String
Dim arrKW() As String
Dim rst As DAO.Recordset
Dim x As Long
Dim y As Long

    arrSelRec = Split(strRec, ",")
    arrKW = Split(strKW, ",")

    Set rst = CurrentDb.OpenRecordset("tblKWAssoc")
    
    For x = 0 To UBound(arrSelRec)
    
        For y = 0 To UBound(arrKW)
        
        With rst
        .AddNew
            .Fields("FK_SelRec").Value = arrSelRec(x)
            .Fields("FK_KW").Value = arrKW(y)
        .Update

        End With
              
        Next y
        
    Next x
    
    Forms!frmRec!frs2.Form.Requery

Exit_Sub:
    If Not rst Is Nothing Then
    rst.Close
    Set rst = Nothing

    End If

    Exit Sub

Err_proUpdKeyword:
    MsgBox Err.Number & ": " & Err.Description, vbInformation
    Resume Exit_Sub

End Sub
Go to the top of the page
 
theDBguy
post Sep 14 2018, 10:54 AM
Post#4


Access Wiki and Forums Moderator
Posts: 73,236
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st September 2018 - 10:38 AM