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

[MSeda]

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

Private Sub Form_Current()
Me.Parent.Form.RecordSource = "Select [myTable].* From [myTable] Where [myID] = " & Me.myID
End Sub

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

[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?

[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:

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...

[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:

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

[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...

[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

[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:

Private Sub Form_Click()
    DoCmd.GoToRecord acDataForm, "MainForm", acGoTo, Me.Form.CurrentRecord
End Sub

There’s probably at least a dozen other ways to do this if this one doesn’t work for you.

[anansi]

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