Autofill Fields

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have two tables:

Table 1 with fields: Rank Code (Numbers) & Rank (Text).
Table 2 (Main Table) with various other personal fields but include Rank
Code & Rank.

I need the rank code to assist me in sorting the records by rank.

I would like to select the rank eg enter Maj, in a form and the rank code be
inserted automatically and entered in the rank code column in table 2.

A bit long winded but help would be much appreciated.

Dave
 
ON the form, select from Combo Boxes that have the Table1
fields in the Query. IF you only have the two fields,
add a third that is Rank_ID and make it Autonumber. Then
in Table 2 take out Rank and Rank Code and replace with
RankID, only store the Rank_ID, which will now "relate"
(ie - relational database structure vs flat) the code and
rank. Select two values, the ID and Rank but only
display one (ie Mjr) by setting the Column widths to
0";2" and the Column Counts to 2 (these are on your
property settings under the Format Tab. You will see the
Rank to select and store the ID (make sure the BOUND
COLUMN - Data Tab - is set to 1, which it is by default)
in your table2. Anytime to wish to sort on either Code
or Rank, you can query table2 and add table1 to the query
and relate RankID in table1 to table2 and place either
field, Rank or Code in your query to search upon.

If you do not wish to change your tables, use the same
Combo Box idea for both Rank and Code and have one update
the other when you select the value. Insert code in the
AFTER_UPDATE event. Make sure you include BOTH the Rank
and Code in your Combo Box query. Your Code would look
something like this depending on the names of your
controls (text boxes/Combo Box):

Private sub cmbRank_AfterUpdate()

me.cmbRankCode = me.cmbRank.Column(1)

End Sub

' Combo Boxes are ZERO based which means that the first
record is Column(0)

Confused?? Hope not!! x-F/A-18 Fighter Pilot and this
sounded military. Good luck.

Drew
 
Drew's already shown you an approach to your problem. I just wanted to
suggest that you should not be storing both the code and text in Table 1.
All you should be storing in Table 1 is the Rank Code. For those times when
you want to display the contents of Table 1 and show the Rank text, create a
query that joins the two tables together, and use the query instead of the
table. This is a basic part of using relational databases: you shouldn't
store information redundantly.
 
Back
Top