Use tempvars to open a related form

[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

 

[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.

 

[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?

 

[Uncle Gizmo]

Mind you, I see that Chat GPT has removed my error handling and replaced it with its own, which is bloody annoying! I'm arguing with it at the moment, trying to get it to use my preferred error handling, but it's like talking to a brick wall sometimes!

Chat GPT made a good suggestion in that I should not use the Form name directly as text but supply it as a variable, but then it goes and removes my variables for holding the function name and the module (or form name in this case) and replacing them with hard coded names! I pointed this out to ChatGPT but I'm just wasting my time. It's like arguing with the Wife!

 

[Uncle Gizmo]

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?

I'm not sure I'm grasping what you mean Paul?