Search Field in Access 2007 select multiple values

Hi,

I have a project table and form and an address table.

I want the ability to from the project form to be able to search the address table and select multiple addresses that need to be linked to the project.

I know i can do this via a lookup but there are 250,000 addresses in the address table so i need a way to be able to search for related records and then select the relevant addresses to attach them to the project.

Any ideas?

Thanks in advance

Brendan
eyeisystemsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can create a subform which linked to your project's unique identifier so that all related records from Addresses can be pulled and populated into the subform.

as a start, you can read how to do that in this article.

Create a form that contains a subform (a one-to-many form)
https://support.office.com/en-za/article/Create-a-form-that-contains-a-subform-a-one-to-many-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b?ui=en-US&rs=en-ZA&ad=ZA
eyeisystemsAuthor Commented:
Hi Ryan,

I have subforms and the relationships are one-to-many

However how do i pick the associated addresses from the address table on my project form so that they appear in the sub form? i.e. how could i search for addresses?

I'm thinking a button that would pop up a window to allow a user to type in a variable that will search a column in the address table i.e. a zip code and bring back the results. The user can then select the ones that are relavant and click 'add' which would then add them to the current project ID.

Presumably if i created a link table with Project ID and Address ID then i could use an append query so that when the user clicks 'add' it appends the address ID's against the project ID in the link table then use that on my sub form?! I'm just guessing now

Question is i've no idea how to code the above o:)

Cheers

Brendan
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
can you upload a sample database with table structures of project and address?

you can start adding a subform into a form (the form need to be in Design Mode) by go to the Design tab, click on the "Subform/SubReport" icon, and then draw the control into your form.

A subform wizard will pop up so you can follow the wizard to link the subform's records with main form's record.

subform wizard
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

eyeisystemsAuthor Commented:
Sample attached with some dummy data in.

Essentially the project needs to be linked to one or more addresses in the CAG table but there are 250,000 records in the CAG so there needs to be a way of searching for them, selecting them and appending them etc.

Cheers

Brendan
test.accdb
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
Hi Brendan,

Ryan is describing how to set up a basic subform, which you do need, but that's not what you're asking.

Once your subform is built showing existing linked Address records, you need a way to link additional Address records.  Showing the * new record line with a combobox won't work with a dropdown list of 250k records.

So, don't show the * new record.  Instead, add a New button to the subform that pops up an "Address Picker" form using Dialog mode.  This form will have some search criteria fields at the top, with a continuous subform of Addresses below.  Require some criteria be specified before you load the subform (to start with it empty, default to Where 1=0).

Side note:  For an easy way to change out your Where clause, see our free J Street SQL Tools download at http://www.JStreetTech.com/downloads.

When the user chooses the address they want on the Picker form, use VBA code to add the appropriate record to the underlying table, then close the form and requery the original subform to show the new related address record.

Hope this helps get you started,
Armen Stein, Access MVP
J Street Technology
eyeisystemsAuthor Commented:
Ok,

So i've created a tab on my main project form called project properties and added a subform which displays the address table fields where the link master field is the Project Properties.value (multivalue because there would be mulitple properties against a project) and the child field is the ID of the address table. In addition i've added a command button to the tab.

I've created another form with the address table in it as a continiuous sub form and added 3 text boxes, one for post code, one for ID and one for address and another button in the main form (so the user can press this to perform a search)

Now the tricky bit....

I want to create an event procedure that takes the values from the text boxes on click of the search button and then displays the results within the sub form....

presumably like

Private Sub Command13_Click()
dim postcode as string
dim address as string
dim uprn as string
'/ uprn is the primary key for the address table and is text value

postcode = postcode.value 
address = address.value
uprn = uprn.value
End Sub

Open in new window


I dont know how to then pass these values to the subform and when the results are provided how to select (multiple) result to add the UPRN to the project record?

It's been a long time since coding VBA and apologies i feel like a newbie all over again! :)

Cheers

Brendan
eyeisystemsAuthor Commented:
Also, i've heard adding multiple values to a field is bad practice is this true? would it be better to add a new table to link the projects to properties (Edit sorry i should have mentioned i'm saying properties but i mean addresses!)

e.g.
Table.ID
Project.ID
Address.UPRN

Open in new window


So in fact the VBA when selecting the records just kicks off an append query to add the UPRN's to the link table against the current project ID and then use fields from this and the address table to pull all the details back?

Cheers

Brendan
eyeisystemsAuthor Commented:
hmm,

also getting an error opening the main project form

The LinkMasterFields property setting has produced the error: "The object doesn't contain the Automation object "Projects."
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
Hi Brendan,

You've got a few issues going on here.

First, you should name form controls with a prefix like txtPostCode to distinguish them from fields in tables.

You don't need to say txtPostCode.Value, since that is the default property.  You can just use txtPostCode.

To filter the results of the search, you need to change the Where clause of the subform's recordsource property.  You can use the J Street SQL Tools to do this - use function ReplaceWhereClause.  If you want to see this approach in action, you can look at another download on our site - Report Selection Techniques.  The techniques are for reports, but the concepts for forms are similar.

If you want to add multiple addresses on the Picker form to the project at the same time, you'll need to load up a temp/work table with a selection checkbox.  This is more complicated to set up, but totally doable.

I don't know what the error means, but it sounds like a naming issue.

Hope this helps for now,
Armen Stein

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eyeisystemsAuthor Commented:
I managed to do it via the add checkbox option.

I added a checkbox yes/no to the master table and created a link table between the project and the property/address table.

Then simply added a dialog form where the user could type some data into a text box and a command button for passing the output to the sub form which displayed the results.

The user can then select the 'add' button and press another command button which runs a macro, running an append query adding the relevant main project ID and property ID to the link table and finally removing the add check from the original table for next use.

It's probably more complicated than other ways but it seems to work well!

Many thanks!

Brendan
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
Hi Brendan,

I'm glad you got a working solution!

Just a quick caution on your approach of adding the checkbox to the master table.  That works great if you have only one user, but it will be a problem if two different users try to attach Addresses at the same time.  They'll each see the other's checkbox values and mess things up.  If you'll never have two simultaneous users, no worries.

But if it could happen, the way to do it is with a work table (in the FE, or even better, another database in the local folder) holding Address key and checkbox.  That way, each user gets their own copy of the checkboxes, and the main table doesn't have them at all.

Cheers,
Armen Stein
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.