Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    NEW YORK
    Posts
    2

    Unanswered: How to store ID of two table in middle table

    Hi EveryOne:

    Let see I have tables like this

    tbl_Record
    RecordID (autonumber)
    Product
    ...


    tbl_Forecast
    RecordID
    ForecastDetailID
    FYF

    tbl_Forecast_Detail
    ForecastDetailID (autonumber)
    Month
    Value

    right now I have all this info in one flatfile table. and i want to send it in these tables, how to i store RecordID and ForecastDetailID into tbl_Forecast,

    Any help..
    Thanking you in advance.

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Do this in Vba. loop through a recordset of your flat file. rsR.Add to your tbl_Record and after setting the product field trap the autonumber assigned into a local variable:

    localR=rs!RecordId

    Do the same for inserting into the Forcast Detail table and trap the ForecastDetailID. You can now insert into your third table.

  3. #3
    Join Date
    Oct 2003
    Location
    NEW YORK
    Posts
    2
    Originally posted by jmrSudbury
    Do this in Vba. loop through a recordset of your flat file. rsR.Add to your tbl_Record and after setting the product field trap the autonumber assigned into a local variable:

    localR=rs!RecordId

    Do the same for inserting into the Forcast Detail table and trap the ForecastDetailID. You can now insert into your third table.

    Thanks, for that tip. If you won't mind can you please give sort of sample code, because i don't know how to use DAO in vba, but if i have any sample code and can twist around the code to make it work.
    Thanking you in advance.

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    dim rsI as recordset, rsR as recordset, rsF as recordset, rsD as recordset
    dim db as database, anFD as long, anR as long

    set db=currentdb()
    set rsI=db.openrecordset("FlatInputTable")
    set rsR=db.openrecordset("tbl_Record")
    set rsF=db.openrecordset("tbl_Forecast")
    set rsD=db.openrecordset("tbl_Forecast_Detail")

    while not rsi.eof
    rsr.addnew
    rsr!Product = rsi!Product
    anR = rsr!RecordId
    ...
    rsr.update

    rsd.addnew
    rsd![Month] = rsi![Month]
    anFD = rsd!ForecastDetailID
    rsd![Value] = rsi![Value]
    rsd.update

    rsf.addnew
    rsf!RecordId = anR
    rsf!ForecastDetailID = anFD
    rsf![Value] = rsi!FYF
    rsf.update

    rsi.movenext
    wend

Posting Permissions

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