number increment depending on other field value

  • Thread starter Thread starter Kresimir Trontl
  • Start date Start date
K

Kresimir Trontl

Hi,
I am a novice Access user trying to create a simple database for managing
articles that I collect.
I have two tables: Area and Articles. Table Area contains symbols for
different types of articles (SC stands for Stamp Collecting; HC for Hobby
crafting, etc.) in a single field.
Two fields in the Articles table are related to Area: in the field Ar, I
select the type of article. In the second field NoAr I would like to have a
number corresponding to that particular area incremented by 1 comparing to
the last article entered in that area. Why the trouble?
So that I can write the combination Symbol + number on the article before
placing it into storage. When I need something, I can search the database
and physically find the article depending on that number.
Example:
SC 1
SC 2
HC 1
SC 3
HC access automatically fills the field with number 2
SC access automatically fills the field with number 4

Kreso
 
Kreso,

Assuming:
* Tour data entry is done through a form
* The control on the form which is bound to field Ar is named txtAr
* The control on the form which is bound to field ArNo is named txtArNo

While in form design, select the txtAr control and display the
properties window (right click);
Select the Events tab;
Place the cursor in the Before Update property;
Click on the little button with the three dots that appears on the
right, and select Code Builder;
when taken to the VBA window, you will see two lines of code like:

Private Sub txtAr_BeforeUpdate(Cancel As Integer)

End Sub

Just paste this line of code in between:
Me.txtArNo = DMax("ArNo", "Articles", "Ar='" & Me.txtAr & "'") + 1
substituting the actual form control names, if different.
Return to the form and saev. The job is done.

HTH,
Nikos
 
Kresimir said:
*Hi,
I am a novice Access user trying to create a simple database for
managing
articles that I collect.
I have two tables: Area and Articles. Table Area contains symbols
for
different types of articles (SC stands for Stamp Collecting; HC for
Hobby
crafting, etc.) in a single field.
Two fields in the Articles table are related to Area: in the field
Ar, I
select the type of article. In the second field NoAr I would like to
have a
number corresponding to that particular area incremented by 1
comparing to
the last article entered in that area. Why the trouble?
So that I can write the combination Symbol + number on the article
before
placing it into storage. When I need something, I can search the
database
and physically find the article depending on that number.
Example:
SC 1
SC 2
HC 1
SC 3
HC access automatically fills the field with number 2
SC access automatically fills the field with number 4

Kreso *
Click to expand...
That presupposes that you know the number. Suppose you you can't
remember the number? A wildcard search would be much more flexible.
 
Tanis said:
That presupposes that you know the number. Suppose you you can't
remember the number? A wildcard search would be much more flexible.
Click to expand...

Tanis,
The search is performed on other fields in Article table, like fields
Authors, Abstract, etc.
The number tracking is necessary for finding the article in the storage room
(my garage).
Kreso
 
Back
Top