Thank you for your suggestions. Unfortunately as this is a 'live' assignment I am unable to post full details. I think that there is a problem with the assignment as the simplest suggestion of an additional table doesn't fit within the criteria; the other two solutions are beyond the level of the students.
This information would have helped earlier, rather than simply dismissing our suggestions and asking if anyone else can help.
I agree with you that there is an issue with the assignments restriction on the number of tables allowed. You could allow your students to use table level validation in this case, but it is not good practise and not something I would promote.
Thank you for your suggestions. Unfortunately as this is a 'live' assignment I am unable to post full details. I think that there is a problem with the assignment as the simplest suggestion of an additional table doesn't fit within the criteria; the other two solutions are beyond the level of the students.
so.... they all fail because the assignment design is flawed? or is this gender thing just a very small portion of it?
oh, and by the way, if any of your students find this thread, then they all have the 'beyond their level' solutions written out for them...
and don't underestimate the students! i remember a VERY simple VB project we had at uni. they barely gave us enough detail for us to 'complete' the project and NOTHING on error handling or other general coding - i went ahead and did my own learning into VB and inserted error handling and message boxes for data entry help.
all the other student's apps crashed if you put the wrong data in - mine warned you that you had wrong data, gave an example of the appropriate data, then let you try again.
i was SO pissed off that i was given a lower score for my project than some of the other students. i even asked to see why i lost a point (i got 9/10) the lecturer - from what we saw when we opened some other students' apps - said that it looked as though whomever marked my app didn't like the colours i used (which was, admittedly the default windows grey - but i had been concentrating on making my app stable)
so i hope you are going to mark your students a little fairer! especially given the direction of the assignment so far
Thank you for your suggestions. Unfortunately as this is a 'live' assignment I am unable to post full details. I think that there is a problem with the assignment as the simplest suggestion of an additional table doesn't fit within the criteria; the other two solutions are beyond the level of the students.
Reading this thread reminds me of why I distrust the abilities of new graduates to produce competent work in the real world. The problem appears to be not with the students but with their teachers who don't seem to have a clue.
Those who can, do. Those who can't, teach. Those who can't teach, teach gym
Hmmmm.... It would have helped if you had answered the question in the first place. It would have saved a lot of reluctance on my part to help you. Also, you were given a suggestion by Mr. B, and it didn't seem like you tried to understand it. So instead of asking "anyone else?" maybe make an effort to understand, then ask a specific question about what you don't understand.
Wouldn't you be better off guiding your students to the answer? The microsoft site really does have a great guide to teach first the basics of form validation, and then more complicated. But that aside, I am glad you got a more complete answer from Mr. B. Next time answer the question, Then I, at least, will be more forthcoming.
I am a little reluctant to even post any other comment to this thread because I find it to be a waste of time. I take my work very seriously. I make every effort to try an learn everything I can from everyone I can. (Believe me, I learm much more from attempting to provide assistance to others than they learn from my postings.)
In this case, I and the others that have responede here have made every effort to try to provide very useful and workable examples. If find it very frustrating first of all that you are trying to teach students something that you do not seem to be able to do yourself.
I also would like the OP to understand a couple of things.
First, you evidently cannot or did not understand the solution that I posted. The very simple VBA code that I provided will take care of every possible senerio that you proposed. If you will take a closer look you will see the the only instance where you would need "M" for the gender (using your information) was when "Mr" was selected form the combo box. All other possible selections would require that "F" be used for the gender.
I think that perhaps my solution was not just "above the level of the students" but may actually be above the level of the teacher.
I am a busy professional who tries, like others here, to "give back" because other were so gracious to help me when I needed to learn. Learning is not about proposing unrealistic senerios and using up the valuable resources of the people on this and other forums to try to find solutions that you then just discard.
As I said at the start of this post, I am reluctant to even respond, but I just feel that our providing our "Free" assistance and help is deserving of more respect than I have seen for the original poster.
I will stop ranting now, and go try to help someone who will really appreciate and use the help.
I'll take the techie side of this discussion. This is a normalization issue and the limit to the solution prevents proper normalization.
When you have a record in a database, if it is properly normalized, then each record in each of your independent tables has a primary key field (or compound field). All other fields depend solely on the value of the PK of their respective tables.
The titles Mr. and Ms., if in the same table, are not dependent solely on the value of the PK. (They depend on the gender field, which depends on the PK. A transitive relationship.) Therefore, the table that contains both title and gender fields is technically denormalized. When you introduce denormalization, you immediately make your job harder because enforcing relationships in a denormalized table becomes more and more difficult with each layer you add.
So... let's look at what you can do in Access:
Validation in a table's validation property typically requires constants for comparison. You are trying to validate against a variable (the contents of another field). I don't recall that you can do that because that expression doesn't accept full VBA. Only constants unless something has changed very recently. So table-level validation is probably not going to be reliable.
Next problem: The mapping of the title to the gender isn't uniform. "Mr" and "Ms" and "Mrs" map cleanly, but "Doctor" and "General" and "Professor" do not map definitively. This means that you actually have a tri-state mapping: Clearly M, clearly F, gender-independent. So you are going to have to design a representation that allows for tri-state logic. (And I would not recommend using Yes/No fields with Tri-State logic enabled.)
A form can do the validation because it can run VBA code behind itself. Once you have VBA, all sorts of logic is available in the BeforeUpdate or AfterUpdate event of a bound control on a form. VBA code can do DLookup calls if needed.
A JOIN query on a table with validation data would also allow you to decide whether the fields are consistent because you could have a second field in the title validation table that shows what is allowed. But that adds an extra table and you said no.
If you are stuck in a table, you have no scaffolding for events that would allow for complex field validation. This is because the Jet database engine that runs underneath Access doesn't support data triggers. There is no event code to be run when dealing with the table directly. No trigger? No complex validation.
Therefore, when you say to us "Answer my question" - I must reply that if you limit the tables, limit the ability of the people involved, and limit the workaround methods you will allow, there IS no solution. The problem, if overconstrained by externally imposed limits, cannot be solved reliably.
This should explain why you weren't getting the answer you wanted. That answer DOES NOT EXIST.
Whether you choose to accept that or not is your issue. But it doesn't matter to me. I've told you why there is no answer within your limits. Your move.
I wonder, if it's an assignment then the worry over gender neutral titles is probably moot. The assignment is probably limited to titles of Mr. Mrs or Miss. But for completeness sake, excellent points brought up, as I didn't think about other titles.
It seemed that he had difficulty understanding the fairly simple solutions provided to him so I'm not sure he would have been able to explain to his students anyway.
I guess I was lucky that my tutor at college was an IT professional and not just someone with a teaching qualification.
Sorry for not completely understanding what you really wanted to do.
Create a combo box for the Title. Set the "Limit To List" property to true. Set the the "Row Type Source" property to: "Value List" and then put the values you need to use in the "Row Source" property like: Mr;Mrs;Miss;Ms no quotes.
Create a Text box for the "Gender" field and set the "Locked" property to true.
In the "AfterUpdate" event of the Title combo box put the follow code:
Code:
If Not IsNull(Me.cboTitle) Then
If Me.cboTitle = "Mr" Then
Me.txtGender = "M"
Else
Me.txtGender = "F"
End If
End If
Change "cboTitle" to the actual name of your Title combo box and change "txtGender" to the actual name of your Gender text box.
This will set the Gender value based on the selection made from the Title combo box.
Thanks for that solution it works just fine. However my students have not used any VB code before so I am still looking for a simpler solution. Maybe a look up would be easier to implement?
Sorry if I offended some people on this forum, I was only asking for a simple solution to a problem with some admittedly 'unrealistic' constraints!
I don't see how you can do this without more tables or VBA. I think that the solution you just showed is the simplest, and it introduces Form events and VBA in a very simple mode. I agree that from what you say your students cannot do this, therefore you must ask the person who set this assignment what their solution is and ascertain what the learning point is.
Thanks for that solution it works just fine. However my students have not used any VB code before so I am still looking for a simpler solution. Maybe a look up would be easier to implement?
Sorry if I offended some people on this forum, I was only asking for a simple solution to a problem with some admittedly 'unrealistic' constraints!
if you still have classes scheduled before the assignment is due, perhaps you can introduce some basic VBA that will help them complete the assignment as required.
what kind of level are these kids anyhow? i assume not kindergarten, but if it's university level i think that you can throw some code at them. with school-age, it depends what level... high school? yeah, maybe. primary? i wouldn't...
Thanks for this Kryst51. I have decided this is the way to go as it will meet the criteria. However I intend to combine Title & Gender in one table (as I have a 5 table limit!) and link this to the Employee table. The Title table will consist of:
TitleID, Title, Gender
1, Mr, M
2, Mrs, F
3, Miss, F
4, Ms, F
