Combo Box to Table Question

[guy123]

I have made a combo box that fills in fields on a form, now I need those values to be sent back to the table when I save the record. Is there a way to do this without writing code? Or just a way to do this? I got the value from the place where I put the combo box to return to the data table by altering the Control Source but the other two things I used the combo box to populate have control sources connected to the combo box.

Any help is appreciated

-guy

 

[wiklendt]

Is there a way to do this without writing code?

yes.

I got the value from the place where I put the combo box to return to the data table by altering the Control Source

that sounds right... is it not working for you? and how is it not working?

but the other two things I used the combo box to populate have control sources connected to the combo box.

...and? (sounds like a cascading combo effect - are you having trouble with this?)

 

[guy123]

I have a combo box named combo159
In this box
SELECT FIREDOORS.[DOOR NO], FIREDOORS.[BETWEEN ROOMS], FIREDOORS.[ROOM/AREA], FIREDOORS.[REQUIRED DETECTION SYSTEM] FROM FIREDOORS ORDER BY FIREDOORS.[DOOR NO];
Is the row source
It gets three values from my table Fire Doors
The Door is in the combobox blank
the next box has =Combo159.column(1) and so on for two more boxes, My problem is after I chose the values and get them updated on the form I need them to be sent to a diffrent table. This is what is not working for me


-guy

 

[Insane_ai]

Try to follow my train of though here, sometimes I fail to be copletely clear.

Once you have the information you want on the form, you can set up a trigger to append the data to another table. This trigger may be a button or the AfterUpdate Event on the combo box.

You may want to update rather than append, in this case, you would use UPDATE

SET .... WHERE.... The trigger would remain the same in either case.

If you prefer to avoid writing VBA, design a query that performs the action you want then build the query in to a macro. You could then call the macro from the properties window on the event and object of your choosing.

pseudocode:

Private Sub Your_Event_Trigger()

Dim mySQLString as String
mySQLString = "INSERT INTO DifferentTable ([DOOR NO], [BETWEEN ROOMS], [ROOM/AREA], [REQUIRED DETECTION SYSTEM])" _
& " SELECT combo159.Column(1), combo159.Column(2),combo159.Column(3), combo159.Column(4)"

DoCmd.RunSQL mySQLString

End Sub

 

[guy123]

Try to follow my train of though here, sometimes I fail to be copletely clear.

Once you have the information you want on the form, you can set up a trigger to append the data to another table. This trigger may be a button or the AfterUpdate Event on the combo box.

You may want to update rather than append, in this case, you would use UPDATE

SET .... WHERE.... The trigger would remain the same in either case.

If you prefer to avoid writing VBA, design a query that performs the action you want then build the query in to a macro. You could then call the macro from the properties window on the event and object of your choosing.

pseudocode:

Private Sub Your_Event_Trigger()

Dim mySQLString as String
mySQLString = "INSERT INTO DifferentTable ([DOOR NO], [BETWEEN ROOMS], [ROOM/AREA], [REQUIRED DETECTION SYSTEM])" _
& " SELECT combo159.Column(1), combo159.Column(2),combo159.Column(3), combo159.Column(4)"

DoCmd.RunSQL mySQLString

End Sub

-This Code is not wokring the &Select line is coming up yellow, but thanks for the response I may get it figured out eventually

 

[wiklendt]

hang on, before we get into complex append code - what kind of other table are you sending it to? this might be very easily put together using a subform with appropriate links to the parent form (sends the PK from main form/table into the FK of the child form/table)...

 

[guy123]

I am sending it to a table for keeping records of the form I got the door number section to save to the form by putting the field in the control source on the form but the others have values in their control source, can you have two? I tried using child/form or table but I am not completly sure how that works If you explained it maybe I could figure out how to get this thing to work


thanks
guy

 

[wiklendt]

I am sending it to a table for keeping records of the form I got the door number section to save to the form by putting the field in the control source on the form but the others have values in their control source, can you have two? I tried using child/form or table but I am not completly sure how that works If you explained it maybe I could figure out how to get this thing to work


thanks
guy

ok, that made nothing any clearer. LOL.

the idea is, if you have a main table with a PK, with a relationship to another table where there is a FK, you can make that FK table as a subform on your PK form.

e.g.,

tblEmployee
---------
EmployeeID (PK, Autonumber)
EmployeeName (text)
.
.
.

tblEmployeeJobs
---------
JobID (PK, Autonumber)
EmployeeID (Number) = FK
JobDescription (text)
.
.
.
.

so you have what i call a 'sub' table, and any table that is a 'sub' table can be implemented as a 'sub' form on a main form.

i.e., your main form would be based on your employee table. you then create another form based on the jobs table, and place it into your main form via the subform control (it's added same way as you would, for example, a combo box).

the forms are then linked by a common field (EmployeeID) and when you choose an employee in your main form (say, from a Combobox, like what you described), it automatically filters the subform to display that employee's jobs - as well as automatically filling in the correct employeeID in the subform (and therefore table) when you add a new record in the subform.

here's a couple of threads for you to check out (all have sample files for you to look at - and all were found using the search feature on the sample databases forum here at access world forums) in addition, now that i've given you the name of this feature ("subform") you can and should search the 'forms' forum for HOW TO actually do this (in case my above description was not enough):

this one has a simple example of a one-to-many subform
http://www.access-programmers.co.uk/forums/showpost.php?p=511947&postcount=11

this one also deals with subforms referencing other subforms (from what i can gather in the description)
http://www.access-programmers.co.uk/forums/showthread.php?t=103988&highlight=subform

this one deals not only with subforms, but with subforms where there is a many-to-many configuration with tables. M-M is dealt with when you have, for example, a table for movies, a table for actors, and then a table for which actors appeared in which movies (because one actor can be in many movies, but also one movie has many actors):
http://www.access-programmers.co.uk/forums/showthread.php?t=153458&highlight=subform


this one tells you how you can have your subforms have alternate row colours - i presume it's dealing with continuous forms, but i can't say for sure b/c i have not looked at the sample db itself, only searched the forum. this is a cosmetic thing, but many people ask how to do it.
http://www.access-programmers.co.uk/forums/showthread.php?t=101099&highlight=subform