I have two forms (as well as underlying tables) called frmClinicInfo which contains a field called ClinicName and frmContactInfo that has a multi-select combo box called AffiliatedClinic. The two tables are linked by the Primary Key ClinicID in frmClinicInfo and Foreign Key Affilitated Clinic.Value in frmContactInfo. What I want to do is to add a button to frmClinicInfo that, when pressed, will open frmContactInfo where ClinicName = Affiliated Clinic.Value. I tried to do this using the inbuilt dialog that appears when you insert the control button (Form Operation -> Open Form -> frmContactInfo-> Open form and find specific values) but Affiliated Clinic.Value does not appear in the dialog. Is there a way to do what I'm trying to do?

Bill,

Is there any way you could copy your db,
Remove sensitive data, zip & attach?

Thanks for your help. It's attached. As you will see, I originally approached the problem with a query. However, the ContactInfo form has a link to another related form, frmCertification, which I'd like to be able to get to as well... Let me know if you need clarification with anything...

Bill,

Question-- Did you design this db from scratch or modify a MS Template?

I ask because unfortunately you've run into The Evils of Lookup Fields
and your tables need Normalization.
These db design missteps are often the case with MS Templates.
It's my opinion that MS designs Templates in a non-normalized way for two reasons.
1. The average individual who downloads a template has no db design background, is at least familiar with a spreadsheet,
and is not interested in making changes to the design,
2.Others, like you, see the potential of using Access and want to take the design and revamp it to really make a working application.
Those people will take the opportunity to learn more about developing appilications in Access.

So before we try to tackle your original issue, let's work on getting your db normalized.
I have taken the liberty of adding some tables to split some of your existing tables and also to use as Lookup Tables.
Looking at your data, I'm pretty sure that you may need to add a few more tables as well.

In your existing tables --Design View--I have typed the fields that should be eventually deleted and where that data should be stored.

I can tell you've already put a lot of work into your queries & vba,
so I would suggest that BEFORE you delete any existing fields, you should:

1. Append/Update the data to the appropriate tables using queries
2. Look at the design of EACH qry and add/edit the appropriate tables & fields
3. Do the same for Forms & Reports RecordSources & Controls
4. Remember 1:M

Here at UA, we are all about helping individuals to learn the Best Practices for db design.
We will be more than happy to help you with this process.

Once we/you get the db tables Normalized, I think we will have also solved your original issue.

Wow, I never expected such personalized support! Thanks a heap, Cynthia.
...did you attach a copy of your changes?

Bill,

I distinctly remember this site "asking" me to upload
before I was allowed to preview my post
maybe the UA janitor swept it up?

(Thought that was a better excuse than the dog ate it )

Here you go.

Thanks a lot. In your previous post you were absoutely right, I started with Access based on templates. Since then I have taken some courses, but for the most part, they have been sort of like "this week's course consists of making a small database" followed by "do everything I do", without a lot of discussion on best practices etc.

I downloaded the database. The file extension was missing... I renamed it with the .accdb extension and when opening it the following error comes up: "Unrecognized database format" followed by the path and file name. ...any ideas?

Bill,

opens on my machine
Try this one

...aarrgh! Same thing! According to what I've been able to find out, it seems that the error may be that the database became corrupted somehow, possibly in the compression or transfer? Maybe just rename the extension .xxx and upload without zipping?

C & R'd

fixed a missing reference

the ext is correct in the zip file

Can only upload zips

Now I'm really stumped. Same error when trying to open it, and file extension still missing. I turned off my virus protection thinking that maybe Norton was doing something when it was downloading but no help. This is a shame because I was really looking forward to reading your comments and seeing how it should be properly done.

Here is another copy. I did nothing other than dowload the database, then zipped it again.

Thanks Raz!

BTW, another suggestion if you still can't get the file to work, is to create a new database and import everything into the new database/file.

OK new zip file works. Now we'll see what we'll see... Thanks!

Well it seems I have a lot of work to do. I did some research on normalization and "the evils of lookup tables". Given that I was hoping to release the database in the next 2 or 3 weeks I'll have to go into overdrive! I thank you for your help!

Bill,

Happy to be able to pay it forward!

Looking forward to seeing how you split & normalize some of those tables.

A little more "homework:-- read up on Relationships & Referential Integrity
Good luck!