using different table as record source

[jalge2]

Hey all. I have my form set up that if a certain checkbox is checked, then a certain table is used as my record source. The problem is, I need all information to save to my main table.

My form works, except that nothing is saving to my main table. How can I do this?

 

[Taff07]

Jalge2

You can set up a command button to save the record to the main table.

IF you expand the following to match each control with each field it should work.

Sub On_click()

dim rstMainTable as recordset

set rstMaintable = currentdb.openrecordset("MainTable&quot

with rstMainTable

.addnew

!field1 = me.control1
!field2 = me.control2
.....

.update

end with

set rstmaintable = nothing

End Sub

A basic setup of the coding.

Hope it helps.

Ian

 

[vmauro]

jalge2 - I dont have an answer to your question, really sorry, but I have a question for you. I posted this today

I think you can help me.

"orginally post in - thread181-708477 "

I have a table of Employees (RN, LPN, MA, NA ...)
each employee has an amount allocated to them to take training and ongoing education. The amount is based on their job type (as mentioned above RN, LPN, MA).

Right now i have a database w/ a table containing thier information (name, job title. date of hire...) and I have a Log Table containing all training and education (dates, titles, cost, time ...), they are linked by a common field (empID). note that each employee can have several trainings in a year.

I enter data through a form which has the employees table as the active form and the Log table as the subform. I search for employees w/ a combo box on the active form and add data to Log table via the subform for that employee.

My problem is when i move from year to year some employees status changed (from LPN to RN) or (Fulltime to Part time) - this will change amount allocated. I run reports by year.

Right know after each year I copy just the employee table and call it employeeTable_2002 or whatever year it might be. Then make any change to the current one to reflect new changes. If I have to run a report for 2002 or earler and I am in the year 2003, I just make a copy of the current and call it employeeTable_2003 or whatever year it might be and move employeeTable_2002 as current to run older reports.

Please help, I know there must be an easier way to select a table and populate a form so that the amount allocated is correct for the year in question.

thanks in advance,

 

[luceze]

VMAURO,

If the tables for the different years you can accomplish this pretty easily.

Create a combo box listing the different years. Name it cboYears.
In the on update event of your combo box place the following code:
me.form.recordsource="Table_" & me.cboYears
me.form.requery

That is assuming that all your tables are named like your example above.
To change your report recordsource you could do something like this:
In your report's on open event place this code:
me.recordsource=forms!YourFormName.recordsource

You will have to have your form open to open your report.

HTH,
Eric

 

[luceze]

OOPS!
The first statement should read. If the tables for the different years are stored in the same database you can accomplish this pretty easily.

Eric

 

[vmauro]

Thanks, works great. I just have to create some error messages to go with it. thanks for the really fast response