DLookUp

R

Rush

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?
 
O

Ofer

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 & "#"
 
Top