468,251 Members | 1,353 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,251 developers. It's quick & easy.

Controlling form's current record through selecting in a subform

15
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
8 17514
MSeda
159 Expert 100+
you could use some code in the subforms on current event to alter the parent forms recordsource.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Me.Parent.Form.RecordSource = "Select [myTable].* From [myTable] Where [myID] = " & Me.myID
  3. End Sub
That's the simplest way I can think of.
Megan
Jan 17 '07 #2
anansi
15
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
15
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,
  2. [personAndTime].time, [personAndTime].VenueID,
  3. [personAndTime].ActivityID,[Activities].rate,[Activities].hours,
  4. [Activities].otherPayments 
  5. 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...
Jan 18 '07 #4
MMcCarthy
14,534 Expert Mod 8TB
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
15
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
14,534 Expert Mod 8TB
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
159 Expert 100+
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Click()
  2.     DoCmd.GoToRecord acDataForm, "MainForm", acGoTo, Me.Form.CurrentRecord
  3. End Sub
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
15
thanks, worked it out you ppl r, once again, legends
Jan 21 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by New2Access | last post: by
18 posts views Thread by Darryl Kerkeslager | last post: by
1 post views Thread by troy_lee | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.