add all possible unique values to subform (1 Viewer)

daniel.winson

Registered User.
Local time
Today, 12:54
Joined
May 28, 2009
Messages
18
Hi,

I have an entry form "frmOrganisations" where a user needs to add multiple entries into a subform "subLocations" from a fixed list (in this case a list of locations serviced by an organisation).

Can you add all possible unique records using an [add all] button, check box, radio button or an option in the combo box?

I have tried using the "Adding (All) Options" to my combo box using VB... but it crashed Access, I now suspect that it isn't designed for what I wanted anyway. - I am trying to sort this out in a separate thread here... http://www.access-programmers.co.uk/forums/showthread.php?t=173264

If I can't figure out a different way to do it I guess I'll go against what I understand are good design principals and add a new record in subLocation called "All" and write the queries to check both for the specific locations requested and then "All".

I have created the buttons in frmOrganisation to show what I mean. zipped MDB file attached. Thanks for your assistance.
 

Attachments

  • ms contacts 1427.zip
    119.1 KB · Views: 97

HiTechCoach

Well-known member
Local time
Today, 07:54
Joined
Mar 6, 2006
Messages
4,357
I would use an Add All command button that runs an append query to add all the records. I do not think the "all" option to a combo box makes a good UI.

I will take a look at your database and see if I can give you any pointers.
 

HiTechCoach

Well-known member
Local time
Today, 07:54
Joined
Mar 6, 2006
Messages
4,357
Take a look at the attached. I made the Add All command button work using:

Code:
' save the parent record
If Me.Dirty Then Me.Dirty = False

' insert the locations
CurrentDb.Execute "INSERT INTO organisationLocation (location,  organisationID ) SELECT locations.location, " & Me.ID & " AS OID FROM locations WHERE (((locations.country)=""Scotland""));"

' requery the sub form to show the locations added.
Me.organisationLocations.Form.Requery


I also creted an new index in the table organisationLocation based on the Org ID and Location

Note: The attachment is still in 2002/2003 format.
 

Attachments

  • ms contacts 1427_HiTechCoach.zip
    127.6 KB · Views: 119

daniel.winson

Registered User.
Local time
Today, 12:54
Joined
May 28, 2009
Messages
18
This is great, thanks so much for your help. It will take me some time to digest how you did it so I can use it in the future.

But basically you've
1) used VB code linked to a button to clear all existing records in the subform and then add all possible records?
2) Changed it forces unique values? How did you do this?

Is this correct? Thanks again.

dan
 

HiTechCoach

Well-known member
Local time
Today, 07:54
Joined
Mar 6, 2006
Messages
4,357
But basically you've
1) used VB code linked to a button to clear all existing records in the subform and then add all possible records?
Actually I do not clear anything first! I just run an append query and let Access handle not adding the duplicates based on the index I created.


2) Changed it forces unique values? How did you do this?
I create a compound/composite index based on two fields.
 

Users who are viewing this thread

Top Bottom