Select record from subform

[TryingMyBest]

Hi,

I have a main form that is unbound. On that main form is a subform which is bound to a query and displays the results in datasheet format. There is nothing else to the subform.

My users want to be able to click on a record in the subform and have the corresponding data displayed in the main form. To do this all I need to do is update one text field with a number that corresponds to the record that is selected...this is an autonumber but not always the record number. The code will take care of the rest.

As an example: if the programme is let's say A, the subform will display all related audits...these may be audit numbers 1,4,7,10 but the record number would be 1,2,3,4. I need to capture that audit number and display it in the audit number field on the main form.

I have managed to display the previous selected record but can't figure out how to update each time a new record is selected.

Here's the code I have so far:

Me.txtAuditNumber.Value = Forms!frmAuditProgrammePlanning!SUBProgrammeDetails!AuditNumber

Many thanks for all your help.

BTW: I've no idea why there is a space in the word 'details' in my code....it's not like that in the application.

Jo

 

[MCantu]

oncurrent?

 

[Epic]

Try to update the control on the Mainform when the OnCurrent event occurs in the subform;

Forms![<MainFormName>]!txtAuditNumber = ME.AuditNumber

 

[mresann]

Go to the subform's Current sub procedure. When you change a value in the subform, the Current subprocedure activates.

In the subform's Current sub procedure:

Me.Parent.txtAuditNumber.Value = Me.AuditNumber.Value

For more form/subform coding protocols, this link will show you the table I use when I need to access data from either subform to form, or vice versa

Refer to Form and Subform properties and controls

 

[TryingMyBest]

Sorry that doesn't work

Thanks for your replies. I have messed about with this some more and given it some more thought and I think the problem is that I only have two events to choose from in my subform: enter and exit. I have created a subprocedure for the enter event which is why I can only return the previously selected value, i.e the value that was selected when the subform was clicked.

How can I change my subform so that I can have more events to choose from?

I set it up with the wizard and chose to link it with my query.

I'm using Access 2002

Thanks again
Jo

 

[mresann]

You are confusing the Main Form's SUBFORM control with the actual subform itself.You are trying to use the subform control's methods on the MAIN form, which of course only has the Enter and Exit methods. You actually have to go to the actual subform's design properties.

So do this: First, close the save changes and close the MAIN form. You have to be out of the main form to be able to modify the subform

Next, open the subform in Design View. Click the Code button to open the VBA module.

Now you can navigate to the On Current Method of the form and perform the steps in the previous post.

Once you enter the code, save your changes and close the form. Open the main form and you should find the changes have worked.

 

[TryingMyBest]

Thank you

I think I've got it now. My subform control got it's data from the query. I've created another form which has data from the query and made my subform control reference this. I can now manipulate my subform properly.

I didn't realise I had to have another actual form.

Thanks for all your help

Jo