Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Insert Into Problem

Discussion in 'Information Technology' started by midiman69, Jul 28, 2006.

Tags:
  1. midiman69

    midiman69
    Expand Collapse
    Guest

    I am trying to insert a record in to tblnewparts if a field contains no
    matching records using the following

    If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

    DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"


    End If

    I gety no error message and the code complies but it doesn't work - can any
    one please help?

    Dave
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Why do you need this DLookUp, what are you trying to return?

    If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then

    When do you assign value to the partno, that you are using in the Insert?


    If the partno is a value that suppose to return using the dlookup, then use
    DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"

    If the partno is string then use
    DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"

    --
    Good Luck
    BS"D


    "midiman69" wrote:

    > I am trying to insert a record in to tblnewparts if a field contains no
    > matching records using the following
    >
    > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    >
    > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"
    >
    >
    > End If
    >
    > I gety no error message and the code complies but it doesn't work - can any
    > one please help?
    >
    > Dave
     
  4. midiman69

    midiman69
    Expand Collapse
    Guest

    Hi Ofer,

    Thanks for the reply.

    The DlookUp is to check if a matching record exists, if not add the "new"
    part number to tblnewparts - I have tried both of your suggestions - still
    not working.
    I have tried using the Before Update and After Update events.
    I am very inexperienced in VBA so I hope you will forgive me if this is some
    thing easy.

    Dave

    "Ofer Cohen" wrote:

    > Why do you need this DLookUp, what are you trying to return?
    >
    > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    >
    > When do you assign value to the partno, that you are using in the Insert?
    >
    >
    > If the partno is a value that suppose to return using the dlookup, then use
    > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    >
    > If the partno is string then use
    > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "midiman69" wrote:
    >
    > > I am trying to insert a record in to tblnewparts if a field contains no
    > > matching records using the following
    > >
    > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > >
    > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"
    > >
    > >
    > > End If
    > >
    > > I gety no error message and the code complies but it doesn't work - can any
    > > one please help?
    > >
    > > Dave
     
  5. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Do you get an error message? and if so, what is it?

    I think you are looking for something like

    ' Check if record exist using the dcount
    If DCount("*", "qrybom", "partno = " & partno)=0 Then
    ' if it doesn't exist enter a new value to the table
    DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    End If


    --
    Good Luck
    BS"D


    "midiman69" wrote:

    > Hi Ofer,
    >
    > Thanks for the reply.
    >
    > The DlookUp is to check if a matching record exists, if not add the "new"
    > part number to tblnewparts - I have tried both of your suggestions - still
    > not working.
    > I have tried using the Before Update and After Update events.
    > I am very inexperienced in VBA so I hope you will forgive me if this is some
    > thing easy.
    >
    > Dave
    >
    > "Ofer Cohen" wrote:
    >
    > > Why do you need this DLookUp, what are you trying to return?
    > >
    > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > >
    > > When do you assign value to the partno, that you are using in the Insert?
    > >
    > >
    > > If the partno is a value that suppose to return using the dlookup, then use
    > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    > >
    > > If the partno is string then use
    > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
    > >
    > > --
    > > Good Luck
    > > BS"D
    > >
    > >
    > > "midiman69" wrote:
    > >
    > > > I am trying to insert a record in to tblnewparts if a field contains no
    > > > matching records using the following
    > > >
    > > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > > >
    > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"
    > > >
    > > >
    > > > End If
    > > >
    > > > I gety no error message and the code complies but it doesn't work - can any
    > > > one please help?
    > > >
    > > > Dave
     
  6. midiman69

    midiman69
    Expand Collapse
    Guest

    Hi Ofer

    That is exactly what I am trying to do - No Error message with the first
    code but you last generates "Data type mismatch in Criteria Expression"
    partno is text.

    Dave

    "Ofer Cohen" wrote:

    > Do you get an error message? and if so, what is it?
    >
    > I think you are looking for something like
    >
    > ' Check if record exist using the dcount
    > If DCount("*", "qrybom", "partno = " & partno)=0 Then
    > ' if it doesn't exist enter a new value to the table
    > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    > End If
    >
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "midiman69" wrote:
    >
    > > Hi Ofer,
    > >
    > > Thanks for the reply.
    > >
    > > The DlookUp is to check if a matching record exists, if not add the "new"
    > > part number to tblnewparts - I have tried both of your suggestions - still
    > > not working.
    > > I have tried using the Before Update and After Update events.
    > > I am very inexperienced in VBA so I hope you will forgive me if this is some
    > > thing easy.
    > >
    > > Dave
    > >
    > > "Ofer Cohen" wrote:
    > >
    > > > Why do you need this DLookUp, what are you trying to return?
    > > >
    > > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > > >
    > > > When do you assign value to the partno, that you are using in the Insert?
    > > >
    > > >
    > > > If the partno is a value that suppose to return using the dlookup, then use
    > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    > > >
    > > > If the partno is string then use
    > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
    > > >
    > > > --
    > > > Good Luck
    > > > BS"D
    > > >
    > > >
    > > > "midiman69" wrote:
    > > >
    > > > > I am trying to insert a record in to tblnewparts if a field contains no
    > > > > matching records using the following
    > > > >
    > > > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > > > >
    > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"
    > > > >
    > > > >
    > > > > End If
    > > > >
    > > > > I gety no error message and the code complies but it doesn't work - can any
    > > > > one please help?
    > > > >
    > > > > Dave
     
  7. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    In that case, if the partno is a text type field, try

    If DCount("*", "qrybom", "partno = '" & partno & "'")=0 Then
    ' if it doesn't exist enter a new value to the table
    DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
    End If


    --
    Good Luck
    BS"D


    "midiman69" wrote:

    > Hi Ofer
    >
    > That is exactly what I am trying to do - No Error message with the first
    > code but you last generates "Data type mismatch in Criteria Expression"
    > partno is text.
    >
    > Dave
    >
    > "Ofer Cohen" wrote:
    >
    > > Do you get an error message? and if so, what is it?
    > >
    > > I think you are looking for something like
    > >
    > > ' Check if record exist using the dcount
    > > If DCount("*", "qrybom", "partno = " & partno)=0 Then
    > > ' if it doesn't exist enter a new value to the table
    > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    > > End If
    > >
    > >
    > > --
    > > Good Luck
    > > BS"D
    > >
    > >
    > > "midiman69" wrote:
    > >
    > > > Hi Ofer,
    > > >
    > > > Thanks for the reply.
    > > >
    > > > The DlookUp is to check if a matching record exists, if not add the "new"
    > > > part number to tblnewparts - I have tried both of your suggestions - still
    > > > not working.
    > > > I have tried using the Before Update and After Update events.
    > > > I am very inexperienced in VBA so I hope you will forgive me if this is some
    > > > thing easy.
    > > >
    > > > Dave
    > > >
    > > > "Ofer Cohen" wrote:
    > > >
    > > > > Why do you need this DLookUp, what are you trying to return?
    > > > >
    > > > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > > > >
    > > > > When do you assign value to the partno, that you are using in the Insert?
    > > > >
    > > > >
    > > > > If the partno is a value that suppose to return using the dlookup, then use
    > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    > > > >
    > > > > If the partno is string then use
    > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
    > > > >
    > > > > --
    > > > > Good Luck
    > > > > BS"D
    > > > >
    > > > >
    > > > > "midiman69" wrote:
    > > > >
    > > > > > I am trying to insert a record in to tblnewparts if a field contains no
    > > > > > matching records using the following
    > > > > >
    > > > > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > > > > >
    > > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"
    > > > > >
    > > > > >
    > > > > > End If
    > > > > >
    > > > > > I gety no error message and the code complies but it doesn't work - can any
    > > > > > one please help?
    > > > > >
    > > > > > Dave
     
  8. midiman69

    midiman69
    Expand Collapse
    Guest

    Hi Ofer,

    Many thanks that works fine - how do I combine this with a message box?
    I am trying

    If DCount("*", "qrybom", "partno = '" & partno & "'") = 0 Then
    MsgBox "This is a New Part - Do You Wish To Add?", vbYesNo, "Project
    Costing Database"
    If vbYesNo = vbNo Then Exit Sub

    Else: DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno
    & "')"

    End If

    Again this doesn't work (your code without the msgbox does) Also, how to I
    clear the "new part" data from the lookup field?

    Thanks for all your help - I'll never get the hang of this VBA!!

    Dave



    End Sub

    "Ofer Cohen" wrote:

    > In that case, if the partno is a text type field, try
    >
    > If DCount("*", "qrybom", "partno = '" & partno & "'")=0 Then
    > ' if it doesn't exist enter a new value to the table
    > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
    > End If
    >
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "midiman69" wrote:
    >
    > > Hi Ofer
    > >
    > > That is exactly what I am trying to do - No Error message with the first
    > > code but you last generates "Data type mismatch in Criteria Expression"
    > > partno is text.
    > >
    > > Dave
    > >
    > > "Ofer Cohen" wrote:
    > >
    > > > Do you get an error message? and if so, what is it?
    > > >
    > > > I think you are looking for something like
    > > >
    > > > ' Check if record exist using the dcount
    > > > If DCount("*", "qrybom", "partno = " & partno)=0 Then
    > > > ' if it doesn't exist enter a new value to the table
    > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    > > > End If
    > > >
    > > >
    > > > --
    > > > Good Luck
    > > > BS"D
    > > >
    > > >
    > > > "midiman69" wrote:
    > > >
    > > > > Hi Ofer,
    > > > >
    > > > > Thanks for the reply.
    > > > >
    > > > > The DlookUp is to check if a matching record exists, if not add the "new"
    > > > > part number to tblnewparts - I have tried both of your suggestions - still
    > > > > not working.
    > > > > I have tried using the Before Update and After Update events.
    > > > > I am very inexperienced in VBA so I hope you will forgive me if this is some
    > > > > thing easy.
    > > > >
    > > > > Dave
    > > > >
    > > > > "Ofer Cohen" wrote:
    > > > >
    > > > > > Why do you need this DLookUp, what are you trying to return?
    > > > > >
    > > > > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > > > > >
    > > > > > When do you assign value to the partno, that you are using in the Insert?
    > > > > >
    > > > > >
    > > > > > If the partno is a value that suppose to return using the dlookup, then use
    > > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    > > > > >
    > > > > > If the partno is string then use
    > > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
    > > > > >
    > > > > > --
    > > > > > Good Luck
    > > > > > BS"D
    > > > > >
    > > > > >
    > > > > > "midiman69" wrote:
    > > > > >
    > > > > > > I am trying to insert a record in to tblnewparts if a field contains no
    > > > > > > matching records using the following
    > > > > > >
    > > > > > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > > > > > >
    > > > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"
    > > > > > >
    > > > > > >
    > > > > > > End If
    > > > > > >
    > > > > > > I gety no error message and the code complies but it doesn't work - can any
    > > > > > > one please help?
    > > > > > >
    > > > > > > Dave
     
  9. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Try this

    If DCount("*", "qrybom", "partno = '" & partno & "'") = 0 Then
    If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, "Project
    Costing Database") = vbYes Then
    DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno
    & "')"
    End If
    End If

    =================
    To clear the text box use
    Me.partno = ""

    --
    Good Luck
    BS"D


    "midiman69" wrote:

    > Hi Ofer,
    >
    > Many thanks that works fine - how do I combine this with a message box?
    > I am trying
    >
    > If DCount("*", "qrybom", "partno = '" & partno & "'") = 0 Then
    > MsgBox "This is a New Part - Do You Wish To Add?", vbYesNo, "Project
    > Costing Database"
    > If vbYesNo = vbNo Then Exit Sub
    >
    > Else: DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno
    > & "')"
    >
    > End If
    >
    > Again this doesn't work (your code without the msgbox does) Also, how to I
    > clear the "new part" data from the lookup field?
    >
    > Thanks for all your help - I'll never get the hang of this VBA!!
    >
    > Dave
    >
    >
    >
    > End Sub
    >
    > "Ofer Cohen" wrote:
    >
    > > In that case, if the partno is a text type field, try
    > >
    > > If DCount("*", "qrybom", "partno = '" & partno & "'")=0 Then
    > > ' if it doesn't exist enter a new value to the table
    > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
    > > End If
    > >
    > >
    > > --
    > > Good Luck
    > > BS"D
    > >
    > >
    > > "midiman69" wrote:
    > >
    > > > Hi Ofer
    > > >
    > > > That is exactly what I am trying to do - No Error message with the first
    > > > code but you last generates "Data type mismatch in Criteria Expression"
    > > > partno is text.
    > > >
    > > > Dave
    > > >
    > > > "Ofer Cohen" wrote:
    > > >
    > > > > Do you get an error message? and if so, what is it?
    > > > >
    > > > > I think you are looking for something like
    > > > >
    > > > > ' Check if record exist using the dcount
    > > > > If DCount("*", "qrybom", "partno = " & partno)=0 Then
    > > > > ' if it doesn't exist enter a new value to the table
    > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    > > > > End If
    > > > >
    > > > >
    > > > > --
    > > > > Good Luck
    > > > > BS"D
    > > > >
    > > > >
    > > > > "midiman69" wrote:
    > > > >
    > > > > > Hi Ofer,
    > > > > >
    > > > > > Thanks for the reply.
    > > > > >
    > > > > > The DlookUp is to check if a matching record exists, if not add the "new"
    > > > > > part number to tblnewparts - I have tried both of your suggestions - still
    > > > > > not working.
    > > > > > I have tried using the Before Update and After Update events.
    > > > > > I am very inexperienced in VBA so I hope you will forgive me if this is some
    > > > > > thing easy.
    > > > > >
    > > > > > Dave
    > > > > >
    > > > > > "Ofer Cohen" wrote:
    > > > > >
    > > > > > > Why do you need this DLookUp, what are you trying to return?
    > > > > > >
    > > > > > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > > > > > >
    > > > > > > When do you assign value to the partno, that you are using in the Insert?
    > > > > > >
    > > > > > >
    > > > > > > If the partno is a value that suppose to return using the dlookup, then use
    > > > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values (" & partno & ")"
    > > > > > >
    > > > > > > If the partno is string then use
    > > > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno & "')"
    > > > > > >
    > > > > > > --
    > > > > > > Good Luck
    > > > > > > BS"D
    > > > > > >
    > > > > > >
    > > > > > > "midiman69" wrote:
    > > > > > >
    > > > > > > > I am trying to insert a record in to tblnewparts if a field contains no
    > > > > > > > matching records using the following
    > > > > > > >
    > > > > > > > If (DLookup("[partno]", "qrybom", IsNull([partno]) = True)) Then
    > > > > > > >
    > > > > > > > DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & "SELECT " & partno & ";"
    > > > > > > >
    > > > > > > >
    > > > > > > > End If
    > > > > > > >
    > > > > > > > I gety no error message and the code complies but it doesn't work - can any
    > > > > > > > one please help?
    > > > > > > >
    > > > > > > > Dave
     
  10. BD

    BD
    Expand Collapse
    Guest

    On Mon, 19 Jun 2006 08:08:01 -0700, midiman69
    <midiman69@discussions.microsoft.com> wrote:

    >Hi Ofer,
    >
    >Many thanks that works fine - how do I combine this with a message box?
    >I am trying
    >
    >If DCount("*", "qrybom", "partno = '" & partno & "'") = 0 Then
    > MsgBox "This is a New Part - Do You Wish To Add?", vbYesNo, "Project
    >Costing Database"
    >If vbYesNo = vbNo Then Exit Sub
    >
    > Else: DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno
    >& "')"
    >
    >End If
    >
    >Again this doesn't work (your code without the msgbox does) Also, how to I
    >clear the "new part" data from the lookup field?
    >
    >Thanks for all your help - I'll never get the hang of this VBA!!
    >
    >Dave




    If DCount("*", "qrybom", "partno = '" & partno & "'") = 0 Then
    if MsgBox( "This is a New Part - Do You Wish To Add?", _
    vbYesNo, "Project Costing Database") = vbNo Then
    Exit Sub
    Else DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & _
    "Values ('" & partno & "')"
    End If
    End If

    []'s
    BD
     
  11. midiman69

    midiman69
    Expand Collapse
    Guest

    Many thanks for you help guys - very much appreciated.

    One more thing, the subform is bound to the main form by two primary keys
    Xfile and issno- how would I modify the code to include these so that the
    "New part" is bound to the main form? is this the "WHERE statement?

    Any further help would be brilliant

    Dave

    "BD" wrote:

    > On Mon, 19 Jun 2006 08:08:01 -0700, midiman69
    > <midiman69@discussions.microsoft.com> wrote:
    >
    > >Hi Ofer,
    > >
    > >Many thanks that works fine - how do I combine this with a message box?
    > >I am trying
    > >
    > >If DCount("*", "qrybom", "partno = '" & partno & "'") = 0 Then
    > > MsgBox "This is a New Part - Do You Wish To Add?", vbYesNo, "Project
    > >Costing Database"
    > >If vbYesNo = vbNo Then Exit Sub
    > >
    > > Else: DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) Values ('" & partno
    > >& "')"
    > >
    > >End If
    > >
    > >Again this doesn't work (your code without the msgbox does) Also, how to I
    > >clear the "new part" data from the lookup field?
    > >
    > >Thanks for all your help - I'll never get the hang of this VBA!!
    > >
    > >Dave

    >
    >
    >
    > If DCount("*", "qrybom", "partno = '" & partno & "'") = 0 Then
    > if MsgBox( "This is a New Part - Do You Wish To Add?", _
    > vbYesNo, "Project Costing Database") = vbNo Then
    > Exit Sub
    > Else DoCmd.RunSQL "INSERT INTO tblnewparts ( partno ) " & _
    > "Values ('" & partno & "')"
    > End If
    > End If
    >
    > []'s
    > BD
    >
     

Share This Page