Opening a Record from a report

TonyHeslop

Board Regular
Joined
Jun 15, 2006
Messages
84
Hi Folks

What i have is a report which is filtered from a data table based on a persons login. The report shows individual records with a number of fields (as they do lol). I have a button at the end of each record for an action to be done. The action i want to be done is to have a form open up filtered to the record which the button was pressed for.

How do i do that? I can open up the form, that bit is easy, however, how do i make it so that the form is at the record that the user selects from the report?

Thanks
Tony
 
Hi Norie

To get the first part working I basically followed the wizard to open another form and filter the form based on the ID field. This part worked flawlessly just by completing the simple wizard.

To answer your question, yes it is like the screenshot below which has an [event procedure] in the on click.

What was wanting to do was even before the other form was opened, if there has been an appeal already submitted for the record, then to just give the user a message box.

Does that make sense?

Cheers
Tony
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Tony

So what code is being used to open the other form?
 
Upvote 0
Hi Norie

The following code which was automatically generated by the wizard

Code:
Private Sub cmdAppealFail_Click()
On Error GoTo Err_cmdAppealFail_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmAppealForm"
    
    stLinkCriteria = "[IDField]=" & Me![IDField]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAppealFail_Click:
    Exit Sub

Err_cmdAppealFail_Click:
    MsgBox Err.Description
    Resume Exit_cmdAppealFail_Click
    
End Sub

What I wanted to add in that code was something along the lines of
Code:
If IsNull(me.AlreadyAppealed) Then
do the above open form code
Else: Msgbox("This appeal has already been submitted")
EndIf

Know what i mean? What i didnt know was whether me.AlreadyAppealed would only look on the record that the box is being clicked on or not.

Thanks
Tony
 
Upvote 0
Hi Norie

I have done it myself, it actually did use the current record, which is fantastic.

I used the following code.
Code:
Private Sub cmdAppealFail_Click()
On Error GoTo Err_cmdAppealFail_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmAppealForm"
    
If IsNull(Me.Compass_or_NBA) Then

    stLinkCriteria = "[IDField]=" & Me![IDField]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Else: MsgBox ("Sorry this fail has already been appealed")
End If
Exit_cmdAppealFail_Click:
    Exit Sub

Err_cmdAppealFail_Click:
    MsgBox Err.Description
    Resume Exit_cmdAppealFail_Click
    
End Sub

Hope that helps you or someone else :)

Thanks for all your help :)
Tony
 
Upvote 0
Tony

Thanks, for that.

I just spent the last minute trying to get Access 2010 to generate an example of that code.

Guess what though, in their infinite wisdom Access doesn't do it for that version.:huh:

It creates 'embedded' macros, which as far as I'm concerned aren't much use.

So now I'm going to change all the accdb's I'd created to mdbs, which they were originally anyway.

And go back to writing all the code myself.

Sorry for the rant.:)
 
Upvote 0
Hi Norie

You shouldnt need to change them to MDB's, I am using office 2007 and it generated that code too ;)

Cheers
Tony
 
Upvote 0
Tony

Is there a way to create an 2007 accdb in Access 2010?

I'm not really bothered too much about losing the feature - it was handy for quick generation of code 'snippets'.

Of course you had to add your own code, as you have, to do exactly what you want but at least you had something to start with.

The ACCDB databases work fine, but there's not an macro, embedded or other in sight - a long time ago I started doing the code myself.

Those wizards were/are a great learning tool - a tiny bit like the macro recorder in Excel.

Almost started another rant there.:eek:

PS Don't get me started on Tabbed Documents and multivalue fields.:)
 
Upvote 0
I know that I am coming in on the tail end of the discussion, and I am not by any means trying to get you to change anything from what Norie has done, but if I understood your original post, I did something similar to this by adding an embedded Macro into the report for a particular text box that was populated by the record ID. The Macro had the criteria of
Code:
Not IsNull([visitID])
that would trigger the Open Form action and this is what the arguments looked like
Code:
SiteVisitDetails, Form, , ="[visitID]=" & [visitID], , Dialog

Again, I only skimmed over the majority of this post, but this is something that I use fairly frequently as a way for the user to access the records that they pull in a report. Not sure if that was something you were originally looking for.
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,710
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top