MS Access Forms, subforms, comboboxes, queries

Question

Hello, I am trying to make a form that will search my contacts table by name, and then populate all of the records fields, ie phone, email etc, into textboxes that can be edited if need be. I also have a subform that when a name is selected it will grab that contacts employerID and then with that I want to grab data from my BuisnessContact table because I don't keep address with each contact, just in the buisnesscontact table. I have another table that is for Contacts address, but that is only used if a contact has a different mailing address the the company he/she works for. I had the address portions (the buisness address, and then if possible a Other address in a tab control but I had to remove it bc it was causing to many problems. I have attached pictures below of my tables, forms and queries I am using with this form. I have a couple of questions pertaining to the form so I'm just going to number them.

1. When I go to click on the BuisnessAddressSubForm I get an error(displayed in pic) saying changes made to the table were not successful because they would create duplicate keys. Now I am assuming that what is happening is that Access is trying to append a record to Contacts Table with the data that is displayed on the form currently. This happens if I refresh as well. How can I stop this? and is it stoppable.
2. My BuisnessAddressSubform does not update like I want it too. After selecting the name at the top of the form, what should happen is the BuisnessID text box in the sub form should be updated with the correct BuisnessID once the Search combo box is changed (using the change event). The Buisness ID changes but none of the other fields do. I tried having the BuisnessAddress subform record source set to a select query that would select the appropiate fields where the BuisnessID (buisnessContactsTable) matches the employerID (contactstable). This did not work, I also tried to build an event to get the fields I need once the BuisnessID in the subform is changed, bc this is the only control in the subform  that actually does change. I would also like this to be locked, meaning I don't want people to be able to edit the data
3. For a contacts other address (different from company address) I want the subform to work similarly to the BuisnessAddressForm, but instead of BuisnessID I would use employeeID. I want this to be editable data, unlike the other
4. After I got both of these subforms working, if I put them into a tab control will that in any way make the subform not work, I know tough to answer but in principal. Just asking bc of one I want to be able to edit one and the other would be just to show to the person searching
5. The button run Query is for an update query which just simply updates the contact table. When I click this several windows pop up, one for you are about to run an update query and then one for you are about change x number of records. I there a way to have a custom pop window, instead of the two default ones. I want it to say "Thanks for doing it correctly"

I am new to Access and am trying to build a database for my Manufacturing Company. I am sorry if I messed up the community rules, and sorry for Writing so sloppy on some of my attached pics. Please help me out!!

Answer 1

Without knowing your data structure it's hard to say, but based on the error I'd suspect that you don't have your Subform Control's Master and Child link set correct.

The Master field should be the Primary Key value of the "Parent" record (the main form record). The Child field should be the field in your "Child" table (your subform's recordsource) where that value would be stored.

Regarding your Search issue: Again, without knowing much about your database it's hard to say. Can you show the code you're using to search with that box?

You should not need to "run a query" to update anything in Access, especially for simple matters like this. If you use a bound form, Access will automatically save your data edits for you.

With that said - it's very important that you ensure your tables are correctly setup before you get too far along in your database work. Can you show us your table schema? You can do that by taking a screenshot of the Relationships window.

Answer 2

OK, First, to create a search combo where you can select a name and retrieve that record you can use the combobox wizard (third option). this will walk you through the process.

Second, if the subform is linked to the Main form on EmployerID, then the subform will display the Employer information what whatever record is active.

Third, the error message about duplicate values is because you did not create the search correctly. it looks like you are not retrieving but copying a record and that's trying to create a duplicate. Without seeing the code you are using behind the search that's just an educated guess. I would scrap what you have done and use the combobox wizard.

This also bears on the problem marked 2).

An embedded subform, need to be linked to a field in the mainform record. If it is, then the corresponding record will be shown. So i would check your links.

Putting subforms on a tab page won't affect the way they work.

For question 5) we really need to see the code you are running. But you can use DoCmd.SetWarnings False to suppress the messages and then use a MsgBox to display your own message. Be sure to use a SetWarnings True to turn them back on

Answer 3

I set up the link properly now, but It still doesn't update when I search.

This is the code for the search combo box at the top of the form

Private Sub Namecbo_Change()

Me!EmployeeIDtxt = Me!Namecbo.Column(1)

Me!EmployerIDtxt = Me!Namecbo.Column(2)

Me!FirstNametxt = Me!Namecbo.Column(3)

Me!LastNametxt = Me!Namecbo.Column(4)

Me!PrimaryEmailtxt = Me!Namecbo.Column(5)

Me!SecondaryEmailtxt = Me!Namecbo.Column(6)

Me!PrimaryPhonetxt = Me!Namecbo.Column(7)

Me!SecondaryPhonetxt = Me!Namecbo.Column(8)

Me!TertiaryPhonetxt = Me!Namecbo.Column(9)

Me!BuisnessAddressSubForm1.Form.BuisnessIDcbo = Me!Namecbo.Column(2)

Inside the subform this is the code for the BuisnessID combo box

Private Sub BuisnessIDcbo_AfterUpdate()

Me!Addresstxt = BuisnessIDcbo.Column(1)

Me!AptNumtxt = BuisnessIDcbo.Column(2)

Me!Towntxt = BuisnessIDcbo.Column(3)

Me!Statetxt = BuisnessIDcbo.Column(4)

Me!Zipcodetxt = BuisnessIDcbo.Column(5)

End Sub

To clarify by a form being bound, you mean if the form has a record source? Bc I have the record source set to Contacts (the table), before I put the update query, I had the save record button, but it would append the record present in the form instead of updating the existing record.

Hey Scott thanks for replying to me, I meant to attach this with the original post but I must have forgotten.

Answer 4

I think that you might be in danger of digging yourself into a hole by overcomplicating what is in essence a relatively simple model.  Let's go back to first principles.

BusinessContacts is a sub-type of Contacts in that a business contact shares all attributes of a contact, but not those of other sub-types.  A business contact might also be a personal contact of course.  A type hierarchy is modelled by a one-to-one relationship type, so the Contacts table would have a primary key ContactID say, which can be an autonumber for convenience.  Other non-key columns in the table would represent attributes common to all contacts.  In the BusininessContacts table the primary key ContactID would not be an autonumber, but a straightforward long integer data type.  It would also be a foreign key referencing the primary key of Contacts.

Each table modelling a sub-type can have its own address columns.  This might, at first glance appear to contradict the principle stated above that a sub-types attributes are not shared by other sub-types.  This is not the case.  The fact that the address attributes in each sub-type table have the same names does not mean that they are the same attributes.  They are simply same-named attributes.  A CityID column in each table would represent the city as an attribute of that sub-type, not of all other sub-types.  A contact is very likely to have a business address in one city, and a personal address elsewhere.

The user interface can be a Contacts form, with subforms for each sub-type, linked on ContactID in each case.  Each subform can be on a separate page of a tab control with no problem.  A common interface in similar situations is to have the primary data from the Contacts table in the detail section of the parent form, with secondary data from the same table on page 1 of a tab control.  The other pages of the tab control will each have a subform based on one of the referencing tables, which might be subtypes or other tables referencing Contacts in a one-to-many relationship type.

When it comes to attributes like email addresses and phone numbers, having multiple columns in a table for primary, secondary instances of each attribute is bad relational database design.  It 'encodes data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

The correct design would be to have related Emails and Phones tables in which each email address or phone number is represented by a value in a single column.  So a contact with two email addresses would have two rows in the related table.  In the related table it is also necessary to differentiate business, personal addresses etc, so an Emails table would be along these lines:

Emails

….ContactID  (FK)

….ContactCategoryID  (FK)

….EmailAddress

This table is 'all key', i.e. its primary key is a composite one made up of all three columns.  The Phones table would be similarly structured.  You can if you wish add an IsPrimary column of Boolean (Yes/No) data type to indicate which is the primary address or phone, or you might want to number the addresses or phones per contact 1,2 etc to indicate their priority.  ContactCategoryID would reference the primary key of a ContactCategories table in which there would be one row per category of contact.  In relational database terms the table is this modelling a binary many-to-many relationship type by resolving bit into two unary (one-to-many) relationship types.

A phones table would probably also have, in addition to the ContactID foreign key,  a PhoneTypeID column as a foreign key referencing a PhoneTypes table with rows for landline, mobile (cell-phone) types, and any others you might think of.

For data entry there would be, within each sub-type subform further subforms based on queries which, for example would restrict the email addresses or phone numbers on the ContactCategoryID value relevant tom that subform.  The DefaultValue property of a hidden ContactCategoryID txt box control in the subform would be the relevant value for that subform.

With a logically structured model like this you should have little trouble putting together the form, its subforms and their subforms.  In each case the LinkMasterFields and LinkChildFields properties would be ContactID.

For searching via an unbound combo box in the form header you can navigate to the relevant contact with code in its AfterUpdate event procedure, which first finds the contact in the form's RecordsetClone, and then synchronizes the form's Bookmark with the recordset's bookmark.  You'll find a simple example in FindRecord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, in addition to simply going to a contact record, there are illustrations of how to navigate or filter a form in other ways, including searching on the basis of a value in a subform.

In the same OneDrive folder is a TypeHierarchy demo, which provides a simple illustration of a model for this, using a hypothetical academic institution as its example, along with one type of interface for such a model.

As regards executing your queries, calling the Execute method of the current database object will avoid the prompts.  This also accepts a dbFailOnError option, which will enable you to trap and handle any error which might occur in the execution of the query.  To indicate successful execution of the query would merely be a matter of calling the MsgBox function if no error has been raised.

Answer 5

So you want me to change my tables, So I would keep BuisnessContacts, Contacts, but add Phone number and email table, and remove it from the two existing tables. Is this to save space? or good design practices. And I want my search to be editable, meaning I want to search for a contact, and once I found them and their data has populated the textboxes and be able to change the data if necessary