Dynamic Data Validation for Fields on a Form

[JungleJme]

Hi,

I'm new to Access and attempting to build a database to improve my skills.

I'm a BA so i'm converting my excel issues list into a database with a front end where i can create new issue records this uses a form that sits on top of the issues_table.

Howver, i have created a table called Projects and a table called Test_Plans

Each issue is logged against a project and a test plan. Lets say ProjectA has TestPlan1 and ProjectB has Testplan2.

On my issues form, i can select ProjectA in the project field.

In the Testplan field i can select EITHER TestPlan1 or Testplan2

Testplan1 is the only valid entry.

How do i

a) Once I have selected ProjectA in the Project field only display Testplan1 in the Testplan field?

or

b) If the user tries to enter testplan2 for it to error?

I've tried creating a query and linking the controlsource field to it but it doesn't like that! :banghead:

If someone could give me the top-level steps to follow that'd be awesome?

Thanks in advance,

J

 

[pbaldy]

Does this work for you?

http://www.baldyweb.com/CascadingCombo.htm

 

[JungleJme]

Thanks for the quick response!

That is excellent in terms of the cascading combo, that sort of relationship is exactly what i was looking for Thank you!

I will try and work this in to my database and see what success i have.

Thanks a lot,

J

 

[pbaldy]

Happy to help! Post back if you get stuck.

 

[JungleJme]

Well, the inevitable happened! I've got stuck. I think i know WHY i'm stuck but i've struggled to fix it all the same.

So, i've got my issues table which has a project name & a test plan name in it.

However, those two fields are themselves Lookups to another two tables. So although they display the name i think the actual value is the Primary Key?

So, when i try to copy your solution i get a "Data Type" mismatch error on the Query.

I've tried several different ways of trying to get everything to work correctly but have been hitting a brick wall:banghead:

Any idea how do i manipluate this solution to work with my data?

Sample is attached fyi

Thanks for any help!

J

 

[pr2-eugin]

The problem was you have set the Rowsource of the Second combobox wrong.. You had..

  strSource = "SELECT testplan_name " & _
              "FROM test_plans_table " & _
              "WHERE project_id = [COLOR=Blue][B]'" & Me.project_name & "'[/B][/COLOR] ORDER BY testplan_name"

by using single quotes as highlighted, you are trying to compare the project_id (which is a number type) to String.. So I changed the Rowsource as..

  strSource = "SELECT[COLOR=Blue][B] testplanid, [/B][/COLOR]testplan_name " & _
              "FROM test_plans_table " & _
              "WHERE project_id = " & Me.project_name & " ORDER BY testplan_name"

As you can see I have added the testplanid to the Rowsource, as the field is bound to a Number type field, so you need the number to be present..

This confusion (String as Number) is the result of using Lookup field in table.. I have changed that too.. Check attachment for more info..

 

[pbaldy]

I guess I was too slow to respond...

 

[JungleJme]

Thanks so much for the response - i knew it was going to be something like that - just couldn't figure it out!

I'm just starting out so i guess i'm going to trip over things like this quite often for a while....

OK - i think i may be too liberal with my lookups. Seems that it would be best practice to leave default/original values showing in the underlying table but then use the rowsource property on the form to return the "user friendly" value?

Thanks again!

 

[JungleJme]

I guess I was too slow to respond...

But the response is still appreciated - another neat trick learned, so thanks!

 

[pr2-eugin]

User Friendliness comes up with Front end(Forms).. The Back end contains RAW data, does not need to make sense to anyone other than people who need to understand.. Making the table information as discreet as possible makes it a bit secure..

Table Level lookup is pure Evil.. Best to avoid it..

Sorry Paul, I stole your thunder there..

 

[JungleJme]

thanks for the link, that all makes perfect sense! I shall stay away from using look-ups at table level!