Updating table from a form

[Light1]

I am working in Access 2003. I have a form that pulls information from a couple of tables. When the user chooses a selection from the dropdown menu of a combo box, it is putting that information back in to the table as a duplicate of a record that is already there. (Example - a field has 3 choices - good, fair, stable - after using it a couple of times and choosing good, would now have good in the drop down menu several times). I only want the results from that combo box choice to update to a record.
The record source for the form shows a SELECT statement of all the fields. I have the row source of the combo box pulling from the table with a SELECT statement. Since I know the Control Source is the field that should be updating back to a table, I have tried setting it several different way, but it seems no matter how I set it, it still updates the original table. I only want it to update to a record.
I don't know if this is explained well enough or enough information included, but if someone can point me at what I should be looking at to fix this, I would appreciate it.

[Minion]

I am working in Access 2003. I have a form that pulls information from a couple of tables. When the user chooses a selection from the dropdown menu of a combo box, it is putting that information back in to the table as a duplicate of a record that is already there. (Example - a field has 3 choices - good, fair, stable - after using it a couple of times and choosing good, would now have good in the drop down menu several times). I only want the results from that combo box choice to update to a record.
The record source for the form shows a SELECT statement of all the fields. I have the row source of the combo box pulling from the table with a SELECT statement. Since I know the Control Source is the field that should be updating back to a table, I have tried setting it several different way, but it seems no matter how I set it, it still updates the original table. I only want it to update to a record.
I don't know if this is explained well enough or enough information included, but if someone can point me at what I should be looking at to fix this, I would appreciate it.

Light, I hate to have to say this, but this is a jumbled mess that makes little to no sense. So, let's try and get some facts ironed out so we can try and track your problem.

1. You said the form's record source is a SELECT statement that pulls from your various tables. Ok, well you'll want to post that so we can see where you're getting your information.

2. You mentioned that when a value is selected from the combo box it appears multiple times in the box. Well two parts to this. First, where is the combo box getting it's information. Post the statement. Second, to what table (if any) is the combo box bound? Luckily this part may be fixed by adding one or two words, but we need to know what you using now.

3. What is the overall objective of what you are trying to do? We can't help you get there unless we know where you're headed.

- Minion -

[haakondahl]

I think you are describing two separate problems. For the combobox, try simply changing your SELECT statement to a SELECT DISTINCT (everything else remains the same. I don't understand the difference between updating a table and updating a record.

[haakondahl]

Without looking at the structure, here is what I think:
You should have a main table with uniquely identifying information, and a bunch of foreign keys from other tables of repetitive information. Say that you have a small table "tblCondition" with two fields, "ID" and "strCondition". Your table looks like this:

tblCondition
________________

ID strCondition
____ _______________

1 Good
2 Fair
3 Stable

Now in your table tblMain, you should have ID, lngSerialNumber, strSpecificInformation, strMoreSpecificInformation, fkGenericInformation, fkConditionID, etc...
fkConditionID is just a Number, in this case, 1, 2, or 3. Your Combobox should lookup and display the values in tblCondition.strCondition and show those to the user, but should update the corresponding value from tblCondition.ID in tblMain.fkConditionID.

Of course, your form should also look to tblCondition.strCondition for what to display in records in the form.

OR, if you can't normalize the tables, just keep "Condition" (or whatever it is actually called) in tblMain (or whatever it is called), but have the combobox draw its info from a SELECT DISTINCT on tblMain.Condition. But this is asking for trouble.

OTOH, I'm no expert.

[Light1]

Sorry, I am completely new to Access other than classes about 6 or 7 years ago. This is the first project for me and I really did not know how much was needed to report this problem.

Overall Objective: I work for a small hospital. Our ER department is still manually writing down their information in a log book and they want to automate it. They do have a registration desk that keeps track of patient information and that is already automated. This is just for their personal department to track. They just want to keep general records of how many people they see, what type of problems are coming in to them and if any of the patients seen are repeated withing 72 hours. I created the form with the form wizard. There are a couple of tables that need to be updated when new information is used, and a couple that simply need to be used without updating. The overall table that needs to track data from all the table is a ServiceRecord table.

Here is the SELECT statement from my form:

SELECT tblServiceRecords.ServiceRecordID, tblServiceRecords.DateAdmitted, tblServiceRecords.TimeAdmitted, tblServiceRecords.[Time of Triage], tblServiceRecords.NatureOfInjury, tblServiceRecords.ServiceRendered, tblServiceRecords.Infection, tblServiceRecords.AlcoholRelated, tblServiceRecords.DrugAbuse, tblServiceRecords.PhysicalAbuse, tblServiceRecords.DismissalDate, tblServiceRecords.DismissalTime, tblServiceRecords.MedicalRecordNumber AS tblServiceRecords_MedicalRecordNumber, tblServiceRecords.TimeSeenByPhysician, tblPatientInformation.MedicalRecordNumber AS tblPatientInformation_MedicalRecordNumber, tblPatientInformation.FirstName AS tblPatientInformation_FirstName, tblPatientInformation.LastName AS tblPatientInformation_LastName, tblPatientInformation.Address, tblPatientInformation.City, tblPatientInformation.State, tblPatientInformation.ZipCode, tblPatientInformation.Birthdate, tblPatientInformation.Sex, tblAdmittingNurse.FirstName AS tblAdmittingNurse_FirstName, tblAdmittingNurse.LastName AS tblAdmittingNurse_LastName, tblPhysicianAttending.FirstName AS tblPhysicianAttending_FirstName, tblPhysicianAttending.LastName AS tblPhysicianAttending_LastName, tblFamilyPhysician.FirstName AS tblFamilyPhysician_FirstName, tblFamilyPhysician.LastName AS tblFamilyPhysician_LastName, tblCondition.strCondition, tblDisposition.Disposition, tblInstructed.Instructed, tblModeOfArrival.ModeOfArrival, tblTypeOfInjury.TypeOfInjury, tblServiceRecords.AdmittingNurseID, tblServiceRecords.AttendingPhysicianID, tblServiceRecords.FamilyPhysicianID, tblServiceRecords.ConditionID, tblServiceRecords.DispositionID, tblServiceRecords.InstructedID, tblServiceRecords.ModeOfArrivalID, tblServiceRecords.TypeOfInjuryID, tblServiceRecords.DeadOnArrival, tblServiceRecords.SurgeryER, tblServiceRecords.LocalStitches, tblServiceRecords.Infection, tblServiceRecords.AlcoholRelated, tblServiceRecords.DrugAbuse, tblServiceRecords.PhysicalAbuse, tblServiceRecords.Closed, tblServiceRecords.ExamTime, tblServiceRecords.[Room Number], tblServiceRecords.[Account Number], tblPatientInformation.MedicalRecordNumber
FROM tblPatientInformation LEFT JOIN (tblTypeOfInjury RIGHT JOIN (tblPhysicianAttending RIGHT JOIN (tblModeOfArrival RIGHT JOIN (tblInstructed RIGHT JOIN (tblFamilyPhysician RIGHT JOIN (tblDisposition RIGHT JOIN (tblCondition RIGHT JOIN (tblAdmittingNurse RIGHT JOIN tblServiceRecords ON tblAdmittingNurse.AdmittingNurseID = tblServiceRecords.AdmittingNurseID) ON tblCondition.ConditionID = tblServiceRecords.ConditionID) ON tblDisposition.DispositionID = tblServiceRecords.DispositionID) ON tblFamilyPhysician.FamilyPhysicianID = tblServiceRecords.FamilyPhysicianID) ON tblInstructed.InstructedID = tblServiceRecords.InstructedID) ON tblModeOfArrival.ModeOfArrivalID = tblServiceRecords.ModeOfArrivalID) ON tblPhysicianAttending.AttendingPhysicianID = tblServiceRecords.AttendingPhysicianID) ON tblTypeOfInjury.TypeOfInjuryID = tblServiceRecords.TypeOfInjuryID) ON tblPatientInformation.MedicalRecordNumber = tblServiceRecords.MedicalRecordNumber;

The easiest combo box I have pulls from an Instructed table (2 fields - InstructedID and Instructed). It should only have 2 records - Yes, or No. Here is the code I have in the Row Source:

SELECT tblInstructed.instructed FROM tblinstructed;

If I put in 'Instructed' in the Control Source, it updates both the Instructed table and the ServiceRecords table. I only want it to update the ServiceRecords table showing what answer the user chose - not keep putting the choice back in the Instructed table. I have tried several different ways and am getting no where.

I also have a combo box that pulls from a table AdmittingNurse. This table has 3 fields - AdmittingNurseID, FirstName and LastName. Here is the code I have in the Row Source for AdmittingNurse:

SELECT tbladmittingnurse.firstname & " " & tbladmittingnurse.lastname FROM tbladmittingnurse;

I want this combo box to do the same as the Intructed one - just update to the ServiceRecord with their answer.

I have a lot to learn, so if you know of books, etc. that would be of good help, please feel free to suggest it. I do like this site. I have found help here already.

Just a note to the other reply I had. If I try and put in tblServiceRecords.strCondition as the Control Source, I get the error 'The value you entered isn't valid for this field. For Example, you may have entered text in a numeric field or a number that is larger than the FieldSize settings permit. I did check my tables and did not find what was causing this error.

Thanks for any help you can provide.

Light, I hate to have to say this, but this is a jumbled mess that makes little to no sense. So, let's try and get some facts ironed out so we can try and track your problem.

1. You said the form's record source is a SELECT statement that pulls from your various tables. Ok, well you'll want to post that so we can see where you're getting your information.

2. You mentioned that when a value is selected from the combo box it appears multiple times in the box. Well two parts to this. First, where is the combo box getting it's information. Post the statement. Second, to what table (if any) is the combo box bound? Luckily this part may be fixed by adding one or two words, but we need to know what you using now.

3. What is the overall objective of what you are trying to do? We can't help you get there unless we know where you're headed.

- Minion -