[RESOLVED] Access VBA Events

[dataempress]

Hi All,

I started a project that seemed simple enough at first, but has since spiraled out of control. I'm looking to automate one of our processes by using the value of one field to determine another.

Firstly, I'm using Access 2007. What I want to do is figure out a field called "placement" from a student's test scores. That part was difficult but I figured it out. The next thing I want to do, I'm getting stuck on. I want to now calculate an assessment value field based on placement. For example, if placement is 100, assessment value should be 50. My problem is, the only events that will allow the assessment value field to populate are "on click" and "lost focus".

I don't want to have to go through all the records and click on placement in order to populate assessment value. That would defeat the purpose, since I plan to import the test scores that placement is based on in the first place. I want the form to populate the table and not vice versa. Is there any way to do this? Thanks in advance!

[Ecniv]

1)
if you manually populate your form from unbound source, you can code in the update

2)
on active event?

3)
put it in a field too. After import, run an update query that sets it

[Spoo]

dataempress

I use Access alot, but typically "drive it" using VB6. So, for example,
I might have a form with a button on it, and if I click that button,
it will loop through all the records in a recordset and do something.

You seem to be wanting to do this using Access VBA.
--1. Do you have a form that displays the data in field "placement"?
--2. Could you add a button to that form?

If yes to 2, that could be the way to capture the "event". You
could then put your code within that button click sub.

Does that help?
Spoo

[visualAd]

Hi All,

I started a project that seemed simple enough at first, but has since spiraled out of control. I'm looking to automate one of our processes by using the value of one field to determine another.

Firstly, I'm using Access 2007. What I want to do is figure out a field called "placement" from a student's test scores. That part was difficult but I figured it out. The next thing I want to do, I'm getting stuck on. I want to now calculate an assessment value field based on placement. For example, if placement is 100, assessment value should be 50. My problem is, the only events that will allow the assessment value field to populate are "on click" and "lost focus".

I don't want to have to go through all the records and click on placement in order to populate assessment value. That would defeat the purpose, since I plan to import the test scores that placement is based on in the first place. I want the form to populate the table and not vice versa. Is there any way to do this? Thanks in advance!

If your field is a calculated field you are better off having it generated on the fly (unless it seriously impedes performance - otherwise you risk data becoming out of date in your calculated field if changes aren't propagated) you can either point the record source of a form at the query or modify the control source of the field which is calculated to read something like the following:

=[field1]*[field2]

I would prefer the second option; as queries are really for retrieval of data rather than update For your information, if you want to trigger an event every time the record pointer is changed, use the form OnCurrent event.

[dataempress]

Hi All,

Thank you so much for responding. I was away from my desk for a few days so I'm just catching up with the replies.

To answer Spoo's questions:

1. Do you have a form that displays the data in field "placement"? Yes
2. Could you add a button to that form? Yes

The only reason I didn't do this was because I was coding If Then events in the on click events of the button, and I had to go through it record by record, which seemed to defeat the purpose for me. I never even thought of looping. Honestly, I have the hardest time wrapping my brain around looping in VB. I can do it okay in Java but in VB I'm never sure which loop to use or how the formats are set up. So I end up writing far too many If/Then statments. If you could give me any insight on how to loop through a recordset I'd really appreciate it.

I have to do an update query at the outset, in order to get my information imported into the table. The table is filled with many fields and I only need to import about 5 fields into specific records. I really wish there was a way to just import the fields you need into the records you need. So I have to import the information into a temporary table and run an update query.

I wish I could make my destination field calculated. That would solve the problem. Unfortunately, I didn't make the database, so I don't really have the power to change a field. That's why I was calculating a value in my calculated field and using the afterupdate event to try to populate my destination field.

Am I making any sense?

[Spoo]

dataempress

The following code frag gives the jist of looping
through a recordset (using DAO -- ADO would be
similar). It is written in VB6, but VBA should be similar

Dim DB99 As Database
Dim RS99 As Recordset
Set DB99 = Workspaces(0).OpenDatabase("c:\my stuff\myMDB.mdb")
Set RS99 = DB99.OpenRecordset("myRS", dbOpenTable)
rc = RS99.RecordCount
RS99.MoveFirst
For ii = 1 to rc
    ' 1. extract value of field placement
    v1 = RS99("placement")
    ' 2. calculate the value for assessment (your algorithm may be different)
    v2 = v1 / 2
    ' 3. put the value into the RS
    RS99.Edit
    RS99("assessment") = v2
    RS99.Update
    RS99.MoveNext
Next ii

Hope that helps.
Holler if you need any clarifications.

Spoo

[dataempress]

With Access 2007, I'm not sure whether to use ADO or DAO. Do I have to set any references?

Set DB99 = Workspaces(0).OpenDatabase("c:\my stuff\myMDB.mdb")
With this line of code, do I have to open the database? This might seem like a stupid question, but if I'm in it, isn't it already open?

Set RS99 = DB99.OpenRecordset("myRS", dbOpenTable)
In this line of code, would I be able to specify certain records, since not every record in my database needs to be changed?

Set RS99 = DB99.OpenRecordset("myRS", dbOpenTable)
Does this recordset need to be from a table, or can it be from a query? Can "myRS" be the name of a query?

ii = 1 to rc
' 1. extract value of field placement
v1 = RS99("placement")
' 2. calculate the value for assessment (your algorithm may be different)
v2 = v1 / 2
' 3. put the value into the RS
If placement=x; assessment=y, would I put that statement under v2?

Thank you so much for your reply. I've just never looped successfully in VB, and I want to get this right. I know it's important to learn.

[osulliga]

I use ADO for my Access 2007 programs. The reference will depend on the version of Access that is being used. For example, I have Access 2007 so I naturally picked the latest version of ADO which is Microsoft ActiveX Data Objects 2.8 Library, but since the program I was developing was for a group that still uses Access 2000 I had to change the reference to 2.1 and had to alter some of the code.

This is how I would declare the objects of an ADO Connection(your database) and a Recordset(Table/Query)

Public myDB As ADODB.Connection
Public myRS As ADODB.Recordset

There are a couple of ways to set the Connection object(which I will refer to as myDB from now on)...
1) If you are accessing a table from the same database, you can use the following code...

Set myDB = CurrentProject.AccessConnection

2) If the recordset you are looking to connect to is in another database, you will need the following...

Set myDB = New ADODB.Connection

----With this setup there is an additional step that you will need to perform, and I do it with the following code

With myDB
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source").Value = ***path/name of db here***
    .Open
End With

Now for the Recordset(which I will refer to as myRS from now on)...

Set myRS = New ADODB.Recordset

---This is how you will instantiate the myRS(or whatever your Recordset object is called)

To give myRS some values you will need to do the following...

With myRS
    Set .ActiveConnection = myDB     <-----setting the recordsets home to the myDB
    .Source = "SELECT * FROM yourTable"  <----SQL Statement string that will grab the data that you specify
    .LockType = adLockOptimistic <----I usually set my Recordset LockType property as this because I frequently am updating the data within my recordsets
    .Open
End With

[osulliga]

for the looping I would use a For...Next iteration to go through the records...

This is how I would implement that...

1) you will need to declare an Interger data type variable and a variable to hold the RecordCount as an Integer data type

Dim i as Integer
Dim myRecCount as Integer

2) assign value to the variables...

i = 1  'Sets to record 1
myRecCount = myRS.RecordCount 'This will get the number of records in the Recordset you have selected

3) create the iteration...

For i to myRecCount
 
                             '****Your Code Here*****
 
Next

[Spoo]

dataempress

With Access 2007, I'm not sure whether to use ADO or DAO. Do I have to set any references?

I'm not sure. I referenced DAO as that is the only format that I am familiar with.

Set DB99 = Workspaces(0).OpenDatabase("c:\my stuff\myMDB.mdb")
With this line of code, do I have to open the database? This might seem like a stupid question, but if I'm in it, isn't it already open?

That line of code "opens" the database.

Set RS99 = DB99.OpenRecordset("myRS", dbOpenTable)
In this line of code, would I be able to specify certain records, since not every record in my database needs to be changed?

I'm not certain how to answer this. My inclination is that the above statement
would be used to "open" the record set. A subsequent statement may be
required to specify the query.

Set RS99 = DB99.OpenRecordset("myRS", dbOpenTable)
Does this recordset need to be from a table, or can it be from a query? Can "myRS" be the name of a query?

Possibly. I'm afraid I don't work enough with queryies to be able to answer.

ii = 1 to rc
   ' 1. extract value of field placement
   v1 = RS99("placement")
   ' 2. calculate the value for assessment (your algorithm may be different)
   v2 = v1 / 2
   ' 3. put the value into the RS
    RS99.Edit
    RS99("assessment") = v2
    RS99.Update
    RS99.MoveNext

If placement=x; assessment=y, would I put that statement under v2?

I was being a little pedantic in my example, wherein the separate
variables v1 and v2 were used to emphasize the steps involved.

To your direct questions:

-- If placement=x, then v1=x. v1 = RS99("placement") assigns the value
of field "placement" to the variable v1 for the given record being "looked at"

-- If assessment=y, the v2=y. My understanding is that you want to
calculate a value for "assessment". I have made the simplifying assumption
that you merely divided by 2. If I am wrong, then this would be the line
at which you could express the proper algorithm.

Actually populating the field "assessment" takes several lines of code.
Those are presented in step 3, put the value into the RS.

Does that help?

Spoo

[dataempress]

Hi All,

Thank you so much for your help. I really appreciate you taking the time to give such detailed replies. I have a better understanding of looping and that seems like a good solution. I will give that a try. Thanks again!