Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: help with a simple Function (syntax and var types), please

    I am not used to Functions, trying to do the following.

    From an unbound text box, I want to call a function to calculate and return a value. The calculation is based on input from other text boxes incl. the current choice in a combo box, they are all numbers, I am not sure if I must use conversion anywhere in the function. Is there a simple way to just make sure that what is passed on will be handled as numeric values, then passed back as such as well? The text box is set to standard numeric format, does that setting affect anything?

    I am receiving #error and #name messages in the unbound text box as I try new settings to figure out what is wrong.

    Question 1: how do I refer to the text fields, what should I encose them in, brackets, or double quotes or not at all? (note that it is the value in the text field BEFORE the record is saved I want to use, so I do not want to refer to what is stored in that field in the underlying record...) I guess that naming the text box txtFieldname and referring to txtFieldname is one way of doing this.
    Example on the control source of the unbound text box:
    =fNewAvg("me.txtclient_number";"me.cboRIC";"Me.txt share_price_after_comm";"Me.txtamount_of_shares_bo ught")

    or

    =fNewAvg([me.txtclient_number];[me.cboRIC];[Me.txtshare_price_after_comm];[Me.txtamount_of_shares_bought])


    Question 2:
    So, there is 4 parametres. Then, to simplify the names, I hope it is possible to rename the references to the parametres on-the-fly in the Function, trying this?)

    Public Function fNewAvg(client_number As Long, RIC As Long, share_price_after_comm As Long, NofShares As Long) As Long

    Then the function:
    (this does not make sense, but I add these to each other just to test the function, to see if I get a result):
    fNewAvg = client_number + RIC + share_price_after_comm + NofShares

    Question 3:
    When that works, I am also trying to set a record source for that function, like this:
    ' rstHoldings = "(SELECT tbl_holdings.CID, tbl_holdings.SID, tbl_holdings.prev_avg, tbl_holdings.prev_bal, tbl_holdings.new_avg, tbl_holdings.new_bal FROM tbl_Holdings WHERE (((tbl_holdings.CID)=client_number) AND ((tbl_holdings.SID)=RIC)))"

    Now, how do I refer to fields in that record source from the calculation? (If there is a match, there is only one record in the rst.)

    Here is my try:
    functionname = ((tbl_Holdings.new_bal * tbl_Holdings.new_avg) + (NofShares * share_price_after_comm)) / (tbl_Holdings.new_bal + NofShares)

    where NofShares and share_price_after_comm is variables defined in the function, values passed on from the calling form field.

    I think that I lack some code to identify the record - even if only one ? - in the rst before referring to any of its "fields"?

    Anyone got time to help me with correcting this code?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: help with a simple Function (syntax and var types), please

    Originally posted by kedaniel
    I am not used to Functions, trying to do the following.

    From an unbound text box, I want to call a function to calculate and return a value. The calculation is based on input from other text boxes incl. the current choice in a combo box, they are all numbers, I am not sure if I must use conversion anywhere in the function. Is there a simple way to just make sure that what is passed on will be handled as numeric values, then passed back as such as well? The text box is set to standard numeric format, does that setting affect anything?

    I am receiving #error and #name messages in the unbound text box as I try new settings to figure out what is wrong.

    Question 1: how do I refer to the text fields, what should I encose them in, brackets, or double quotes or not at all? (note that it is the value in the text field BEFORE the record is saved I want to use, so I do not want to refer to what is stored in that field in the underlying record...) I guess that naming the text box txtFieldname and referring to txtFieldname is one way of doing this.
    Example on the control source of the unbound text box:
    =fNewAvg("me.txtclient_number";"me.cboRIC";"Me.txt share_price_after_comm";"Me.txtamount_of_shares_bo ught")

    or

    =fNewAvg([me.txtclient_number];[me.cboRIC];[Me.txtshare_price_after_comm];[Me.txtamount_of_shares_bought])


    Question 2:
    So, there is 4 parametres. Then, to simplify the names, I hope it is possible to rename the references to the parametres on-the-fly in the Function, trying this?)

    Public Function fNewAvg(client_number As Long, RIC As Long, share_price_after_comm As Long, NofShares As Long) As Long

    Then the function:
    (this does not make sense, but I add these to each other just to test the function, to see if I get a result):
    fNewAvg = client_number + RIC + share_price_after_comm + NofShares

    Question 3:
    When that works, I am also trying to set a record source for that function, like this:
    ' rstHoldings = "(SELECT tbl_holdings.CID, tbl_holdings.SID, tbl_holdings.prev_avg, tbl_holdings.prev_bal, tbl_holdings.new_avg, tbl_holdings.new_bal FROM tbl_Holdings WHERE (((tbl_holdings.CID)=client_number) AND ((tbl_holdings.SID)=RIC)))"

    Now, how do I refer to fields in that record source from the calculation? (If there is a match, there is only one record in the rst.)

    Here is my try:
    functionname = ((tbl_Holdings.new_bal * tbl_Holdings.new_avg) + (NofShares * share_price_after_comm)) / (tbl_Holdings.new_bal + NofShares)

    where NofShares and share_price_after_comm is variables defined in the function, values passed on from the calling form field.

    I think that I lack some code to identify the record - even if only one ? - in the rst before referring to any of its "fields"?

    Anyone got time to help me with correcting this code?
    Is the function in the same project or is it in a module?

    If in a project then reference the text controls by their names Ex: CountTxt.Value

    If in a module then you'll need to reference them with the corresponding Form that the control is on Ex: Forms![My Form Name Here]![Text Control Name Here].Value

    Since text controls values are text I recommend that you perform the required and appropriate numeric conversion as part of your calculations Ex:

    MyText.Value=CInt(Text1.Value)-CInt(Text2.Value)+CInt(Text4.Value)/CInt(Text5.Value)

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Re: help with a simple Function (syntax and var types), please

    Originally posted by M Owen
    Is the function in the same project or is it in a module?

    MyText.Value=CInt(Text1.Value)-CInt(Text2.Value)+CInt(Text4.Value)/CInt(Text5.Value)
    Yes, in a module, so I will use the Forms! references, thanks.

    When I try only this for a test:

    Public Function fNewAvg(client_number As Long, RIC As Long, share_price_after_comm As Long, NofShares As Long) As Long

    fNewAvg = CInt(client_number) + CInt(RIC) + CInt(share_price_after_comm) + CInt(NofShares)

    (plus some - insufficient I think - exit coding)

    -l then I get the following error, and have to shut down Access using task manager as I cannot close the error message without it poping up the next microsecond:

    "Object variable or With block variable not set."

    The control source now looks like this:

    =fNewAvg(Forms!frmBuy4_trader!txtclient_number.Val ue;
    Forms!frmBuy4_trader!cboRIC.Value;
    Forms!frmBuy4_trader!txtshare_price_after_comm.Val ue;
    Forms!frmBuy4_trader!txtamount_of_shares_bought.Va lue)

    Which variable? That is the only code...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •