multi-value field

[Rob S]

I'm still learning Access, so I must apologize in advance for my question.
I have four tables, tblBrochure, tblStaff, tblOrganization,
tblResourceDistribution.

tblBrochure has two fields
-BrochureName
-Active (y/n)

tblStaff has two fields
-StaffName
-Active (y/n)

tblOrganization has two fields
-OrgName
-textbox (0 and 1, explained below)

tblResourceDistribution has 5 fields
-DistribDate
-StaffName (lookup from tblStaff ->Staffname
-Organization (lookup from tblOrganization ->OrgName)
-Brochure (lookup from tblBrochure ->BrochureName)
-Quantity

The form has a combo box for StaffName and Brochure. It has a textbox for
the date and quantity. Then I have two list boxes associated with the
organization name. the first list box is a listing of all of the entries in
tblOrganization with a 0 and the second listbox is a listing of the entries
in tblOrganization with a 1. These two listboxes would move the name of the
organization from one to the other using an update query.

So, all of this lets me ask - Is it possible to distribute 25 brochures each
to 4 organizations (total 100) and when entering the distribution, move the
4 organizations out of the first listbox and into the second listbox using
my update query and then click "submit" and have it update
tbResourceDistribution in such a way to accomlish two tasks; (1) run a
report that shows the total distribution and (2) run a report that shows how
many brochures each organization received. This is one portion of the
database I'm working and the goal is to make the entering of similar data a
thing of the past - one entry is better than 4 in the eyes of my coworkers.

Is this possible? I created an update query thinking it would move the data
from the form fields into tblResourceDistribution, but it doesn't seem to
move the data over. I also changed the update query to a new table query to
see if it was even working correctly. It creates the temp table with the
fields, but the fields are empty even though I identify the appropriate
fields from my form in the query. BTW - I'm using Access 2007.

 

[Allen Browne]

Your design looks good, so you should be able to Execute an Append query to
put the data into the junction table.

To get you started, this is how you would automatically add all brochures
for all organizations:
1. Create a query using tblBrochure and tblOrganization. There will be no
line joining the tables in the upper pane of table design, so the query
outputs every combination of brochure and organization.

2. In the Criteria row under the OrgName and BrochureName fields, enter any
criteria you want to limit the organization or brochure (e.g. to exclude any
that have already been assigned.)

3. Change it into an Append query (on the ribbon.) When Access will ask what
table to append to, its tblResourceDistribution. You see a new Append row in
the query design grid.

4. In the Append row, choose the target field names. For example, put
Organization under OrgName, and Brochure under BrochureName.

5. In a fresh column of the Field row, enter the date, e.g.:
#1/20/2010#
In the Append row under this field, choose DistribDate.

6. Repeat step 5 for the Quantity you want to assign.

At this stage, if you run the query, you would append the records to the
target table. If you want to do this programmatically, you need the query
statement you just created to use in your code, so switch the query to SQL
View (on the ribbon), copy, and paste into your code. The code would look
like this:

Dim db As DAO.Database
Dim strSql As String

Set db = dbEngine(0)(0)
strSql = "INSERT ...
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) appended."
Set db = Nothing
Forms![YourForm].Requery 'so it sees the new records

This may help you get the SQL statement into your code:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html