How do I do this in VBA?

[Julia82]

On button press, check a value from the table1 of current record, field 1 and
if it's nothing there do something(I have an UPDATE table statement), if it
is, leave it that way.

I tryed with Dlookup with Nz(Dlookup with if IsNull(Dlookup ... can't get it
done.

Thank you!

 

[Jeanette Cunningham]

You need a way to identify the current record - such as its Primary Key.

Private Sub cmdBtn_Click()
Dim varReturn As Variant

varReturn = DLookup("[Field1]", "Table1", "[PkField] = " & Me.PkField)
End Sub


Note: in the above, varReturn may be Null if there is no matching record in
the table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Julia82]

Oh, I think I got the idea.

The thing is working... partyally... meaning that when the update happens,
it's changing all the rows in the table for the specified field. I'll try
what you told me and I'll get back to you.

You need a way to identify the current record - such as its Primary Key.

Private Sub cmdBtn_Click()
Dim varReturn As Variant

varReturn = DLookup("[Field1]", "Table1", "[PkField] = " & Me.PkField)
End Sub


Note: in the above, varReturn may be Null if there is no matching record in
the table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

On button press, check a value from the table1 of current record, field 1
and
if it's nothing there do something(I have an UPDATE table statement), if
it
is, leave it that way.

I tryed with Dlookup with Nz(Dlookup with if IsNull(Dlookup ... can't get
it
done.

Thank you!

.

 

[Julia82]

Dim varReturn As Variant
Dim a As String
varReturn = DLookup("[USer1]", "tblDatabase", "[UID] = " & Me.UID)

If varReturn = notnull Then ???

a = 1
Else

DoCmd.RunSQL "UPDATE tblDatabase SET User1=' " & Me.txtuser & " '"

End If

..... I don't know how to say if varReturn (the field verified) has nothing
in it, do nothing, else DoCmd.RunSQL statement above...

 

[Julia82]

Got it!

If IsNull(varReturn) Then
.....
End If


Thanks!

 

[John Spencer]

Even easier would be to use DCount instead of DLookup. If Dcount is zero then
nothing was found

Dim LReturn As Long
Dim a As String
LReturn = DCount("User1", "tblDatabase", "[UID] = " & Me.UID)

If DCount=0 Then
a = "1"
Else
DoCmd.RunSQL "UPDATE tblDatabase SET User1='" & Me.txtuser & "'"
End If


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County