 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / February 2007record fields
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)  SignatureWayne Manchester, England. > 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? [quoted text clipped - 6 lines] > [tbloption].option = [tbloption2].[option2] > end if 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. > Hi > [quoted text clipped - 10 lines] > > [tbloption].option = [tbloption2].[option2] > > end if >wont allow more than one autonumber count within one table 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....... > >wont allow more than one autonumber count within one table > [quoted text clipped - 11 lines] > > 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 [quoted text clipped - 15 lines] > 2 3 karen > 3 2 tim ..... and so on....... 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) > >lets say i have assignments in 3 different subjects (math, english, > >science)... and people assigned to different assignments... so by their name [quoted text clipped - 36 lines] > > 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 [quoted text clipped - 4 lines] >project, but the same task number could be assigned within different projects >(1, 2 or 3) 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........ > >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 [quoted text clipped - 30 lines] > > John W. Vinson [MVP] >i need it to whereas Bill is working on projects 1, 5, 6 and 13........ 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... > >i need it to whereas Bill is working on projects 1, 5, 6 and 13........ > [quoted text clipped - 4 lines] > > John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.