Using a subform to update date in a record

[jabberw0ck]

Edit: I meant "data", not "date" in the subject

Office 2003, Windows XP

I have a main table, tblDistrictSub, as well as another table, tblLetters. These share a 1 to many relationship. For EVERY entry in tblDistrictSub, there is an entry in tblLetters that shows what kind of letter (and its status) is being worked on for that record in tblDistrictSub. The tblLetters table structure, as well as the tables relating to it, is shown below:

tblLetters
pkLetterID
fkDistrictIncidentNum (this is the link to my main table, tblDistrictSub)
fkLetterTypeID
fkStateTypeID
txtTrackingID
dteDateSent
mmoDescriptionInLetter

tblLetterTypes
pkLetterTypeID
txtLetterType (There are 8 different letter types)

tblStateTypes
pkStateTypeID
txtStateType (There are 5 different states a letter can be in)

On my main form, frmDistrictSub, I've added a subform, frmLetters, that displays the information for the letter relating to the record that is currently displayed in frmDistrictSub. The query I'm using to populate frm Letters is below:

SELECT tLetters.fkLetterTypeID, tLetters.fkStateTypeID, tLetters.txtTrackingID, tLetters.dteDateSent, tLetters.mmoDescriptionInLetter, tLetterTypes.txtLetterType, tStateTypes.txtStateType, tLetters.fkDistrictIncidentNum
FROM tStateTypes INNER JOIN (tLetterTypes INNER JOIN tLetters ON tLetterTypes.pkLetterTypeID = tLetters.fkLetterTypeID) ON tStateTypes.pkStateTypeID = tLetters.fkStateTypeID
WHERE (((tLetters.fkDistrictIncidentNum)=[Forms]![fDistrictSub]![DistrictIncidentNum]));


Here is my question: How can I construct my frmLetters in such a way as to allow the enduser to modify the LetterTypeID and StateTypeID for the record currently being displayed in frmDistrictSub? My problem lies in the fact that the data I want to update in tblLetters, namely the fkLetterTypeID and fkStateTypeID, is not the data that I want displayed on the page. The end user is not going to remember what each ID stands for, so I need to actually DISPLAY the txtLetterType and txtStateType that correspond to their respective IDs. How do I create a combobox that will let me see the txt values for the IDs, but will assign the ID value (NOT the txt value) to the record entry in tblLetters?

I hope that made sense. Any help is greatly appreciated.

 

[Guus2005]

plz attach what you have so far if that's possible. Your problem is easier to solve using the actual database. Strip everything that isn't related to the problem and compact, zippit.

 

[jabberw0ck]

Sorry, but there is no way I can delete out enough to make the file size below 393 KB. Instead I've uploaded the zipped front and back end to the link below.

http://www.box.net/shared/y2luvhu5n2

I stripped out almost all the data so it should be a little easier to work with.

 

[Guus2005]

I am sorry but you have to strip your database down to the problem for me to help you. It takes to much time to track it down to the problem you describe.

 

[jabberw0ck]

Nevermind...I patched something together that will suffice.