Use command button to open selected record on a form without filtering? (1 Viewer)

AliG

Registered User.
Local time
Today, 09:55
Joined
May 3, 2016
Messages
21
I have a continuous form which displays a small amount of data from each record in my table ProjectT (i.e. project name, status) and a command button in the footer which I would like to open the selected record in its expanded single form (where all of the relevant info is displayed).

At first I set this button up using Access's wizard, but realized that Access opens a selected record by filtering the data on the form. The problem with this is that once the expanded form is opened, I want a user to be able to move to other records without having to select to unfilter the results. If I change the button on my continuous form to simply open the expanded single form, is there code I can run to make the form open to the selected record without putting a filter on?

Initially I thought to set the expanded single form's (named ProjectF) default value to Forms!ProjectListF!ProjectID (where ProjectListF is the continuous form and ProjectID is the autonumber primary key for ProjectT), but this was not successful, I think because there is more than one ProjectID displayed on ProjectListF.

Another thing to consider is that I have another button on my Main Menu form which opens the ProjectF form in data entry mode to prevent the user inadvertently changing/deleting an existing record when they are trying to add a new one; I have no idea if this might be important when trying to find a solution to my issue.

I'm open to any suggestion--I have an okay handle on SQL, and have delved into a bit of VBA but am completely self taught. Any ideas? Thanks!
 

sneuberg

AWF VIP
Local time
Today, 06:55
Joined
Oct 17, 2014
Messages
3,506
I think if you pass the ProjectID in the OpenArgs you can get in the the form's open and then do something like:

Code:
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "ProjectID=" &  Me.OpenArgs
Me.Bookmark = rst.Bookmark
 

speakers_86

Registered User.
Local time
Today, 09:55
Joined
May 17, 2007
Messages
1,919
This is something that we do from time to time, which means it justifies a routine in a module I think. Here is some reusable code:
Code:
Public Sub MoveTo(frmToMove As Form, strNameOfIdField As String, lngId As Long)     On Error GoTo err          'this will move a form to a record with a specified id     Dim rs As Object      Set rs = frmToMove.Recordset.Clone     rs.FindFirst "[" & strNameOfIdField & "] = " & lngId     If Not rs.EOF Then frmToMove.Bookmark = rs.Bookmark      Exit Sub err:     MsgBox err.Description End Sub
Then you can call it like so:
Code:
 moveto forms!frmnotes, "NoteId",200 moveto forms("frmNotes"),"noteid",200
If you are interested in testing for success (which is probably wise), you can change it from a sub to a function and return a Boolean upon completion.
 

Simon_MT

Registered User.
Local time
Today, 13:55
Joined
Feb 26, 2007
Messages
2,177
Because the Review can be filtered in a variety ways I do this:

Code:
Function SchemesReview_Entry()
    With CodeContextObject
        If IsNull(.[Pig Scheme Number]) Then
            Exit Function
        Else
            DoCmd.OpenForm "Pig Schemes Entry", acNormal, "", GetFilter, acFormEdit, acWindowNormal
            DoCmd.GoToRecord acDataForm, "Pig Schemes Entry", acGoTo, .CurrentRecord
        End If
    End With
End Function

Simon
 

AliG

Registered User.
Local time
Today, 09:55
Joined
May 3, 2016
Messages
21
I ended up doing what @sneuberg suggested and passing the ProjectID in OpenArgs, but because I have a separate button in my Main Menu that opens the form data entry mode I had to adjust for a Null ProjectID like so:
Code:
Private Sub Form_Open(Cancel As Integer)
Dim rs As Recordset
Set rs = Me.RecordsetClone
[COLOR="Red"]If IsNull(ProjectID) Then
    DoCmd.GoToRecord , , acNewRec
Else[/COLOR]
    rs.FindFirst "ProjectID=" & Me.OpenArgs
    Me.Bookmark = rs.Bookmark
[COLOR="red"]End If[/COLOR]
End Sub
It works beautifully now! Thank you to everyone for your suggestions!

I think if you pass the ProjectID in the OpenArgs you can get in the the form's open and then do something like:

Code:
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "ProjectID=" &  Me.OpenArgs
Me.Bookmark = rst.Bookmark
 

Users who are viewing this thread

Top Bottom