continuous form

[Guest]

I have a continuous form setup that displays 5 fields from a table. Is there
a way to access an event for each row as it is loaded into the form?

In other words, I have 10 rows that will be displayed on the continuous
form. I want to alter the data displayed with VBA code for each row as each
row is loaded. The OnCurrent event is only fired when you select one of the
rows after the form is displayed.

Thanks in advance.

 

[Albert D. Kallal]

You can't really do as you wish during the "loading".

However, you can make a function that returns a value,a nd use that as the
source for a text box.


FirstName LastName MyFunction
Albert Kallal Albert
Kallal


FirstName source is [FirstName]
LastNmae source is [LastName]
MyFucntion source is (=(MyCoolName([FirstName],[LastName])))

And, in the forms code we go:

public Function MyCoolName(First as variant, Last as Variant) as string

MyCoolName = First & ", " & Last

end function

So, for any text box, you can pass it some values (like first/last name, or
even a key value), and then the code will "run" for each time the text box
is displayed. So, as long as you bind a text box to a expresson, or
function..this should work...

 

[Van T. Dinh]

Perhhaps you can try to manipulate the Form's Recordset?

Explain with more details:

* What are you trying to achieve?
* Do want these changes to be updated into the Table(s)?
* Can the user edit the data after?

You should give a few rows of sample data from the Form's RecordSource
before after the altering process.

 

[Guest]

Albert... thank you very much for your idea, but unfortunately it doesn't
work...the function is only called once, not for each row returned.

Any other ideas?

 

[Albert D. Kallal]

Albert... thank you very much for your idea, but unfortunately it doesn't
work...the function is only called once, not for each row returned.

Any other ideas?

I not sure what you mean by the above?

If you have a continues form, and bind a text box in that continues form to
a function, then that function DOES get called for each record displayed....

You might want to actually explain what you are trying to do here?

You can use a function, and this function will be called for each row
displayed (not sure why you don't get this behaviors). However, I would not
use this approach to actually "modify" the data, as that don't make sense.
(and, the same goes for a report...it would not make sense to try and have
the mere fact of the running the report modify data - you can certainly
record the fact that report was printed, or have some code run *after* the
report is printed...but the idea or concept of modifying data *DURING* the
report generation don't fly).

If you need to modify the data, then simply run some code that processes all
the records attached to the current form. (why not do this????).

So, I don't think it makes any sense to have some code "modify" the data
during display. However, my suggestion of using a function to display
information based on the current record will certainly let you "display"
data, or show the results of some function, and that value will be based on
each record shown.

I even have a working example of a contnues form with a check box for each
row. This check box is NOT a field, but in fact a function value. This
example lets you "check" a whole bunch of reocrds to incoude into a
report..but in fact I don't have a check box field...but use a fucntion. You
can find the example here:

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

The one you want is the 2nd last one: Multi-Select Example.

 

[Guest]

Albert...
The form is a continuous form that is for display only...so no updating is
going on. Think of a timesheet where each day of the week has a company
name, the type of work and hours. Not everyone works every day. The form
displays the employee name, week ending, total hours, blah, blah and a single
column for company name (the first company name found). I want to look at
each company name for each day of the week and use the first company name
that is not null. I'll try to lay it out...

employeename
weekending
sunday_company
sunday_service
sunday_hours
monday_company
monday_service
monday_hours
(and so on until)
saturday_company
saturday_service
saturday_hours

on the form I have a text box with the control source set t
=SetCompanyName([sunday_customer],[monday_customer],[tuesday_customer],[wednesday_customer],[thursday_customer],[friday_customer],[saturday_customer])

the function in the form looks like this
Public Function SetCompanyName(sunday_name As Variant, monday_name As
Variant, tuesday_name As Variant, wednesday_name As Variant, thursday_name As
Variant, friday_name As Variant, saturday_name As Variant) As String
On Error Resume Next
If sunday_name > "" Then
Me.CompanyName = sunday_name
Exit Function
End If
If monday_name > "" Then
Me.CompanyName = monday_name
Exit Function
End If
If tuesday_name > "" Then
Me.CompanyName = tuesday_name
Exit Function
End If
If wednesday_name > "" Then
Me.CompanyName = wednesday_name
Exit Function
End If
If thursday_name > "" Then
Me.CompanyName = thursday_name
Exit Function
End If
If friday_name > "" Then
Me.CompanyName = friday_name
Exit Function
End If
If saturday_name > "" Then
Me.CompanyName = saturday_name
Exit Function
End If
End Function

when I open the form, all of the Me.CompanyName fields on the form are
blank. If I run it in debug, all of the Me.CompanyName fields have #Error in
them except for the first entry. There are 127 entries in the table.

I really do apprecieate your persistance in this...do you have any idea what
I'm doing wrong?'

Thanks Albert!

 

[Albert D. Kallal]

Ah..ok....

You can't use

if somevalue = null

or

if somevalue > ""

Null values are un-defined, and thus you CAN NOT use them in a expression.
You must "wrap" the possible null value into a function that returns
something that is legal

So, likely, you need something like:

If isnull(sunday_name) = false Then
Me.CompanyName = sunday_name
Exit Function
End If

Further, as a suggestion, you likely would have been better to define this
data with:

EmployeeID WorkDate Hours.


You then just enter a date for workdate...and can extract the weekday with:

weekday(WorkDate)

This would eliminate the difficult in writing code...and you only deal with
3 fields...not 7......

(generating totals and reports is real easy when you do this....).

Anyway, lets leave the data normalizing issue for another day.....and lets
just give the isnull() thing a try....it should work.....

 

[Guest]

I'm an idiot, disregard the previous message....in the function I wasn't
setting the value of the function name to the company name found....it's
working fine...thanks again! sorry to have bothered you again....

 

[Guest]

all is well...thanks for your assistance!!!!!!!!!