Data Parsing from Text Box

[rmercado]

I have unbound Textbox as txtEntry

In the textbox, recorded is the Score of each Employee as follows:

Employee1 01 02 03 08 02 05 Game1
Emp2 02 05 08 01 Game5
Employ1 08 05 06 08 09 12 11 01 15 13 02 03 Game3

I have button (cmdSave) to record this entry in a table (tblGame)
Maximum number entry per game is 12.
I want to record each value as follows:
EmployeeName --> tblGame/Emp
Score1 --> tblGame/Scr1
Score2 --> tblGame/Scr2
Score3 --> tblGame/Scr3
Score4 --> tblGame/Scr4
Score5 --> tblGame/Scr5
Score6 --> tblGame/Scr6
Score7 --> tblGame/Scr7
Score8 --> tblGame/Scr8
Score9 --> tblGame/Scr9
Score10 --> tblGame/Scr10
Score11 --> tblGame/Scr11
Score12 --> tblGame/Scr12
GameID --> tblGame/Game

 

[IndianSummer]

Hi Mercado,

Are you stuck with this structure? If i am understanding you correctly, the
way you have your table and form setup at the moment it will be quite
difficult to achieve your goal.

Things would be heaps easier if you could change your table structure (and
form) to include fields Employee(ID?) (Employees could be selected from a
combobox), GameNo, EntryNo and Score. Your data entry may take a bit longer
as you would have to enter a record for each score/game. But it would enable
you to easily enter as many combinations as you like for each person/game.

To ensure a max of 12 entries per person per game, when you do your data
entry you could apply a filter to the combo of Employee and GameNo and a
descending sort on EntryNo, to do a manual check that their EntryNo is <=12.
(this validation can also be automated if you know any VBA?)

Otherwise, you may be better off setting up your structure up in Excel and
using the Data Form there?

Btw I am not an MVP in anything, but have done a lot of work in Access (and
Excel and other Office apps) over the years The others here may have
better ideas...

cheers,

Helen

 

[Jennifer Carlson]

Hi Mercado,
if anybody is from rockfalls illinois there is a severe thunderstorm
warning out until 10o just wanted to let everyone know
Are you stuck with this structure? If i am understanding you correctly,
the
way you have your table and form setup at the moment it will be quite
difficult to achieve your goal.

Things would be heaps easier if you could change your table structure (and
form) to include fields Employee(ID?) (Employees could be selected from a
combobox), GameNo, EntryNo and Score. Your data entry may take a bit
longer
as you would have to enter a record for each score/game. But it would
enable
you to easily enter as many combinations as you like for each person/game.

To ensure a max of 12 entries per person per game, when you do your data
entry you could apply a filter to the combo of Employee and GameNo and a
descending sort on EntryNo, to do a manual check that their EntryNo is
<=12.
(this validation can also be automated if you know any VBA?)

Otherwise, you may be better off setting up your structure up in Excel and
using the Data Form there?

Btw I am not an MVP in anything, but have done a lot of work in Access
(and
Excel and other Office apps) over the years The others here may have
better ideas...

cheers,

Helen

 

[rmercado]

Hi Helen,

It seems that I dont have other option if I got those information via mail.
Copy & Paste that value to the Text Box then press save command.
validation will be initiated once data is transfered to table.

the possible interactive validation I can do from the text box is to check
if the Employee name is correct and the Game ID.

Sorry to bother, but I want to learn more about access (I am newbie)

 

[Jamie Collins]

validation will be initiated once data is transfered to table.

Agreed, relying on the front end form to ensure data integrity
introduces unnecessary risk.
Perhaps the easiest way to do this at he table is to use a 'sequence'
column (unique integers for each key value), incorporate this column
into the PK (or other UNIQUE constraint) and put a validation rule on
the sequence column e.g. BEWEEN 1 AND 12. Example (aircode):

CREATE TABLE OrderItems
(
order_number CHAR(10) NOT NULL
REFERENCES Orders (order_number),
order_item_seq INTEGER NOT NULL,
CONSTRAINT max_12_items_per_order
CHECK (order_item_seq BETWEEN 1 AND 12),
UNIQUE (order_number, order_item_seq),
...
);

Jamie.

--

 

[rmercado]

Opps,

I forgot to mention, I have manual entry and it is already working:
I entered the detail manually.

The Problem is if I receive the details via email in bulk. Let say 20 - 25
records, I dont want to enter those info one-by-one.

So I instructed them to format their data (as seen from the example) as a
start.

I am thinking if the following are possible: Please suggest possible
function for each....

Can I count the lines in the Box?
Can I count the blanks in each line in the Box?
Can I get each blank location for each line in the Box?

Thanks to all of you...

 

[IndianSummer]

Hi again, yes I understand re: newbie. Look i do a lot of training in
Access and I think for starters you should stay focussed on the fundamentals,
and avoid situations that require programming to resolve.

By fundamentals I mean, be clear on the purpose of your database, the output
you want from it, and that your tables are well designed to serve that
purpose. All other objects are built on your table structure - all your
queries, forms and reports, so it is essential that your "foundations" are
sound.

From what i understand from your initial question I believe that your table
is not well designed - sorry. It will be very difficult for you to extract
and manipulate your data with this design. So i say again, look at changing
your table structure as your first step.

 

[Monique Kawamitsu]

Opps,

I forgot to mention, I have manual entry and it is already working:
I entered the detail manually.

The Problem is if I receive the details via email in bulk. Let say 20 - 25
records, I dont want to enter those info one-by-one.

So I instructed them to format their data (as seen from the example) as a
start.

I am thinking if the following are possible: Please suggest possible
function for each....

Can I count the lines in the Box?
Can I count the blanks in each line in the Box?
Can I get each blank location for each line in the Box?

Thanks to all of you...

 

[Renate.Hilber]

Vielen Dank für die zahlreichen mails, leider kommt es mit der Sprache nicht
so gut hin, ich verstehe, deutsch, ein wenig weniger franz. und ital. danke