Combo Box creates duplicate records in a table 1

[magicscreen]

Access 2007

I want to populate a form with fields from a Combo Box.

I have a table that has a contact list with information for an apartment in an apartment house.

I have a second taple that has one record for each apartment in the house that has a problem. There are no duplicate apartments.

I have a query that joins the tblAptProblem table to the tblContact one to one.

I am using this query to populate a Combo Box and want to fill other fields on the form based on the result of the query.

The fields that I am filling on the form are bound to the tables in the join.

If I select a value from the Combo Box and fill the fields on the form in the AfterUpdate event, I get a message telling me that the update could not be performed because it would create a duplicate record in the table.

The Combo Box is bound to the key field [Apartment] in the tblApartment table.

The other fields in the query are bound to the tblContact table.

I think that the [Apartment] field that is bound to the tblApartment table is trying to add a new record which is causing the issue but I am not sure and I don't know how to determine the table causing the issue. I tried to set up Control break but could not stop the issue. When I get the message, I tried to do a Ctrl-Brk but that did not get me back into code.

Any suggestions would be be welcome.

 

[MajP]

You need to provide additional information because how you described this makes no sense. Can you post your table structure, the sql for the pertinent queries, the recordsource for your form, and pertinent code. Something like

tblAptProblem
fieldName (primary key)
fieldName2
fieldNameX

tblContactInfom
fieldName (primary Key)
fieldName2 (foreign key to tblAptProblem)

combobox row source Sql
Form Sql
After update code
Recordsource for your form

The reason your description does not make sense is you say you have a query that brings together information from apartments with problems and their contact information. Then when you pick a record, and it fills a form with information. Why would you fill a form with information that you already have? Maybe you are trying to navigate to the selected record?

 

[magicscreen]

I loaded a shortened version of my .accdb file into Mediafire. Folder Name" Access 2007. .accdb file in the folder.

Link:

http://www.mediafire.com/?49fjs6djyaeh5

 

[MajP]

http://www.4shared.com/file/J7VOBbgr/MajP_ArtsProblem.html

See if this is what you want it to do?
If so check out the link master and link child fields of the the subforms. You can actually make the main form unbound and simply navigate with the combo.

You need to explain the problem number field. It seems to be in the wrong place, but you may just need to calculate it not save it.

 

[magicscreen]

Thank you for your help in this matter.

This is exactly what I wanted.

I had some minor issue trying to place the Apartment combo box on the form with the SubfrmContact beneath it.

When I went to Formview the combo box disappeared.

It was too close to the top of the form. When I lowered it down the form worked perfectly

I have a sub form with continuous records embedded into the Problem sub form.

One of the fields in the continuous form is another combo box which used to populate all of the records in the form with the selected value. I fixed this and all works as designed.

Thank you for your help. It is rare that I fing someone as knowledgeable as you when asking for help.

 

[MajP]

On this site you will find plenty of knowledgeable people willing to help.

I find that often people make their forms too complicated, where the main form can be used to add and modify records and to navigate records. I do not use a main form for adding or editing. If I want to add/edit a record on the mainform then I make the user pop open a form for adding/editing/and deleting. This also lets me make the main form unbounded. I only use the main form to navigate and display information.
Also any control used for navigation cannot be bound. Your combo was bound to the apartment field, which means if you changed the value you actually changed the record.
You could have done away with the contact subform, like I put in. Instead you would then have the main form be bounded and use the combo to navigate to a record on the main form.

 

[magicscreen]

Thanks again for your help.

I am in a learning curve and you saved me hours of trial and error.