Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012

    Unanswered: how to create a set of records based on value from a form


    I'm not an expert so please have mercy.

    I have tables: tblYears, and tblMonths
    Fields for tblYears are: TrnYear (primary key), Balance
    Field for tblMonths are: TrnYear, TrnMonth, Balance (no primary key)

    I have a form: CreateYear with button and field called: txtYear and cmdCreateYear

    When I click the button I need it to create a ne record in tblYears with the value eneterd in the text box on the form. Then, I also want it to create a new set of records for TrnMonth in tblMonths.
    So when I expand the TrnYear from tblYears it should show 12 new records, 1,2,3...12 under TrnMonths in tblMonths.

    Could someone please help with some code.


  2. #2
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 2
    Air code:
    Dim strSQL As String
    Dim intYear As Integer
    Dim intMonth As Integer
    strSQL = "INSERT INTO tblYears (TrnYear) VALUES (" & txtYear & ");"
    CurrentDB.Execute strSQL
    For intMonth = 1 To 12
        strSQL = "INSERT INTO tblMonths (TrnYear, TrnMonth) VALUES (" & txtYear & ", " & intMonth & ");"
        CurrentDB.Execute strSQL
    Next intMonth
    Note that this does not check to make sure you are not trying to duplicate a year!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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