How do I do this in VBA?

  • Thread starter Thread starter Julia82
  • Start date Start date
J

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!
 
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
 
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.

Jeanette Cunningham said:
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 said:
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!
Click to expand...


.
Click to expand...
 
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...
 
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
 
Back
Top