Use tempvars to open a related form

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

 

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

 

[Pat Hartman]

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?

How about if I answer? 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.

If you are only referencing the recordset fields once, I prefer to just use them rather than having intermediate variables.

 

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

 

[Pat Hartman]

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

"Drastically" means that the reference was inside a loop that was executed many times.

If you are only referencing the fields ONCE, then the intermediate variable only increases the time the code takes to run. I haven't run timing tests like this since my COBOL days. At some point the intermediate variable becomes more efficient. It all depends on how many times a loop must execute. Is it 10 times, is it 100, is it 1000? I don't know. The length of a name would not impact my choice to use an intermediate variable I would use copy paste to minimize typing.

 

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

 

[Pat Hartman]

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?

I'll answer that also, if you don't mind. It makes testing easier because you can see the return value before the function exits.

 

[Pat Hartman]

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

This is a fairly recent change to Access. This problem used to exist only with reports where Access would rewrite your RecordSource query and eliminate any column that wasn't bound to a control. Access did this because it knows it is smarter than you and it assumed you were sloppy and made a mistake.

They now are doing this with forms so if you want to reference a field in code, it MUST be added as a bound control to a form. I do this all the time because most of my tables have changeDT and changeBy columns which are not usually shown on the form so this particular change is seriously annoying for me. I use the values for debugging. So, when I put the hidden controls on my forms, I make them tiny and set their background color to bright yellow so they are easy to find. When you do this on a report, make sure that these hidden controls do not overlap any other control. On a form, you can hide them under other controls but you can't do that on a report because it interferes with the shrink/grow process which doesn't apply to forms.

 

[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

 

[Pat Hartman]

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.

That is the point. The Before events must be cancellable in order to prevent bad data from being saved. Your instruction is wrong. It is really important to understand what event is intended for what purpose. They are not interchangeable or random. If the data in a record is invalid, you WANT to cancel the insert/update. Why would you ever want to save a bad record and then try to fix it later?

When you use the BeforeInsert event, you get to populate the FK BEFORE the record is saved. If you use the AfterInsert event, the record has already been saved and you probably got an error that prevented the save anyway because if you have enforced RI and if the FK is required. If the FK is not required, even if you have enforced RI, you have created an orphan record. It is untied to any parent If you were able to save the orphan record, and update the FK in the AfterInsert event, that would force Access to save the record again.

In the BeforeUpdate event, you have validation code that ensures that required data is present and valid where possible, thereby preventing the saving of invalid/empty records- you can of course rely on the error messages generated by the database engine if you bother to define the required fields as required. That means things like sanity checks for dates. 1/1/203 is a valid date as far as Access is concerned but it is almost certainly a typo. So, you use the Cancel argument to prevent Access from attempting to save a bad record and give the user a chance to correct the error.

 

[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

 

[Pat Hartman]

NO ------ You need to add the FK BEFORE the record gets saved. The AfterUpdate event runs AFTER the record gets saved. So, that's after the horses have escaped from the barn. You have already saved BAD data. A child record without the FK is technically bad data even if you don't bother enforcing RI.

Also, technically, modifying a record in the AfterUpdate event puts the form into a never ending loop. When you set the FK, the form's dirty flag is set. That means that Access must save the record before closing the form so, that means it has to run the BeforeUpdate event again, save the record, and when that is done it runs the AfterUpdate event and that dirties the record so it has to run the BeforeUpdate event, save the record, and then run the AfterUpdate event again - are you getting the picture??? NEVER update the current record in the form's AfterUpdate event, EVER. There are only a few "NEVER, EVERs" in Access and this is one of them.

Earlier versions of Access used to freeze with a slight screen flicker when you did this. You needed to cntl-alt-del to get your PC back. Current versions are smarter and they recognize this type of recursion and break out of the loop gracefully.

My suggestion of using the BeforeInsert event is correct and the other suggestion to set the default for the control in the Load event is also correct. All other options are either outright wrong or have flaws. For example, you could set the FK in the form's BeforeUpdate event but if you do it there, you need to do it inside an IF statement since you should not touch the FK unless you are on a new record which has not yet been saved. The first two suggestions don't require an If since they run only ONCE and not each time you save a record.

 

[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