Adding a combo box list item to a table

[PRodgers4284]

I have created two combo boxs which are populated by value from two tables and i want to add a command button to be able to add these records to another table, can anyone help?

 

[Pat Hartman]

Are you sure you don't want to use a bound form? It sounds like you are creating the junction table of a many-many relationship. Typically, this is done as a subform from one side or the other, whichever makes more sense. Sometimes, you will want two forms so records can be added in either direction. For example, on a student form, you would have a subform with classes and you could add the student to a new class with this subform. You could also have a form for a class with a subform that showed students in that class which could also be used to add a student to a class. When done this way, the main form controls one of the FK fields so you only need a single combo in the sub form. In the first example, the combo would list classes and on the second example, the combo would list students.

 

[PRodgers4284]

Are you sure you don't want to use a bound form? It sounds like you are creating the junction table of a many-many relationship. Typically, this is done as a subform from one side or the other, whichever makes more sense. Sometimes, you will want two forms so records can be added in either direction. For example, on a student form, you would have a subform with classes and you could add the student to a new class with this subform. You could also have a form for a class with a subform that showed students in that class which could also be used to add a student to a class. When done this way, the main form controls one of the FK fields so you only need a single combo in the sub form. In the first example, the combo would list classes and on the second example, the combo would list students.

Hey thanks for that, all i need to do is create a button to adds the values of the populated combo boxes to another table. I have two tables created, one that lists names of hospitals and the other that lists the name of wards. I have created a relationship between the two tables so that i can link the various hospitals to their wards. The combo boxes are just populated using the values from these table, i have set the form up so that when a user selects a particular hospital only the wards within that hospital are populated in the ward combo box. I now want to store whatever the user selects from the two combo boxes into another table eg patient.

 

[jal]

You haven't clarified what columns are being inserted. Let's suppose you are copying from table Wards1 to table Wards2, for a particular hospitalID

CurrentDb.Execute "INSERT INTO Wards2 SELECT * FROM Wards1 WHERE Wards1.HospitalID = " & comboxHospitalId

Well add quotes if hospID is a string:

CurrentDb.Execute "INSERT INTO Wards2 SELECT * FROM Wards1 WHERE Wards1.HospitalID = '" & comboxHospitalId & "'"

Or if you just want two particular columns:

CurrentDb.Execute "INSERT INTO Wards2 (column1, column2) SELECT column1, column2 FROM Wards1 WHERE Wards1.HospitalID = " & comboxHospitalId


Or let's say you only wanted to insert the selected ward into a dest table.

Dim qDef as DAO.queryDef
Set qDef = CurrentDb.CreateQueryDef("")
qDef.SqL = "INSERT INTO DestTable (ward, hospitalID) VALUES (@Ward, @HospitalID)"
qDef.Parameters("@HospitalID").value = cboHosp
qDef.Parameters("@Ward").value = cboWard
qDef.Execute

if the hospital ID# is the second column of the cbo, you may need to do:

cboHosp.Column(1)


I like params because you don't have to add quotes.

 

[PRodgers4284]

You haven't clarified what columns are being inserted. Let's suppose you are copying from table Wards1 to table Wards2, for a particular hospitalID

CurrentDb.Execute "INSERT INTO Wards2 SELECT * FROM Wards1 WHERE Wards1.HospitalID = " & comboxHospitalId

Well add quotes if hospID is a string:

CurrentDb.Execute "INSERT INTO Wards2 SELECT * FROM Wards1 WHERE Wards1.HospitalID = '" & comboxHospitalId & "'"

Or if you just want two particular columns:

CurrentDb.Execute "INSERT INTO Wards2 (column1, column2) SELECT column1, column2 FROM Wards1 WHERE Wards1.HospitalID = " & comboxHospitalId


Or let's say you only wanted to insert the selected ward into a dest table.

Dim qDef as DAO.queryDef
Set qDef = CurrentDb.CreateQueryDef("")
qDef.SqL = "INSERT INTO DestTable (ward, hospitalID) VALUES (@Ward, @HospitalID)"
qDef.Parameters("@HospitalID").value = cboHosp
qDef.Parameters("@Ward").value = cboWard
qDef.Execute

if the hospital ID# is the second column of the cbo, you may need to do:

cboHosp.Column(1)


I like params because you don't have to add quotes.

Thanks for your help, i am inserting values from two combo boxs with the following names for each:

"Hospital"
"Ward"

I want to insert values selected from these into a table called "admission" in the fields "HospitalName" and "WardName".


Heres is a copy of my test database with a test form to give you a better idea:

 

[jal]

Give this a try.

 

[PRodgers4284]

Give this a try.

jal thankyou so much for that, i really appreciate your help

 

[PRodgers4284]

jal i just noticed on the form that when i go through the records on the table using the form the hospital name and ward details do not change for each record, it just stays the same for each, is there anyway i can amend this?

 

[jal]

i'm not sure what you have in mind. The form's recordSource (also called a binding) is the Admission table but you haven't made use of this databinding. (Right now you are pretty much working unbound). You haven't pulled any column names from the Admission table onto the form. You can do this at View > FieldList. But doing so might not necessarily be what you need - I'm not sure what you want to do next with this project.

 

[PRodgers4284]

i'm not sure what you have in mind. The form's recordSource (also called a binding) is the Admission table but you haven't made use of this databinding. (Right now you are pretty much working unbound). You haven't pulled any column names from the Admission table onto the form. You can do this at View > FieldList. But doing so might not necessarily be what you need - I'm not sure what you want to do next with this project.

Jal im just using this as a test form, im planning to add the functionality to another much larger form. I have got a solution to the databinding problem thanks jal.

 

[PRodgers4284]

Jal how do i add a textbox value to the admission table, can i just add another field name to the insert statement? for example if i was to create another field called "Test" in the admissions table and then create a textbox assigned to the "Test" field in the form and be able to add this value to the table from the add button.

 

[jal]

Jal how do i add a textbox value to the admission table, can i just add another field name to the insert statement? for example if i was to create another field called "Test" in the admissions table and then create a textbox assigned to the "Test" field in the form and be able to add this value to the table from the add button.

That should work fine, follow the pattern in the MDB file I uploaded. Your SQL will now have an additional parameter, something like:

INSERT INTO DestTable (ward, hospitalID, Test) VALUES (@Ward, @HospitalID, @Test)