Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Red face Unanswered: Default Value Troubles

    I am now losing sleep over this problem that I know can be solved. I just lack that special something to do the job

    I need to set the default value of a field to a number that is taken from an field in a different table.

    For example: I have constructed a table (REPORT NUMBER) to log a manually generated report number (field name that contains the the report number is convieniently named REPORT NUMBER. The table is also named REPORT NUMBER).

    I also generated a "Show Top 1 SQL query" to retrieve the last report number record from table REPORT NUMBER (the name of the query is REPORT NUMBER_query) -- This works fine.

    I have a seperate table (named COMMENTS) that needs to use the value of the last report number in query REPORT NUMBER_query.

    I know this is a simple expression code but I have tried everything from DLookup, DStart, DFirst and can't seem to grab it. When I place the code in I get either a syntax error or a pop-up indicating it was an "unknown funtion with DFirst in validation expression or defalt value on COMMENTS.REPORT NUMBER.

    The code I am using is:

    DFirst("[REPORT NUMBER]"," REPORT NUMBER_query ")

    Is this clear as mud or what??
    Any help will be appreicated

    Dan W.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't think you can set that as a default value. You need to populate they key during runtime.

    Also, have a go with DMax()
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Feb 2006
    Posts
    56
    A ? for you.
    How do you generate the report id number now?
    there are several ideas that come to mind on how to get this number... for instance if the form is based on a table or query and you have an iterative counter, you could always move the recordset to the end (rs.movelast) and get that value, increment, set to a variable, then add to the new table when you save the record.

    you could do something like this:

    dim db as database
    dim rs as recordset
    dim sumvalue
    dim strSQL as string


    strSQL = "select * from table_a;"
    set db = currentdb()
    set rs = db.Openrecordset(strSQL)
    rs. movelast
    sumvalue = rs!field_name
    rs.close
    db.close

    sumvalue = sumvalue + 1 'manipulate sumvalue to what you need

    from there you could set your record id for the new record to equal sumvalue.

    for i.e.
    text1.setfocus
    text1.text = sumvalue

    you could put all of this in the add new record code module


    sorry, this is just right off the top of my head. without knowing more about your situation, this is the best I can do in 2 minutes. hope it helps.

Posting Permissions

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