Results 1 to 3 of 3

Modifying fieldname results in VBA error

  1. #1
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    312

    Modifying fieldname results in VBA error

    Experts:



    I need some assistance with modifying a few lines of VBA. Attached are two database examples which contain two listboxes from which one or multiple record(s) can be moved across the listboxes.

    ******************************

    1. "Two Listboxes v01 -- works ok" -- this version works fine. It includes:
    - 3 tables (one of them is a junction table)
    - 3 queries
    - 1 Form

    Process:
    - When opening the form, the user can pick "Working Group A, B, C, ... I" from the drop-down menu.
    - Then, for any of the ten working groups, positions (billets) can be moved from "Available Billets" to "Assigned Billets" or vice versa.

    Again, this version works great. However, I would like to update a fieldname in table [T10_JunctionTable_BWG]. That is, I would like to change field name "WorkingGroupIDpk" to "WorkingGroupIDfk" ("f" for foreign key vs. "p" for primary key). This may seem trivial but it will assist me later on when this database grows; further, it preserves consistency with naming references (since I already change the same for the BilletIDfk).

    ******************************

    2. "Two Listboxes v02 -- with VBA error" -- I get a VBA error in this version.
    - This version is a copy of v01 and I already changed the fieldname and updated the 3 queries accordingly.
    - Now, when opening the Form and trying to add, e.g., more billets from "Available" to "Assigned", an error occurs.
    - As, there are several references to "WorkingGroupIDpk" and I'm not sure which one needs to "WorkingGroupIDfk" based on the fieldname change in [T10_JunctionTable_BWG].
    - At one point, I thought I had updated the correct instances (changing "pk" to "fk" where appropriate) and it appeared that it worked fine. However, after I cleared all records from the junction table and then attempted to all more than 10 billets to a WG,
    the same error came up.

    ******************************

    In summary, it would be awesome if someone could point out which instances require the change from "WorkingGroupIDpk" to "WorkingGroupIDfk" (preferably copy the full VBA or updated working database v02).

    Thank you,
    EEH

    P.S. Also attached is a snapshot highlighting the fieldname that requires the change.
    Attached Thumbnails Attached Thumbnails ChangeOfFieldname.JPG  
    Attached Files Attached Files

  2. #2
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,079
    Code:
    Private Sub cmdAddOne_Click()
        Dim db As DAO.Database
        Dim varItem As Variant
        Dim rst As DAO.Recordset
        Dim IsRptOpen As Boolean
        
        Set db = CurrentDb()
        Set rst = db.OpenRecordset("Select * from [T10_JunctionTable_BWG] where BilletIDfk=-1")
        
        For Each varItem In Me.lstAvailable.ItemsSelected
            With rst
                .AddNew
                .Fields("WorkingGroupIDfk") = [WorkingGroupIDpk]
                .Fields("BilletIDfk") = Me.lstAvailable.ItemData(varItem)
                .Update
            End With
        Next varItem
        
        rst.Close
        Set rst = Nothing
        Set db = Nothing
        
        Me.lstAvailable.Requery
        Me.lstSelected.Requery
        Me.cboWorkingGroup.Requery
        
    End Sub
    
    Code:
    Private Sub cmdRemoveOne_Click()
        On Error GoTo cmdRemoveOne_Click
        Dim db As DAO.Database
        Dim varItem As Variant
        Dim strSQL As String
        Dim strWhere As String
        Dim IsRptOpen As Boolean
            
        For Each varItem In Me.lstSelected.ItemsSelected
            strWhere = strWhere & "BilletIDfk=" & Me.lstSelected.ItemData(varItem) & " OR "
        Next varItem
        
        strWhere = Left(strWhere, Len(strWhere) - 4)
        strSQL = "Delete * from [T10_JunctionTable_BWG] where WorkingGroupIDfk=" & [WorkingGroupIDpk] & " AND (" & strWhere & ");"
        Set db = CurrentDb
        db.Execute strSQL
        
        Me.lstAvailable.Requery
        Me.lstSelected.Requery
        Me.cboWorkingGroup.Requery
        
        Set db = Nothing
        
    Exit_cmdRemoveOne_Click:
        Exit Sub
        
    cmdRemoveOne_Click:
        Resume Exit_cmdRemoveOne_Click
    End Sub
    
    You also have an error in query3, the rowsource for lstAvailable.
    Code:
    SELECT o.BilletIDpk, o.RA_BIN AS RA_BIN_NUMBER, o.RA_Billet_Title, mq.BilletIDfk
    FROM T01_Billets AS o LEFT JOIN (SELECT T10_JunctionTable_BWG.[BilletIDfk], [T10_JunctionTable_BWG].[WorkingGroupIDfk] FROM T10_JunctionTable_BWG WHERE T10_JunctionTable_BWG.[WorkingGroupIDfk]=Forms!F01_Listboxes![WorkingGroupIDpk])  AS mq ON o.BilletIDpk = mq.BilletIDfk
    WHERE (((mq.BilletIDfk) Is Null));
    Last edited by davegri; 07-24-2019 at 12:41 AM. Reason: Added query mod

  3. #3
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    312
    davegri -- perfect solution!!!!!

    Thousand thanks for provided a response that solved this issue!!!
    EEH

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-03-2018, 12:38 PM
  2. Replies: 4
    Last Post: 02-07-2018, 02:57 PM
  3. Replies: 7
    Last Post: 09-07-2016, 08:29 AM
  4. modifying only certain fields in query results
    By fainterm in forum Queries
    Replies: 9
    Last Post: 12-24-2013, 12:28 PM
  5. Replies: 14
    Last Post: 11-16-2010, 03:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums