Controlling form's current record through selecting in a subform

anansi

Hi

I have a form called 'shiftviewer' and it contains a subform called 'all shifts for current month subform'. the subform is in datasheet view (access 2007) and the main form is a column orientated form.

Both forms contain records of 'shifts'
I want to be able to select a record (a shift) in the subform, and be able to set the parent form's current record to that record, through the identifying primary key that a shift record has (ShiftID)

I am semi capable of visual basic so hit me with it..
thank in advance

Jan 17 '07 #1

MSeda

you could use some code in the subforms on current event to alter the parent forms recordsource.

That's the simplest way I can think of.
Megan

Jan 17 '07 #2

anansi

Thanks thats working pretty well, not got it fully working, but will post when i'v had time. How to SELECT from myquery, not mytbl?

Jan 18 '07 #3

anansi

NOOOO!!

I accomplished what i wanted, but now, in the main form, all the controls are UNEDITABLE!

This defies the object. The code i put into the subform's current function is below:

The subform is however editable, not this is not necessary, what i wanted was to only be able to edit the subform through the main form...

thanks megan for so far...

Jan 18 '07 #4

MMcCarthy

NOOOO!!

I accomplished what i wanted, but now, in the main form, all the controls are UNEDITABLE!

This defies the object. The code i put into the subform's current function is below:

Expand|Select|Wrap|Line Numbers

1. Me.Parent.Form.RecordSource = "Select [personAndTime].promoID,[personAndTime].date,[personAndTime].time, [personAndTime].VenueID,[personAndTime].ActivityID,[Activities].rate,[Activities].hours,[Activities].otherPayments From [personAndTime],[Activities] Where [ShiftID] = " & Me.ShiftID

The subform is however editable, not this is not necessary, what i wanted was to only be able to edit the subform through the main form...

thanks megan for so far...

That's because you're selecting from a query not a table.

What I don't understand is the way you have this set up. The logic of changing a record on a main form based on the selection in a subform is the reverse of what is normally done. The best advice I can give is to open a new form instead with the appropriate record which should be editable.

Mary

Jan 18 '07 #5

anansi

That's because you're selecting from a query not a table.
Mary

I believe i am selecting from tables only. Activities and personAndTime are both tables. correct me if i am in the wrong.

The rational behind this form is that the user selects the item they want to edit from a datasheet subform, listing all the records in a viewable table format. the editing i want to have done must be done through the controls in the main form. but thats neither here not there.

Could you please explain why i cant edit the controls, if Activities and personAndTime are indeed tables, my FROM parameters. The subform however, is based on a query, so maybe im answering my own question...

Jan 18 '07 #6

MMcCarthy

I believe i am selecting from tables only. Activities and personAndTime are both tables. correct me if i am in the wrong.

The rational behind this form is that the user selects the item they want to edit from a datasheet subform, listing all the records in a viewable table format. the editing i want to have done must be done through the controls in the main form. but thats neither here not there.

Could you please explain why i cant edit the controls, if Activities and personAndTime are indeed tables, my FROM parameters. The subform however, is based on a query, so maybe im answering my own question...

Your tables are being accessed through a query. When you put the further criteria on this query to bring up the new record the query becomes uneditable.

Mary

Jan 18 '07 #7

MSeda

Sorry for the confusion I only tested the code on a form bound to a table not a query on two tables. Probably adding criteria to the query makes the recordset unupdateable

The following code should work and doesn’t alter the record source.

In the subform’s on click event:
There’s probably at least a dozen other ways to do this if this one doesn’t work for you.

Jan 18 '07 #8

anansi

thanks, worked it out you ppl r, once again, legends

Jan 21 '07 #9