Solved Form problems with OpenArgs (1 Viewer)

BeardedSith

Member
Local time
Today, 18:49
Joined
Feb 5, 2020
Messages
73
I have a form that serves as the central "hub" for my database. It's called frmRewards. Sometimes (when opening from Reports) I want the form to open to a specific record. Other times, I want it to open to a new record. The main issue lies within record navigation after I open the form using either of the following procedures.

frmRewards Form_Load():
Code:
Private Sub Form_Load()

On Error GoTo Form_Load_Err
FilterList
Call FormStartUp: Call IssueRewards
    If (IsNull(OpenArgs)) Then
        'DoCmd.GoToRecord , "", acNewRec
        Exit Sub
    End If

Form_Load_Exit:
    Exit Sub
Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit
End Sub

Opening the form from another form, I'm using:
Code:
Private Sub cmdCustomers_Click()
    DoCmd.Close acForm, "frmLanding", acSaveNo
    DoCmd.OpenForm "frmRewards", acNormal, , , acFormAdd
End Sub

Opening the form from a report, to a specific record I'm using:
Code:
Private Sub txtID_Click()
On Error GoTo txtID_Click_Err
    If (IsNull(ID)) Then
        Beep
    End If
    
    If (Not IsNull(ID)) Then
        DoCmd.OpenForm "frmRewards", acNormal, "", "[ID]=" & ID, , acDialog
        On Error Resume Next
        DoCmd.Requery ""
    End If

txtID_Click_Exit:
    Exit Sub
txtID_Click_Err:
    MsgBox Error$
    Resume txtID_Click_Exit
End Sub

I can do one of two things, but I need both.
1) Open the form to a specific record
2) Navigate the form after it's opened using a listbox.

The issue is in order to get it to open to a specific record, it breaks the ability to navigate to other records on the form. This might be because "acFormAdd". I had to add this in because opening the form from another form always opened it to the first record in the database, instead of a "new" record. Effectively, sometimes I need OpenArgs, and sometimes I don't. Making one work breaks the other (report to specific record VS form to a new record).
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:49
Joined
May 21, 2018
Messages
8,463
In the listbox navigation if you turn off the form filter all records will be available
Me.filter = ""
me.filteron = false
'may be needed me.requery
... code to move to record
 

BeardedSith

Member
Local time
Today, 18:49
Joined
Feb 5, 2020
Messages
73
What's the code for FilterList() ?
Code:
Property Get ListQuery() As DAO.QueryDef
If m_qdf Is Nothing Then Set m_qdf = CurrentDb.CreateQueryDef("", LIST_SQL)
    Set ListQuery = m_qdf
End Property
Private Sub FilterList(Optional criteria As String)
    criteria = "*" & criteria & "*"
    With Me.ListQuery
        .Parameters(0) = criteria
        Set Me.lstCustomers.Recordset = .OpenRecordset
    End With
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
21,358
Hi. Maybe you could simply use the OpenArgs to do both. For example, you could pass the ID value to navigate the form to a specific record, and pass "new" to send the form to a new record. Just a thought...
 

BeardedSith

Member
Local time
Today, 18:49
Joined
Feb 5, 2020
Messages
73
In the listbox navigation if you turn off the form filter all records will be available
Me.filter = ""
me.filteron = false
'may be needed me.requery
... code to move to record
Just because I didn't include it earlier, he's the code for the list double-click:
Code:
Private Sub lstCustomers_DblClick(Cancel As Integer)
Dim PurchaseSQL As String
Dim RewardSQL As String
PurchaseSQL = "SELECT tblMembers.ID, Last(tbPurchases.PurchaseDate) AS LastPurchase, Last(tbPurchases.PurchaseAmount) AS LastOfPurchaseAmount FROM tblMembers INNER JOIN tbPurchases ON tblMembers.ID = tbPurchases.MemberID GROUP BY tblMembers.ID HAVING ((([Forms]![frmRewards]![txtID]) = [tblMembers]![ID]))"
RewardSQL = "SELECT tblMembers.ID, Last(tblRewards.IssueDate) AS LastReward, Last(tblRewards.IssueAmount) AS LastRewardAmount FROM tblMembers INNER JOIN tblRewards ON tblMembers.ID = tblRewards.CustomerID GROUP BY tblMembers.ID HAVING ((([Forms]![frmRewards]![txtID]) = [tblMembers]![ID])) "
    DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))
    DoCmd.Requery "lstCustomers"
'------------------------------------------------
'-----------------Pull Last Purchase-------------
    Dim Pprm As DAO.Parameter
    Dim Pqdf As DAO.QueryDef
    Set Pqdf = CurrentDb.CreateQueryDef(vbNullString, PurchaseSQL)
    For Each Pprm In Pqdf.Parameters
        Pprm.Value = Eval(Pprm.Name)
    Next
    Set Keys = Pqdf.OpenRecordset
    If Keys.RecordCount = 0 Then
        Me.txtLastPurchase.Caption = "No Purchases"
    Else
        Me.txtLastPurchase.Caption = FormatCurrency(Keys.LastOfPurchaseAmount) & " on " & FormatDateTime(Keys.LastPurchase, 2)
    End If
'------------------------------------------------
'-----------------Pull Last Rewards--------------
    Dim Rprm As DAO.Parameter
    Dim Rqdf As DAO.QueryDef
    Set Rqdf = CurrentDb.CreateQueryDef(vbNullString, RewardSQL)
    For Each Rprm In Rqdf.Parameters
        Rprm.Value = Eval(Rprm.Name)
    Next
    Set Keys = Rqdf.OpenRecordset
    If Keys.RecordCount = 0 Then
        Me.txtLastReward.Caption = "No Rewards Issued"
    Else
        Me.txtLastReward.Caption = FormatCurrency(Keys.LastRewardAmount) & " on " & FormatDateTime(Keys.LastReward, 2)
    End If
    

On Error GoTo lstCustomers_DblClick_Err

lstCustomers_DblClick_Exit:
    Exit Sub
lstCustomers_DblClick_Err:
    MsgBox Error$
    Resume lstCustomers_DblClick_Exit
End Sub
 

BeardedSith

Member
Local time
Today, 18:49
Joined
Feb 5, 2020
Messages
73
Hi. Maybe you could simply use the OpenArgs to do both. For example, you could pass the ID value to navigate the form to a specific record, and pass "new" to send the form to a new record. Just a thought...
How would I do a new record with OpenArgs?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
21,358
How would I do a new record with OpenArgs?
For example:
Code:
DoCmd.OpenForm "FormName", , , , , "new"
Then:
Code:
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
    Select Case Me.OpenArgs
        Case "new"
            DoCmd.GoToRecord , , acNewRec
        Case Else
            'check if it's an ID value then navigate to it in here, etc...
    End Select
End If

End Sub
 

BeardedSith

Member
Local time
Today, 18:49
Joined
Feb 5, 2020
Messages
73
For example:
Code:
DoCmd.OpenForm "FormName", , , , , "new"
Then:
Code:
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
    Select Case Me.OpenArgs
        Case "new"
            DoCmd.GoToRecord , , acNewRec
        Case Else
            'check if it's an ID value then navigate to it in here, etc...
    End Select
End If

End Sub
I gave this a try using this:
Code:
Private Sub Form_Load()

On Error GoTo Form_Load_Err
FilterList
Call FormStartUp: Call IssueRewards
    Select Case Me.OpenArgs
        Case "new"
            DoCmd.GoToRecord , , acNewRec
        Case Else
            DoCmd.SearchForRecord , , , [ID] = Me.OpenArgs
    End Select
'    If (IsNull(OpenArgs)) Then
'        'DoCmd.GoToRecord , "", acNewRec
'        Exit Sub
'    End If

Form_Load_Exit:
    Exit Sub
Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit
End Sub

And using this as the command button to open:
Code:
Private Sub cmdCustomers_Click()
    DoCmd.Close acForm, "frmLanding", acSaveNo
    DoCmd.OpenForm "frmRewards", acNormal, , , , , "new"
End Sub

And I got this error:
1583512453948.png


I'm not really all that great with this programming stuff. I know enough to get myself into trouble, but perfecting it isn't something that comes easy to me lol
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a guess, since I can't see your form. One reason the form can't get to a new record is maybe because the AllowAdditions property is set to No.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
21,358
Okay, just as a test, please comment out all the other code you have in the Load event and just test the part with the GoToRecord acNewRec. If the error goes away, there may be a conflict with your other code.
 
Last edited:

BeardedSith

Member
Local time
Today, 18:49
Joined
Feb 5, 2020
Messages
73
You're on to something there. That fixed the problem opening from another form. Here is the code for the two functions I created and call when opening that form:

Code:
Public Function FormStartUp()
    DoCmd.NavigateTo "acNavigationCategoryObjectType"
    'DoCmd.RunCommand acCmdWindowHide
End Function

Public Function IssueRewards()
Dim IssueSQL As String
IssueSQL = "INSERT INTO tblRewards ( CustomerID, IssueDate, IssueAmount )" _
            & " SELECT qryEligibility.ID, DateSerial(Year(Now()),Month(Now()),Day(Now())) AS Today, qryEligibility.RealAmount" _
            & " FROM qryEligibility" _
            & " WHERE (((qryEligibility.IsEligible)=1))"

CurrentDb.Execute IssueSQL, dbFailOnError
End Function

Private Sub FilterList(Optional criteria As String)
    criteria = "*" & criteria & "*"
    With Me.ListQuery
        .Parameters(0) = criteria
        Set Me.lstCustomers.Recordset = .OpenRecordset
    End With
End Sub

This has all worked in the past, until I tried to get the form to open from a report hyperlink.

Also, when trying to use this new method to open frmRewards from a report, I get an error where it always opens the first record. Here is the code I'm trying to use from the report to open this form to a specific record:
Code:
Private Sub txtID_Click()
On Error GoTo txtID_Click_Err
    If (IsNull(ID)) Then
        Beep
    End If
   
    If (Not IsNull(ID)) Then
        'DoCmd.OpenForm "frmRewards", acNormal, "", "[ID]=" & ID, , acDialog
        DoCmd.OpenForm "frmRewards", acNormal, , , , , [ID]
        On Error Resume Next
        DoCmd.Requery ""
    End If

txtID_Click_Exit:
    Exit Sub
txtID_Click_Err:
    MsgBox Error$
    Resume txtID_Click_Exit
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
21,358
Hi. I think, the Requery line is doing it. When you requery a form, it goes back to the first record. So, what you'll need to do is "remember" which record you were before the requery and then navigate back to it after the requery.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
21,358
I'm not at all sure how to accomplish that :(
What you could try then is modify the code when opening the form from the report and simply past the ID as an OpenArgs. Don't use the WhereCondition argument.
 

Cronk

Registered User.
Local time
Tomorrow, 09:49
Joined
Jul 4, 2013
Messages
2,770
Might be time after 16 posts on this thread, to post the database.
 

apr pillai

AWF VIP
Local time
Tomorrow, 04:19
Joined
Jan 20, 2005
Messages
735
As a sample method there is a Text Box and a Command Button on the first Form. Employee Code is entered into the Text Box and the user clicks on the Command Button to Open the Employees Form. The Command Button-Click Event opens the Employees Form and passes the EmployeeID as OpenArgs. If the Text Box is empty then the Employees Form is open normally.

Code:
Private Sub Command2_Click()
Dim i, frm As Access.Form

i = Nz(Me![Text0], "")
If Len(i) > 0 Then
   DoCmd.OpenForm "Employees", acNormal, , , , , i
Else
   DoCmd.OpenForm "Employees", acNormal
End If
End Sub

On the Employees Form_Load() Event, if employee id is received as OpenArgs then the recordsetclone is searched for a match and if found make that record current on the form, by copying the the recordset.bookmark to the Employees Form's bookmark. This action will make the searched record current on the Form.

If OpenArgs is Null then the Employees Form creates a new record on the Form.

Code:
Private Sub Form_Load()
Dim EmpID, rst As Recordset

EmpID = Nz(Me.OpenArgs, 0)
If EmpID > 0 Then
    Set rst = Me.RecordsetClone
    rst.FindFirst "EmployeeID = " & EmpID
    If Not rst.NoMatch Then
       Me.Bookmark = rst.Bookmark
    End If
    rst.Close
Else
    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If
    
End Sub
 
Last edited:

Users who are viewing this thread

Top Bottom