Moving subform listbox items to table

I need to move items into a table based on selections in a listbox. (Database Attached)

Attached is the database I am working with. The form is titled frm_Act1TO.

This form is used to log weekly activities done by the user in order to submit a weekly report to our client.

The user puts a date at the top of the form, chooses a task order, then chooses a subtask order, and then adds actvities to their report. New activities are added on the right hand side of the form.

I want the user to also be able to choose items from older reports and add them to their new report. This way, if there are no changes, the user doesn't have to type everything again.

On the left there are fields for two dates and a list box. When the user enters his or her information in the top of the form, he or she can then go to the date range boxes, enter dates, and pull up a list of items that have been logged in previous reports.

What I want to have happen now:
The user pulls up the list in the subform.
The user selects however many items they want added to the new report with the date they are currently useing.
The user clicks the Add Items to Current Report button and it takes the ActID numbers that are selected in the subform, adds them and the STOID and TOID from the Activities table and puts them into the StaffActivities table with the date selected at the top of the frm_Act1TO form.

Basically, I want the user to be able to add old items into their new report with the new date.

I am not sure how to do this because of the different elements involved (form, subform and table). I am probably just psyching myself out.

If testing the form, I recommend using Task Order BC and then choosing Sub Task Order 00-23rd Floor Receptionist. There are plenty of records there.

Thank you in advance!!!!
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Nick67Connect With a Mentor Commented:
Look at this
No offense, but your question reads much less like a question and much more like you have a user interface plan in mind, without the skills to execute it.  I cannot open accdb format files, so I cannot see how much of the interface you have fleshed out, though, to judge properly.

First, have a look at my article here.
It deals very much with the thorny problem of wanting to reuse past data without editing it.

If you can save your sample as an mdb and have a go at implementing the techniques discussed in the article, and Compact & Repair it (to reduce size) before posting it again, that'd be great too, and I'll help you iron out the wrinkles.

MeginAuthor Commented:
That is totally fair.

I have some experience writing code, but sometimes I get to actions that seem so complicated that I am not even sure where to start with them.

But I may be relying on the experts here too much.

I will look at the link you have posted and will try to use those techniques to go forward.

Thank you for taking the time to point me in the direction for this information.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Keep me in the loop, and we'll see what we can do for you.
I've never been a "Hello, World" kind of learner.
Instead I ask, what's the most complicated thing I can conceive of executing (and in Access that is the data entry forms), and what building blocks of skills do I need to master to do it?

I can then move on to doing the things I couldn't imagine :)
Helen FeddemaCommented:
In case you need to work with items in a multi-select listbox (I think  you may need one as part of your interface), here is some sample code:
Private Sub cmdProcessListboxItems_Click()

On Error GoTo ErrorHandler

   Dim lst As Access.ListBox
   Dim strData As String
   Dim strPrompt As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   Set lst = Me![lstSelectContacts]
   'Check that at least one item has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one item"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
      GoTo ErrorHandlerExit
   End If
   strTitle = "Information missing"
   'Test for required information, using listbox columns
   For Each varItem In lst.ItemsSelected
      'Check for required address information (or whatever you need to check)
      strTest = Nz(lst.Column(5, varItem))
      Debug.Print "Street address: " & strTest
      If strTest = "" Then
         strPrompt = "Skipping this record -- no street address!"
         MsgBox prompt:=strPrompt, _
            buttons:=vbExclamation + vbOKOnly, _
         GoTo NextItem
      End If
      'Do something with info from the listbox columns, using
      'this syntax
      strData = Nz(lst.Column(5, varItem))
   Next varItem

   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

MeginAuthor Commented:
I am not sure I understand this piece of code:

      'Do something with info from the listbox columns, using
      'this syntax
      strData = Nz(lst.Column(5, varItem))

Open in new window

What does it mean by "do something"?

Does that add the selected items into the table? Because I don't see how that works in this code.
There are many, many, MANY things you may wish to do with the data
Way more than we can guess :)

You may want to display it as the caption of a label
me.label1.caption = Nz(lst.Column(5, varItem),"")

Usually column 0 is bound to a primary key, so you could want

dim db as database
dim rs as recordset

set db = currentdb
set rs = db.openrecordset("select * from tblSomeData where MyPrimaryKey = " & Nz(lst.Column(0, varItem),0), dbOpenDynaset, dbSeeChanges

and then do stuff with that recordset

or, just for testing, our old friend

Msgbox Nz(lst.Column(1, varItem),"") to see if you are getting the value you intended

It sometimes helps to write pseudocode.
It ain't functional, but it lines out the idea and logic

'see if anything is selected
'bail, the user's gotta pick something
'see if more than one thing is selected
'do some stuff and bail
'for each thing selected
    'do some stuff with the data from the listbox
'next item loop
'some message to say Done!

The syntax for getting the data from a multiselect listbox is a bit complex
Each item (row) is a Variant
Each item has an array of values, one from each column in the listbox
The array numbers from 0 up
So do the items

Say you have an litstbox with 3 columns and 12 items called lstGoodStuff

When run
Msgbox Me.lstGoodStuff(2,10) would messagebox out the value from the third column and 11th item of the listbox
MeginAuthor Commented:
I am still working on this. This got busy in the office.

I am not forgotten!
MeginAuthor Commented:

I have some code. It isn't working, but I have some code written. I have a starting place!

Here is the code:

Private Sub btnAddOldItems_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim PrevRpt As Object
Dim ActDate As Object
Dim sublist As ListBox
Dim strtitle As String
Dim strprompt As String
Dim rstStaffAct As TableDef

Set ActDate = Forms![frm_Act1TO].[ActDate]

Set PrevRpt = Forms![frm_Act1TO]![frm_Act_Previous].Form![ActID]
Set sublist = Forms![frm_Act1TO]![frm_Act_Previous].Form![lstActivities]

Set rstStaffAct = db.OpenRecordset("StaffActivities")

rstactID.Value = PrevRpt

If sublist.ItemsSelected.Count = 0 Then
    strtitle = "No Items Selected"
    strprompt = "Please select at least one item"
    MsgBox prompt:=strprompt, _
        buttons:=vbInformation + vbOKOnly, _
    GoTo ErrorHandlerExit
End If

    rst!ActDate.Value = Nz(Me!ActDate.Value, Date)
    rst!ActID.Value = PrevRpt
    rst!STOID.Value = cmbSTO.Value
    rst!TOID.Value = cmbTO.Value


End Sub

Open in new window

I started running into trouble when I declared rstStaffAct as TableDef. I think I have this all wrong. I am trying to declare the table that I need to move the information into.
Later, when I try to use the AddNew command, I get errors. I originally had it as rstStaffAct.addnew, but that was before I declared it as tabledef.

I haven't gotten past this error yet. I am not sure how to declare the table. I don't think I can delare it as an object, can I?
TableDefs are the structural definitions of a table.
They are highly infrequently used.
Generally, you build the table in the GUI, and then never need to mess with it again.

One use I make of TableDefs is when the table is a linked table, and I want to change it's source in code
Dim rstStaffAct As TableDef Bad

It's a recordset
Dim rstStaffAct As recordset

But you are WAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAY ahead of yourself with your code.

You need two separate routines.
One is to find the items you'd like to populate with list box with and do so.
Another is to take the items that the user selected in the listbox, commit them to the database, and clear the list box.

So, things are quite muddled.
Dim db As DAO.Database 'good
Dim rst As DAO.Recordset 'good

Very few things get declared as objects, generally outside programs like
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

Dim dbs As Object
Set dbs = Application.CurrentProject

Dim ol As Object
Set ol = New Outlook.Application

so these probably aren't right
Dim PrevRpt As Object 'bad
Dim ActDate As Object 'bad
much more likely
Dim PrevRpt As Long '(it's an ID number right?)
Dim ActDate As Date  '(it's a date, right?)

'set is not used for straight, data type variables
ActDate = Forms![frm_Act1TO].[ActDate]
PrevRpt = Forms![frm_Act1TO]![frm_Act_Previous].Form![ActID]

Now, none of the rest of your code really belongs
First, with the GUI, you want to create a parameter query with Forms![frm_Act1TO].[ActDate] and  Forms![frm_Act1TO].[ActDate] and save it with a name

Then you'll need to get that query with your code
Dim qdf as QueryDef
Set qdf = db.QueryDefs("WhatEverYouNamedThatQueryIJustToldYouToCreate")

Then you feed it your parameters
qdf(0) =ActDate
qdf(1) =PrevRpt

Then you open a recordset from that querydef
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)

Now the fun part
You need a loop
It's going to walk through the recordset and add all the stuff to the listbox, each thing separated by a semicolon

dim MysublistData as string
With rst
    Do until rst.eof
         MysublistData = MysublistData & rst!WhateverTheFirstDesiredFieldIs & ";"
         MysublistData = MysublistData & rst!WhateverTheSecondDesiredFieldIs & ";"
'and so on
end with

Now set the recordsource of your listbox to that string
me.sublist.RowSource = MysublistData

Test it out
Post a smaple
Of course, if your query is completely righteous and needs nothing added, then you don't need any code at all.
In Design view you just make the RowSource of the list box =  "WhatEverYouNamedThatQueryIJustToldYouToCreate"

Here's working code that populates a listbox dynamically
Private Sub PopulateReprintStats()
Dim db As Database
Dim rs As Recordset
Dim JobCount As Long
Dim ReprintCount As Long
Dim ReprintPercentage As Double
Dim TotalReprintPercentage As Double

Set db = CurrentDb
Set rs = db.OpenRecordset("select Count(tblInsDetails.JobID) as JobCount from tblInsdetails where >=#6/1/2013#", dbOpenDynaset, dbSeeChanges)
JobCount = Nz(rs!JobCount, 1)
Set rs = Nothing

Set rs = db.OpenRecordset("select sum(qryReprintCountSinceMrb.IndividualReprints) as reprintcount from qryReprintCountSinceMrb;", dbOpenDynaset, dbSeeChanges)
ReprintCount = Nz(rs!ReprintCount, 0)
Set rs = Nothing

Me.lblReprintStats.Caption = "Reprint Stats since 1-Jun-13: " & vbCrLf & "Jobs created: " & JobCount & vbCrLf & "Jobs reprinted: " & ReprintCount

Set rs = db.OpenRecordset("qryReprintCountSinceMRB", dbOpenDynaset, dbSeeChanges)
If rs.RecordCount > 0 Then
End If
Me.ReprintStats.RowSource = "Name;Percentage;#"

TotalReprintPercentage = 0

Do While rs.EOF = False
If JobCount = 0 Then JobCount = 1
    ReprintPercentage = Round(rs!IndividualReprints * 100 / JobCount, 2)
    TotalReprintPercentage = TotalReprintPercentage + ReprintPercentage
    Me.ReprintStats.RowSource = Me.ReprintStats.RowSource & ";" & rs!Name & ";" & ReprintPercentage & "%" & ";" & rs!IndividualReprints

Me.ReprintStats.RowSource = Me.ReprintStats.RowSource & ";" & "Total;" & TotalReprintPercentage & "%" & ";" & ReprintCount
End Sub

Open in new window

MeginAuthor Commented:

I am reading through your response and first of all, you are a great explainer! I really like the way you write.

Second, I am lame and still confused.  My list box is already populates with a query that is run based on items selected in the drop down menu and by the date range that the user puts in. I only need to move information once the items are selected.

It looks like you are telling me how to code the query behind the list box. Is that what you are saying? Or do I need a query for another reason?

And I don't know what a GUI is.

At least I got that one line of code right!
I want the user to also be able to choose items from older reports and add them to their new report. This way, if there are no changes, the user doesn't have to type everything again.

That left me the impression that you needed to build the contents of what is in the listbox.
Which you can do (and apparently have already done) by setting the RowSource of the listbox to the name of a query
You can also do it manually by building a big string of phrases separated by semicolons -- but you don't seem to need that functionality -- YET
But you know it exists, and that may intrigue you in the years ahead :)
GUI : Graphical User Interface -- in other word Windows and Access regular normal stuff--and not what you are building in code

So now you want to get data into a table.
A listbox's selected items generally have one value.
It tends to be an ID--it's whatever you selected to be the Bound Column

Getting data into a table is straight forward enough
Dim db As DAO.Database
Dim rst As DAO.Recordset
set db = currentdb
set rs = db.OpenRecordset("TheNameOfSomeQueryThatHasTheFieldsYouWantToFillIn", dbOpenDynaset, dbSeeChanges)

And then

With rst
    !SomeFieldName = SomeNumber
    !SomeOtherField = "SomeString"
    !AnotherField = Me.SomeControlILike.Value
    'and so on until all the fields you want to fill in have the values you want
end with

Now, you want to add a bunch of records, and one of those fields (hopefully only one!) comes from the selections of your listbox.
So you need to loop through the ItemsSelected of your listbox, and add a record each loop
Dim varitem As Variant 'no matter what is in your listbox, when you go to loop, the items must be variants
For Each varitem In  Me.sublist.ItemsSelected
    'Now we execute our rst.AddNew stuff
    With rst
        !SomeVeryImportantField = Some Element of varitem I will discuss below
        !SomeFieldName = SomeNumber
        !SomeOtherField = "SomeString"
        !AnotherField = Me.SomeControlILike.Value
        'and so on until all the fields you want to fill in have the values you want
    end with

Ok,  Some Element of varitem I will discuss below, varitems are the selected rows
If what you wanted is the bound column value that'll be
But there can be other possibilities
Me.sublist.Column(1, varitem)
will get you the second column(! most collections start numbering at zero) of the selected item in the listbox
Me.sublist.Column(2, varitem) will get you the third
Me.sublist.Column(0, varitem) is the first column
Usually, that's the bound column and equivalent to  Me.sublist.ItemData(varitem), but for some weird reason you may have bound a different column.

When you want to de-select everything, you use this
'unselect everything in the listbox
For Each varitem In Me.sublist.ItemsSelected
    Me.sublist.Selected(varitem) = False
Looking at your code probably

Dim varitem As Variant 'no matter what is in your listbox, when you go to loop, the items must be variants
 For Each varitem In  Me.sublist.ItemsSelected
     'Now we execute our rst.AddNew stuff
     With rst
         !ActDate = Nz(Me!ActDate.Value, Date)
         !ActID = PrevRpt
         !STOID= me.cmbSTO.Value
         !TOID = me.cmbTO.Value
         !SomeVeryImportantField = Me.sublist.ItemData(varitem)
     end with
MeginAuthor Commented:
I am going to try to get to this today. Work has been busy today.

My main job is not to create databases, which is probably obvious. Building this database is a small part of my job. I am the only one on my team who has any experience with Access/VBA/SQL, though still very little.
MeginAuthor Commented:
When I get to this:
For Each varitem In  Me.sublist.ItemsSelected

The name of the list box isn't an item I can choose from the drop-down menue that comes up.

I am told that "Variable not defined" and it highlights "RS."

I am sure this is really simple. I tried setting RS = Variant, but that didn't really get me very far.

Why won't the "For Each Item" section recognize the list box? Is it because it is part of a subform?
The bold part is the name of the list control.
I probably guessed that wrong
Your code had Set sublist = Forms![frm_Act1TO]![frm_Act_Previous].Form![lstActivities]

Although looking at that now, I can see that you are perhaps coming from an ASP.NET, VB.NET type background.
There, to use a controls value in the code, you usually have to declare a control in code and set its value.
That's VERY confusing to an Access guy.
Every control is automatically a member of the form class and ready for use, so where my code shows sublist what you probably wanted was lstActivities

Post all of your code so far!
MeginAuthor Commented:
I actually have no coding background. I took a short SQL course and just finished a beginners VBA course (3 days), but nothing other than that. I have a lot of experience with Access, but not with the coding.

Here is my code so far.

Dim rst As DAO.Recordset
Dim sublist As Long

Dim varitem As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("Qry_Temp_Report", dbOpenDynaset, dbSeeChanges)
set sublist = Forms![frm_Act1TO]![frm_Act_Previous].Form![lstActivities]

Dim varitem As vairant
For Each varitem In me.

With rst
    !ActDate = Me.ActDate.Value
    !ActID = Me.ActID.Value
    !STOID = Me.cmbSTO.Value
    !TOID = Me.cmbTO.Value
End With


End Sub

Open in new window

I had taken out the Set Sublist part, but I put it back in. When I go to type the part about "For Each Varitem in Me.___ nothing comes up in the drop-down. Not "sublist." Not "LstActivities."  Nothing that seems useful.

Also, thank you for going through this with me so much! I can't believe how much trouble I am having with it. It should be simple, right?
This stuff is wrong
Dim sublist As Long
set sublist = Forms![frm_Act1TO]![frm_Act_Previous].Form![lstActivities]

and this is mis-spelled and a repeat of an earlier declaration
Dim varitem As vairant
and you are missing
Dim db as database

That's the kind syntax you use when you are trying to refer to a control that's on another form's subform.
Are you using a form and subform?
MeginAuthor Commented:
Yep. I am trying to get pull information from a form and a subform into a table.
Now, I can't really envision what you are trying to do without a sample, and my guesses have been wrong
And an mdb sample, not an accdb one.

See what you can do to post a sample database for me to look at and we'll see what can be done
Me.___ nothing comes up in the drop-down.
Because your listbox control isn't on Me, its on something else.
suggest that your main form, the one that has the button that we are coding for is
Now, with subforms, naming conventions are handy things and getting them right always makes life simpler
I name subforms subSomeUsefulName.  They are immediately distinguished from forms meant to load independently
When you put a subform control on a form, the subform control requires a name, too
I name it the same as the subform that it will display
At present, I think your subform is
and the listbox is

To test, throw a button on the main form
In it's Click event put
msgbox Me.frm_Act_Previous.form!lstActivities.Value

Attached is a sample to begin to play with
You ONLY get Intellisense support for DOT (.) operators and not BANG(!) operators or anything after them
So, when you are referencing subforms you get this for free
But this part, YOU have to get right yourself
MeginAuthor Commented:
I haven't gone over your notes yet, but I wanted to get this attached before I get distracted. I converted the database to mdb format, but there might be some errors because it was created in Access 2010.

I will get to your notes shortly.

Also, thank you so much for your patience! I can't tell you how grateful I am for it.

I think you have more fundamental issues to think about.
Work your way entirely through this
It's about data normalization, which is thoroughly critical to getting everything else to work right.
Looking at your relationships, I can't quite make sense of what you have going on.

I would guess that you want to create a database to track tasks.
Each task is performed for ONE company.  Tasks may have a TaskOrderNo generated by some other system, and a description.  Perhaps they have a creation date, completion date and some type of status

Each task can have many subtasks.  Subtasks have an identifier and a description

Each subtask can have many activities.  Activities have an identifier and a description.  Each Activity can have  Activity type.  Activities may be performed by more than one staff member.

Staff members have all those good things that describe people.

Staff can be members of a single team.  Teams have an identifier and a name

All Activities completed for all the subtasks for a single task may only be performed by members of a team that is assigned to the company for whom the task has been created.

Each team may have many companies assigned to it.

Is this a good description of what you are trying to model?  Because your data does NOT presently relate in this way.

It is of utmost importance to get your data modeled correctly before moving on to creating the forms and reports
MeginAuthor Commented:
I haven't abandoned this. Things just got really crazy at work. I will get back to this soon.
MeginAuthor Commented:
I'm Back!

I have rebuilt the database. You were totally right about the normalized tables. I think my mistake may have had something to do with trying to create the tables in SQL, but since it has been a while, I am not sure.

Anyway, I think they are better now.

Again, I am at the same point in the code.
MeginAuthor Commented:
...and I meant to attach this.
Ok, things are looking more sensible from the table relationship point of view, but is this design really what you want?  The linchpin of the design you have now is StaffSTO--and I am not sure that that table is correctly constructed.  It has StaffID as an autonumber  and a relationship with StaffID in the table Staff!  Each new record in StaffSTO is going to increment its StaffID by one--but you won't be having hundreds of staff members!  I think you need a new 'StaffSTOid'  to be the primary key autonumber, and not STAFFid.

I'd also preface all your table names with 'tbl'  You've done so for queries and forms.  You then never have any ambiguity when you are discussing it.  'Staff', 'Team', or 'Activities' can mean the actual people, groups, or actions -- or your tables.  'tblStaff', 'tblTeam', and 'tblActivities' removes that ambiguity.

I would have thought that the task orders would have been the critical thing, but maybe not.

Now, the staff member is associated to the subtask.  You don't have any real data in here, so it is tough to know if that's truly what you want.  Are ALL of the activities of any given subtask performed by a single staff member?  Activities are only related to staff indirectly, so your present structure will not actually tell you which staff members performed an activity, only that certain staff members were associated with a sub task and that sub task had certain activities performed.  Is that your intent?

Now,  I've made some sense of what you've got.  If I pick Task Order AA and Sub Task Order 6 with dates 1-Jul-14 and 16-Jul-14, and click the button, the Previous Activities listbox populates with four items, which can be multi-selected.  But you have a muddle.  Which NEW TO and STO do you intend to tag them to?

And things aren't completely normalized yet.
I'm working on it--but I have to sleep, now
MeginAuthor Commented:
Thank you for the feedback!

Yes, I need the center of the connections to be the STO numbers. I determined them to be the central factor for tracking.

I still need to look through your other recommendations. I haven't had a chance (or a functioning brain cell) to really go through them carefully.

Also, I can't open that attachment.
I have the attachment issue, too, in IE 11, but not in Chrome.
I've pinged other Experts to see if it is an EE glitch.

In the meantime, right click the link.
Choose Save As...
It'll want to save it as as type All Files
Change the .htm to .accdb

You should be good then.

MeginAuthor Commented:
I ran into more items at work to get to.

I swear I will get back to this! It might not be until Friday.
I see you have new questions, but how is this coming?

MeginAuthor Commented:
This is beautiful!

I totally slacked on getting back to this. I implemented changes and forgot to come back to this.

I ended up changing a few things in the end, but this is what worked.

Thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.