Trying to get pk into fk field

[magster06]

Hello all,

I know this subject has been asked before, but I seem to be having a hard time getting it to work.

I have the following:

tblComplaint

pkComplaintID/autonumber
fkComplainantID/number

tblComplainant

pkComplainantID/autonumber

I have a main form with tabs. On one tab, I have a subform.

From what I have read, in order for me to get the pkComplainantID from the subform control to the parent form control I would have to reference the control on the subform to the parent form control to populate the control on the parent form. lol, I hope this is making sense.

This is what I am using:

Forms![Citizen Initial Call]![txtComplainantID] = Forms![sfComplainant].Form![ComplainantID].Value

I am not sure where to place the code above. I have tried the afterupdate of the subform, as well as the beforeupdate events. Nothing has worked so far.

Any help would be appreciated.

 

[jzwp22]

How many complaintants are related to the complaint, one or many? From the table structure you show, it appears that a complaint only has one complaintant


So if one complaintant is related to a complaint, the main form should be based on the complaint table and you would use a combo box bound to the complaintant table to populate the foreign key field.

 

[magster06]

So if one complaintant is related to a complaint, the main form should be based on the complaint table and you would use a combo box bound to the complaintant table to populate the foreign key field.

Yes, I have the main form based on the complaint table.

jz, I know this is a stupid question, but you mean to populate the combo with the names of the complainant? or the autonumber from the ComplainantID?

So, there is no way to just pull the value (autonumber) from the subform textbox control and place the value into the textbox on the main form before I save it to the table?


Ok, I created the combo box and linked it to the subform. I also (from the combo prompts) linked the ComplainantID from the subform to store in the fk of the Complaint table.

I tested it and it did not work. The value from the Complainant table did not store in the Complaint table. Not sure why.

Any ideas?


UPDATE: ok, for some reason the combo is working now, but I would still like to be able to use a textbox control on the main form that is auto populated by the subform and then this will be saved in the Complaint table.

I would prefer that the user not have to select what ID to use. I can see where the user in a rush could select the wrong ID number.

 

[magster06]

jz, your solution worked great, but how do I limit what the user can select in the combo box so that they do not select the wrong ComplainantID?

 

[jzwp22]

If you look at the row source of the combo box, it is just a query. You can add any necessary criteria to that query to accomplish the filtering. I assume that you need to limit the list in the combo box to certain choices. Even then, a user can make a mistake and select the wrong one. How does your current business process guarantee that the wrong complaintant is not chosen/listed?

 

[magster06]

How does your current business process guarantee that the wrong complaintant is not chosen/listed?

Our PSU bureau currently uses excel to keep track of complaints and complainants. Each complaint is assigned a case number, which is then assigned to the complainant.

So far, they have been lucky and no one has entered in the wrong case number to the complainant.

With the combo box, I feel there is more room for error on the users part to select the wrong value.

Hence the reason for me wanting to use a text box (on the main form) that self populates with the primary key from the complainant subform text box.

 

[jzwp22]

If you use a text box, you will have to be able to search your existing complainants to find the closest match and then you will also have to give some feedback to your user to have them confirm what you found. Once confirmed, then you can go get the ID a push it to the control on the form. This will take some coding.

 

[magster06]

jz, I apologize. I am terrible at explaining what I am trying to accomplish.

The form is data entry only. The form is when a complaint is received.

I noticed that even though the tables are linked in a relationship, that when information is entered in the Complainant table (via the subform), the pk is not automatically entered in the fk of the Complaint table.

I need the pk value to be entered into the fk field of the main table so that the case number will follow the complainant.

 

[jzwp22]

Since based on your table structure, there can be only one complainant per complaint trying to enter the complainant information via a subform is not the appropriate way to handle it. As I indicated earlier, subforms are used for the many side of a one-to-many relationship i.e. one complaint with many complainants. You have just split up the complaint record over several tabs but since the complainant info must be entered into the complainant table in addition to referencing it in the complaint table, it will take a different approach as I described previously especially if you do not want to use a combo box.

 

[magster06]

Since based on your table structure, there can be only one complainant per complaint

jz, there is the possibility that there could be more than one complainant per complaint, but the probability of that happening is very low.

Should I have the main table (Complaint) setup with another pk (ComplainantID) so that there will be a one (Complaint) to many (Complainant) relationship?

Would this make things easier?

 

[jzwp22]

Should I have the main table (Complaint) setup with another pk (ComplainantID) so that there will be a one (Complaint) to many (Complainant) relationship?

Would this make things easier?

No it would not make things easier, and it would violate normalization rules.

If there can be many complainants associated with a complaint (one-to-many) and a complainant can be associated with many compliants (one-to-many) that describes a many-to-many relationship which is handled with a junction table.

tblComplaint
-pkCompliantID primary key, autonumber
-CaseNumber
-CallTaker
-CallInDate
-CallInTime
etc.

tblComplainants
-pkComplainantID primary key, autonumber
-FirstName
-MI
-LastName
etc.
(BTW it is best not to have spaces or special characters in your table or field names)


tblComplaintComplainants
-pkComplaintComplainantID primary key, autonumber
-fkComplaintID foreign key to tblComplaints
-fkComplainantID foreign key to tblComplainants

For each complainant for a particular complaint there would be a corresponding record in the junction table. So if 5 people were associated with a complaint, there would be 5 records in the junction table (the fkComplaintID would be the same for all 5 records but the fkComplainantID would be different)

 

[magster06]

a complainant can be associated with many compliants

I take it to mean that if one person should file many complaints?

I have attached the revised tables to what you have advised. Let me know if they look correct to you.

Also, I tried to enter the data from my form and the two tables populated as they should, but the junction table was blank.

 

[jzwp22]

The table structure looks OK. As to the form, you have to use a subform based on the junction table in order to add records to the junction table. Again, typically you would use a combo box in the subform to populate the complainants for each complaint. If you do not want to use a combo box, you will have to explain the process you want to follow for entering complainants and from there code will have to be developed.

 

[magster06]

jz, sorry for the delayed response, been out of town. I'll work with your idea of the combo box when I get back to the office on Tuesday!

I still have the example db that you made up for the junction box and I will try to incorporate this into my form.