Is the form RecordSource a query that joins tables?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Yes, what I've done is link two tables - Program Information and Contact Information - where the contact information has multiple records for each Program.
Instead of joining tables, probably need to build form/subform arrangement.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
That may not work in this context; what I've done is made records clickable on the Program List table to open a form that displays several values from the Program List table along with a Contact Details subform.
There is no way to have the Program List table stop displaying duplicate values based on 'same' fields displayed in the form?
Not with that query and not if you want the records editable. If you don't want to allow editing, then make the query a GROUP BY or DISTINCT.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Okay, so I've created the Program List display form through a query... when I try to make the records clickable - open another, windowed form, with more information - I get an error. Not sure what I'm doing wrong!
Basically, all of the fields I want displayed in the form are included in the query design - once the record is clicked on I want a separate form to be displayed that contains information from the table that includes the original information (Program List table) and another table (Contact Details table). The following are the error messages and query design.
I'm really learning a lot from this process, so thank you June7.
Don't understand what you are trying to do with that query. Why are you joining these tables in query? The only field from ContactDetails is ProgramID and the data is already in ProgramDetails.
I don't use macros, only VBA. I really don't know why that errors. If you want to provide db for analysis, follow instructions at bottom of my post.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Sorry for the confusion; basically when you look at the db, the Program List form has all the functionality I need - essentially a search function and the ability to click on records to open a profile form (the Profile Page form). However, duplicates are created for the Program List record entries for some reason (even though no duplicates are in the Program List table). I think it may be because some of the program records have the the same/repeated contact name/information.
Last edited by June7; 03-02-2015 at 01:32 AM.
Yes, that would cause 'duplicate'.
Didn't answer my question. But now I see in the db the query pulls in ContactName field.
Clean up the duplicates from ContactDetails. Why does Vicky Schreiber have two records?
If each program can have multiple contacts, then that is what will be reflected when you join these tables. This is to be expected in 1-to-many or many-to-many relationship.
Is this real data? Should not post databases with real data.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Right, so the issue appears to be that for records with more than one contact, a duplicate record entry is shown on the Program List form... Is there a way to fix this so that only one record entry is displayed in the form, per program record?
Remove ContactDetails from the query.
Again, why are you including this table and don't show any data from it on the form?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I assumed I needed to provide the relationship between Program ID's in the query in order for the separate form to display correctly - that when you clicked the record entry it would show the appropriate information from a separate table - but I was wrong! I can just use the Where Condition to have the appropriate ID's connect to display the right information!
Adding DISTINCT to the SQL as explained in this youtube video solved my problem.
Thanks a bunch for your help!