I need to touch and loop

  • Thread starter Thread starter rrwyatt
  • Start date Start date
R

rrwyatt

Very simply, I have a form in a very complex application I've
inherited. On that form is a date field which, if changed, fires off
all the action I need to do (a LOT of it) in the access form. (This is
a Jet database, not real triggers.) It's set by a lost_focus even
that checks the value.

However, I need to do this for several hundred records so I want to
automate it from a separate form. I can change the value, but it
doesn't fire the changed event. I tried creating a public procedure in
the target form to call the update event procedure, but my calling
form can't seem to find it. (Maybe not calling it right?)

I'm a little rusty at this, so any help appreciated. Thanks!
 
I would not use the lost focus, since that always occurs when the users
moves out of the control on the form in question.

You want to likely use the after update event (which does not fire if the
user enters..and then exits the field without any changes).

So, lets now assume we talking about the after update event.

in the after update event of the date field, you can run your processing
code.

Perhaps this code is a bunch of sql statements and updates to other tables.
Typically
you can leave the code in the forms module, or move out the processing code
into a standard code module. (this would be a good idea if you plan to
call/use the code in more then one place in the application).
However, I need to do this for several hundred records so I want to
automate it from a separate form.
Click to expand...

Now you lost me? What do you mean separate form? I thought we just decided
went he user updates the date value, then we going to run some update code?
What does this have to do with another form??? (you are confusing me now).
As mentioned, you can run code that updates data..and that code does not
have to belong, or have ANY relation to a particular form. Code can well
update data...and does not have to use a form, or controls on a form to
modify data.
I can change the value, but it
doesn't fire the changed event.
Click to expand...

Changing values in code does not trigger the event code (can you imagine
trying to run code that changes a bunch of values on a form???).

You might want to just explain what the update is supposed to do after the
date field is changed...

Typical to update tables via code, we use sql statements in code, or in some
cases reocrdsets. not knowing what kind of update you need here, it hard to
guess which approach is better...
 
I would not use the lost focus, since that always occurs when the users
moves out of the control on the form in question.

You want to likely use the after update event (which does not fire if the
user enters..and then exits the field without any changes).

So, lets now assume we talking about the after update event.

in the after update event of the date field, you can run your processing
code.

Perhaps this code is a bunch of sql statements and updates to other tables.
Typically
you can leave the code in the forms module, or move out the processing code
into a standard code module. (this would be a good idea if you plan to
call/use the code in more then one place in the application).


Now you lost me? What do you mean separate form? I thought we just decided
went he user updates the date value, then we going to run some update code?
What does this have to do with another form??? (you are confusing me now).
As mentioned, you can run code that updates data..and that code does not
have to belong, or have ANY relation to a particular form. Code can well
update data...and does not have to use a form, or controls on a form to
modify data.


Changing values in code does not trigger the event code (can you imagine
trying to run code that changes a bunch of values on a form???).

You might want to just explain what the update is supposed to do after the
date field is changed...

Typical to update tables via code, we use sql statements in code, or in some
cases reocrdsets. not knowing what kind of update you need here, it hard to
guess which approach is better...
Click to expand...

I'll try to explain. In 'normal' mode everything works fine. User
selects (on form A) from a bunch of records and form B comes up. If
the user changes for B, the (you're right) after-updated event does a
ton of processing.

The trouble is I want the same processing without the user touching
anything. Esssntially emulating if a user had done this hundreds of
times. The external form is to skip form A and call form B for each
of the hundreds of records and trigger the after_update event as if a
user had entered it. Trying to emulate the code in that event would
very difficult as it is extemely funky.
 
Ok, so, in effect, we could launch a simple form with a button on it, and
when pressed, it would process a given set of records..and runt he code for
each record.


a typical update in code looks like:

dim strSql as string

strSql = "update tblCustomers set City = 'New York'" & _
" where city = 'N.Y.'"

currentdb.Execute strSql

So, the above would look for all records that are "N.Y.", and update them to
the full name of "New York"

So, typical the processing of data is done with sql. You can like build a
query that joins together the two tables you need, and use a single update
as above (your ability to do this is going depend on your sql skills).

Another approach (one we might have to use) is to use a reocrdset, and that
allows us to process each record by record.

Sub MyProcess()

Dim rstParent As DAO.Recordset
Dim strSql As String
Dim lngID As Long

strSql = "select * from tblMain where active = true"

Set rstpartent = CurrentDb.OpenRecordset(strSql)

Do While rstParent.EOF = False

rstParent.Edit
rstParent!Active = False
rstParent.Update

rstParent.MoveNext

Loop

rstParent.Close

End Sub

The above processing loop would take each record, and set the "active" field
to false.

The above is not the best example since we could use a single sql update
statement such as

currentdb.execute "update tblMain set active = false where active = true"

So, using sql update statements is preferred.

In your example, it seems we have a set of "main" records, and for each main
record, we need to execute a update on the child records. Hence, we could
use something like:



Sub MyProcess()

Dim rstParent As DAO.Recordset

Dim strSql As String
Dim lngID As Long

strSql = "select * from tblMain where active = true"

Set rstpartent = CurrentDb.OpenRecordset(strSql)

Do While rstParent.EOF = False

strSql = "update tblChild set somefield = somevalue " & _
" where main_id = " & rstParent!ID
CurrentDb.Execute strSql

rstParent.MoveNext

Loop

End Sub

so, you don't actualy "call" the after update code, but simply "reproduce"
the same code in the above loop to update the records in the manner in which
you need.

As a general rule, data processing code does not run, or use forms "after
update" event code. So, simply lay out the logic, and how the update is
supposed to work, and then write code as a above....

That is why I said it better to leave out the forms mumbo jumbo,a nd simple
state the problem:

eg:

for each active record in a main table, I need to update some
relented records in a child table.

Now, not being a mind reader, I don't know how you plan to select those
records that need updating..but the above gives the general idea....

If you are new to running update code, MAKE SURE you ALWAYS ALWAYS ALWAYS
make a backup..as this type of code can trash and "run over" data in a table
in a flash!!

So, in effect, the idea is not to run the after udpate code over and over,
but layout the problem at hand, and then write the code to solve that
problem...
 
Back
Top