main form recordsource to other form

[garfieldvv]

hello,
I have a DB that is made with a table for each year. I use a main form, set as continues, to display the content of these tables and I use a combo box to change the recordsource. on this form, on each line, I have a button to open another form to display and modify the full record data.

this is the command I use to open the form:

DoCmd.OpenForm "JobForm", acNormal, , "msd=" & msd

my problem is that I can't make the second form open to the correct RS. on both forms the fields are bound to the field of the "RecordSource" table, to allow the record change.

I have tried putting the RS on a global variable, tried setting the form's RS right after it opens and right before and many other options, I just can't seem to get it to work. I am very close to create duplicate forms with hard set RS and open them with "case" statement.

anybody has the answer to this situation?

Thanks!
Tom

 

[dhookom]

A "table for each year" is not exactly the generally accepted solution. Your requirements might not allow this. Do you need to be able to edit the records in the form? If not, you could possibly base the form on a union query. If you have to be able to edit, consider passing the record source using OpenArgs. Your On Open of the JobForm could then use this information to set the appropriate record source.



Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I would use Duane's suggestion of a union query. But if not possible then simply

DoCmd.OpenForm "JobForm", acNormal
set forms("JobForm").recordset = me.recordset

Since the recordsets are the same it will open to the current record.

 

[garfieldvv]

MY GOD! I WORSHIP YOU TWO!

I have myself warp around this issue for two days now!
finally it's working the way it should.

but I want to understand what dhookom said, is it more correct keeping all the records in one big list? at the end of the year I just lock all the information of last year and create a brand-new db table for the year to come. this way I can keep up with the totals and everything. is it more conventional just adding a year reference field and filter results with it?

Thanks a lot!
Tom

 

[dhookom]

IMO, adding a field to store the year is more appropriate since you wouldn't be here asking about changing record sources ;-). You would just filter the records in the form's record source.

Duane
Hook'D on Access
MS Access MVP