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
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 strSQL as string
strSQL = "select * from table_a;"
set db = currentdb()
set rs = db.Openrecordset(strSQL)
sumvalue = rs!field_name
sumvalue = sumvalue + 1 'manipulate sumvalue to what you need
from there you could set your record id for the new record to equal sumvalue.
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.