Function vs sub (office 2000 on windows 2000)

Topic

New Reply

I am having a great deal of trouble calling a sub from an on click event on a tabbed form. I am trying to reduce the size of my database by creating a module to handle a routine that is called from about 10 different places on a form. I wanted to write the code once in a module rather than have it exist 10 different times in on click events in the form. So I moved the code to a module and tried to call the sub from the on click events on the various places it occurs on the form. The first on click event I tried was on a tree tabbed sub form. The sub makes various controls visible and sets some other properties depending on which tab is clicked. Getting to the issue!!!!!! It doesn’t work. When I go to compile the sub I get an “Invalid use of property” error. The steps I used to create the module are as follows: I created a new module and named it. I added a sub to the module which sets the different properties on the form. In the on click event of the tabbed sub form I call the sub by its name exactly. Can someone guide me through this issue and let me know what I am doing wrong?

Thank You

Reply | Quote

Replies

How are you referring to the objects/controls on the form? If you are using Me at all, that’s your problem. You’ll need to refer to the form directly, like Forms(“frmMyForm”).MyControl , etc.

Reply | Quote

Yes, I did this already, thanks I changed all the reference using “Me!” and fully qualified the text. Any other suggestions?

thanks

Reply | Quote

Can you post the code?

Reply | Quote

Drew, thanks for getting back to me. I have been out sick. The code I am using is listed below:

This is the line of code used to call the module procedure:

Private Sub SUPSHIP_Negotiator_Position_Click()
SUPSHIP_Negotiator_Position
End Sub

This event is triggered from the on click event of a tab on a tab control on the form.

The code for the procedure in in the module is as follows:

Sub SUPSHIP_Negotiator_Position()

If IsNull(DLookup(“[Pricing_ID]”, “Pricing Detail EB”, “[Pricing_ID] = Forms![frmPricingTabbed]![Pricing_ID]”)) Then
DoCmd.SetWarnings False
Beep
MsgBox “There is no CONTRACTOR Pricing Detail for this pricing action, therefor no data is available to use as your default position. Please start your data input now!!!!”
End If
If IsNull(DLookup(“[Pricing_ID]”, “Pricing Detail TAR”, “[Pricing_ID] = Forms![frmPricingTabbed]![Pricing_ID]”)) Then
DoCmd.SetWarnings False
Beep
MsgBox “The TAR has not been completed for this Change Proposal. Enter Pricing Information directly ino the Negotiator input now!!!! If you are performing a desk TAR, please input your pricing Data directly into the negotiator position. If you are not performing a desk TAR please uncheck the Desk TAR block on the screen and enter your pricing data into the negotiator position. ”
Forms![frmPricingTabbed]![Desk_TAR].Visible = True
Forms![frmPricingTabbed]![Desk_TAR].Value = True
Forms![frmPricingTabbed]![Desk TAR Label].Visible = True

Exit Sub
End If

If IsNull(DLookup(“[Pricing_ID]”, “Pricing Detail SUPSHIP”, “[Pricing_ID] = Forms![frmPricingTabbed]![Pricing_ID]”)) Then
DoCmd.SetWarnings False
‘ Beep
‘ MsgBox “There is no SUPSHIP NEGOTIATOR Pricing Detail for this pricing action. To simplify input, the Technical Advisory Report (TAR)position Data will be used as the SUPSHIP NEGOTIATOR default position. Data may be edited if you decide not to use the TAR position!!!”
DoCmd.OpenQuery “APPEND SUPSHIP DETAIL”
Forms![frmPricingTabbed]![SOS_Rate_Name] = Forms![frmPricingTabbed]![TAR_Rate_Name]
Forms![frmPricingTabbed]![SOS_Esc_Matl] = Forms![frmPricingTabbed]![TAR_Esc_Matl]
Forms![frmPricingTabbed]![SOS_Matl_Fee] = Forms![frmPricingTabbed]![TAR_Matl_Fee]
Forms![frmPricingTabbed]![SOS_Labor_Fee] = Forms![frmPricingTabbed]![TAR_Labor_Fee]
Forms![frmPricingTabbed]![SOS_Des_Matl] = Forms![frmPricingTabbed]![TAR_Des_Matl]
Forms![frmPricingTabbed]![SOS_Rate_Version] = Forms![frmPricingTabbed]![TAR_Rate_Version]
Forms![frmPricingTabbed]![vfcm_sos] = DSum(“[Deesc_FCM]”, “Pricing Detail SUPSHIP”, “[Pricing_ID] = ‘” & Forms![frmPricingTabbed]![Pricing_ID] & “‘”)
Forms![frmPricingTabbed]![vdepreciation_sos] = DSum(“[Deesc_Depreciation]”, “Pricing Detail SUPSHIP”, “[Pricing_ID] = ‘” & Forms![frmPricingTabbed]![Pricing_ID] & “‘”)
Forms![frmPricingTabbed]![vlabor_sos] = DSum(“[Labor_Total]”, “Pricing Detail SUPSHIP”, “[Pricing_ID] = ‘” & Forms![frmPricingTabbed]![Pricing_ID] & “‘”)
Forms![frmPricingTabbed]![vLaw_Energy_sos] = Int(DSum(“[Law]”, “Pricing Detail SUPSHIP”, “[Pricing_ID] = ‘” & Forms![frmPricingTabbed]![Pricing_ID] & “‘”) + 0.5) + Int(DSum(“[Energy]”, “Pricing Detail SUPSHIP”, “[Pricing_ID] = ‘” & Forms![frmPricingTabbed]![Pricing_ID] & “‘”) + 0.5)
DoCmd.Requery
Forms![frmPricingTabbed]![Gov_NegID] = Forms![Splash Screen]![VIdCode]
DoCmd.SetWarnings True
End If

Forms![frmPricingTabbed]![Pricing Detail SUPSHIP].Form.Requery

End Sub

Thanks for any suggestions that you might have.

Kevin

Reply | Quote

Okay, first of all, when your code tells you there is an invalid property, it should be highlighting what it doesn’t ‘like’.

Next, just a cursory glance at your code, and two spots stick out. You have two objects that have spaces in their name. Desk TAR Label and Pricing Detail SUPSHIP. Spaces are BAD, when naming an object. In fact, the reason they are bad, is because your code won’t like dealing with the spaces sometimes. This may be one of them.

Reply | Quote

If your routine is called in 10 different places on the same form, there is no need to move the code to a standard module; you can keep it in the form module, where you don’t have to worry about how to refer to the form.

If you want to call the routine from different forms, you should indeed put the code in a standard module. In that case, you can refer to the form that calls the code as CodeContextObject instead of Me, e.g. CodeContextObject.NewRecord or CodeContextID.Requery.

Reply | Quote

Kevin,

How are you calling the sub?

Are you doing it in the Code behind the form or are you doing it from the property Sheet?

If you are doing it in the property sheet, then the procedure HAS to be a function and not a sub. Also you need to have an = sign before the name of the funcion, =MyFunction()

If you are calling it from code, can you post the line calling the sub and the sub declaration line?

Reply | Quote

If you have 10 copies of the same code then what you are doing should reduce the DB size a bit. However the code itself probably takes up little room in the DB. What you are doing in reality is a good coding practice by improving the readability/updateability of your DB.
Concerning the error message I suspect you are not referencing something correctly. ie Refering to a subform as a form or you have declared cntl as Control and then request a property specific to an edit control or even specifying a property that doesn’t exist or isn’t modifyable.

Reply | Quote