Save selections on form to a table

[robina]

I have a data entry form that contains 3 combo boxes and 2 list boxes. I want the selections to be saved to a table named "Classes_taken" (This is what I have set for my form record source). There are two tables used to populated the combo boxes and list boxes. They are "Class_Catalog" and "Emp". The combo boxes are cascading. Everything works fine except saving a record. After the user makes their choices and clicks the "new" record selector, only one field [LstBoxCDate] is saved to the table. Here are my controls:

Control Name Control Source Row Source
CboName SELECT Emp.Last_Name FROM Emp
ORDER BY Emp.Last_Name;

CboCName SELECT DISTINCT
Class_Catalog.Class_Name FROM
Class_Catalog ORDER BY
Class_Catalog.Class_Name;

LstBoxCDate Date_Taken SELECT Class_Catalog.Class_Date FROM
Class_Catalog WHERE
(((Class_Catalog.Class_Name)=[Forms]!
[entry]![CboCName]));

lstboxEmpID Emp_ID SELECT Emp.Emp_ID FROM Emp WHERE
(((Emp.Last_Name)=[Forms]![entry]!
[CboName]));

LstBoxCCode Class_Code SELECT DISTINCT
Class_Catalog.Class_Code FROM
Class_Catalog WHERE
(((Class_Catalog.Class_Name)=[Forms]![entry]!
[CboCName])) ORDER BY
Class_Catalog.Class_Code;

Thank you for your help. If you need

 

[Mr. B]

If the record source of your form is the "Classes_taken" table then each of your controls should have the appropriate fields from the "Classes_taken" table as its Control Source. This will cause the selections made to be saved to the correct fields in the "Classes_taken" table.

 

[robina]

Me. B,
Classes_taken is the table i want to save the record TO
The combo box source tables are Emp and Class_Catalog
I have it to where after making your selections, the record will save to the Classes_taken table only when you click in the fields you want to be saved (even though they have auto-populated) before clicking save.

 

[Mr. B]

Classes_taken is the table i want to save the record TO

You you have the Classes_taken table (or a query based on that table) set as the record source for your form?

How are you setting the default value your combo and lists boxes?

Can you post a copy of your database here so I can take a look?

 

[robina]

the Classes_taken table is set as my form's record source. I haven't set the default value for controls on the form and I'm having a hard time with the form loading blank like i need it to. Right now it saves a new record that is blank. Unless after you make selections, you go back and just click on the emp_ID and Class_ID fields first, then it saves. My database is attached. I really appreciate your help.

 

[Mr. B]

Take a look at the attached database file. I have created a couple of forms that should help you with the development of you database.

Just open the file. A form will be displayed. I thing you will be able to just follow the path as show in the form.

I have implemented a special function that I use to show/hide, enable/disable or lock/unlock controls. It is rather straight forward as to how it work.

Hope this helps.

 

[robina]

I sure appreciate your help! I have just a couple questions. The form works fine to add a new class until I try to add more than one in a session. The error debugger goes here:
DoCmd.OpenForm "frmManageClassesTaken", , , , acFormAdd, , Me.txtEmp_ID

and then when I choose a class I need to have a list of available dates since it will have more than one choice. I wouldn't bother you about this but I think that where my problems lie. How to get the date and class ID to store in the table when these fields are auto-populated from the class name that is selected.

I really like your interface to be able to edit the employee info. I can learn a lot from what you've done. If you can help me to store the autopopulated fields, i will be able to spend time getting to know how the forms are working. This has really stumped everyone.

 

[Mr. B]

You really do not need to have the auto populated fields. That information is already related to the selected class. You will be able to retrieve it anything you want it. The popup form that allows you to add a record for the employee and a class attended is already prompting for all you would need to complete the record in the table and it will already store that information in the Classes_Taken table.

 

[robina]

Thank you for taking the time to help me so much.

 

[robina]

This is an update. I figured this out myself today. I changed the list boxes to text boxes. Used a query for the first combo box that includes all of the fields from the Emp table that I want to display on the form. I chose the Employee Name field as the bound column, set the column count to 4 with only the employee name column showing. On the "On Change" event of the combobox I added this code:

Me.lstboxEmpID = Me.CboName.Column(0)

lstboxEmpID refers to the text box name and CboName is the combobox name.

I did the same code with the other combo box and text box. Then for the save command button I used the following code:

Dim mySQL As String
mySQL = "INSERT INTO Classes_taken (Emp_ID, Class_ID)"
mySQL = mySQL & "VALUES (lstboxEmpID,LstBoxCID )"
DoCmd.RunSQL mySQL