Use tempvars to open a related form

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

 

[HillTJ]

@MajP , You were correct. I added the control to the form, made it invisible. All good. I had the field in the recordset but not the control on the form!. Cheers.

 

[ebs17]

Code according to #16: The recordset can contain a lot of records despite filtering. However, a default value can only hold one value. Therefore, carrying through a loop is unfavorable either way.
Additionally: When you filter, it can easily happen that the recordset is empty. That should also be taken into account.

 

[spaLOGICng]

This is just my 1 1/2 cents…

I do use TempVars frequently. I have created shortened functions and they work great.

As for the phantom record, I would use the After Insert or After Update as opposed to the the Before events. The Before Events are cancellable and that is a problem.

Glad you solved your conundrum.

 

[HillTJ]

@spaLOGICng , thanks. What you said makes sense to me. Will review my code. Cheers

 

[HillTJ]

@Pat Hartman , and other contributers. Thanks & thanks to all. I understand now. Will make the changes you recommend & make sure all my forms reflect this practice in future. That is, 'Before_Update' to validate data and/or back out & 'After_Update' to add FK after the data has been validated by 'Before_Update'. Thanks

 

[HillTJ]

@Pat Hartman , sorry Pat I said it back to front. You make total sense.

 

[Gasman]

@Pat Hartman , and other contributers. Thanks & thanks to all. I understand now. Will make the changes you recommend & make sure all my forms reflect this practice in future. That is, 'Before_Update' to validate data and/or back out & 'After_Update' to add FK after the data has been validated by 'Before_Update'. Thanks

Did you not read Pat's reply?

 

[HillTJ]

@Gasman, I did read Pats reply. I'm getting myself in a knot as my project is driving me nuts. My other thread is below. I appreciate all you do & are mindful of not driving you nuts too!

Help with Development of CRM Model

Friends, I’ve started a new project which is to be a CRM system along the lines of that published by CRM Design Tutorial. I like the flexibility that this design provides. I have built the database tables and relationships. A design consideration is the 1:1 relationship of PartyID to both...

www.access-programmers.co.uk

 

[HillTJ]

All, got it sorted. Using Before_Update to validate data & if me.newrecord to add Fk. Will have a go with Before_Insert as an alternative approach to the if me.newrecord in the Before_Update event. Took me while to get my head round these important events. Thanks for the comprehensive answers.

 

[HillTJ]

@Pat Hartman , Thanks. Will watch the video & have a look at the database.