Select multiple fields

[Kila]

We have 10 sites in our organization and have a database to keep track of the requests of each site. Each request has a separate record. Is there a way I can pick "All sites" and automatically create the 10 records...an identical one for each site?

 

[Rob.Mills]

Are you saying you want to make nine duplicate records for the other sites for each request?

 

[Kila]

Clarification

Yes, I want each site to have it's own record. Another nice leap past that would be to select all or select some (such as Ctrl-pick) and have it automatically create an identical record for each of the 3 or 5 or 7, etc. Thanks.

 

[Rob.Mills]

This can be done. It's gonna require a lot of VBA. Are you familiar with that?

Also, if you're going to select which ones it should duplicate for, that will force you to do that for each request. Is that what you want?

 

[Kila]

Please suggest a better way?

There may be a better way to do this, but the searches for the reports are mostly based on the site, and the field only holds one entry. I have nothing against using only 1 record to notate all the different sites who received the contact, but I could not think of a "clean" way to do it to run the site-based reports. AM I doing it the long way?

 

[Rob.Mills]

Wait... now you're getting ahead of me? I can't tell whether or not this is the best way because I'm not sure what you're trying to accomplish with your application. Can you give me more detail about the whole process?

 

[Kila]

Yes. Sorry, I'm new to this website. I have been teaching myself Access since 1995, but I have managed to avoid all but the simplest VBA...although I have gotten REALLY good with the other tools. I know I have reached the point where I need to learn it, and am willing if you don't mind pointing me in the right direction. If you could recommend a good starting book I would appreciate it.

My database tracks suspenses (basically requests) from the regional office to 10 different sites. These requests may go to just one site, or they may go to some or all. There is a form with data about each suspense linked to a main data table. Then there is a "detail" table that is used to create a subform in the main data form. The subform has one field for each site receiving the suspense (you can pick them using a drop-down box), related to the main data table using the main data table's field ID number assigned to each detail table record it is related to. It would be much more convenient to pick all of them, or be able to select several at a time. Thanks for your time!!!

 

[Kila]

I don't mind forcing the request each time. I think they send items to all sites more often than not.

 

[Rob.Mills]

Ok. It's clearer now. I'd recommend using a listbox for this in place of that subform. Here's what I would do:

1. Eliminate the subform
2. Place an unbound listbox on the main form.
3. Make its rowsource display the names of all the sites with the listbox bound column equal to the ID field. You can hide the id field so that only the name shows.
4. Set the listbox Multi-Select Property to Extended. This will allow people to use ctrl or shift to select multiple selections at a time.
5. Create a button next to the box to run the update (name it whatever you want)
6. Now you need to create the procedure.

First make sure you've referenced the DAO library. You do that by opening the Tools menu in the VBA editor and clicking References. Then if it's not already clicked scroll to find 'Microsoft DAO 3.6 Object Library' and click it.

Setup this procedure for the button's on click event:

Private Sub cmdButton_OnClick()

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim intID as Integer
Dim vItm as Variant

intID=Forms!MainFormName!IDField

Set db=CurrentDb
Set rst=db.OpenRecordset("[Name of table originally bound to subform]", DbOpenDynaset)

For Each vItm in Me![Listbox Name].ItemsSelected
rst.AddNew
rst.Fields("[Name of field that is foreign key housing request ID]")=intID
rst.Fields("[Name of field housing Site ID]")=Me![ListboxName].ItemData(vItm)
rst.Update
Next vItm

msgbox "All requests have been added", vbOkOnly

End Sub

What this does is create a new record for each item that's selected in the listbox.

Good chance that you'll encounter some errors the first time you run it. But I'll help you through those.

 

[Kila]

Thank you! Give me a few days to try it (I have meetings all afternoon). I will definitely get back to you!

 

[Kila]

Just out of curiosity, did you know the anser to my other post this morning regarding a search box to jump-to a record in a form?

 

[blueBeard]

when i do this for a simlar scenario, i get a message that says "you didn't specify search criteria with a findrecord action"
any ideas?