Edit Current Sub-form Record

[Baldrick]

I have a form that enables viewing of Attendee info (name, department, etc.) with Project Info displayed in a sub-form. The datasource for both is a query (qryAttendeeProjects). I can edit the Attendee records, but not the Project records.

So, I have created a 'Edit' button in the sub-form that opens a pop-up form to edit the Project info in the Projects table (tblNewProjects). I have created a macro in the 'On Current' property of the pop-up form to match the Project IDs (Proj_ID)of the pop-up form and the sub-form, but this doesn't work. Instead it gives me a dialog box "Enter Parameter Value". If i enter a valid Project ID it works. But, I want to eliminate this step. I want it to match the Project ID of the current sub-from record and return that record in the pop-up window.

Any thoughts?

 

[pcdummy]

Try This

Create a Seperate Sub Called EditProject

Private Sub EditProject()
On Error GoTo Err_Editproject
DoCmd.OpenForm "yourformname", , , "[tblNewProjects]![Proj_ID] = " & Me![Proj_ID]
Exit_EditProject:
Exit Sub

Err_EditProject:
MsgBox Err.Description
Resume Exit_EditProject
End Sub
After you have created a separate sub with the above code then behind the On Click Event of your command button just insert
the code

EditProject

You should not need the macro on the On Current Event.. You may also wish to requery your subform.. by using something like..

Me.SubFormName.Requery this should now allow you to view the subform in it's updated state. The above Sub should open your Projects form with a filter to set to the Proj_Id already displayed..

HTH

Jeff

 

[Baldrick]

Thanks for the input pcdummy. It got rid of the msgbox requesting a parameter value, but it brings up the pop-up with 'auto number' in the Project ID field. I don't know if it's going to a new record or what. I had to edit your code a bit because the sub behind my 'On Click' is called "cmd70".

 

[pcdummy]

Wait.... Design Error???

Your linked child field should be AttenddeeID or something like that... Include a number field in your Projects table for this autonumber field from the Attendees Table... Then

Check your relationships, and make sure that you have a one to many relationship from Attendees to Projects (One Attendee can have many projects). then make sure to have your child link setup to AttendeeID (Or Whatever you wish to call it..)) also you probably do not need to use a query as your record source for the main form... Your attendees table should work fine... then your sub form would use the projects table..

HTH

Jeff

 

[Baldrick]

I have a many-to-many relationship. I have a many to many relationship set up between 'tblAttendees' and 'tblNewProjects' with a third table 'tblAttendeeProjects'. Do I still not need the query to view projects by attendee? I thought I tried this early on and it didn't work. I'll try again if you insist.

 

[Baldrick]

pcdummy it does work. i don't know why i used a query in the first place. now your code should work.