CODEWITHSUNDEEP
ms-accessms-access-2007vba
andrewb
andrewb

Reputation: 5339

Is it possible to click on a report record to open relevant form in Access using VBA

I have a report with details of jobs/tasks, and also a form which contributes the majority of the data towards that report. Given that a report is a nice way of looking at the larger picture of the data, and a form is the best way of editing data, I would like to be able to click on a row, and have it open up the relevant record in the form view.

Does anyone know how to do this through VBA? In my mind it should be possible, though my knowledge of objects in Access is limited.

Update

I've implemented the following code for my report:

Private Sub Edit_Click()

  Dim EntityName As String
  Dim DocName As String

  DocName = "Entity: Overview"

  strWhere = "[Entity Name]='" & Entity & "'"
  DoCmd.OpenForm DocName, acNormal, , EntityName

End Sub

It successfully opens the correct form, and it also grabs the correct entity name, however it isn't filtering properly. I can't see what the issue is with the code.

Upvotes: 3

Views: 24267

Answers (3)

HansUp
HansUp

Reputation: 97101

In your Edit_Click() procedure, you have EntityName as the WhereCondition parameter to OpenForm.

  DoCmd.OpenForm DocName, acNormal, , EntityName

However, you haven't assigned anything to EntityName, so it's an empty string. I think you should use strWhere as the WhereCondition.

Private Sub Edit_Click()
  Dim strWhere As String
  Dim DocName As String

  DocName = "Entity: Overview"
  strWhere = "[Entity Name]='" & Me.Entity & "'"
  DoCmd.OpenForm DocName, acNormal, , strWhere
End Sub

I assumed Entity is the name of a control, and that's where you get a value to build strWhere. If there is no control in the report by the name of Entity, then the code won't work even if there is an Entity in the original query.

Upvotes: 3

Fionnuala
Fionnuala

Reputation: 91316

You say report, but you should not be using a report but a continuous form or datasheet. You can then add events to any of the controls on any line, and add a double-click event, if you do not want to drive your users insane. You could also add a command button if that would be clearer to your users, or format the "link" textbox to underline. The record opened by the action shown by @dbaseman will open which even record has the focus (current record). And that will lead you to discover what you can and can't do with a continuous form :D

Upvotes: 1

McGarnagle
McGarnagle

Reputation: 102743

You should be able to add an On Click event in the Detail section of the report, then add something like this in the VBA handler:

DoCmd.OpenForm "MyForm", acNormal, "", "ID=" & ID.Text

(where MyForm is the target form, and ID is a hidden or visible control in your report that identifies the current record).

Upvotes: 1

Related Questions