Technique for Adding what is essentially a new set of data to tabl

[Guest]

I am hoping someone will check my logic here. I can post my code, but I am
concerned that what I am trying to do may be better done with another method
entirely.

I have a master list of items which may need to be submitted on any given
project. The actual items needed will vary from project to project.
I the user to be able to select the project for which s/he will set up the
new list of items to be submitted.
By clicking on a button, a form will come up showing all possible items to
be submitted, which will allow the user to select the desired items from this
list.
They will select the items they want and click on another button, which will
append the selected items to a table, however each item selected must now
have the project number as a foreign primary key.
That table will then allow me to track such things as when the item was last
submitted, to whom, what action was taken, etc.

What I have now is a form that allows the user to select the project, based
on a table that contains only one record with a project number.
The user presses a button that runs a macro which sets the value of that
project number to the user selected project number.

I then want to run query1 that associates that project number with my master
list.
Then a second query that turns query1 into a table.
Then open a form based on that table, or more precisely, on a query linked
to the newly made table.

What appears to be happening is that even though I set the value of the
project early on in the process, this new value is not being used when I run
the queries.
Using DoCmd.OpenQuery is not helping.

Any idea what's wrong?

 

[Guest]

The OpenQuery is for select queries and will not update anything. I don't
know for sure what you have set up so far, but the method I would suggest for
selecting the items would be a multi select list box. Then you can use the
ItemsSelected property of the list box to create the records. You would need
a command button to append the records to your table. Here is an example.

Dim rst As Recordset
Dim ctl As Control
Dim varItem As Variant

Set rst = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
Set ctl = Me.lstItemsList

With ctl
For Each varItem In .ItemsSelected
rst.AddNew
rst![ProjectID] = Me.txtProjectID
rst[Item] = .ItemData(varItem0
rst.Update
Next varItem
End With

rst.Close
Set rst = Nothing
Set ctl = Nothing