Select a control using text manipulation

  • Thread starter Thread starter Espen K
  • Start date Start date
E

Espen K

Hi group.

I need to select a field in a table based on a control in a form and a text
string.
In the form, I have choosed one element, i.e. jump.
The table has fields called jump1A1ex, jump1A1draw, jump1A2ex, jump1A2draw,
jump1B1, jump1B2, jump1B3, jump1B4.
The form will change its apperance if I choose freestanding in sted of jump.
I will therefore like to select which field to update based in which element
I have chosen.
I have tried variations over: ControlSource =[TblBoys.Element] & "1A1ex" in
the form where [TblBoys.Element] = jump , but so far no success.

Can anyone please help?

-Espen
 
Espen,

A question for you before an answer... Is the structure of your table
set in concrete, or are you able to consider a design change? It looks
to me that you have fallen into the "fields as data" trap, and your work
will be much, much simpler and more efficient if your data is stored in
table fields, rather than in the names of table fields.
 
I can change the structure, but I am not shure how that will make things
easier.

The program I have made (am making), is a program to register results for a
gymnastics competition. Each girl are compeeting in four disciplines, boys
in six. Each discipline is judged by six referees, and I have to store the
results from each referee, for each boy and girl, in each dicsipline.

I don't know if this explaines why I have to do what I have done, or if this
just clearifies my mistake.

Espen

Steve Schapel said:
Espen,

A question for you before an answer... Is the structure of your table
set in concrete, or are you able to consider a design change? It looks
to me that you have fallen into the "fields as data" trap, and your work
will be much, much simpler and more efficient if your data is stored in
table fields, rather than in the names of table fields.

--
Steve Schapel, Microsoft Access MVP


Espen said:
Hi group.

I need to select a field in a table based on a control in a form and a text
string.
In the form, I have choosed one element, i.e. jump.
The table has fields called jump1A1ex, jump1A1draw, jump1A2ex, jump1A2draw,
jump1B1, jump1B2, jump1B3, jump1B4.
The form will change its apperance if I choose freestanding in sted of jump.
I will therefore like to select which field to update based in which element
I have chosen.
I have tried variations over: ControlSource =[TblBoys.Element] & "1A1ex" in
the form where [TblBoys.Element] = jump , but so far no success.

Can anyone please help?

-Espen
Click to expand...
Click to expand...
 
Espen,

Thanks for your further explanation, which helps me to see what you have
done, and yes, it clarifies your mistake. The approach you have taken
is the kind of thing that might be done in a spreadsheet or some such,
but it is not really applicable in a database.

I think it is best to just forget about the forms for now, until you get
the tables set up properly. Without knowing more about your actual data
requirements, I am only guessing here to some degree, but here is an
outline to give you an idea...

Table: Competitors
CompetitorID
CompetitorName
Sex
Age
whatever

Table: Entries
EntryID
CompetitorID
Discipline

Table: Referees
RefereeID
RefereeName
etc

Table: RefereeAssignments
AssignmentID
RefereeID
Discipline

Table: Results
ResultID
AssignmentID
EntryID
Score

Something like that. :-)

After that, the data will be in a suitable format, not only to
facilitate data entry, but to allow simple data summaries, totalling,
averages, etc.
 
Thanks, I will rewrite my routines according to your suggestions, hopefully
it makes makes things easier.

Regards, Espen
 
Back
Top