Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    81

    Unanswered: SQL function on an Access Form

    I have a SQL function that take input params and return a single value. I want to call this function from an Access Form/Report and have the value displayed in a control on the Form. The input params for the function comes from data in the table the Form is bound to.

    I can get the form to show a record set based on a control on the form from stored procedures but I don't understand how to display the data directly from a SQL function on the .adp Form. How do I do this?

    Bjorn

  2. #2
    Join Date
    Nov 2003
    Location
    Makati City, Philippines
    Posts
    14

    Re: SQL function on an Access Form

    i've been wanting to do the same thing but unfortunately i still cannot. i've been trying to read several sql books including Mastering SQL just to look for the answer. for the meantime what i do is i run the ff. sample:

    dim RetVal as Variant (or specific data type if you wish)

    set myDB = CurrentDB
    set rsInfo = myDB.OpenRecordset([SQL command that returns a single value])

    ' then I do the ff. and save the value return to a variable

    With rsInfo
    .MoveFirst
    RetVal = ![Fieldname or return fieldname]
    .Close
    End With

    ' you can then have the variable be displayed in one of your form's control

    Set rsInfo = Nothing
    Set myDB = Nothing

    if you happen to get the SQL command that would solve the same problem we have, could you pass it on to me. if you don't mind.

    cheers,
    ReggieSter


    Originally posted by Bjorn
    I have a SQL function that take input params and return a single value. I want to call this function from an Access Form/Report and have the value displayed in a control on the Form. The input params for the function comes from data in the table the Form is bound to.

    I can get the form to show a record set based on a control on the form from stored procedures but I don't understand how to display the data directly from a SQL function on the .adp Form. How do I do this?

    Bjorn

  3. #3
    Join Date
    Jan 2003
    Posts
    81
    Thanks! I will try it. It does not seem ideal as you still have to deal with a record set to get only one value but I will work with it for now.

    Bjorn

  4. #4
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    Use DLookup. You can use variables in VBA to make the DLookup command more dynamic, using arguments passed into your sub or function.

    DLookup(<field_name>,<table_name>,<criteria>)

    <criteria> is a string, a WHERE clause without the 'WHERE'
    <field_name> can be an expression

    There are other domain functions, DMax, DFirst, etc that may be more useful, depending on your needs. Look them up in help.

  5. #5
    Join Date
    Jan 2003
    Posts
    81
    Thanks!

Posting Permissions

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