How to add data to another table using list box.

[whoknows361]

Hello all.
I have 2 tables:
tbl_Referral_Data
Referral ID (autonumber) PK
client name
client address
client dob
etc.

tbl_Open_Episode
Referral ID (as each referral ID has only one Open Episode)
Admit Date
Date Sent to Billing

I want to create a form which will display a combo box where users will be able to pick the client name which has an open episode form completed. So they would pick the client name.. Then fill in the Admit Date and Date Sent To Billing Fields, and this would automatically add the record to the tbl_Open_Episode, including adding the Referral ID frm tbl_Referral_Data to the Referral ID field in tbl_Open_Episode. But I can't seem to identify how to do this. Your help is appreciated..

 

[fneily]

(as each referral ID has only one Open Episode)"
Why the need for two tables? Why not just put AdmitDate and DateSentToBilling in tbl_Referral_Data?
Then just make a form on tbl_Referral_Data, create a combobox from the wizard using the third option.

 

[whoknows361]

thank you fneily this seems an obvious fix.. and i got it to work. But I continue to have the same problem whenever I am trying to add data to other tables than tbl_Referral_Data based on the Refferal_ID field. Here's what I mean.

3 tables :

tbl_Referral Data
Referral ID (auto#) PK
client Name
Client address
etc.

tbl_Therapist_Assigned
Thx ID (auto#) PK
Referral ID
Thx Name
Thx Dept
Assigned Date

tbl_Therapist_Staff
Thx Name
Thx Focus

relationships: tbl_Therapist_Staff has staff names and foci which are manually entered and fill in the Thx Name field in tbl_Thearpist_Assigned. ie a one to one relationship.
tbl_Referral_Data is related to tbl_Therapist_Assigned via the ReferralID field in a one to many relationship - as each Referral ID can have numerous Therapists assigned to it over time.

I think these are correct relationships with what I am trying to do:
The problem: I am having a hell of a time creating a form to do what I want. I want a user to pick the client name from a combo box, the client name has a ReferralID associated with it in the tbl_Referral_Data. Once that client name is clicked on (chosen) - I want the user to be able to pick the Therapist assigned to the Client Name from a drop down box, then enter the Thx Department, and Assigned Date and hit an enter button.
When the button is hit, I want a new record to be made in tbl_Therapist_Assigned which displays this info(thearpist assigned, thx dept, etc) as well as add the ReferralID field in tbl_Assigned_Therapist with the ReferralID value associated with the Client Name.

I tried to be as clear as possible.
I can't seem to be able to do this, though I think I am missing some basic concept here.

Additionally fneily, when I add a combo box to a form with the wizard- I only get 2 options not 3. I see "I want the combo box to look up the values in a table or query. or "I will type the values that I want." ??

Thanks again - i do appreciate all assistance greatly.

 

[fneily]

Don't know if you read this:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Last question first - if a form is not bound to any recordset(table/query) then you only get two options in the Wizard. So the form must be bound to see the third option.

"Referral ID can have numerous Therapists assigned to it over time." So a single Refferal could have many Therapists. This also implies that a single therapist could have many referrals. This is a many-to-many relationship. So the tables are:
tbl_Therapist_Staff
TSID Primary Key
FirstName
LastName
Focus
Dept(I'm assuming therapist are in a dept. and don't wander)
other therapist specific fields

tbl_Referral_Data
ReferralID Primary Key
FistName
LastName
Address
other client specific fields

tbl_Therapist_Assigned (known as a junction table)
TAID Primary Key
TSID Foreign Key
ReferralID
DateAssigned
Other fields that are COMMON to each table

Now you build your form, using the Form Wizard, based on the junction table. TSID and ReferralID you can, after you create the form, make them comboboxes.

 

[whoknows361]

Fneily, thank you so much, I just didn't understand the many to many relationship. But it makes much sense now.

I did as you stated, created the tables, and the relationships. I created the form with one hitch. I created the form based on the junction table as you stated, was able to turn referralID and ThxID into comboboxes and even assign the query statemnets to pull in the correct data so that referralID listed the client names and ThxID the Therapist's names, however...
When I make the data type values for referralID & ThxID Numbers (as they are) - in the form view, although they list the correct text data, it states that I have a data type mismatch and cannot continue. IE there is text in a number field.

When I make the data type text, again the combo boxes correctly show the correct data, and I can even add the record after filling in all the info, the problem is that the database saves ReferralID as the Client Name, and saves ThxID as the Therapist name, not the actual numbers they should be. As ReferralID and ThxID are both number data types.

Can you indicate how to correct this data type mismatch?

 

[PHV]

Have a look at the Bound column property of your combos.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[whoknows361]

Yes, PHV... so I changed the bound column so that it shows the correct number.. hoewver, when u click on the down arrow on the combo boxes it shows all the names... when u choose a name it puts the correct number in the text box... and it all saves correctly in the table..

but what I want is for the user to click the down arrow on the combo box, see all the names... click the correct name... the name still appears in the combo box as your filling out the rest of hte data... but when u add the record.. the associated number is saved.. not the name..

(want the name shown as a user is filling out the form so that they dont get confused, as a user will not know what the numbers even mean)
is this possible?

 

[PHV]

set the width of the 1srt column to 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[whoknows361]

PHV! thanks so much, that is perfect.. now one more follow up and I should be golden...

i think i should re-describe the current db so anyone viewing this post will be caught up and address my current question. I have 2 tbls.

tbl_Referral_Data
ReferralID PK (auto#)
Client Name
Client Address
Admit Date
etc.

tbl_Therapist_Assigned
Thx ID (auto#) PK
Referral ID
Thx Name
Thx Dept
Assigned Date

forms:
form_referral_rcvd
adds client name, address, etc.

form_Open_Episode_rcvd
adds the assigned therapist to the client name by referencing the referral id, and the assigned therapist date, adding records to the tbl_Therapist_Assigned

But when an open episode is received it also includes admit date (there is only one admit date per Referral ID, hence the reason that the admit date field is in the tbl_Referral_Data)..

I want to add a textbox to the form_Open_Episode_rcvd, that will update the field Admit Date in the tbl_Referral_Data, with the correct admit date.

Now I perceive that you would do this with subforms, but the datatype for Referral_ID in the tbl_Referral_Data is autonumber, and the datatype in Referral_ID in the tbl_Therapist_Assigned is text (so that the combo boxes show client names and not referral id numbers). and when i try to add a subform it says type mismatch.

trying to clarify:
so if i want to update my main table (tbl_Referral_Data) through the form_Open_Episode_Rcvd(which is based on tbl_Open_Episode_Rcvd), by adding an admit date textbox.. how do I get this textbox to update the field "Admit Date" in the tbl_Referral_Data?

This may be confusing.. I can post my db for access 2003 if necessary... let me know if u need this.

You have all been a great help thus far.. I think once I get this down... i can finish up the db.

Thanks again.

 

[PHV]

the datatype in Referral_ID in the tbl_Therapist_Assigned is text
It should be Numeric (Long), indexed and in relation with Referral_ID in the tbl_Referral_Data

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

So you got rid of tbl_Therapist_Staff?
And a therapist will only have ONE client FOREVER? If the answer is no, then you're not normalized and need a junction table.