• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

Arg! Open Args questions

Hello, I have a few questions about the open args statement and how to best implement it in a form.  Currently, I have controls at the top of several forms (move forward and backwards) which keep the same [Call Number] field entered in each form. to do this, I used a code similar to:

Private Sub Command397_Click()
    If Me.Dirty Then
        RunCommand acCmdSaveRecord
    End If

On Error GoTo Command104_Click_Err
    DoCmd.OpenForm "frmHealthOutcomeMortality", , , , , , Me.CallNumber
       DoCmd.Close acForm, "frmHealthOutcomeFunctionQOL"

Command104_Click_Exit:
    Exit Sub

Command104_Click_Err:
    MsgBox Error$
    Resume Command104_Click_Exit
End Sub

Private Sub Form_Current()
Me.CallNumber = Me.OpenArgs

End Sub

Open in new window


For each of the controls.  This works pretty well in my experience, but some of the users have been complaining about how the controls are working.  Some mentioned that data might be overwritten or not recorded.  The field [Call Number] is a pk for these forms/tables.  Does anyone have suggestions on if this is a poor way to pass the record number from one form to another?  What could be causing these issues?

Any comments are as always greatly appreciated and welcomed,
Bevo
0
Bevos
Asked:
Bevos
  • 5
  • 3
5 Solutions
 
dqmqCommented:
Your problem is in the On Current event.  That does NOT navigate the form to the record of interest as your code implies.  Instead, it modifies the CallNumber of whatever record is current (probably the first record).  I'd suggest using the where condition argument instead:


 DoCmd.OpenForm "frmHealthOutcomeMortality", , , "CallNumber=" &  cstr(Me.CallNumber)
0
 
BevosAuthor Commented:
Hi dgmg, thanks for the response!  I think I stated something somewhat misleadingly.  So, when the user first begins entering data there will be no records in the database.  So I want the call number from one form to copy to the next (like it is the the current statement).  If I use the where statement it would filter for that record number correct, but not create a new record essentially with that call number I believe.  Is there a way to get it to do 'both' (i.e. if the record number isn't present create new record else goto where cstr(Me.CallNumber)?
0
 
dqmqCommented:
Correct, the WHERE filter will not create a new record.  Actually, I'm a little uncertain what creates a new record in your scenario.  

I don't see any problem passing the CallNumber in openargs.  But, you not unconditionally change the CallNumber in the OnCurrent event.

Try this:

'pickup the  call number for a new recored
Private Sub Form_Current()
If me.NewRecord then
   me.CallNumber = me.openargs
end if
End Sub


'navigate to the call number for an existing record
Private Sub Form_Load()
Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.FindFirst ("CallNumber=" & Me.OpenArgs)
If Not rs.NoMatch Then
   Me.Bookmark = rs.Bookmark
End If
End Sub



0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
BevosAuthor Commented:
Hi dgmg, this didn't seem to work as intended.  It still is acting as it was before with the 'form_current' me.callnumber = me.openargs.  I'm attaching the db in access 2007 and 2003 format.  Do you think you could please take a look?

Thank you so much for all the help,
Bevo
EE-Example.accdb
EE-Example.mdb
0
 
dqmqCommented:
OK.  Let's try this:

1.  Avoid the On Current Event entirely

2.  In the Form Load Event, do like this:

Private Sub Form_Load()
'navigate to first record matching CallNumber or
'create a new record if CallNumber does not exist
Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.FindFirst ("CallNumber=" & Me.OpenArgs)
If rs.NoMatch Then
   DoCmd.GoToRecord , , acNewRec
   Me.CallNumber = Me.OpenArgs
Else
   Me.Bookmark = rs.Bookmark
End If
End Sub

3.  Make sure to add the above on load event to the startup (first) form

4. Code your New Record button like this:

Private Sub Command270_Click()
On Error GoTo Command270_Click_Err
    Dim SaveCallNumber As Integer
    SaveCallNumber = Me.CallNumber
    DoCmd.GoToRecord , "", acNewRec
    Me.CallNumber = SaveCallNumber

Command270_Click_Exit:
    Exit Sub

Command270_Click_Err:
    MsgBox Error$
    Resume Command270_Click_Exit

End Sub

0
 
dqmqCommented:
On second thought...

By virtue of their primary keys, your child tables only support 1 row per CallNumber.  I believe that to be intentional.  The code I gave you creates that single row in the On Load event when you open a form that does not already have a row matching the open arg.

It therefore follows, that you don't need the button to add a new record.  In fact, the button creates a new record  with a duplicate callnumber and fails when you try to save it by navigating to the next form.  

So, I don't think you need or want that button on any of the forms except the first.  And in the first form, you do not want to use the code I gave you in step 4 because you want the user to type in the new CallNumber.
0
 
dqmqCommented:
And a third thought...

Your first form, especially, needs to work with or without a CallNumber in the openargs. So, add the following statement to the front of the on load event:

      If isnull(me.openargs) then exit sub


 
0
 
BevosAuthor Commented:
Thank you so much for all of the great advice!

Bevo
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now