creating input masks

[timeh]

I am designing a database for a scoring system at a local sports event.

There are 4 teams in the competition
the user enters a position (chosen from a drop down list) for each team.
what i want is for the position to give a corresponding number of points
e.g.
if the user selected 1st place for team1, 10points would be allocated to team1.
i have made a text box next to the combo from which a position is chosen and changed it's control source to that of the combo box.
i am relatively new to access but i wonder it might be possible to edit an input mask, so that "10" appears in the text box when the user selects "1st" from corresponding combo box.

any help would be very much appreciated, but bare in mind
that i'm a beginner so please try to keep things simple for my inferior mind

thanks in advance

 

[Alexandre]

Na input mask is not what you need.
You should make a lookup table holding correspondances between ranks and number of points:

TblPointsPerRank
- RankNumber (Integer primary key, not autonumber)
- PointsPerRank

Then, your uses would choose the rank for example from a combobox, and using the after update event of the combo, you would display on the form the related number of points from your table.

 

[timeh]

Thanks mate, i've done what you sed, however, i'm not sure what code to put in the 'after update' field on the combo box.
You select the position from the Combo box for say 'team1' - i then want the corresponding points to appear next to the combo.

For example, if "1st" was selected, i would want "6" to appear in a box next to that. ( i now have a table containing the positions and their corresponding points like you said) - and the position i choose from the combo box is a lookup from the position in that table

Any help from anyone would be much appreciated
thanks in advance

 

[pitou]

your question

hello Timeh

I read your question and hope to answer wholly to it
Sometimes I fear to answer because technical terms are a bit ambiguous and making a fool of oneself is not so far away ....

Well I created a database with two tables inside (cf previous Alexandre's idea)
the first one has for name "toto" with two fields field1 and field2 (string, both of them)
in these two fields just put those following values :

one 10
two 20
three 30
four 40

the second table has for name "table_tmp' and has a string field named "field2". No values for the moment.
Let Create a form. On this form you will position as you said, one dropdownlistbox, on the left side, and a listbox on the right side

You will name the dropdownlistbox "tag1" and the listbox "toto"

You will create, in the database, a query named "query1"
Il will have as script :

--------------------------------------------------

INSERT INTO table_tmp ( field2 )
SELECT toto.field2
FROM toto
WHERE (((toto.field1)=[Formulaires]![Formulaire1]![tag1]));

-----------------------------------------------

Populate the dropdownlistbox with the same words like before : one; two; three; four (it may be your teams). When you click the dropdownlistbox, these four words must appear.

Still in the dropdownlistbox, paste this code. Choose for that the event "change"

---------------------------------------------

Private Sub tag1_Change()

Dim dbs As Database
Set dbs = CurrentDb()

DoCmd.SetWarnings False

'Erase the previous value

dbs.Execute "delete * from table_tmp"

' Trigger the query and load the table 'table_tmp'
' with the new value

DoCmd.OpenQuery "query1"

' refresh ans requery the whole form

Me.Refresh
Me.Requery
End Sub

----------------------------------------------------

For the listbox, the result of this control has to be the new value of the table "table_tmp" . you can be helped and assisted automatically when you create the listbox. Access asks you if the listbox has to have as source of information such query or field or table, so choose field2 of the table "table_tmp"

So now you can run the form and the dropdownlistbox. If you choose "four" in the dropdownlistbox, "40" must normally appears in the listbox.

I m sorry , all that may seem a bit complicate but it is all what I have to offer !
Hope also to have answered, even partly, to your problem.

Pitou

 

[timeh]

pitou mate,

thank you very much for taking the time to do all that, i really do appreciate it.

I've started a new database to test out your idea before trying to incorporate it into my own. The only problem arises when i input the code you suggested for the drop down box, i.e.

-----------------------------------------------------------------

Private Sub tag1_Change()

Dim dbs As Database
Set dbs = CurrentDb()

DoCmd.SetWarnings False

'Erase the previous value

dbs.Execute "delete * from table_tmp"

' Trigger the query and load the table 'table_tmp'
' with the new value

DoCmd.OpenQuery "query1"

' refresh ans requery the whole form

Me.Refresh
Me.Requery
End Sub

-------------------------------------------------------------------------------

It's probably something i've forgotten to do or sumthin, but when i select a number from the dropdown it brings up an error message saying

compile error: user-defined type not defined

and it highlights
"dbs As Database" in the code, suggesting that this is where the problem lies. I have included what i've done so far in this database in the post, so if you have any time to look over it you may be able to see what i've done wrong
p.s. i'm not too sure about the query - i entered that code in "sql view" which i am unfamiliar with, being a beginner

Again thank you for taking your time to help me - the assistance is very useful and i am most grateful

 

[Rich]

I don't have your version of Access but I think it should be
Dim dbs As DAO Database, search here for references

 

[pitou]

your database

hello timeh

I think I fixed the problem. The application that I send, works.

In fact, you will remark that I destroyed the query "query1" and pasted it's code into the code of the dropdownlistbox (have a look at it).

One thing that might cause troubles is that I have the Access software but all in French!
secondly to solve the message
"compile error: user-defined type not defined", you must be, in the window, where you can see the VBA code, in the menus bar select :
tools ---> references
Then, an other window appears where you have to choose
"Microsoft DAO 3.6 object library"
Why that ? by default, access 2000 take into account the ADO Model which must replace in the forthcoming years the DAO model. The DAO model enables you to work only with Access, not others SQL databases.

So Rich is also true

try the db i send you
hope that everything is under control

cordiales salutations