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 "tblConditi on" with two fields, "ID" and "strConditi on". Your table looks like this:

tblCondition
_______________ _

ID strCondition
____ _______________

1 Good
2 Fair
3 Stable

Now in your table tblMain, you should have ID, lngSerialNumber , strSpecificInfo rmation, strMoreSpecific Information, fkGenericInform ation, fkConditionID, etc...
fkConditionID is just a Number, in this case, 1, 2, or 3. Your Combobox should lookup and display the values in tblCondition.st rCondition and show those to the user, but should update the corresponding value from tblCondition.ID in tblMain.fkCondi tionID.

Of course, your form should also look to tblCondition.st rCondition 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.Conditi on. 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 tblServiceRecor ds.ServiceRecor dID, tblServiceRecor ds.DateAdmitted , tblServiceRecor ds.TimeAdmitted , tblServiceRecor ds.[Time of Triage], tblServiceRecor ds.NatureOfInju ry, tblServiceRecor ds.ServiceRende red, tblServiceRecor ds.Infection, tblServiceRecor ds.AlcoholRelat ed, tblServiceRecor ds.DrugAbuse, tblServiceRecor ds.PhysicalAbus e, tblServiceRecor ds.DismissalDat e, tblServiceRecor ds.DismissalTim e, tblServiceRecor ds.MedicalRecor dNumber AS tblServiceRecor ds_MedicalRecor dNumber, tblServiceRecor ds.TimeSeenByPh ysician, tblPatientInfor mation.MedicalR ecordNumber AS tblPatientInfor mation_MedicalR ecordNumber, tblPatientInfor mation.FirstNam e AS tblPatientInfor mation_FirstNam e, tblPatientInfor mation.LastName AS tblPatientInfor mation_LastName , tblPatientInfor mation.Address, tblPatientInfor mation.City, tblPatientInfor mation.State, tblPatientInfor mation.ZipCode, tblPatientInfor mation.Birthdat e, tblPatientInfor mation.Sex, tblAdmittingNur se.FirstName AS tblAdmittingNur se_FirstName, tblAdmittingNur se.LastName AS tblAdmittingNur se_LastName, tblPhysicianAtt ending.FirstNam e AS tblPhysicianAtt ending_FirstNam e, tblPhysicianAtt ending.LastName AS tblPhysicianAtt ending_LastName , tblFamilyPhysic ian.FirstName AS tblFamilyPhysic ian_FirstName, tblFamilyPhysic ian.LastName AS tblFamilyPhysic ian_LastName, tblCondition.st rCondition, tblDisposition. Disposition, tblInstructed.I nstructed, tblModeOfArriva l.ModeOfArrival , tblTypeOfInjury .TypeOfInjury, tblServiceRecor ds.AdmittingNur seID, tblServiceRecor ds.AttendingPhy sicianID, tblServiceRecor ds.FamilyPhysic ianID, tblServiceRecor ds.ConditionID, tblServiceRecor ds.DispositionI D, tblServiceRecor ds.InstructedID , tblServiceRecor ds.ModeOfArriva lID, tblServiceRecor ds.TypeOfInjury ID, tblServiceRecor ds.DeadOnArriva l, tblServiceRecor ds.SurgeryER, tblServiceRecor ds.LocalStitche s, tblServiceRecor ds.Infection, tblServiceRecor ds.AlcoholRelat ed, tblServiceRecor ds.DrugAbuse, tblServiceRecor ds.PhysicalAbus e, tblServiceRecor ds.Closed, tblServiceRecor ds.ExamTime, tblServiceRecor ds.[Room Number], tblServiceRecor ds.[Account Number], tblPatientInfor mation.MedicalR ecordNumber
FROM tblPatientInfor mation LEFT JOIN (tblTypeOfInjur y RIGHT JOIN (tblPhysicianAt tending RIGHT JOIN (tblModeOfArriv al RIGHT JOIN (tblInstructed RIGHT JOIN (tblFamilyPhysi cian RIGHT JOIN (tblDisposition RIGHT JOIN (tblCondition RIGHT JOIN (tblAdmittingNu rse RIGHT JOIN tblServiceRecor ds ON tblAdmittingNur se.AdmittingNur seID = tblServiceRecor ds.AdmittingNur seID) ON tblCondition.Co nditionID = tblServiceRecor ds.ConditionID) ON tblDisposition. DispositionID = tblServiceRecor ds.DispositionI D) ON tblFamilyPhysic ian.FamilyPhysi cianID = tblServiceRecor ds.FamilyPhysic ianID) ON tblInstructed.I nstructedID = tblServiceRecor ds.InstructedID ) ON tblModeOfArriva l.ModeOfArrival ID = tblServiceRecor ds.ModeOfArriva lID) ON tblPhysicianAtt ending.Attendin gPhysicianID = tblServiceRecor ds.AttendingPhy sicianID) ON tblTypeOfInjury .TypeOfInjuryID = tblServiceRecor ds.TypeOfInjury ID) ON tblPatientInfor mation.MedicalR ecordNumber = tblServiceRecor ds.MedicalRecor dNumber;

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.i nstructed 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 - AdmittingNurseI D, FirstName and LastName. Here is the code I have in the Row Source for AdmittingNurse:

SELECT tbladmittingnur se.firstname & " " & tbladmittingnur se.lastname FROM tbladmittingnur se;

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 tblServiceRecor ds.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 -