Way to have VBA run on continuous forms

  • Thread starter Thread starter ArielZusya
  • Start date Start date
A

ArielZusya

I've got a continuous form, frmMain, that displays a list of the records in
tblMain. Each record appears as a hyperlink and when clicked the record will
open a form, frmInd, with just the record clicked displayed. I'd like to
have an unbound text field display some information on the frmMain based on
the record it's next to. I tried using the on current and the on load to do
an if-then statement to test the value in another field but that doesn't
work. That code was something like:

sub Form_Current()
if me.txtFirstName.value = "Joe" then
me.txtUnbound.value = "Hello Joe"
else
me.txtUnbound.value = "No Joe"
end if
end sub

or

Sub Form_Load ()
if me.txtFirstName.value = "Joe" then
me.txtUnbound.value = "Hello Joe"
else
me.txtUnbound.value = "No Joe"
end if
End Sub

This code seems to show all records the same as the first record so if the
first record has "Joe" in txtFirstName then all records will have "Hello Joe"
despite the value of txtFirstName on the other individual records. Is there
a way to make this work? Thanks!
 
ArielZusya said:
I've got a continuous form, frmMain, that displays a list of the records
in
tblMain. Each record appears as a hyperlink and when clicked the record
will
open a form, frmInd, with just the record clicked displayed. I'd like to
have an unbound text field display some information on the frmMain based
on
the record it's next to. I tried using the on current and the on load to
do
an if-then statement to test the value in another field but that doesn't
work. That code was something like:

sub Form_Current()
if me.txtFirstName.value = "Joe" then
me.txtUnbound.value = "Hello Joe"
else
me.txtUnbound.value = "No Joe"
end if
end sub

or

Sub Form_Load ()
if me.txtFirstName.value = "Joe" then
me.txtUnbound.value = "Hello Joe"
else
me.txtUnbound.value = "No Joe"
end if
End Sub

This code seems to show all records the same as the first record so if the
first record has "Joe" in txtFirstName then all records will have "Hello
Joe"
despite the value of txtFirstName on the other individual records. Is
there
a way to make this work? Thanks!
Click to expand...


You have to understand that there is only ever one current record, so doing
things in the Current event won't work for your purpose. What you can do is
have a calculated control that depends on the value of a field in the
record. For example, a control with this as its ControlSource:

=IIf([txtFirstName="Joe", "Hello Joe", "No Joe")

You can write your own function in the form's module or a standard module,
and then call it in the ControlSource expression; e.g.,

' ------ code in form's module -----
Function SayHi(vName As Variant) As String

If IsNull(vName) Then
SayHi = Null
Else
SayHi = "Hello, " & vName & "!"
End If

End Function
'------ end code -----

And then in the text box's ControlSource, have this expression:

=SayHi([txtFirstName])
 
That's very cool... Thanks for your help. I knew on current wasn't right but
I wasn't sure what I should do... now I do... thanks so much!

Dirk Goldgar said:
ArielZusya said:
I've got a continuous form, frmMain, that displays a list of the records
in
tblMain. Each record appears as a hyperlink and when clicked the record
will
open a form, frmInd, with just the record clicked displayed. I'd like to
have an unbound text field display some information on the frmMain based
on
the record it's next to. I tried using the on current and the on load to
do
an if-then statement to test the value in another field but that doesn't
work. That code was something like:

sub Form_Current()
if me.txtFirstName.value = "Joe" then
me.txtUnbound.value = "Hello Joe"
else
me.txtUnbound.value = "No Joe"
end if
end sub

or

Sub Form_Load ()
if me.txtFirstName.value = "Joe" then
me.txtUnbound.value = "Hello Joe"
else
me.txtUnbound.value = "No Joe"
end if
End Sub

This code seems to show all records the same as the first record so if the
first record has "Joe" in txtFirstName then all records will have "Hello
Joe"
despite the value of txtFirstName on the other individual records. Is
there
a way to make this work? Thanks!
Click to expand...


You have to understand that there is only ever one current record, so doing
things in the Current event won't work for your purpose. What you can do is
have a calculated control that depends on the value of a field in the
record. For example, a control with this as its ControlSource:

=IIf([txtFirstName="Joe", "Hello Joe", "No Joe")

You can write your own function in the form's module or a standard module,
and then call it in the ControlSource expression; e.g.,

' ------ code in form's module -----
Function SayHi(vName As Variant) As String

If IsNull(vName) Then
SayHi = Null
Else
SayHi = "Hello, " & vName & "!"
End If

End Function
'------ end code -----

And then in the text box's ControlSource, have this expression:

=SayHi([txtFirstName])



--
Dirk Goldgar, MS Access MVP
[URL="http://www.datagnostics.com"]www.datagnostics.com[/URL]

(please reply to the newsgroup)
Click to expand...
 
Back
Top