How to get a value from a selected row in a data grid

[pigsie]

I've done a bit of digging around but can't seem to find an answer to this one. What am I trying to do is the following:

I have a subquery which is displayed on a form. What I would like to be able to do is, when a user selects a row - have a unique id from that row passed to another form/control.

Is this even possible?

Thanks

 

[jrbarnett]

It is certainly possible. You need to have the Current event on the subform pass the primary key value of that table of the currently selected record across to the other form or control.

For example:

Private Sub Form_Current

  Parent.Form!subfrmForm2.Form.txtTextbox = "The value of field PrimaryKey is " & Me!PrimaryKey, vbOkOnly

End Sub

-This updates a textbox called txtTextbox on a subform called subfrmForm2 with the value of a field called PrimaryKey in the current form.

John

 

[VBslammer]

You might check to see if the other form is loaded to prevent runtime errors:

Private Sub Form_Current()
  If CurrentProject.AllForms("OtherForm").IsLoaded Then
    Forms("OtherForm").Controls("TheControl").Value = Me!PrimaryField
  End If
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[pigsie]

Hi

Thanks for the response, I should have been a bit clearer - I have a datasheet/grid which is bound to a query and displays a list of results. I want to peform some action so that the user can select a row and the id of the row selected is then passed to another form.

Thanks

 

[VBslammer]

If you are using a subform in datasheet view, as opposed to a Microsoft DataGrid Control (OLEDB), then the examples provided do exactly what you are describing. When the user selects a row in a datasheet, it triggers the Form_Current() event.

Are you using an ActiveX DataGrid Control?

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[pigsie]

Hi

I think part of the problem is I'm not 100% clear on the terminology - I believe its the subform datasheet view that I am using. I selected subform/subreport from the tool bar, drew a square on my form and then bound it to a query so I'm assuming this is indeed a datasheet and not a microsoft datagrid control. is the form_current() event an event of the subform or the enclosing form?

Thanks

 

[jrbarnett]

The Form_Current event code should be placed in the subform which holds the data you need to access. You will be using the Access control (rather than the microsoft Datagrid control) if you didn't specifically insert an ActiveX object of type Microsoft Datagrid control.

John

 

[pigsie]

Hi

Thanks for the quick reply. Looking at the code behind my form , there doesn't appear to be a Form_Current I shouldn't have t o manually add the event handler should I?

 

[jrbarnett]

Access won't create an empty event handler unless you specifically ask it to create one. Even then, it won't do anything until you put some code into it.

You will need to manually create the event handler - by putting the line "Private Sub Form_Current" in a form module, Access will recognise this as the event handler for the Current event for this particular Form object. The handler will finish at the line "End Sub"

John

 

[pigsie]

Thanks

I managed to add the event handler - but if fires as soon as my form is launched. I think I have it set up incorrectly as I appear to have a form, and then a subform within that on which I have 6 tabs, on one of the tabs I then have my datagrid but it appears my event handler applies to the complete form. At the bottom of the outer form I have buttons to move back and fourth - clicking back or forwards on this executes my event handler. However on the inner form (on which I want this to occur) moving back and forward does nothing. I have tried selecting the inner form and then selecting code from the menu to add the event handler but this seems to always open the module for the outer form. Hopefully I'm making some sense.

Thanks

 

[pigsie]

Hi

I decided to use a Listbox as it was more inline with what I was trying to achieve. Thanks for all the responses. I've managed to use a list box and retrieve the value from the selected row. The only other thing I need to work out how to do is to now use the value on the next tab (i'm using a tab control). The next tab contains a form which allows me to move back and fourth through a query I would like it to jump to the record with the matching id.