Jump to content


Photo

connecting Mysql 2 Microsoft Access with 'Foreign Keys drop downs'


  • Please log in to reply
2 replies to this topic

#1 arianhojat

arianhojat
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 26 May 2006 - 08:11 PM

Hello all,
I need to link to a Mysql database which ive done using MySqlODBC.
but i also need to update the tables with Access interface for our employees (cant be a web app since they might be looking at many thousands of rows and they prefer Access as well.).

I need data entry in rows to be 'smart' so when they update tables, they have drop downs linked to other reference tables.
I dont have much experience with Access but saw a table(not a form or report) the other day where the drop down is linked this way, so they choose via a drop down from within the regular boring table interface. I was hoping to duplicate this by just making the Field a Foreign Key to a reference table in MYSQL. but Access still doesnt link directly but (although it will say the value you entered isnt in other table). By smartly i mean...

Ranking Table has (rankingID, title)...
1 Low
2 Medium
3 High
4 Ridiculous

Girls_Ranking table (girlID, girlName, rankingID)
1 'The Girl from the movie the 5th Element' 3
2 'Kate Moss' 4
3 'Tyra Banks' 3
4 'Angalina Jolie' 4
5 'Bette Midler' 1

I want it so when you update the Girls_Ranking table in Access, there is a dropdown for Ranking.title (users never sees the rankingID), so choosing Medium from this Dropdown, will set the rows rankingID to be 2.
I assume a QueryINNER JOIN has to be done, but then not sure how to do the drop down part from there.

#2 arianhojat

arianhojat
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 26 May 2006 - 08:26 PM

heh i probably need an advanced access tutorial getting checkboxes, n' drop downs to show up in tableview. or a custom form view that repeats (versus the default form that is 1 record per page).

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 May 2006 - 09:53 PM

If you need help with access, you're in the wrong place. In principle, you should be able to pull the FKs from anywhere and put whatever text you want in the dropdowns/checkboxes.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users