Button to launch a form from a report

[JamesWB]

I have a fairly simple report which lists rows from one table with a little bit of logic. At the end of each row, I want an action button, which when you click it, goes to a form with the correct ItemID for that row.

I tried the VBA code like this on the View button on click method:

DoCmd.OpenForm "CreateCAF", , , "[ItemID] = " & Me.ItemID

Of course, what happens is it opens the form CreateCAF with the first ItemID in the report, regardless of which row you click on. I want it to pick up the ItemId of that row.

How do I do this please?

thanks
James

 

[pbaldy]

That should work. The report is opened in a Report view, not Preview? ItemID has a numeric data type?

 

[JamesWB]

That should work. The report is opened in a Report view, not Preview? ItemID has a numeric data type?

Yes, it's in Report mode.

I think the problem seems to be that the ItemID is not based on the rowcount, it just loads the first one from the recordset.

It sounds like I need some code for the button that does something like this:

For the current row...
Load the ItemID based on that row in the recordset....
Then load the form based on that ItemID....

I presume that's a piece of SQL in the VBA? Not very good at that, sigh.

Is it something like:

Function LoadFormID(ReportRowValue)
Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = CurrentDb.OpenRecordset("TheOriginalQueryForThisReport")

If rst.RecordCount = 0 Then
LoadFormID = 0

Else
With rst
.MoveTo(ReportRowValue) (not sure how to write this bit!)
LoadFormID = The ItemID at the current record (not sure how to write this)
End With
End If
End Function

Is that vaguely right?

 

[pbaldy]

No, you shouldn't need any of that. I don't use Report view much, but this is from a production db :

DoCmd.OpenForm ("frmROEdit"), , , "RONum = '" & Me.RONum & "'"

In my case the RONum field is text, thus the extra quotes. Can you post your db here?

 

[JamesWB]

No, you shouldn't need any of that. I don't use Report view much, but this is from a production db :

DoCmd.OpenForm ("frmROEdit"), , , "RONum = '" & Me.RONum & "'"

In my case the RONum field is text, thus the extra quotes. Can you post your db here?

By "RONum" there, do you mean a specific rowcount property in the report?

My equivalent is

DoCmd.OpenForm "CreateCAF", , , "[ItemID] = " & Me.ItemID

and that doesn't work, ItemID is the autonumber field and appears in the recordset, but the View buttons down the page only load the ItemID from the first row of the Report's bound recordset.

I don't think your solution would work in a report either, that's form code, not report code. Reports don't 'know' which row is being looked at when a button in the row is clicked.

See the attached picture of the report.

 

[pbaldy]

I don't think your solution would work in a report either, that's form code, not report code. Reports don't 'know' which row is being looked at when a button in the row is clicked.

That code was pulled from a report that gets opened in Report view. It correctly opens the form to the record clicked on in the report.

RONum is the name of the field I want the wherecondition applied to.

 

[JamesWB]

That code was pulled from a report that gets opened in Report view. It correctly opens the form to the record clicked on in the report.

RONum is the name of the field I want the wherecondition applied to.

You mention a 'where' condition - is there some more code in your solution that determines what row it sits in?

 

[JamesWB]

Interestingly, this kind of works, but only loads the form with the row number as the ItemID, not the actual ItemID.

Public Function GetItemID(Loc)
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("qryCAFAReportList")

If rst.RecordCount = 0 Then
GetItemID = 0

Else
With rst
.AbsolutePosition = Loc
GetItemID = .Fields("[ItemID]")
End With
End If
End Function

Private Sub Command13_Click()
DoCmd.OpenForm "CreateCAF", , , "[ItemID] = " & GetItemID(CurrentRecord)
End Sub

 

[pbaldy]

You mention a 'where' condition - is there some more code in your solution that determines what row it sits in?

No, that's it. The wherecondition is the argument of OpenForm you're using to filter the form. I take it you can't post a sample of the db here?

 

[JamesWB]

No, that's it. The wherecondition is the argument of OpenForm you're using to filter the form. I take it you can't post a sample of the db here?

I've tried all kinds of things now. The target form opens from the same query but no matter what I try, I can't get the form to open on the same query as the report row. Sigh.

The DB is too big to attach - can I just upload forms, queries and tables somehow?

EDIT: See next post - I've uploaded it as a zip. Many thanks for any help!

The report is ApprovedCAFs

It tries to load the form TestCreateCAF

They both use the query qryCAFAReportList

 

[JamesWB]

I've tried all kinds of things now. The target form opens from the same query but no matter what I try, I can't get the form to open on the same query as the report row. Sigh.

The DB is too big to attach - can I just upload forms, queries and tables somehow?

Trying to upload the DB as a zip. Please see attached.

 

[pbaldy]

You have the ItemID field/textbox in the report header. It should be in the detail section (it can be hidden).

 

[JamesWB]

You have the ItemID field/textbox in the report header. It should be in the detail section (it can be hidden).

Oh gosh, how bloomin' obvious. :banghead:

Thanks so much!

 

[pbaldy]

Happy to help!