Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    SQL - Insert help (2000)

    Hi all,
    I have a sql statement that almost works. I have a form that I enter a date (forms.frmdate.tdate). I have another form that I enter information for a transaction (forms.frmsisoa). Frmsisoa has 4 fields, tdate, quan, jn, sisoa; tdate has default of frmdate.tdate; this form is bound to tbltranshistory. I have in the after update property of frmsisoa for the quan amount to subtract that amount from the tblinventory where jn is equal to the jn of tblinventory. This works like it is suppose to work. My problem is when the quan in tblinventory goes negative. I tried to make a SQL statement that would append a record to the tbltranshistory to show bringing the quan back to zero. I got the jn and quan fields to append by using the select statement but when i try to add to the statement the fields for tdate and sisoa I am having a problem.
    Private Sub Form_AfterUpdate()
    Dim strSQL As String
    strSQL = "UPDATE tblInventory SET tblInventory.Quan = [quan]+forms.frmSISOA.[quan]WHERE (((tblInventory.JN)=forms!frmSISOA![jn]));"
    Debug.Print strSQL
    DoCmd.RunSQL strSQL
    If DLookup("[quan]", "[tblinventory]", "[jn] =" & Chr(34) & Me!JN & Chr(34)) < 0 Then
    DoCmd.RunSQL "insert into tbltranshistory (jn, quan, sisoa, tdate) select tblinventory.jn, (tblinventory.quan)*(-1) from tblinventory where tblinventory.jn =" & Chr(34) & Me.JN & Chr(34), "CompAdj", #forms!frmdate!tdate#
    DoCmd.RunSQL "update tblinventory set tblinventory.quan = 0 where tblinventory.jn =" & Chr(34) & Me.JN & Chr(34)
    Else
    End If
    End Sub
    The error I get is the linkmasterfields property setting has produced this error: 'wrong number of arguments or invalid property assignment' when I open frmsisoa.
    Thank you in advance.
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL - Insert help (2000)

    Link Master Fields is a property of a subform. Where does a subform come into this?

    I don't understand the instruction

    DoCmd.RunSQL "insert into tbltranshistory (jn, quan, sisoa, tdate) select tblinventory.jn, (tblinventory.quan)*(-1) from tblinventory where tblinventory.jn =" & Chr(34) & Me.JN & Chr(34), "CompAdj", #forms!frmdate!tdate#

    RunSQL has two arguments: the SQL string and a boolean specifying whether to use a transaction. You specify a string as second argument and a date as third argument. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: SQL - Insert help (2000)

    There is a subform on frmsisoa that shows the item description from tblinventory linked by JN. This works when I append tbltranshistory with only 2 fields (jn and quan). I get several other errors now that I closed form and saved it.
    When I first enter the form the error is "method item of object forms failed". When I enter a character in the jn field I get the error about the linkmasterfields.
    In the instruction I am trying to create a new record in tbltranshistory and get the 4 fields filled in with the values of jn and quan from tblinventory where jn = frmsisoa.jn , the text "CompAdj" into the sisoa field, and the date from frmdate!tdate into the tdate field of this new record for tbltranshistory.
    Is it possible to do this or is there a better way to accomplish what I am trying to do?
    Thank you.
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL - Insert help (2000)

    It's rather confusing if you jumble three or more problems into one question. I'm afraid I don't have the slightest idea what you're doing. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions. After that, we'll tackle the problems one by one, instead of all together.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: SQL - Insert help (2000)

    I hope the attached file shows you what I am trying to accomplish.
    Thank you again.
    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL - Insert help (2000)

    Try this for the append (insert) code. I have removed the invalid 2nd and 3rd argument of RunSQL, and added the missing values for the INSERT statement.
    <code>
    DoCmd.RunSQL "INSERT INTO tblTransHistory (jn, quan, sisoa, tdate) " & _
    "SELECT tblInventory.jn, (tblInventory.quan)*(-1), " & _
    Chr(34) & Me.SISOA & Chr(34) & _
    ", #" & Format(Date, "mm/dd/yyyy") & "# FROM tblInventory " & _
    "WHERE tblInventory.jn =" & Chr(34) & Me.JN & Chr(34)</code>

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: SQL - Insert help (2000)

    For those who are following this thread, this is how I changed the code. I wanted the sisoa field to be CompAdj when the computer adjusts the quantity to zero instead of picking up the value from the open frmsisoa form. I also did not want to use todays date for tdate when the computer makes an adjustment. I wanted to use the date that was entered in the form frmdate.

    DoCmd.RunSQL "insert into tbltranshistory (jn, quan, sisoa, tdate) " & "select tblinventory.jn, (tblinventory.quan)*(-1), " & Chr(34) & "CompAdj" & Chr(34) & ", forms!frmdate!tdate from tblinventory " & "where tblinventory.jn =" & Chr(34) & Me.JN & Chr(34)

    This seems to work like I wanted it to work.

    Thank you Hans. I could not have done it without your help.
    John

Posting Permissions

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