Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2005
    Posts
    12

    Unanswered: Simple question...

    heya all,

    I'm Currently holding a table for Constants (ID,DESC,VAL)
    In addition I have a few other tables. Let's say one of them is table A with the single NUM number-type column.

    Now I want to create a Form which will show A.Num's value and a few calculations based on A.NUM and any of the constants.

    I tried to do that with a query by selecting both tables and doing the right calculations - but its only showing results of existing data and not letting me Add a new row to A (new values to A.NUM). I want the form to be used to alter data and create new rows in A.

    If you're asking why I don't just put the constant in the calculation, instead of getting it from a table - I want the user to be able to change the constants without having to change all the queries that rely on it...

    thanks,

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    suck your constants into some vars or form fields, populate your form from A, add some unbound boxes on the form with ControlSource something like:
    = [someFieldFromA] * myConstantZ

    example: getting your constants into formfields (assumes your constants table has only one row with ID=1).

    dim dabs as dao.database
    dim recs as dao.recordset
    dim rfld as dao.field
    set dabs = currentdb
    set recs = dabs.openrecordset(SELECT * FROM myCstTable WHERE ID=1)
    with recs
    for each rfld in recs.fields
    me(rfld.name) = rfld
    next
    end with
    set rfld = nothing
    recs.close
    set recs=nothing
    set dabs=nothing

    !!!requires that the boxes on the form have the same name as the fields in myCstTable!!! if you are showing more than one record from A, put these "constant fields" in the header/footer (.visible False if you like)

    that was DAO - ADO can probably do something similar.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2005
    Posts
    12
    Thanks A Lot !

    At first I didn't understand what I needed to do - I don't usually deal with the code (just doing a small project). But after some trial and error and code modification I finally got what I wanted!

    Again - Thanks !

  4. #4
    Join Date
    Oct 2005
    Posts
    12
    Heya... I've got one more question:
    Let's say I Have a table A containing a date field, which is a foreign key to Another Table B containing Date(PK) and Value.

    I want to assign the Value from B to a TextBox in a form in accordance to the date the user chooses in the form. I tried doing it as :
    ...
    Dim d as Date
    d = Me(DateTextBox.Name) //this gives d the value the user chose - it works fine
    set recs = dabs.openrecordset("SELECT [Value] FROM [B] WHERE [Date]=" & d)

    but I get no records back. I tried to replace the last expression with "WHERE [Date]=22/12/2001" (a constant date that exists in B) but it didn't work.
    I'm assuming I can't use '=' to compare dates, so I tried using the Datediff function but with no luck.

    I can solve this by creating a query based on A and B, and setting it to be the recordsource of the form, but I still want to know how it can be done in code.

    any help would be appriciated. thx!

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Mr Gates requires you to use US date format for datestrings (ISO date format also works i believe) and to enclose the datestring in #

    write yourself a little public function in a module along the lines of:

    public function strServerDate(X as variant) as string
    if not isdate(X) then exit function
    'for MSSQL
    'strServerDate = "'" & format$(X, "mm/dd/yyyy") & "'"
    'for JET
    strServerDate = "#" & format$(X, "mm/dd/yyyy") & "#"
    end function

    then you use:
    set recs = dabs.openrecordset("SELECT [Value] FROM [B] WHERE [Date]=" & strServerDate(d))

    note that the function pretends to check isdate() but it is already too late (the empty string return is going into the SQL anyway). your calling routine needs to check isdate() before calling strServerDate()

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Oct 2005
    Posts
    12
    Izy - You Rock.
    Thanks a bunch!

    Next time I think I'll ask you directly (-;

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Glad Izy was able to help you out!

    Please keep questions in the forum though, it helps people who have similar issues. They can search for threads about the topic and see if anyone else already worked through it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Oct 2005
    Posts
    12
    You are absolutly right...
    Besides, Why rely on one person, as good as he may be, when I can have more than one to try and help me?

    BTW: I was joking when I said I will ask him directly. Even though Izy was most helpful, I had no intention of actually doing that.

    Best regards

Posting Permissions

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