Use tempvars to open a related form (1 Viewer)

[HillTJ]

Friends, i've been passing the foreign key to the related form i wish to open & on the receiving form using the onload event of the form to populate that key to the new form. I realised that ifthe user closes the form without entering data, then a 'fantom' record is added that contains the fk. Makes sense. Been thinking of changing the code to fire instead on the 'beforeupdate' event. Code must only add a fk for a new record & not save form if the user closes the form without entering data. Just a bit confused about how to code the beforeupdate event & cannot find a specific example. Appreciate any hep.

 

[Gasman]

I think you use the BeforeInsert event to set the FK

 

[Pat Hartman]

I realised that ifthe user closes the form without entering data, then a 'fantom' record is added that contains the fk. Makes sense.

Not of you do this correctly. Use the BeforeInsert event. This event only runs when a new record is added and does not run until the first character has been typed into the form. Therefore, the user dirties the form, NOT your code.

In addition to placing the "populate the FK" code in the correct event, you also probably need at least a minimum amount of validation code in the form's BeforeUpdate event. Surely there must be some field that is required in addition to the FK. Make sure the required fields are present by checking them in the form's BeforeUPdate event. You also should do sanity checks where ever possible, especially on dates. It is a really simple typo to enter 1/1/203. This is a valid date but almost certainly not rational.

 

[Edgar_]

If you close the form without entering anything, but a new record still appears, it means your code is making that record. Can you show the code you use when the form loads and when you open the form? That's where you'll find the solution.

 

[HillTJ]

I've used the following code on the Form_Current event.

Private Sub Form_Current()
If Me.NewRecord Then
        Me.[ApiaryID] = Me.OpenArgs
    End If
End Sub

So, as written, If it is at a new record & the user saves without entering any fields, then a new record is created with only the [ApiaryID] having a value.

Instead, use this code on the before_update event ?

 

[theDBguy]

I've used the following code on the Form_Current event.

Private Sub Form_Current()
If Me.NewRecord Then
        Me.[ApiaryID] = Me.OpenArgs
    End If
End Sub

So, as written, If it is at a new record & the user saves without entering any fields, then a new record is created with only the [ApiaryID] having a value.

Instead, use this code on the before_update event ?

Lots of ways to do it. Me, I prefer the Open event.

Private Sub Form_Open(Cancel As Integer)
Me.[ApiaryID].DefaultValue = """" & Me.OpenArgs & """"
End Sub

Sent from phone...

 

[HillTJ]

@theDBguy , so if I understand the default value is only applied to a new record?. If the user closes the record without entering any data then no 'phantom' record is added unlike my technique where it is possible as I've assigned a value to the [apiaryID] field when the form opens?

 

[ebs17]

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' test whether all mandatory fields have been filled out
    ' if yes, continue, otherwise back (further entries or cancellation)
    If Me.NewRecord Then
        If Not IsNull(Me.OpenArgs) Then Me.txtForeignID = CLng(Me.OpenArgs)
    End If
End Sub

 

[HillTJ]

@ebs17 , thanks. Got the concept. Will give it a go. Thanks & thanks to everyone else whom assisted. As always I've learnt something new that makes me a bit better with my coding.

 

[Oviziest]

So, it sounds like you're dealing with a situation where a "phantom" record gets added if the user closes the form without entering data. To avoid this, you're thinking of triggering the code on the 'beforeupdate' event instead of the 'onload' event. The idea is to make sure the code only adds a foreign key for a new record and doesn't save the form if the user closes it without entering anything. Coding the 'beforeupdate' event might be a bit tricky, and I get that finding an example can be tough. How about focusing on creating a conditional check within the 'beforeupdate' event to handle adding the foreign key for new records only? That way, it won't save the form without any entered data

 

[Minty]

Setting default values as per the DBGuys suggestion in the open event is the easiest way to handle needing to pre-populate without "dirtying" the new record.

 

[Gasman]

TBH, I see nothing wrong with @Pat Hartman's method.
Each are one liners.

 

[theDBguy]

@theDBguy , so if I understand the default value is only applied to a new record?. If the user closes the record without entering any data then no 'phantom' record is added unlike my technique where it is possible as I've assigned a value to the [apiaryID] field when the form opens?

Yes, that is correct.

 

[Pat Hartman]

So, it sounds like you're dealing with a situation where a "phantom" record gets added if the user closes the form without entering data. To avoid this, you're thinking of triggering the code on the 'beforeupdate' event instead of the 'onload' event. The idea is to make sure the code only adds a foreign key for a new record and doesn't save the form if the user closes it without entering anything. Coding the 'beforeupdate' event might be a bit tricky, and I get that finding an example can be tough. How about focusing on creating a conditional check within the 'beforeupdate' event to handle adding the foreign key for new records only? That way, it won't save the form without any entered data

Looks like someone new is using ChatGpt to answer questions that have already been answered. Please don't do this. If you don't know the answer, how will you determine if ChatGpt gave you good advice. What you posted is close but wishy-washy and not 100% accurate since simply adding the FK isn't sufficient to make a valid record. Records always need data other than the FK and just because the BeforeUpdate event is running doesn't mean that there is actual data to be saved. If you type in a control, that dirties it. If you use the esc key, you clear the typing and reset the dirty flag. If you simply backspace as too many do, the control is "empty" but it is still dirty and so the BeforeUpdate event will still run.

 

[Uncle Gizmo]

I use the same method as the dbguy.

Pass the info to the controls default value.

See this video:-
This Video shows how to directly transfer data into a Forms controls using an SQL statement. By transferring the information into the control’s “Default Value” then the information isn’t saved. A save is only forced when new information is added to the record by the user..

Which is a video number 6 in my blog here:-

Move Info from One Table to Another - Nifty Access

Move Info from One Table to Another A brief summary summary of the "OP's" (Original Posters) question HERE. I've done a set of YouTube videos to answer the question. The first answer demonstrates the INSERT INTO method of adding records to another table. This method is where you update the...

www.niftyaccess.com

which demonstrates the whole process from beginning to end.

 

[Gasman]

I use the same method as the dbguy.

Pass the info to the controls default value.

See this video:-
This Video shows how to directly transfer data into a Forms controls using an SQL statement. By transferring the information into the control’s “Default Value” then the information isn’t saved. A save is only forced when new information is added to the record by the user..

Which is a video number 6 in my blog here:-

Move Info from One Table to Another - Nifty Access

Move Info from One Table to Another A brief summary summary of the "OP's" (Original Posters) question HERE. I've done a set of YouTube videos to answer the question. The first answer demonstrates the INSERT INTO method of adding records to another table. This method is where you update the...

www.niftyaccess.com

which demonstrates the whole process from beginning to end.

 

[Gasman]

Tony,
Why all the intermediary variables?
Why not assign direct from recordset?

 

[Uncle Gizmo]

Why all the intermediary variables?
Why not assign direct from recordset?

Hi Gasman,
Cheers for pointing out the room for trimming down the code. Spot on! What you're talking about is often called "refactoring, taking out the bits that aren't needed.

The code I put up was indeed in the middle of its journey. I got the main bits down, but it still needs a bit of tidying up. Your suggestion for simplification is bang on the money.

This is the code refactored:- (I got a ChatGPT to do it!)

Option Compare Database
Option Explicit

Public Sub fRSL_DefaultValueInsert() 
    On Error GoTo Error_Handler
    
    Dim curDB As DAO.Database
    Dim rsAwarded As DAO.Recordset
    Set curDB = CurrentDb
    
    Dim strSQL_RSL As String
    strSQL_RSL = "SELECT ShortTitle, ProjectTitle, Funder, Scheme, DPAGInvestigator, X5reference, FunderType FROM tblApplications WHERE (((Awarded)=True));"
    
    Set rsAwarded = curDB.OpenRecordset(strSQL_RSL, dbOpenForwardOnly)
    
    Do Until rsAwarded.EOF
        Dim strFrmName As String
        strFrmName = "frmAward"
        DoCmd.OpenForm strFrmName, , , , acFormAdd
        
        With Forms(strFrmName)
            .txtShortTitle.DefaultValue = Chr(34) & rsAwarded!ShortTitle & Chr(34)
            .txtProjectTitle.DefaultValue = Chr(34) & rsAwarded!ProjectTitle & Chr(34)
            .txtFunder.DefaultValue = Chr(34) & rsAwarded!Funder & Chr(34)
            .txtScheme.DefaultValue = Chr(34) & rsAwarded!Scheme & Chr(34)
            .txtDPAGPI.DefaultValue = Chr(34) & rsAwarded!DPAGInvestigator & Chr(34)
            .txtX5reference.DefaultValue = Chr(34) & rsAwarded!X5reference & Chr(34)
            .txtFunderType.DefaultValue = Chr(34) & rsAwarded!FunderType & Chr(34)
            .txtExtraFld3.DefaultValue = Chr(34) & "Default Value Method" & Chr(34)
        End With
        
        rsAwarded.MoveNext
    Loop
    
Exit_ErrorHandler:
    rsAwarded.Close
    Set rsAwarded = Nothing
    Set curDB = Nothing
    Exit Sub

Error_Handler:
    MsgBox "Error From --- basAgeRangeCount, fRSL_DefaultValueInsert --- Error Number >>>  " _
    & Err.Number & "  <<< Error Description >>  " & Err.Description, , "Your Application Name"
    Resume Exit_ErrorHandler

End Sub

 

[HillTJ]

People thanks. I"ve got the concept. Really appreciate your points of view as always.

 

[Gasman]

Tony,
I was asking, as I have seen the same in functions, where a variable is set to the result of the function, THEN the name of the function is set from that variable.
See that all the time?