Why is my code different than examples?

[jaykellogg]

I see many examples utilizing this syntax: Forms!FormName.ComboName

Yet I have to write mine like this: Form_FormName.ComboName

I've been dealing with it for awhile now so i have a lot of code already written like this. But, my concern is that I might have to re-write all of the code in the future if I am using an "old" reference.

thanks for the help!

Jay

 

[RuralGuy]

The example syntax is using the Forms collection. You are referencing your current Form from the current form and could use "Me" instead of "Form_FormName".

 

[jaykellogg]

RG, forgive me but can you clarify if the referencing that I am using is "ok"?

I have a main form with a tabbed control. Each tab has a form on it with subforms. I've been putting all of my code in a module and utilizing functions that the different forms/subforms call. So, I'm not sure if using "Me" would work.

I guess what i'm really worried about is if my database is using an old reference library. And at some point I am going to have to change the reference library and consequently update all of my code.

 

[RuralGuy]

So your code is *not* in the code (class) module for each form? If the code is in Standard Modules you are missing out on a ton of features of Form Class Modules.

 

[jaykellogg]

I am not 100% sure of the difference between class module and standard module. What I have done is create a module called modEstimate. When the form frmBid is opened, it and all of the other forms and subforms on it have the code in modEstimate. The only code in each form is to call a Public Function in modEstimate.

Many of the forms are using the same Public Functions to calculate and update costs.

I thought that this created a single spot to see all of the code. And was necessary to compartmentalize code that performed the same function.

I've been using Access for 20+ years but have never had any formal education on software. If I am making it more difficult or not following normal practice, I am not opposed to change.

 

[Galaxiom]

Yet I have to write mine like this: Form_FormName.ComboName

This syntax is referring to the form object via its VBA module and should never be used.

Calling such a reference without the form already loaded will cause a hidden instance of the form to be opened. If the form is then commanded open there will two instances of the form loaded. Both will have the same name.

 

[RuralGuy]

@Galaxiom - never knew that nor have I ever used that syntax. Thanks.

@jaykellogg - I suspect you could do a global search and replace for "Form_" and replace with "Forms." That looks like it would work but I would try it on a backup first to see if it breaks anything. You are also correct that "Me." would not work from a Standard Module which is what you have.

 

[Galaxiom]

@jaykellogg - I suspect you could do a global search and replace for "Form_" and replace with "Forms."

Unfortunately that would break any Form event procedures.
For example:
Form_Current

 

[jaykellogg]

@Galaxiom - I will quit using this sytanx! thanks for the info

@RuralGuy - I will utilize the replace method to correct. I typically copy the database every couple of days and put in a dated folder. Helps when I really blow something up!

 

[jaykellogg]

Unfortunately that would break any Form event procedures.
For example:
Form_Current

not sure what you mean by this. I will have to do some reading! Do you mean the events like Deactive, GotFocus, LostFocus... will not work?

I will switch all of the incorrect syntax then play with the program and see if I am getting weird responses.

 

[Galaxiom]

not sure what you mean by this. I will have to do some reading! Do you mean the events like Deactive, GotFocus, LostFocus... will not work?

Event Procedure declarations use this syntax.

Private Sub ObjectName_EventName()

eg
Private Sub Combo1_Click()

The Form procedures begin with Form_

 

[jdraw]

jay,

Can you tell us more about this database?
Are you developing it now?
Do you have documented requirements/specification?


These links may be helpful.
https://msdn.microsoft.com/en-us/library/office/ff195841.aspx Form Object
https://msdn.microsoft.com/en-us/library/office/jj249049.aspx Form Events

 

[Galaxiom]

You could to a global Find and Replace in two steps.

First, replace "Form_" With "Forms!"

Then fix the Form Procedure declarations by converting them back.
Replace "Sub Forms!" with "Sub Form_"

 

[jaykellogg]

jay,

Can you tell us more about this database?
Are you developing it now?
Do you have documented requirements/specification?

The database will be used to develop construction estimates. I want to have custom estimating software so that I can build in features that other estimating software doesn't have. It is currently up and running and I have completed multiple estimates. I am getting a few annoying error messages that I need to eliminate. Now that it is working, I want to start adding the additional features. I don't have any documented requirements/specifications.

These main forms and many other subforms are done.

• Labor form enables user to enter base rate, fringe rate, taxes (with threshold caps), Health Insurance, Paid Time Off rules, OT rules, Union/Non-Union rules. All by company and by estimate. Workers Comp can be specified for the estimate or individual activities. OT rules can be specified for the estimate or individual activities.
• Equipment form calculates operating expenses (fuel cost per gallon can be changed and all equipment rates will be recalculated), lube rate, maintenance rate, Ownership portion calculated by depreciation and or Rental rate.
• Estimate form enables the estimate to be developed by keying in individual information (labor, equipment, materials, subcontractors...) by selecting a crew code or by selecting a Activity. If a Activity is chosen, the program pulls in the crew (with a historical production), the materials and subcontractor and no additional data entry is needed.

Here are some of the features I am going to be adding.

• Tree View to Estimate form
• Need to come up with a new way to choose an Estimate Activity since it is too hard to scroll through them and find the correct entry. I have over 3000 records in my activity library and expect it to grow ten fold.
• New form to calculate theoretical productions based on Caterpillars equipment data.
• Improve the process to update material and subcontractor unit prices on a completed estimate.
• Comparison of Estimated productions to Historical productions.
• Evaluation of the completed estimate to advise the estimator of items they may want to review prior to submitting the estimate.

 

[jaykellogg]

You could to a global Find and Replace in two steps.

First, replace "Form_" With "Forms!"

Then fix the Form Procedure declarations by converting them back.
Replace "Sub Forms!" with "Sub Form_"

I believe all I will have to do is the First replace. I don't recall ever using the "Sub Form_" syntax.

 

[moke123]

Many of the forms are using the same Public Functions to calculate and update costs.

If you need to reference the active form you could always pass it to the procedure in your arguments or use screen.activeform

 

[jdraw]

I am getting a few annoying error messages that I need to eliminate.

It might be helpful for you and readers if you listed some of these and any associated code.

Do you make use of error handlers in your code?

Here are some of the features I am going to be adding.

Tree View to Estimate form
Need to come up with a new way to choose an Estimate Activity since it is too hard to scroll through them and find the correct entry. I have over 3000 records in my activity library and expect it to grow ten fold.
New form to calculate theoretical productions based on Caterpillars equipment data.
Improve the process to update material and subcontractor unit prices on a completed estimate.
Comparison of Estimated productions to Historical productions.
Evaluation of the completed estimate to advise the estimator of items they may want to review prior to submitting the estimate.

That's a lot of features to add to a developing yet operational system, especially with no specifications.
Do you have a data model helping you with your incremental design?

Good luck.

 

[jaykellogg]

You could to a global Find and Replace in two steps.

First, replace "Form_" With "Forms!"

Then fix the Form Procedure declarations by converting them back.
Replace "Sub Forms!" with "Sub Form_"

I tried updating one of the procedures and I get the error message:

Run-time error '2465:
K5-Estimating can't find the field 'frmBidSubSetup' referred to in your expression.

Here is the procedure:
Public Function PrevailingWageUpdate()
'Update the Unit Price in the Resource Table to equal the Labor Rate based on the Prevailing Wage selection
If Forms!frmBid!frmBidSubSetup.Form.PrevailingWages = "Y" Then
strSQL = "UPDATE Resource INNER JOIN Labor ON Resource.ResourceID = Labor.LaborID SET Resource.RUnitPrice = [Labor].[StraightRatePU];"
CurrentDb.Execute strSQL, dbFailOnError
Else
strSQL = "UPDATE Resource INNER JOIN Labor ON Resource.ResourceID = Labor.LaborID SET Resource.RUnitPrice = [Labor].[StraightRateNP];"
CurrentDb.Execute strSQL, dbFailOnError
End If

'Since Prevailing Wage was changed, the cost in the estimate needs updated. Update ActivityResource table.
Forms!frmBid.Refresh
RecalcSetupARLaborCostAll

'Jay to do: I cant figure out how to get the BidItem costs to update on the screen. They are updated in the tables but only showing if the form is closed and re-opened.
'Forms!frmBid.Form!frmBidSummary.Requery
'Forms!frmBid.Form!frmBidSubPricingDetail.Requery

End Function

The main form = frmBid. The subform = frmBidSubSetup. It is on a tabbed control on frmBid.
On the subform there is a combobox named PrevailingWages. When the user changes PrevailingWages, AfterUpdate runs the procedure named PrevailingWageUpdate.

 

[jaykellogg]

I tried updating one of the procedures and I get the error message:

Here is the old code. BTW, I noticed that I had taken some shortcuts, here is a better example.

Public Function PrevailingWageUpdate()
'Update the Unit Price in the Resource Table to equal the Labor Rate based on the Prevailing Wage selection
Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim strSQL As String

If Form_frmBidSubSetup.Form.PrevailingWages = "Y" Then
strSQL = "UPDATE Resource INNER JOIN Labor ON Resource.ResourceID = Labor.LaborID SET Resource.RUnitPrice = [Labor].[StraightRatePU];"
CurrentDb.Execute strSQL, dbFailOnError
Else
strSQL = "UPDATE Resource INNER JOIN Labor ON Resource.ResourceID = Labor.LaborID SET Resource.RUnitPrice = [Labor].[StraightRateNP];"
CurrentDb.Execute strSQL, dbFailOnError
End If

'Since Prevailing Wage was changed, the cost in the estimate needs updated. Update ActivityResource table.
Form_frmBid.Refresh
RecalcSetupARLaborCostAll

Set rst = Nothing
Set dbs = Nothing

'Jay to do: I cant figure out how to get the BidItem costs to update on the screen. They are updated in the tables but only showing if the form is closed and re-opened.
'Forms!frmBid.Form!frmBidSummary.Requery
'Forms!frmBid.Form!frmBidSubPricingDetail.Requery

End Function

 

[RuralGuy]

Is the name of your SubFormControl on the "frmBid" Form the same as the SubForm it is displaying? In other words, are they both named "frmBidSubSetup"?
FYI: I tend to use the Dot "." rather than the Bang "!" as it seems to have less issues when running.
You did not state which line was failing.