Results 1 to 14 of 14
  1. #1
    Join Date
    May 2004
    Posts
    10

    Arrow Unanswered: Create Records from another table

    Does anyone know how to create multiple records based on information in another table?
    I am trying to create a database that will hold customer installments, so the user will enter the number of installments (say 5) on the customer form, they will then click on a 'Create installments button', then when they go into the installments screen, the 5 records will be created in this table. If anyone can help, it would be most apreciated .
    Last edited by Yeian; 05-07-04 at 16:33.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, but are you sure this is what you want to do? You are, in effect, creating n identical records in a table, presumably on the basis that they will be altered by the user? Or are there differing values for each resord, making them unique? If it is a whole load of identical records, the easiest and safest thing to do would be for the person to hit the 'Create installments button' (without specifying the number of installments) and for the default values for the text boxes on your Create Installments form to be set to something like =Forms!PreviousForm!RelevantTextBox. That way, they can't create 10 identical records, go off on their lunch break and forget all about it. Each new record has the information you want to carry across but they can only create one at a time. Best of both worlds? If not, let me know and i'll post the code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2004
    Posts
    10
    Thanks for your reply. Each racord will have a different date (IE monthly installments). Each installment will also have it's own installment number, making each record unique. Any help you could give would be great, many thanks again.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No probs. The below should do it I think

    Dim i As Integer
    Dim strSQL As String

    'Create a loop so the process runs as many times as the user entered in the txtInstallmentNo text box
    For i = 1 To Me.txtInstallmentNo

    'You will need an expression to increment the date if you haven't sorted that already.
    strSQL = "Insert into tblInstallment (TextFieldinTable, DateFieldinTable, NumericFieldinTable) values " & _
    "('" & Me.txtString & "', " & Me.txtNo & ", #" & Me.txtDate & "#)"

    'if you don't use DAO - make sure the Error trap sets warnings back to true!
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    'or if you do use DAO
    'CurrentDb.Execute strSQL, dbfailonerror

    Next i
    I just reread your post and noticed that the data is to come from a table, not a form. In that case, change the me.txtString to TableName.FieldName. Hope this is of use.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2004
    Posts
    10
    Thank you very much, I will try this out. Your help is much appreciated, have a good day.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    And you extra text extra text
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yeah. If the data does come from a table you will need a where condition in your SQL statement. Oops.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try using an integers table to cross join with, to generate the number of entries you need

    see http://www.dbforums.com/showthread.p...83#post3671683
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2004
    Posts
    10
    Sorry, might be being a bit thick here, but have tried the above suggestion, but get syntax error when trying to run the code, here is what I have adapted the above code to:

    Dim inst As Integer
    Dim InstNo As Integer
    Dim DateVar As Date

    InstNo = Me.No_of_Installments
    DateVar = Me.Date_of_1st_Installment

    For inst = 1 To Me.No_of_Installments

    strSQL = INSERT INTO Payments (ContractNo, Intallment No, Installment Date, Installment Amount)
    VALUES " & _"('" & ('Me.[Contract No] & "', " & Me.InstNo & ", #" & Me.DateVar & "#)"

    InstNo = InstNo - 1

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    Have I done something wrong, or do I need to define something to tell VB that I am using an SQL statement?

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you have several syntax errors:

    strSQL = "INSERT INTO Payments (ContractNo, [Intallment No], [Installment Date], [Installment Amount])
    VALUES ('" & Me.[Contract No] & "', " & Me.InstNo & ", #" & Me.DateVar & "#);"

    lots was removed from VALUES

    you possibly also have a typo with
    Intallment No

    izy
    Last edited by izyrider; 05-10-04 at 14:36.
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and one more:
    you are inserting three values into four fields [installment amount] seems to be missing.

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    May 2004
    Posts
    10
    Ah yes, thanks, think I am getting the hang of this now. All my values are reading into my variables correctly now. However, I still have a problem in that when I run my code, I get "Run-time error '3134' Syntax Error in INSERT INTO Statement" ? Here is the code now:

    Private Sub CrtInst()

    Dim inst As Integer
    Dim InstNo As Integer
    Dim InstAmnt As Currency
    Dim DateVar As Date
    Dim ContNo As String

    InstNo = Me.No_of_Installments
    InstAmnt = (Me.LPC_Price / Me.No_of_Installments)
    DateVar = Me.Date_of_1st_Installment
    ContNo = Me.Contract_No

    For inst = 1 To InstNo

    strSQL = "INSERT INTO Payments (ContractNo, [Installment No], [Installment Date], [Installment Amount])"
    VALUES = "('" & ContNo & "', " & InstNo & ", #" & DateVar & "#," & InstAmnt & ");"

    InstNo = InstNo + 1

    Next inst

    DoCmd.SetWarnings False
    DoCmd.strSQL
    DoCmd.SetWarnings True

    End Sub


    I have read that when defining my INSERT INTO string that I need to define all the fields in my table, is this correct? If so, do I need to enter a null value into each of the fields that I'm not defining in the procedure above?

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    A couple of things (changes/ additions in red):

    Private Sub CrtInst()
    'Need to trap errors otherwise you'll never be warned about any changes ever again!
    on error Goto CrtInst_Error

    Dim inst As Integer
    Dim InstNo As Integer
    Dim InstAmnt As Currency
    Dim DateVar As Date
    Dim ContNo As String

    InstNo = Me.No_of_Installments
    InstAmnt = (Me.LPC_Price / Me.No_of_Installments)
    DateVar = Me.Date_of_1st_Installment
    ContNo = Me.Contract_No

    For inst = 1 To InstNo

    strSQL = "INSERT INTO Payments (ContractNo, [Installment No], [Installment Date], [Installment Amount])"
    VALUES = "('" & ContNo & "', " & InstNo & ", #" & DateVar & "#," & InstAmnt & ");"

    'Don't you want instno to count down rather than up?
    InstNo = InstNo + 1

    DoCmd.SetWarnings False
    'DoCmd.strSQL
    docmd.runsql strSQL
    DoCmd.SetWarnings True

    'the running of the SQL needs to be within the loop, otherwise you change the string many times but only run the last one.
    Next inst

    CrtInst_Resume:
    exit sub

    CrtInst_Error:

    msgbox err.number & ", " & err.description
    DoCmd.SetWarnings True

    resume CrtInst_Resume

    End Sub

    All the other fields in the table should handle themselves fine (i.e. default to null if no default value is specified) unless you have a field that is set to Required: Yes and you aren't inserting a value (the exception would be an autonumber field).

    NB I wrote this in the browser and noticed a couple of minor errors of my own so I would treat the code and comments as a guide rather than just cut and paste!
    Last edited by pootle flump; 05-11-04 at 07:26.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the keyword VALUES must be part of the INSERT statement

    look at strSQL, it does not include VALUES

    I have read that when defining my INSERT INTO string that I need to define all the fields in my table, is this correct?
    no, you can list only the ones you have a value for
    If so, do I need to enter a null value into each of the fields that I'm not defining in the procedure above?
    no, that's done for you automatically
    Last edited by r937; 05-11-04 at 08:22.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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