By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,402 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

Controlling form's current record through selecting in a subform

P: 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
Share this Question
Share on Google+
8 Replies


MSeda
Expert 100+
P: 159
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

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

P: 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
Expert Mod 10K+
P: 14,534
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

P: 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
Expert Mod 10K+
P: 14,534
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
Expert 100+
P: 159
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

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