Query a table then return the value to another table

[Kaallis]

Hi there,

Let me explain my problem more clearly:

I have:
a form FRM_Main
2 tables TBL_2007_OT and TBL_2007_Date
a querry QRY_Dates_2007

The TBL_2007_Date is a number from 1 to 365 as primary key and the date associated to it. ex: 1 would be 1/1/2007.

The TBL_2007_OT would have a field named Date_ID where I want the number from TBL_2007_Date to be stored.

Now my form have a day combo box a month combo box and a year combo box which then populate a disabled textbox and it look like this (1/1/2007).

Everything works fine. As you can see, there is nowhere in the form where we talk about a number from 1 to 365.

What I want to do is, upon click of my save button, search the table TBL_2007_Date for the value of the textbox and then store the number from 1 to 365 in the table TBL_2007_OT field Date_ID

Tell me if you need more informations

Regards

[jaxjagfan]

It will be similar to this. You will need to use the DLookup to find the value and a Insert (Append) to add the value to the other table. Add this to your button.

Dim iDt
Dim strSQL As String
iDt = DLookup("[Date_ID]", "TBL_2007_Date", "[YourDateColumn] =" _
& Forms![FRM_Main]!YourTextBox)
strSQL = "Insert Into TBL_2007_OT (Date_ID) Values (" & iDt & ");"
DoCmd.RunSQL strSQL

If you need dates to have ID's why not use:

CLNG(Format(Date(),"mmddyyyy") - This would return 12192007 using today's date. It would be unique and easily distinguishable.

If you need the day number similar to your sample (1-365 for this year or 1-366 for next year) I would do a DateDiff + 1. DateDiff(SelectedDate,cdate("1/1/" & SelectedYear)) + 1 - this would return 352 (12/19/2007 - 1/1/2007) + 1.

[Kaallis]

Thanks a lot "jaxjagfan"

I will try this rigth now and let you know if it works.