Solved Form problems with OpenArgs

BeardedSith

BeardedSith

Member
Local time
Yesterday, 22:09
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).
 
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
 
cheekybuddha said:
What's the code for FilterList() ?
Click to expand...
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
 
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...
 
MajP said:
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
Click to expand...
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
 
theDBguy said:
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...
Click to expand...
How would I do a new record with OpenArgs?
 
BeardedSith said:
How would I do a new record with OpenArgs?
Click to expand...
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
 
theDBguy said:
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
Click to expand...
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
 
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.
 
BeardedSith said:
It's set to "Yes".
View attachment 79627
Click to expand...
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:
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
 
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.
 
BeardedSith said:
I'm not at all sure how to accomplish that :(
Click to expand...
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.
 
Might be time after 16 posts on this thread, to post the database.
 
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:
You must log in or register to reply here.

Similar threads

jwcolby54
OpenArgs (my) class way
3 4 5
Replies
83
Views
6,637
Josef P.
J
D
Solved OpenArgs From SubF.CurrentRecord[FieldName]
Replies
12
Views
1,192
Mike Krailo
Mike Krailo
D
Opening record by ID opens a new record
Replies
8
Views
452
Mark_
M
C
Matching vba recordset to form recordset
Replies
12
Views
447
Gasman
Gasman
E
Solved Add Record VBA Not working
Replies
18
Views
1,008
E9-Tech
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom