Welcome to SPN

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

Sign Up Now!

DLookUp

Discussion in 'Information Technology' started by Rush, Nov 9, 2005.

Tags:
  1. Rush

    Rush
    Expand Collapse
    Guest

    I am trying to write a macro for a command button on a "Master" form. The
    master form has 6 variables on it that act like one primary key. When I
    click the button, I want the second form's table to be searched to see if
    these 6 variables exist together in one record in the table. If all six of
    these don't exist together, I want to create a new record. I have tried
    writing a DLookUp statement for this, but it ends up being too long and it
    all won't fit. Can anyone help?
     
  2. Ofer

    Ofer
    Expand Collapse
    Guest

    Don't create a macro, on the Onclick event of the button create this code

    Dim MyDb as Dao.DataBase, MyRec As Dao.RecordSet
    Set MyDb=currentdb
    Set MyRec = MyDb.OpenRecordset("Select * From TableName Where Field1 = " &
    Me.Field1 & " And Field2 = " & Me.Field2 & " And Field3 = " & Me.Field3 & "
    And Field4 = " & Me.Field4 & " And Field5 = " & Me.Field5 & " And Field6 = "
    & Field6)

    if MyRec.Eof ' No match
    myrec.Addnew
    MyRec!Field1 = Me.Field1
    MyRec!Field2 = Me.Field2
    MyRec!Field3= Me.Field3
    MyRec!Field4 = Me.Field4
    MyRec!Field5= Me.Field5
    MyRec!Field6 = Me.Field6
    Myrec.Update
    Else
    MsgBox "Exist"
    End If
    ============================
    If you need more help, I need the name of the table, and the name and type
    of all the fields
    When you filter on number you write
    Where Fieldname = " & Param

    on string
    Where Fieldname = '" & Param & "'"
    On date
    Where Fieldname = #" & Param & "#"


    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Rush" wrote:

    > I am trying to write a macro for a command button on a "Master" form. The
    > master form has 6 variables on it that act like one primary key. When I
    > click the button, I want the second form's table to be searched to see if
    > these 6 variables exist together in one record in the table. If all six of
    > these don't exist together, I want to create a new record. I have tried
    > writing a DLookUp statement for this, but it ends up being too long and it
    > all won't fit. Can anyone help?
     

Share This Page