record fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

can one field within a record switch between two or more records to read
other fields within it using an IF statement? How would I set it up?

example:

IF sectionnumber = 1 then
[tbloption].option = [tbloption1].[option1]
else
[tbloption].option = [tbloption2].[option2]
end if
 
Hi

Can you rephrase the question - I don't understand (other people may though)
 
fields that are within form view attached to main record (table) file; i want
to be able to have one of those fields choose between three different tables,
since access wont allow more than one autonumber count within one table... so
i need 3 tables that has an autonumber within each of them... but on the
form, a field will select the correct table containing the autonumber based
on another field that has either a 1, 2 or 3 in it...

IF sectionnumber = 1 then
option = [tbloption1].[option1]
else
option = [tbloption2].[option2]
end if

so if field called SECTIONNUMBER has a 1 in it... then another field within
the same form called OPTION will put an autonumber in its field automatically
from tbloption1.

if field called SECTIONNUMBER has a 2 in it... then another field within the
same form called OPTION will put an autonumber in its field automatically
from tbloption2.

if field called SECTIONNUMBER has a 3 in it... then another field within
the same form called OPTION will put an autonumber in its field automatically
from tbloption3.

Wayne-I-M said:
Hi

Can you rephrase the question - I don't understand (other people may though)


--
Wayne
Manchester, England.



accessdesigner said:
can one field within a record switch between two or more records to read
other fields within it using an IF statement? How would I set it up?

example:

IF sectionnumber = 1 then
[tbloption].option = [tbloption1].[option1]
else
[tbloption].option = [tbloption2].[option2]
end if
Click to expand...
Click to expand...
 
wont allow more than one autonumber count within one table
Click to expand...

STOP.

An Autonumber *is not a count*.

An Autonumber has one purpose and one purpose only - to provide an
automatic unique key. Autonumbers will have gaps (if you delete
records, or even hit ESC while entering a record).

If you want to count records, DON'T use an autonumber; use a totals
query *and count the records*, without trying to store the count
anywhere.

John W. Vinson [MVP]
 
lets say i have assignments in 3 different subjects (math, english,
science)... and people assigned to different assignments... so by their name
they get either a code of 1, 2, or 3 (representing math, english, or
science)....but they can have more than one different assignment within each
subject is for the autonumber count, because i wanted the number to come up
automatically, seeing how the next assignment number would increment for the
next person... not two people having the same assignment number within each
subject, but the next number...

subject assignment person name
1 1 karen
2 1 james
1 2 robert
1 3 paul
2 2 david
3 1 bobby
1 4 robert
2 3 karen
3 2 tim ..... and so on.......
 
lets say i have assignments in 3 different subjects (math, english,
science)... and people assigned to different assignments... so by their name
they get either a code of 1, 2, or 3 (representing math, english, or
science)....but they can have more than one different assignment within each
subject is for the autonumber count, because i wanted the number to come up
automatically, seeing how the next assignment number would increment for the
next person... not two people having the same assignment number within each
subject, but the next number...

subject assignment person name
1 1 karen
2 1 james
1 2 robert
1 3 paul
2 2 david
3 1 bobby
1 4 robert
2 3 karen
3 2 tim ..... and so on.......
Click to expand...

An Autonumber would NOT be suitable for the purpose. For one thing, it
doesn't start over!

What is the structure of your tables? Hopefully you have a Students
table (with a unique StudentID, names are NOT unique); a Subjects
table, again with a SubjectID primary key; and a third table of
assignments? Why do you feel that you need an incrementing assignment
ID? If you have the Assignments table displayed on a Subform of a Form
based on either Subjects or Students, you'll just see the students for
that subject, or vice versa.

If you really need the number, you can put VBA code in the Subform's
Subject combo box AfterUpdate event:

Private Sub cboSubject_AfterUpdate()
Me!txtAssignmentNo = NZ(DMax("[AssignmentNo]", "[Assignments]",
"[Subject] = " & Me.cboSubject))+1
End Sub

John W. Vinson [MVP]
 
not that its a classroom setting, i was just making that up... its good that
the autonumber does not start over... instead of students, lets say engineers
working on different assigned tasks for projects... 3 different projects but
many task numbers up to 100 in each project and same people could end up
working on all 3 projects but only one person is assigned a task number at
any given time. no two people have the same task number within the same
project, but the same task number could be assigned within different projects
(1, 2 or 3)

John W. Vinson said:
lets say i have assignments in 3 different subjects (math, english,
science)... and people assigned to different assignments... so by their name
they get either a code of 1, 2, or 3 (representing math, english, or
science)....but they can have more than one different assignment within each
subject is for the autonumber count, because i wanted the number to come up
automatically, seeing how the next assignment number would increment for the
next person... not two people having the same assignment number within each
subject, but the next number...

subject assignment person name
1 1 karen
2 1 james
1 2 robert
1 3 paul
2 2 david
3 1 bobby
1 4 robert
2 3 karen
3 2 tim ..... and so on.......
Click to expand...

An Autonumber would NOT be suitable for the purpose. For one thing, it
doesn't start over!

What is the structure of your tables? Hopefully you have a Students
table (with a unique StudentID, names are NOT unique); a Subjects
table, again with a SubjectID primary key; and a third table of
assignments? Why do you feel that you need an incrementing assignment
ID? If you have the Assignments table displayed on a Subform of a Form
based on either Subjects or Students, you'll just see the students for
that subject, or vice versa.

If you really need the number, you can put VBA code in the Subform's
Subject combo box AfterUpdate event:

Private Sub cboSubject_AfterUpdate()
Me!txtAssignmentNo = NZ(DMax("[AssignmentNo]", "[Assignments]",
"[Subject] = " & Me.cboSubject))+1
End Sub

John W. Vinson [MVP]
Click to expand...
 
not that its a classroom setting, i was just making that up... its good that
the autonumber does not start over... instead of students, lets say engineers
working on different assigned tasks for projects... 3 different projects but
many task numbers up to 100 in each project and same people could end up
working on all 3 projects but only one person is assigned a task number at
any given time. no two people have the same task number within the same
project, but the same task number could be assigned within different projects
(1, 2 or 3)
Click to expand...

well... it's still a classic many to many relationship.

Each engineer can work on many projects.
Each project can involve many engineers.

Three tables - Projects, Employees, ProjectAssignments. The latter
table would have the ProjectID and the PersonID; inserting a record
into it (using a subform on either the Projects form or the Employees
form) assigns that project to that person.

You could use the code I posted on the subform to assign a sequential
number - IF you think it's necessary. I don't; in fact, if projects or
personnel come and go, or get reassigned, keeping the numbers
sequential will become a real headache! You'll need to decide which is
more important: keeping the numbers stable (so Kathy can remember that
the Widget Optimization Project is #4 on her list), or keeping the
number sequences free of gaps (so you don't run into the situation
where Bill is working on projects 1, 5, 6 and 13).

Given that with proper form/subform design you can see all of the
projects listed by name for a given person; or all of the people
assigned to a project, again by name; what purpose does this number
serve?

John W. Vinson [MVP]
 
i need it to whereas Bill is working on projects 1, 5, 6 and 13........
 
i need it to whereas Bill is working on projects 1, 5, 6 and 13........
Click to expand...

But - unless I'm misunderstanding here - the Widget Optimization
Project is Bill's #1, and it's Janet's #3, and it's Rahim's #11...

or do you mean for each project to have a permanent stable number?

John W. Vinson [MVP]
 
that is correct: and based on an initial option of 1, 2 or 3 (representing
the 3 different projects).... an automatic number will automatically appear
in another field, and that number that appears may only be used by 1 person
at a time...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with query and subform 1
Print reports only where records contain data. 1
check box adding records to table 4
Switchboard question! 4
Wildcard Usage in Combo Box Programming 2
Option1 & Option2 to select only one group for a record. 3
Using COUNTIF or SUMPRODUCT, or something else?! 0
Passing option group value to vba function 2

Back
Top