I am using this website first time and need desperate help. I am kind of novice to VBA utilization. So if someone can extend help I will appreciate for the patience and kindness.
I have a master table called 'tblProjects' - it has 2 fields - 'ProjectBUScopeID' & 'ProjectBUScopeName'. These two fields data needed to be stored from a form 'frmProjects' where I have a multi select listbox that is created from a query out of a table 'tblBUScope' that has 15 items listed.
I need to assign or select via a command button 1 or more selected items from the 'tblBUScope' to 'ProjectBUScopeID' & 'ProjectBUScopeName' that applies to the each individual project and stores them to the individual table; also I need to show the assigned or selected items on a different window of the form 'frmProjects.
What I did so for is I created a list box using a query from the 'tblBUScope' and I am able to select multiple choices but when I click on the command button next to it, it gives me error. I created a click event on the button to update/store the selected item calling a function but I don't think I did everything quite right.
The following codes that I tried which is not working - anotherwords I got the error - '3265 item not found in this collection.
Private Sub Command553_Click()
On Error GoTo Err_Command553_Click
Me.TextNotice = CreateProjectBURecords(Me.ProjectBUScope) & " Records Created"
Public Function CreateProjectBURecords(ctlRef As ListBox)
Dim i As Variant
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Dim RecCount As Integer
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qBUScopeAppend
Set rst = qd.OpenRecordset
RecCount = 0
For Each i In ctlRef.ItemsSelected
rst!BUScopeID = ctlRef.ItemData(i)
rst!BUScopeName = Me.ProjectBUScopeName
RecCount = RecCount + 1
Set rst = Nothing
Set qd = Nothing
CreateProjectBURecords = RecCount
Select Case Err.Number
Case 3022 'ignore duplicate keys
RecCount = RecCount - 1
MsgBox Err.Number & "-" & Err.Description
Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.
When developing Access applications, often we need to know whether an object exists. This article presents a quick and reliable routine to determine if an object exists without that object being opened.
If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted. Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…