Form Event procedure

[Adrienne]

I 'm trying to establish a VBA event prodcedure on my form
that fills in other fields automatically. I have 150
different Account numbers and after each account number I
have a research field and division field a pool field and
a notes field that should be automatically updated once
the event reads the account number. Someone gave me an
event procedure that fills in other fields automatically
for zip codes and it looks something like this:

Private Sub ZipCode_AfterUpDate

If ZipCode is entered fill in city and state automatically.
Select Case Zip Code

Case "10000" to "10200"
City = "New York"
State = "NY"

End Select
End Sub


My information looks like this:

ACC#:0789 Research:LAB1 DeptCAE Pool:RSUMS Notes:R&D


If Account Number is entered fill in Research,Dept,Pool
and Notes.

Case "0789"
Research = "LAB1"
Dept = "DCAE"
Pool = "RSUMS"
Notes = "R&D"

End Select
End Sub


Should I establish a new text box and open the VBA window
to make this happen?? I'm not sure. I need help?

 

[Kevin Sprinkel]

Adrienne,

There should be no need to "auto-fill" these fields.
Access is a relational database, meaning that if those
four field values are associated with a given account
number, define them ONCE, then they are forever available
to you via a query.

I suggest you create a new table, called, say, Accounts,
which will have a one-to-many relationship with your
original table:

Accounts
========
AccountNumber Number (Key Field)
Research
Dept
Pool
Notes


Now create a query based on your original table and the
new Accounts table. Drag the AccountNumber field from one
table to the other to establish the one-to-many
relationship. You may also do this through View,
Relationships, in which case any query you create will
inherit this relationship.

Select all the fields of your original table except the
AccountNumber field, then select all the Accounts fields.
Base your form on this query. Now when you enter the
AccountNumber, all of the other field controls will
display the correct information associated with the
AccountNumber, but your table will *store* only the
AccountNumber foreign key.

This is a quite common misunderstanding, which I struggled
with for some time when I started with Access. It points
out the difference between a "field", which stores data in
a table, and a form or report "control" which displays or
prints data. A control can take as its source a table
field, a query field, a calculation or constant, the
result of a function, etc.

HTH
Kevin Sprinkel

 

[sanfu at techie dot com]

I 'm trying to establish a VBA event prodcedure on my form
that fills in other fields automatically. I have 150
different Account numbers and after each account number I
have a research field and division field a pool field and
a notes field that should be automatically updated once
the event reads the account number. Someone gave me an
event procedure that fills in other fields automatically
for zip codes and it looks something like this:

Private Sub ZipCode_AfterUpDate

If ZipCode is entered fill in city and state automatically.
Select Case Zip Code

Case "10000" to "10200"
City = "New York"
State = "NY"

End Select
End Sub


My information looks like this:

ACC#:0789 Research:LAB1 DeptCAE Pool:RSUMS Notes:R&D


If Account Number is entered fill in Research,Dept,Pool
and Notes.

Case "0789"
Research = "LAB1"
Dept = "DCAE"
Pool = "RSUMS"
Notes = "R&D"

End Select
End Sub


Should I establish a new text box and open the VBA window
to make this happen?? I'm not sure. I need help?

Adrienne ,

That might be ok if you had 1 - 5 case numbers (but time consuming with
150 - and what happens if you have to add or change info?), but if the
data is in a table, you can use the afterupdate event to fill in the
other controls.

How are you selecting the Account Number? Are you typing it in an
unbound text box or using an unbound combo box?

If the data is in a table, what are the field names ant the field types?


The code would look something like this (** AIR CODE **):

For a combo box named cboAccount, the combo box row source might be:

Select lngAcctID, txtAcctNum, txtResearch, txtDept, txtPool, txtNotes
From tblTableName
ORDER BY txtAcctNum;


The code in the afterupdate event for the combo box:

Private Sub cboAccount_AfterUpDate()

' columns are zero based
' control_on_form = combo_box_column()

AccNum = Me.cboAccount.Column(1) ' "0789"
Research = Me.cboAccount.Column(2) ' "Lab1"
Dept = Me.cboAccount.Column(3) ' "DCAE"
Pool = Me.cboAccount.Column(4) ' "RSUMS"
Notes = Me.cboAccount.Column(5) ' "R&D"


End Sub


This is assuming that all fields above are text except lngAccID
(autonumber).


You could also use an unbound text box, using the AfterUpdate or
LostFoucs event with similar code.


HTH

 

[Adrienne]

I have to use a VBA procedure in this model as a part of a
grade for a class. I was ask by the instructor to use an
event prodcedure to auto fill these fields based on the
account transaction table. Here are my two tables:

Table# 1
Account Transaction)
Table#2(Original Trans Table)
Account# 5754 Account# 8425754
Dept FY
Research Serial#
Pool Joint Funding
Notes Total amount of transaction


Please help!!!!

 

[Kevin Sprinkel]

-----Original Message-----

I have to use a VBA procedure in this model as a part of a
grade for a class. I was ask by the instructor to use an
event prodcedure to auto fill these fields based on the
account transaction table. Here are my two tables:

Table# 1
Account Transaction)
Table#2(Original Trans Table)
Account# 5754 Account# 8425754
Dept FY
Research Serial#
Pool Joint Funding
Notes Total amount of transaction


Please help!!!!

I suggest you follow Sanfus advice, then, and use the
Column function. Things to consider.

- Column is zero-based; the first column is labeled 0,
then 1, 2, etc.
- Be sure that the Column Count property is equal to the
number of columns.
- You needn't display all columns in the combo box. Just
set their Column Width property to 0", e.g., a Column
Width of

0";2";0";0"

would only display the 2nd column, i.e., Column(1)

 

[Adrienne]

To answer your question, the account number is from
another ODBC source that I generated a make table query
from. In other words I have a table with all 150 Account
numbers and the other fields associate with that account
number in table1 that looks like this:

Acct # Research Dept Pool Notes
0789 Lab1 DCAE RUMS Training Project
0927 Lab8 TMMC SREA Research Project
5389 Lab10 RECO CENTERS R & D
5471 Lab19 ANRC TRAINING Projects & Centers

The other transaction table2 looks like this:

Acct # FY Total Amount Type
0789 03 $56,789.00 AI
0927 03 $67,800.00 TM
5389 03 $50,000.00 RP
5471 03 $79,000.00 DC

All I want to do for my class project is to perform a
Visual Basic autofill where Table 1 Account # field looks
at the Account # field in Table 2 and auto fills the other
four columns above Research, Dept, Pool and Notes. I have
over 8,000 transactions in Table 2. I know there are 20
different ways to accomplish this but I trying to use
Visual Basic for the project. Again I have only used MS
Access 1 month so I need very basic step by step
instruction to accomplish this task. Someone in my class
suggested I could use the AfterUpDate event procedure in
Access as the code portion??? But no more detail was
provided. I also looked at your suggestion to create a
combo box named Account # one of my fields in my
form/table is already entitled Account #??? Should I add
a new combo box entitled Account #?? and then proceed with
your instructions? I need more guidance on this!!!
Thanks,

 

[SteveS]

Comments in line

Rant first then questions.....

This is a very good site you should bookmark and refer to to help you (I
have and do).

Specifically, look at this page:

http://mvps.org/access/general/gen0012.htm

You should learn to use a naming convention. I use the Leszynski/Reddick
convention (see above page). What does "Account #???" tell you when you
see it in code? Compare it to txtAccountNum (a text type field in a
table) or cobAccountNum (a combo box for Account numbers).

"Account #???" has a space (never use spaces in names of objects) and
special characters (#???). Spaces and special characters will give you
headaches later when trying to code or maintain your dB.

OK, enough of that....

Questions:
1) What is the primary key for table 1?
2) What is the primary key for table 2?
3) Are the tables linked by Acct #?

To answer your question, the account number is from
another ODBC source that I generated a make table query
from. In other words I have a table with all 150 Account
numbers and the other fields associate with that account
number in table1 that looks like this:

Acct # Research Dept Pool Notes
0789 Lab1 DCAE RUMS Training Project
0927 Lab8 TMMC SREA Research Project
5389 Lab10 RECO CENTERS R & D
5471 Lab19 ANRC TRAINING Projects & Centers

The other transaction table2 looks like this:

Acct # FY Total Amount Type
0789 03 $56,789.00 AI
0927 03 $67,800.00 TM
5389 03 $50,000.00 RP
5471 03 $79,000.00 DC

All I want to do for my class project is to perform a
Visual Basic autofill where Table 1 Account # field looks
at the Account # field in Table 2 and auto fills the other
four columns above Research, Dept, Pool and Notes. I have
over 8,000 transactions in Table 2. I know there are 20

Why look at table2 to get info in table1 that has the same field?
With over 8,000 entries in table2, there must be duplicates in the Acct
# field whereas table1 should have only 1 entry.

different ways to accomplish this but I trying to use
Visual Basic for the project. Again I have only used MS

4) what do you mean by
"........ but I trying to use Visual Basic for the project."
Do you want to use DAO recordsets to fill in the fields?

Access 1 month so I need very basic step by step
instruction to accomplish this task. Someone in my class
suggested I could use the AfterUpDate event procedure in
Access as the code portion??? But no more detail was
provided. I also looked at your suggestion to create a
combo box named Account # one of my fields in my

Actually I suggested creating an unbound combo box named cboAccount and
clicking on the query builder (...) for the row source to fill the combo
box. The afterupdate event would then fill in the other 4 field.

form/table is already entitled Account #??? Should I add
a new combo box entitled Account #?? and then proceed with
your instructions? I need more guidance on this!!!
Thanks,

HTH