Moving subform listbox items to table

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.
https://www.experts-exchange.com/Database/MS_Access/A_6692-Overcoming-unbound-continuous-forms-limitations-by-staging-data.html
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.

Nick67

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.

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 :)

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, _
         Title:=strTitle
      lst.SetFocus
      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, _
            Title:=strTitle
         GoTo NextItem
      End If
      
      'Do something with info from the listbox columns, using
      'this syntax
      strData = Nz(lst.Column(5, varItem))
      
NextItem:
   Next varItem

ErrorHandlerExit:
   Exit Sub

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

End Sub

Select allOpen in new window

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))

Select allOpen 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.

LOL
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
'no?
'bail, the user's gotta pick something
'yes?
'see if more than one thing is selected
'no?
'do some stuff and bail
'yes
'loop
'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

I am still working on this. This got busy in the office.

I am not forgotten!

Ok

OKAY!

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")

rst.AddNew
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, _
        Title:=strtitle
    sublist.SetFocus
    GoTo ErrorHandlerExit
End If


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

    


End Sub

Select allOpen 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
    .movelast
    .movefirst
    Do until rst.eof
         MysublistData = MysublistData & rst!WhateverTheFirstDesiredFieldIs & ";"
         MysublistData = MysublistData & rst!WhateverTheSecondDesiredFieldIs & ";"
'and so on
         rst.movenext
    loop
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 tblInsDetails.date >=#6/1/2013#", dbOpenDynaset, dbSeeChanges)
JobCount = Nz(rs!JobCount, 1)
rs.Close
Set rs = Nothing

Set rs = db.OpenRecordset("select sum(qryReprintCountSinceMrb.IndividualReprints) as reprintcount from qryReprintCountSinceMrb;", dbOpenDynaset, dbSeeChanges)
ReprintCount = Nz(rs!ReprintCount, 0)
rs.Close
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
    rs.MoveLast
    rs.MoveFirst
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
    rs.MoveNext
Loop

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

Select allOpen in new window

Okay.

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
    .AddNew
    !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
    .Update
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
        .AddNew
        !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
       .Update
    end with
NextVarItem


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
Me.sublist.ItemData(varitem)
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
Next
Me.sublist.Requery

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
         .AddNew
         !ActDate = Nz(Me!ActDate.Value, Date)
         !ActID = PrevRpt
         !STOID= me.cmbSTO.Value
         !TOID = me.cmbTO.Value
         !SomeVeryImportantField = Me.sublist.ItemData(varitem)
     end with
 NextVarItem

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.

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?

Me.sublist.ItemsSelected
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!

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
    .AddNew
    !ActDate = Me.ActDate.Value
    !ActID = Me.ActID.Value
    !STOID = Me.cmbSTO.Value
    !TOID = Me.cmbTO.Value
    
    .Update
End With

NextVarItem



End Sub
               

Select allOpen 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

Now
Forms![frm_Act1TO]![frm_Act_Previous].Form![lstActivities]
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?

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.
Right.
Because your listbox control isn't on Me, its on something else.
Now,Forms![frm_Act1TO]![frm_Act_Previous].Form![lstActivities]
suggest that your main form, the one that has the button that we are coding for is
frm_Act1TO
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
frm_Act_Previous
and the listbox is
lstActivities

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
Me.frm_Act_Previous.form
But this part, YOU have to get right yourself
!lstActivities.Value
listbox.mdb

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.
From-Experts-Exchange.mdb

Ok,

I think you have more fundamental issues to think about.
Work your way entirely through this
http://phlonx.com/resources/nf3/
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

I haven't abandoned this. Things just got really crazy at work. I will get back to this soon.

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.

...and I meant to attach this.
NewStartPMAC.accdb

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

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.

Nick67

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?

Nick67

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!