Open form ignoring On Load Event (1 Viewer)

Zydeceltico · Mar 26, 2019

I have Form1 and Form2.

75% of the time Form2 is only opened by clicking a button on Form1. When this happens an ID number is passed from a control on Form1 to a control on Form2 during Form2's Load event. Form1 remains open. When the Save button is clicked on Form2 both forms are saved and closed at the same time.

Here is Form2's Load event code that passes the ID:

Code:

Me.InspectionEvent_FK = Forms!frmInspectionEvent!InspectionEvent_PK

The challenge is that Form2 has two Date/Time controls. User clicks a button and =Now() is recorded. Basically Start Time and Stop Time.

Usually this process is of short duration and does not get interrupted so Form2 is open the entire time - however - that doesn't always happen and it becomes necessary to save and close Form2 and come back later to click the Stop Time button.

At a later time, when it is time click the Stop Time button on Form2, I would like to open Form2 to the most recent record and click the Stop Time button but when I open the form it wants the ID that was passed to it from Form1.

Is there a workaround for this?

Form2 is set to Data Entry = Yes.

If I set it to Data Entry = No, what is the code to open it from Form1 for data entry?

Or is there yet a different way to accomplish what I want to do?

Thanks as always!

Tim

pbaldy · Mar 26, 2019

You can use the form either way by setting data entry to no, and controlling it with the data mode argument of OpenForm:

https://docs.microsoft.com/en-us/office/vba/api/Access.DoCmd.OpenForm

This may also be of interest:

http://www.baldyweb.com/wherecondition.htm

theDBguy · Mar 26, 2019

Hi. I think you can open a form in data entry mode by using the acFormAdd argument of the OpenForm method.

Zydeceltico · Mar 26, 2019

pbaldy said:
You can use the form either way by setting data entry to no, and controlling it with the data mode argument of OpenForm:

https://docs.microsoft.com/en-us/office/vba/api/Access.DoCmd.OpenForm

This may also be of interest:

http://www.baldyweb.com/wherecondition.htm

Thanks for the links. I have a clearer understanding of how to use the various variables of DoCmd.OpenForm....

I tried to solve my issue by changing Form2 Data Entry = No - - but it still wants the ID value passed from Form 1 which is not open in this condition.

I made a copy of Form2, renamed it, and deleted the Load event code and it works just fine - - but I am using two forms instead of one.

Now I am wondering if there is a way for Form2 to be opened from two different places. For instance, if a button in Place1 is clicked then Form2 opens and uses its Load Event code whereas if a button is clicked in Place2, Form2 opens up ignoring its Load event code.

Is something like that possible?

pbaldy · Mar 26, 2019

You could use the OpenArgs argument of OpenForm to pass the value. In the load event, test that and use it if it's present.

Uncle Gizmo · Mar 26, 2019

Zydeceltico said:
I would like to open Form2 to the most recent record

is this the same record as when Form1 opened it before you closed it? Or is it a new record?

Sent from my SM-G925F using Tapatalk

Zydeceltico · Mar 26, 2019

Uncle Gizmo said:
is this the same record as when Form1 opened it before you closed it? Or is it a new record?

It's the same record - not a new one. Logistically, it is HIGHLY unlikely that we would have two real world instances of this condition happen at the same time so closing the form and returning later to the most recent record will be more than adequate.

Uncle Gizmo · Mar 26, 2019

Zydeceltico said:
It's the same record - not a new one.

Sounds to me like you need to add a "Custom Form Property" to the Form. Form Custom Properties are slightly different from the Custom properties that you can put in a forms module, they actually become part of the form and retain their value between openings and closings of the form. I show how to use them to make a form "Sticky" - A sticky form is a Form that when it's in your way you move it out of the way. Open it again and it's in the position you last moved it to.

I've recently made a new product which allows you to easily add these Custom FORM Properties to a form. You can download it for a couple of Dollars or so from my website here:- http://www.niftyaccess.com/sticky-form-builder/ I also provide links to the places I gathered the information from to create this product, so you are able to add the Custom FORM Properties yourself if you are so inclined.

Zydeceltico · Mar 27, 2019

pbaldy said:
You could use the OpenArgs argument of OpenForm to pass the value. In the load event, test that and use it if it's present.

Hi Paul,

I tried your suggestion - at least partially - I pass OpenArgs from Form1 to Form2 on the Load Event for Form2 which works as expected when Form2 is first called from Form1. After saving and closing Form2 and Form1, I check the table for Form2 and the value passed with OpenArgs is recorded.

When I reopen Form2 later to edit (without opening Form1), there is no OpenArgs value to pass so that ID field then gets deleted.

I have a second button control on my Main Menu to return to Form2 after it has been closed. What does the code look like to test that FieldValue1 is not null? It is a numeric integer value foreign key - considering that Form2 already has this code occupying it's Load event for when it is first opened from Form1:

Code:

Private Sub Form_Load()
    Me.FieldValue1 = Me.OpenArgs
End Sub

Thanks a bunch.

Tim

Zydeceltico · Mar 27, 2019

It appears that this code does what I need:

Code:

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindFirst "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
    End If
  End If
End Sub

I'll need to run some tests but I think I have the answer - -thank you to whoever showed me this some time back. I just realized that it is what I am looking for.

Learning little by little.

Tim

pbaldy · Mar 27, 2019

Glad you got it sorted, though opening the form with all records can be a performance problem.

Zydeceltico · Mar 27, 2019

pbaldy said:
Glad you got it sorted, though opening the form with all records can be a performance problem.

Is there a more efficient way to handle it?

MajP · Mar 27, 2019

check if a record exists before opening up the form. If it exists then open form filtered to that record if not open form in addnew.

pbaldy · Mar 27, 2019

Zydeceltico said:
Is there a more efficient way to handle it?

Using the second link from post 2 to open the form filtered to the desired record.

Zydeceltico · Mar 27, 2019

pbaldy said:
Using the second link from post 2 to open the form filtered to the desired record.

I tried this: DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName

The only thing is that the second time I open Form2 I open it from a different form than Form1 and the ID value does not exist on the new calling form.

To recap: 3 forms: Form1, Form2, Form3.

Form2 can be called from Form1 or Form3. The first time it is ever called is from Form1 - 100% of the time - forever. When this happens an Event_ID is passed from Form1 to Form2 so they are related.

The second time Form2 is called is from Form3 - 100% of the time - forever. Form3 does not have an Event_ID that I can pass using the value of "Me.ControlName."

I just want Form2 to open from Form3 to the most recent record in the table that Form2 is bound too - without bringing in the entire recordset via the code that I show creating a Recordset.

Is that possible?

I should note that only Form1 has a Date/Time stamp - it doesn't exist on Form2 or Form3. In other words any Date/Time stamp is found in the same record that has the Event_ID which cannot be directly accessed from Form3 when opening Form2.

A solution likely entails somehow looking at the autonumber ID of the table bound to Form2 but I really don't know how to do that.

Zydeceltico · Mar 27, 2019

pbaldy said:
Using the second link from post 2 to open the form filtered to the desired record.

Or is there a way to add a filter to this code that only returns the last 4 most recent records as a Recordset?

Code:

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindFirst "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
    End If
  End If
End Sub

pbaldy · Mar 27, 2019

Set rs = CurrentDb.OpenRecordset("SELECT TOP 4...FROM...ORDER BY...", dbOpenDynaset)

Zydeceltico · Mar 27, 2019

pbaldy said:
Set rs = CurrentDb.OpenRecordset("SELECT TOP 4...FROM...ORDER BY...", dbOpenDynaset)

Now I know how to use SQL with VBA - kinda - Thanks!

pbaldy · Mar 27, 2019

No problem. It would be more common to use a variable for the SQL. It would look like:

http://www.baldyweb.com/ImmediateWindow.htm

Open form ignoring On Load Event (1 Viewer)

Registered User.

Wino Moderator

I’m here to help

Registered User.

Wino Moderator

Nifty Access Guy

Registered User.

Nifty Access Guy

Registered User.

Registered User.

Wino Moderator

Registered User.

You've got your good things, and you've got mine.

Wino Moderator

Registered User.

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Similar threads

Users who are viewing this thread