access 2002, run a query to update a form field when button isclicked?

[Broons Bane]

Sorry, am fairly new to access, but not databases,

i have a query nextmembernumber which returns one and only one
integer. I want to associate this query with a new button on a form,
so that (pseudo code):

if the new button is clicked then
if the membernumber form field is null then
execute nextmembernumber query and place the result in the
membernumber form field
end if
end if

how do i do it?

Thanks in anticipation

 

[Ken Snell MVP]

Replace generic names with real names:

Private Sub CommandButtonName_Click()
If IsNull(Me.MemberNumber.Value) = True Then _
Me.MemberNumber.Value = DLookup("FieldNameInQuery", _
"NextMemberNumber")
End Sub

 

[Tom van Stiphout]

On Sat, 18 Apr 2009 06:40:34 -0700 (PDT), Broons Bane

private sub myNewButton_Click()
if isnull(Me.myMembernumber)
Me.myMembernumber = DLookup("myQueryField", "nextmembernumber")
end if
end sub
(replace myObjectNames with yours)

Personally I would make that an Autonumber, so Access automatically
provides the next number. Your technique is appropriate if the number
is not just a meaningless number but a very specific one.

-Tom.
Microsoft Access MVP

 

[Broons Bane]

On Sat, 18 Apr 2009 06:40:34 -0700 (PDT), Broons Bane


private sub myNewButton_Click()
if isnull(Me.myMembernumber)
  Me.myMembernumber = DLookup("myQueryField", "nextmembernumber")
end if
end sub
(replace myObjectNames with yours)

Personally I would make that an Autonumber, so Access automatically
provides the next number. Your technique is appropriate if the number
is not just a meaningless number but a very specific one.

-Tom.
Microsoft Access MVP

Thank you Tom, much appreciated.