tbl_Employee
pkEmployee_ID (autonumber)
strTitle (4 char string) "Mr", "Mrs", "Miss" or "Ms"
strGender (1 char string) "M" or "F"

I need to ensure consistency between Title and Gender.

What is the best way to do this? Validation rule?

Thank you.

Have a separate table for titles with its own pk, that is an fk in your employee table. Have a separate gender table for your two genders with its own pk and an fk in your titles table.

Set up the ralationships in the relationship screen and voila!

I can see how that would work, but I don't want a separate table for Title.

I need to include both Title and Gender in the Employee table but ensure that they match up.

Why do you need to store them in the Employee table?

If you set it up right, it's exactly the same as storing them together, it just makes things easier. I don't see why you wouldn't want to do it that way.

That's why a relational database is so great. :)

I have been specifically asked to create these two fields, Title and Gender in the Employee table and ensure consistency between them.

I am sure that whoever has asked you does not understand that storing them in different tables is no different to storing them in the same table as long as your relationships are set up correctly, apart from being able to perform the validation in a simple way if you store them separately.

Any form you create would be based on a query that links the three tables. You really should go with kryst51's suggestion...

I can't see why I can't put all Employee details in one table?

I have 5 tables in all, but I need to enter all Employee details in one table. Surely its not so hard to ensure Title and Gender are entered consistently?

I don't know of any other way. I don't understand why you can't make separate tables. That's what a relational database is for. To make life easier. Your end result will still be the same, plus it makes data entry on your forms easier. I'll post a sample, and you can see what I mean. :)

Some one else can probably give you a different way, but it will be much more complicated I'm sure.

If you really do want to store these fields in the Employee table then you can use validation on the form used to enter the data, place validation in each controls BeforeUpdate event and also in the forms BeforeUpdate event.

Of course this assumes that you will be using a form to input data.

I have never been a fan of table level validation.

Also, you can use a queryto see it all as if it were in one table! but the validation would be built in.

Edit: Here's my sample

Getting consistancy for the Gender can be done without the extra table. Just use a combo box and hard code the "M" and "F" as the only choices available. Set the "Limit To List" property of the combo box to "True". To set this up, create your combo box, then change the "Row Type Source" property to: "Value List" and then put the values you need to use in the "Row Source" property: M;F

As long as you know that the only values that you will want chosen for the "Title" are: "Mr", "Mrs", "Miss" or "Ms" then you can do the same to force consistance for this field as well.

Just be aware that if, in the case of the "Title" field, you ever need to have any additional value to be available, you will have to go into design mode and manually add any needed values, like "Dr", etc.

Using the method that has been described by others is definately the most advantageous way to go, but you can do it the way just described.

I need to ensure that "Mr" in Title always = "M" in Gender and "Mrs", "Miss" or "Ms" in Title always = "F" in Gender.

You already have in my opinion the best 2 solutions available. If you really don't want to split the data into multiple tables then form validation is the way to go, but it is definitely simpler to go with Kryst51's suggestion, especially if you are not familiar with VB.

I need to ensure that "Mr" in Title always = "M" in Gender and "Mrs", "Miss" or "Ms" in Title always = "F" in Gender.

I am limited in the number of tables I can use therefore none of the solutions suggested so far can be used to ensure consistency.

Why are you limited to your number of tables?

I am limited in the number of tables I can use...
Let me guess - School Project?

Using form validation means you do not have to use more tables - if you really are restricted to the number of tables you can use (I really do not see why anyone would risk good Database design by giving you this restriction) then this is the best way of validating your data.

I am looking for a solution to this problem, does anyone have the answer?

Let me guess - School Project?

Yeah, that thought occurred to me, which is why I asked about the limitation of tables.

Mr. B's suggestion can work, without adding more tables. But you will need to do some work and research on that method first. And come back with some specific questions about it.

Especially if this is a homework assignment, no one wants to spoon feed you, as you are supposed to be learning from the assignment.

Edit: Mr. B's suggestions with the validation suggested by dbDamo

Using form validation means you do not have to use more tables - if you really are restricted to the number of tables you can use (I really do not see why anyone would risk good Database design by giving you this restriction) then this is the best way of validating your data.

As far as I can see the Form Validation only restricts the entries to "M" or "F" but doesn't ensure consistency with "Mr", "Mrs" etc.

As far as I can see the Form Validation only restricts the entries to "M" or "F" but doesn't ensure consistency with "Mr", "Mrs" etc.

You can do more validation with vba or the control's query to ensure the consistency as dbDamo suggested. In addition to restricting the available entries.

Restrict the entries how exactly?

I am looking for a solution to this problem, does anyone have the answer?


Form validation, I will not keep saying it!!!

I do not like table level validation, not least for the fact that you can only provide one message. Not a good idea.

You state that you are limited to the number of tables you are allowed to use, I cannot see why but must take your word so Kryst51's suggestion, while being the best option, is a no go.

Mr. B's suggestion can work with some tweaking, but as Kryst51 says you will really need to read up on the method and isn't very straightforward.

Form validation, while requiring the use of VB, is actually very simple.

Restrict the entries how exactly?

Search Google and the site, for info. I still think this could be ahomework assignment and am unwilling to do the work for you. Mr. B gave you an excellent start.

Try searching for things like combo box values, limit to list. Which will gain you a start and might help lead you to more search terms for the gender bit.

Edit: maybe a search on form validation.

Edit2: Microsoft site had and ecellent page for learning validation rules and how to use them.

Perhaps someone can be more helpful and provide me with an answer?

Thank you.

Perhaps someone can be more helpful and provide me with an answer?

Perhaps you could answer our questions too??? :confused:

Perhaps someone can be more helpful and provide me with an answer?

Thank you.

In addition to what Bob said, you need to understand something. Your FIRST STEPS have already been given by Mr. B and dbDamo, You are not willing to take them, nor to learn. It is doubtful if anyone will help you if you do not take the suggestions given to help you achieve things the way you want them to be done.

Also, back to Bob's comment of:

Perhaps you could answer our questions too??? :confused:

Is this a homework assignment?

Perhaps someone can be more helpful and provide me with an answer?

Thank you.

I'm sorry, but are you being serious? I have never seen someone ask a question like this after being provided with a number of solutions that WILL work.

We have given you the answers already. It is now down to you to make a decision as to which solution is best for you. Once you have made that decision you can then ask more specific questions on how to achieve it, and if you haven't offended us too much by then you will get an answer.

I can't see why I can't put all Employee details in one table?

I have 5 tables in all, but I need to enter all Employee details in one table. Surely its not so hard to ensure Title and Gender are entered consistently?

if you have a separate table for titles, you can add the gender to a third field in that table and thus they will all stick together.

i.e.;


tblTitles
-------------------
titleID (autonumber, PK)
title (text)
gender (text)

values for title and gender would stay in the same record

title gender
---------------
mrs F
miss F
mr M

but there are instances where you might come across problems.... like for a person who uses the title "doctor"... you've no idea what the gender ought to be there.

thus the gender MUST be added separately to title, to facilitate these weird titles.

so.... that leads me to recommending what everyone else has... a separate table for titles, and a separate table for gender. these would appear in the employee table as a 'foregin' key (but effectively you still get what you wanted - all the info in one table).

i.e.,

tblEmployees
--------------------
employeeID (autonumber PK)
titleID (number, FK)
genderID (number, FK)
.
.
.
then in your relationship view you would create links between your two "titleID"s and two "genderID"s (and make sure you check 'referential integrity' so that a person cannot be attached to a non-existant title or gender!)

then in you form, where you do the data entry, make the title a combobox (or dropdown box) whose source is the tblTitles but which saves the data into the titleID field of the tblEmployees table.

same with the gender field.

so, on the form, you don't even KNOW that they're three different tables. to you, to your boss, to your peers, it all looks like you've magically done it one table (in fact, the keys are all stored in the one table anyway!)

does that help explain it better for you?

in the examples given above, there is no "coding" - you don't have to maintain anything and it's all done for you by access because of the relationships you've stipulated in the relationship window. it also makes new titles and new genders (such as "U" for "undisclosed") easier to add - and there would be no need to have to go back to the code and re-program for a new title/gender...

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:


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.

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:


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.

yup, that will do it too, but i would only do it this way if i was REALLY not able to use extra tables.

if this really is an assignment, then i am baffled as to why they are forcing you to use BAD database design techniques.

if this really is an assignment, then i am baffled as to why they are forcing you to use BAD database design techniques.
THAT doesn't surprise me. We see that all of the time here. The "instructors" are commonly not schooled in database design and are teaching things which are not good practice. It has been one of those on-going items that we cringe at.

I said the same earlier, which is why I am not sure that is the case. It just doesn't make sense placing a limit on the number of tables that can be used as it could utlimately lead to bad design.

Having been one of those instructors, as well as a developer, I would just mention that there are times that you are attempting to teach one specific thing and not yet attempting to totally approach the "best practices" issue. In this case, if this is an assignment, then it could be that the goal is to have the student learn how to use the various controls even when the data is not already available in a table.

I know that I am reaching way out on this one, but it is not totally bad, just not what most of use who do this sort of thing evey day would do.

I am currently working on a demo file that I thing I will have ready shortly that takes the concept of managing small lists like Prefix, Suffix, Title, etc. from a single table which has been demonstrated by Allen Brown, to one more level and allows for custom sorting of each list. I'll be posting the demo to the forum as soon as I have it ready. I will also have several other commonly discussed issues demonstrated in the same file. I will have documentation with it so as to try to help others who need to know how to manage info like this.

This is indeed an assignment set for my students by someone else, hence the restriction on number of tables. However I need a solution myself in order to guide my students towards a solution.

What is the assignment supposed to achieve? Obviously it is not to teach the students good Database design. Is it just to learn about validation?

It would help if you could post the assignment, then we can see what the aim is and base our solutions on this. It would also help to know the level of the students.

3 solutions have been provided. These are the ways that professional programmers validate fields, so it would be good practise to pass this on to your students. You can't use Kryst51 solution, so you have to use my form validation suggestion, or Mr. B's combo box suggestion.

What is the assignment supposed to achieve? Obviously it is not to teach the students good Database design. Is it just to learn about validation?

It would help if you could post the assignment, then we can see what the aim is and base our solutions on this. It would also help to know the level of the students.

3 solutions have been provided. These are the ways that professional programmers validate fields, so it would be good practise to pass this on to your students. You can't use Kryst51 solution, so you have to use my form validation suggestion, or Mr. B's combo box suggestion.

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 is indeed an assignment set for my students by someone else, hence the restriction on number of tables. However I need a solution myself in order to guide my students towards a solution.

why is someone else giving YOUR students an assignment? can you not negotiate with this 'someone else' about freedom of design? or at least NORMALISATION of the database?

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.

How do you plan to handle titles that can apply to either gender? ('Dr', 'Prof', 'Rev', 'Lt Cmdr', etc)?

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

(yes, i have a little baggage about that).... :rolleyes:

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:D

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. :)

Good luck.

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.
As far as I can see the Form Validation only restricts the entries to "M" or "F" but doesn't ensure consistency with "Mr", "Mrs" etc.
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.

How do you plan to handle titles that can apply to either gender? ('Dr', 'Prof', 'Rev', 'Lt Cmdr', etc)?

i was going to make this point

this is where a lookup table of title with an associated gender fails.

the point is that unless you use gender specific titles there is probably no way of uniquely determining the sex of a person -

the best way is to use a lookup that says

Mrs
Miss
Ms must aLWAYS be F

Mr must always be M

Prof
Dr may be either.


so ultimately you DO need to store the gender in the employee table!

and you can verify it either by using the lookup (best) or by hard-coding every potential value of the title. (much worse)

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.

... i wonder where the OP is?
.
.
.
.
.

maybe he gave up :O

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.

Hope I didn't scare him away, but sometimes you have "rub their noses" in the mess that they are making. And that was heading for a mess.

Hope I didn't scare him away, but sometimes you have "rub their noses" in the mess that they are making. And that was heading for a mess.

I don't think it was just you, we all had some pretty hard things for him to hear....:(

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:


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.

Brian

I think we would all like to know the solution from the person who set this assignment...

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...

Also, you can use a queryto see it all as if it were in one table! but the validation would be built in.

Edit: Here's my sample

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

Its simple and does the job!

I thought you couldn't create any more tables as you had already reached your 5 table limit?

I thought you couldn't create any more tables as you had already reached your 5 table limit?

I had to consolidate two other tables to accommodate this. I still haven't heard back from the creator of the problem.

Hopefully that won't cause you problems later in design...

Hopefully that won't cause you problems later in design...

I am not happy with the design now because its not possible to properly normalise with the limit on the number of tables.

Also the data entry form for Emloyee is not ideal requiring a title ID to be entered (1,2,3,4) instead of Mr, Mrs, Miss or Ms. I can create a Form based on a query to show this information but it is not possible to enter data into a Form based on a query.

Here ya go

I hope it does the trick


Al

Here ya go

I hope it does the trick


Al

Hey, I just looked at this. It has lookups at table level which should be avoided, and stores the gender in two spots which is a normalization issue. I'll fix it, and post the correction here. Good job music_al :)

I dont understand why its a normalisation issue.

I'd be interested to see the proper way to do it...

Here's a cheat I got sick and tired of creating tables "descriptive tables" so I created a Table to contain multiple descriptors - obviously the combined Table can't be joined to allow Entry as the Foreign key is no longer unique however you can combine:

Artist Category "C"
Artist Origin "O"
Artist Style "S"
Artist Type "T"

In effect I consolidated Artists Categories, Artist Origin, Artist Style and Artist Type into one Table.

Going back to this tome regarding gender it is quite standard to fill in Sex M/F as to assume gender is not accurate enough. If you are dealing with personnel you should include the Gender option as any international or professional titles will not always conform to any standard rules. So you design your system that works for a variety of scenarios beyond conceptual considerations.

In other words you design your system to be flexible. Any arbitrary concepts are likely to send you back to the drawing board.

We have an expression in NZ - But does it work?

Simon

Is this the preferred way to do it ??


Al

Is this the preferred way to do it ??


Al

ALMOST - get rid of the lookup at table level in tblPerson and you have it.

I dont understand why its a normalisation issue.

I'd be interested to see the proper way to do it...

Well, it's bad enough that we have to duplicate the gender, because of the table limit. But in your design the gender was stored in the title table and the person table. That isn't necessary. But, I understand your reasoning, however, if you store the gender in both places it can cause issues. Say you realized that Chris is indeed a woman, short for Christina. then you would have to change the title, but because gender is in both places, you would have to change it in both tables, and since thetables are related that really isn't necessary. You only need to store it with the title.

Edit: Also, storing the gender in two places ruins the point, which is data validation of the title to gender.

Also, lookups at the table level need to be avoided at all costs. Use the combo box wizard at the form level to achieve the lookup. I have attached a modified verson of music_al's db. I added in a couple of bells and whistles, I couldn't help myself. :p

Edit2: While writing and posting, music_Al posted his, Great, that's it except for, as Bob said, the lookup at the table level.

Kryst51 - did you attach your modified version of music_al's db?

Kryst51 - did you attach your modified version of music_al's db?

I thought I did, I'll have to wait until I get to work this morning. :)

OK, I had deleted my version thinking I had posted it. As I said this has bells and whistles, but you don't need them to make the validation work, if you ignore the buttons and visible invisible stuff, then it's exactly like Music_Al's new one without the lookup at the table level.

ok, here's a tip: compact and repair.

it just made your 4Mb+ file down to below 400kb.

i also think, in most versions of access, you can tell access to "compact on close" the current database, which basically does 'compact and repair' each time you close the database and you don't have to remember to do it manually.

ok, here's a tip: compact and repair.

it just made your 4Mb+ file down to below 400kb.

i also think, in most versions of access, you can tell access to "compact on close" the current database, which basically does 'compact and repair' each time you close the database and you don't have to remember to do it manually.

Hm, thanks for that, as long as it uploads, (fits in the allowable limit) I usually don't worry to much. But I'll try to remember to do that in the future. :)

second point: i am wondering why you need a separate query for concatenated name when you already have a concatenated field in the first "qryPerson" query. seems redundant to me.... is there a limit on queries like there is on tables? in fact, i don't think you need the standalone query at all - you can just make an SQL source for the form (basically a query, but 'built-in' to the form, if you willl.)

Hm, thanks for that, as long as it uploads, (fits in the allowable limit) I usually don't worry to much. But I'll try to remember to do that in the future. :)

it's not just about meeting the upload limit. as the name suggests, "compact and repair" has more practical and helpful uses.

something else i noticed. you naming convention is inconsistent. you have "tbl_" and "frm_" but no underscore with "qry"... i personally would lean towards no underscores at all, but definitely would try to make the names consistent. in the field names, also, you use CamelCase. this is what i would use in your other names (e.g., tblPerson, tblTitle)

as another suggestion, i would use "tbl" for table with data, and "tlkp" for a table whose only data is to be as a source for a dropdown (Table LooKuP = tlkp). the practical upshot of which is that when access orders your tables alphabetically, you get all of your important data tables at the top of the list, followed by you lookup tables further below, and they're not all mixed up together.

next observation:

you have a lot of repetative code. i.e.,


Me.Concatenated.Visible = False
Me.FirstName.Visible = True
Me.LastName.Visible = True
Me.FirstName.SetFocus

and to toggle between true and false.

i would place the two visibility states:

first & last name TRUE
concatenated FALSE; and

first & last name FALSE
concatenated TRUE

into a public sub or function in a module (eg. ViewConcatTrue() and ViewConcatFalse() or similar) and refer to these as you need them in the code, rather than repeating the code over and over at each event on your form.

the practical upshot of this is if you want to change somethin down the track, you only need to do it in one place (the function) rather than remembering which events have which view and what you want to change it to...

hm. and if you're going to have a "save" button, perhaps you also need a "cancel" or "undo" button?

?hm. and if you're going to have a "save" button, perhaps you also need a "cancel" or "undo" button?

Ye-e...:rolleyes: Is it realy necessary?

:confused:I have watched this thread from the beginning but only actually visited once long ago when DCrake posted a function. I am bewildered how this thread's title could possibly lead to more than eighty posts to date.

Then perhaps there is another user out there just like me who just decided that enough was enough at 82 and this has been going on for weeks.:D

:confused:I Like yourself only picked up this post today as the forum is pretty quiet. However I have not contributed to it as you suggest. Maybe you got a bit confused by the number of posts, which quite frankly, I am suprised has got to 80+.

David

However I have not contributed to it as you suggest.

I know it was a nice function and long ago. Seemed like something you would suggest. I can't even find the post now.

And another user wonders,,,,,, 84 posts??????? Matching title and gender.

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:


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.

Could this code be modified to allow non-specific gender titles such as Dr?

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:


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.

Can anyone tell me how to modify this code to accommodate titles that are of non-specific gender e.g. Dr?

Thanks!

Can anyone tell me how to modify this code to accommodate titles that are of non-specific gender e.g. Dr?

Thanks!

So if the Title is Dr what do you want it to do?

Surely the purpose of matching Title and Gender is to avoid a Mistake of alloting eg Mr to a Female , but you cannot drive the Title or gender from the other information.

Brian

Can anyone tell me how to modify this code to accommodate titles that are of non-specific gender e.g. Dr?

Thanks!

maybe something like:


If Not IsNull(Me.cboTitle) Then
If Me.cboTitle = "Mr" Then
Me.txtGender = "M"
Else
If Me.cboTitle = "Miss" or "Ms" or "Mrs" Then
Me.txtGender = "F"
Else
Me.txtGender = "n/a" '(or "unk" for unknown)
End If
End If


(or should that be "= Or("Miss","Ms","Mrs")" ? you'll just have to try a few combinations, i'm not fabulous with off-the-cuff code)

the other thing you could have in your people details is a field for title and a separate field for gender, which would make more sense if you need to incorporate more titles (like Dr, Prof., Councilor, Colonel.... etc) and still need to identify gender - if you think about it, most paper forms you fill out have both title, name and gender (usually only M and F, though the odd one does have a third option of which is something like 'not disclosed' or whatever.)

maybe something like:


If Not IsNull(Me.cboTitle) Then
If Me.cboTitle = "Mr" Then
Me.txtGender = "M"
Else
If Me.cboTitle = "Miss" or "Ms" or "Mrs" Then
Me.txtGender = "F"
Else
Me.txtGender = "n/a" '(or "unk" for unknown)
End If
End If


(or should that be "= Or("Miss","Ms","Mrs")" ? you'll just have to try a few combinations, i'm not fabulous with off-the-cuff code)

the other thing you could have in your people details is a field for title and a separate field for gender, which would make more sense if you need to incorporate more titles (like Dr, Prof., Councilor, Colonel.... etc) and still need to identify gender - if you think about it, most paper forms you fill out have both title, name and gender (usually only M and F, though the odd one does have a third option of which is something like 'not disclosed' or whatever.)

Ideally I would like Mr to force gender to M (male) Mrs, Miss and Ms to force gender to F (female) but non-specific gender titles such as Dr, Prof to force manual gender entry. Is this feasible?

Ideally I would like Mr to force gender to M (male) Mrs, Miss and Ms to force gender to F (female) but non-specific gender titles such as Dr, Prof to force manual gender entry. Is this feasible?

again, the best setup would be to have two fields in your people table, one with title and another with gender. remembering that if you do this, txtGender would logically be changed to a combo box and really be called "cboGender" or "cmbGender", whatever nomenclature you are following.

you could use the same if statemnt i provided earlier, but change the last "else" bit to something like:


MsgBox "Please enter a gender.", vbOkOnly
Me.cmbGender.Setfocus = True


you could make the gender field required, but if you do that you have to be prepared to have an "unknown" or "not supplied" option in the dropdown, because it ISN'T always possible to know the gender of people at the time of data entry (e.g., email correspondance, or a paper survey, with a fictional "Dr. Huong Huozhin".... what (usually naive and ignorant) anglo is going to know that Huong is a female name? and how many anglos are savvy to the fact that many asians write Surname Firstname (no comma)?)

anyway, it may be helpful to know your current data entry setup.

maybe something like:


If Not IsNull(Me.cboTitle) Then
If Me.cboTitle = "Mr" Then
Me.txtGender = "M"
Else
If Me.cboTitle = "Miss" or "Ms" or "Mrs" Then
Me.txtGender = "F"
Else
Me.txtGender = "n/a" '(or "unk" for unknown)
End If
End If


(or should that be "= Or("Miss","Ms","Mrs")" ? you'll just have to try a few combinations, i'm not fabulous with off-the-cuff code)

the other thing you could have in your people details is a field for title and a separate field for gender, which would make more sense if you need to incorporate more titles (like Dr, Prof., Councilor, Colonel.... etc) and still need to identify gender - if you think about it, most paper forms you fill out have both title, name and gender (usually only M and F, though the odd one does have a third option of which is something like 'not disclosed' or whatever.)

I can't get this to run?

I can't get this to run?

if you describe your problem a little more than "it doesn't work" perhaps we can help.
(that is to say: what is your exact code you are using (and where have you put it), what is your table structure, form setup, are you receiving any error messages? what IS happening, if not what you expect?)

I can't get this to run?

The code as posted appears to have three If Statements, and only two EndIf Statements. I am posting a suggested correction. Perhaps wiklendt can verify her intention.


If Not IsNull(Me.cboTitle) Then
If Me.cboTitle = "Mr" Then
Me.txtGender = "M"
Else
If Me.cboTitle = "Miss" or "Ms" or "Mrs" Then
Me.txtGender = "F"
Else
Me.txtGender = "n/a" '(or "unk" for unknown)
End If
End If
End If

if you describe your problem a little more than "it doesn't work" perhaps we can help.
(that is to say: what is your exact code you are using (and where have you put it), what is your table structure, form setup, are you receiving any error messages? what IS happening, if not what you expect?)

I originally used the code suggested by Mr. B. That code worked but when I modified it to match your suggestion it no longer matched gender to title. I have attached a copy for you to see.

Thanks

The code as posted appears to have three If Statements, and only two EndIf Statements. I am posting a suggested correction. Perhaps wiklendt can verify her intention.


If Not IsNull(Me.cboTitle) Then
If Me.cboTitle = "Mr" Then
Me.txtGender = "M"
Else
If Me.cboTitle = "Miss" or "Ms" or "Mrs" Then
Me.txtGender = "F"
Else
Me.txtGender = "n/a" '(or "unk" for unknown)
End If
End If
End If


oops, yes, that extra end if should be there - like i said, disclaimer on the 'off-the-cuff' code!

templar, i'll get to your file soon, i've got to get back into the lab (im' at work).

oops, yes, that extra end if should be there - like i said, disclaimer on the 'off-the-cuff' code!

templar, i'll get to your file soon, i've got to get back into the lab (im' at work).

I added the extra "End If" but it still isn't working?

I added the extra "End If" but it still isn't working?

When you say "...it still isn't working?" what does that mean? What does it do, etc.?

When you say "...it still isn't working?" what does that mean? What does it do, etc.?

The code I have been given does not run. If you look at my post 11-23-2009 02:41 PM I have attached a copy of my file (before adding the missing "End If") if you care to take a look?

Thanks.

The code I have been given does not run. If you look at my post 11-23-2009 02:41 PM I have attached a copy of my file (before adding the missing "End If") if you care to take a look?

Thanks.

The code that I posted with the correction for the additional EndIf is below. Is this EXACTLY what you are using? If not please post what you are using.

http://www.access-programmers.co.uk/forums/showpost.php?p=908578&postcount=93

The code I have been given does not run. If you look at my post 11-23-2009 02:41 PM I have attached a copy of my file (before adding the missing "End If") if you care to take a look?

Thanks.

I can't take a look as I don't have 2007 here at work. If you can save it to an Access 2003 or earlier version and upload, I can.

you put the code in the wrong place. the code should be in the afterupdate event of the title combo. if you think about it logically, it is the change in title that triggers the gender.

now a couple of pointers:

first, compact and repair your database before uploading. this drastically reduces the file size (your original file size upon unzipping was over 2mb. after, it was 356 kb) - you can even set this to happen automatically each time you close the application by settings Office Orb | Access Options | Current Database | compact on close.

second, you would have received your answer a lot sooner if you simply posted the code you were using (all of it - that includes everything from "Private" to "End Sub") we would have immediately seen where you went wrong. "it doesn't work" is like telling a doctor "i'm sick" - the more (relevant) info, the better.

I can't take a look as I don't have 2007 here at work. If you can save it to an Access 2003 or earlier version and upload, I can.

Here is a 2003 version.

you put the code in the wrong place. the code should be in the afterupdate event of the title combo. if you think about it logically, it is the change in title that triggers the gender.

now a couple of pointers:

first, compact and repair your database before uploading. this drastically reduces the file size (your original file size upon unzipping was over 2mb. after, it was 356 kb) - you can even set this to happen automatically each time you close the application by settings Office Orb | Access Options | Current Database | compact on close.

second, you would have received your answer a lot sooner if you simply posted the code you were using (all of it - that includes everything from "Private" to "End Sub") we would have immediately seen where you went wrong. "it doesn't work" is like telling a doctor "i'm sick" - the more (relevant) info, the better.

Run-time error '13':

Type mismatch

Debugger highlights in yellow:
If Me.cboTitle = "Mrs" Or "Miss" Or "Ms" Then

Change your code to this:

Private Sub cboTitle_AfterUpdate()
If Not IsNull(Me.cboTitle) Then
If Me.cboTitle = "Mr" Then
Me.txtGender = "M"
Else
Select Case Me.cboTitle
Case "Mrs", "Miss", "Ms"
Me.txtGender = "F"
Case Else
Me.txtGender = "unk"
End Select
End If
End If
End Sub

as something somewhat related, there is also that argument that there should only be "Ms" available for women, and here are two reasons:

1) theoretically it does not matter to know a woman's marital status
2) after all, there is only "Mr" for men, and no one wants to know THEIR marital status

edit:

and, if marital status IS important, it should theoretically be important for BOTH men and women, so there should be a SEPARATE field for that, which would include all the various options such as



single
married
separated
divorced
...etc

Titles can be so "provoking" :D

Titles can be so "provoking" :D

yes, well, having said what i just said, i do get a 'kick' out of using "Miss" on forms that provide the option ;) makes me feel younger!

yes, well, having said what i just said, i do get a 'kick' out of using "Miss" on forms that provide the option ;) makes me feel younger!

Same here :D

Same here :D

hmmm. Miss Bob Larson. has i nice ring to it ;P

Change your code to this:

Private Sub cboTitle_AfterUpdate()
If Not IsNull(Me.cboTitle) Then
If Me.cboTitle = "Mr" Then
Me.txtGender = "M"
Else
Select Case Me.cboTitle
Case "Mrs", "Miss", "Ms"
Me.txtGender = "F"
Case Else
Me.txtGender = "unk"
End Select
End If
End If
End Sub

Thanks for this it works great up to a point. When title = Dr, gender is automatically entered as "unk" = unknown, but I need to be able to manually enter M or F? I don't seem to be able to overwrite this "unk"?

Thanks for this it works great up to a point. When title = Dr, gender is automatically entered as "unk" = unknown, but I need to be able to manually enter M or F? I don't seem to be able to overwrite this "unk"?

yup. already provided that solution in post # 90 on page 6 ;)

admittedly, this thread has become somewhat of a maze... :rolleyes:

yup. already provided that solution in post # 90 on page 6 ;)

admittedly, this thread has become somewhat of a maze... :rolleyes:

This throws up a compile error:

Expected Function or variable

This throws up a compile error:

Expected Function or variable

ok, figured it out - victim of air-code. that should be


Me.cboGender.SetFocus


not


Me.cboGender.SetFocus = True


also, i don't know if you realise, but you've got a combo box for gender with no option in it. people can't actually enter anything (i've just tried). how about you make it textbox, or a combo with values? again, this is something that should probably have it's own table (tblGender) BUT, it's always possible you're one of 1961templar's students ;P

Well a big thank you to everyone that has contributed to this thread, especially to: boblarson, Mr. B, Kryst51 and wiklendt. I have now got the Form that automatically enters M (=Male) for Mr and F (=Female) for Mrs, Miss or Ms and the message box "please enter gender" for Dr (or any other non-specific gender title I choose to accept).

However to accept the manual entry of non-specific gender titles I have had to unlock the form, therefore the validation on Mr, Mrs etc. no longer exists in that it is now possible to manually overwrite M and F.

I think the criteria that Mr MUST = Male and Mrs, Miss & Ms MUST = Female is achievable, but as soon as choice is allowed by introducing Dr (etc.) MAY = Male OR Female, validation becomes impossible.

This has been a great learning exercise for me and I truly appreciate all your help.