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.

 

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

 

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

 

[Gasman]

Something like this

Public Function AgeYears(ByVal datBirthDate As Date) As Integer
  ' Comments: Returns the age in years
  ' Params  : datBirthDate    Date to check
' Returns : Number of years
  ' Source  : Total Visual SourceBook
  On Error GoTo PROC_ERR

  Dim intYears As Integer

  intYears = Year(Now) - Year(datBirthDate)

  If DateSerial(Year(Now), Month(datBirthDate), Day(datBirthDate)) > Now Then
    ' Subtract a year if birthday hasn't arrived this year
    intYears = intYears - 1
  End If

  AgeYears = intYears

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
  Resume PROC_EXIT
End Function

Took me a while to find a function like that now.

Could omit intYears?

 

[HillTJ]

So, I tried @theDBguy's recommendation from post #6 & got 'Compile Error' Method or Data Member Not Found. Also tried

Me.[ApiaryID].DefaultValue = CLng(Me.OpenArgs)

, thinking that OpenArgs had to be converted to long from string. Nope.

Interestingly, if I start typing in the line of code from post #6, intelasense does not provide me with an option to pick 'DefaultValue'.

ApiaryID is long data type
Ideas?

 

[MajP]

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

I do that often, but mainly because I am lazy and try to avoid typing. Normally my procedures have pretty long descriptive names so I know what they do. If I have to write that long name in the function multiple times I use a short version.
This one is not that long but typing strOut 4 times is easier than typing CombineFilters 4 times.

Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
  Dim FilterCombiner As String
  Dim i As Integer
  Dim strOut As String
 
  If And_Or = ct_And Then
    FilterCombiner = " AND "
  Else
    FilterCombiner = " OR "
  End If
 
  For i = 0 To UBound(Filters)
    If Filters(i) <> "" Then
      If strOut = "" Then
        strOut = Filters(i)
      Else
        strOut = strOut & FilterCombiner & Filters(i)
      End If
    End If
  Next i
  CombineFilters = strOut
End Function

If you are going to reference recordset fields multiple times, it is slightly more efficient to save them to variables first since there is less overhead to reference a variable than a recordset field.

I believe someone posted recently an example where this can drastically improve performance.

 

[MajP]

Method or Data Member Not Found.

The first guess is that you have a field called ApiaryID, but not a control with the same name. Maybe your control is something like txtBxApiaryID.
A field on a form without a control only has a Value property. If you have a control and a field with the same name you will get the properties of the control.

If you type ApiaryID.Defaultvalue and it is a field not a control you get the method or data member not found because that object only has a value property.