update a join table in a many -many relationship

[Kyosa]

i have a table that is an intermediate table used to join two other table together. This table contains 3 fields (an autonumber prim key, the lmrp id -the prim key from my LMRP table and the diagnosis id which is the prim key from my dx table)

i am trying to figure out the best way to update that table. There are times i will have to input several hundred diagnosis codes in this but can;t seem to figure out how.

What i would like to be able to do is type the diagnoss (not he prime key id) and then append that info to the intermediate table. the trouble i'm having is that no matter what i do i always seem to be dealing with the diagnosis primary key and not the dx itself. if i set up the subform and use a list box for the diagnosis values (which works well for the data entry part)

(i know i'm not explaining this well and i'm sorry) ithink it's my frustration coming thru. this is the first time i've had to really deal much with many:many relationships and it's throwing me off.



Kyosa


"The key to flexibility is indecision!"

 

[lespaul]

Kyosa,

I followed your design thread so I have a basic idea of what you're talking about, but not so sure what you are asking for.

You have your LMRP table with fields:
LMRPID
LMRPDescription

You have your diagnosis table with fields:
DXID
DXDescription

and you have your intermediateTable with:
ID
LMRPID
DXID

Which table are you trying to update/insert records into? tblDiagnosis or the intermediate table?

Leslie

 

[Kyosa]

LesPaul,
wow that was quick..

Sorry, i am trying to update the intermediate table.. The other two act more like lookup tables than anything else.

I had set up yet another table to use as an update table and placed it as a subform on my update form. I really wanted to limit the amout of data entry that was needed so i had set up a combo box that the user could select the LMRP info from and then had the LMRP ID default into LRPID of my update table. SO that oart wasn't hard. BUT i didn;t want the user to have to go trolling thru a list of 15000 diagnosis codes and have to pick and choose.

So i wanted the user to be able to just tyoe the diagnosis codes from there on but am having a hell of a time figuring out a way to let the user enter the diagnosis yet have the diagnosis ID be what was stored.

i know it's going to end up being somethign goofy and that i'll end up banging my head against the wall when it's pointed out to me. but any advice would be MOST appreciated.

thanks for your time.





Kyosa


"The key to flexibility is indecision!"

 

[lespaul]

So you have a form where the user selects the LMRP to add Diagnosis codes to. Now you want the user to enter the diagnosis description to add to the LMRP and create a new record in the intermediate table for the LMRP that was selected and the DXID for the dianosis that was entered.

Do I have it now?

I'll tell you right now that I don't do much programming in Access, so I'm not sure how you would go about this. But what I would do is have a dropdown list that displays the diagnosis (not the ID) and have it show matching entries. You'll have to set up bound columns so that Access knows to display the Description, but use the DXID for the insert. Once the user has selected the correct diagnosis, then run an insert query that takes the LMRP and the DXID for the query.

Does that make sense? I'd have to look into it a little more to give you more detailed description.

HTH

leslie

 

[GoDawgs]

Leslie is right, you need to set up a combo box where the user can pick the diagnosis. I'll try to walk you through getting this to show the description and not the ID:

1. Add the combo box
2. Set up the Row Source for the combo box (in the properties). This should be the Diagnosis ID and the Diagnosis (just those two fields).
3. Set the Column Count to 2
4. Set the Bound Column to 1 (the ID field)
5. Set the Column Width to 0";2" (the 0 hides the ID field, the 2" is the size of the other field...adjust as needed).

That's it, now your combo box will let the user select the description but store the ID. Hope that helps.

Kevin