an one-to-one relationship

[CCSC0208]

I have 2 tables: Table A is personal info, Table B is termination info. Two tables have an one-to-one relationship by ID. I've created a form to show all fields from Table B and 2 fileds from Table A. I want to enter ID in the form, after clicking Tab, automatically the data of those 2 fields from Table A are brought up. Can somebody help me? It works in an one-to-many relationship situation, but not here.

Thank you!

 

[GingerR]

my first question is if the information is one-to-one, why do you have two different tables? why not have all the info in one table?

that being said--i need a little clarification. do you only have one form? or a main and sub form? what is the recordsource of the form(s)? when you say you want to enter ID in a form, do you mean just to search existing data? what is the code behind the text box you are typing the ID into?

thanks--

 

[CCSC0208]

Actually, I have many tables. All other table have one-to-many relationships to Table A. Only Table B does have a one-to-one relationship to Table A. Each table stores a type of medical information with a composite key ID+VISIT while Table A stores the patient's personal info with the primary key ID. If a patient is withdrawing from a clinical study, then he needs to provide some information, and since it doen't happen to all patients, I have this piece of information seperate from Table A and with the primary key ID; therefore, Table A and B has an one-to-one relationship.

Each form is designed to enter one type of medical information for one visit. For this withdrawn information, I have a query to select all fields from Table B and name and DateOfBirth from Table A with matching ID. I have only one form, no main/sub form, recordsource is the query, after entering the ID, I hope to see the name and DOB come up, so I know I enter the correct ID for this patient. I don't have any code since I don't know much about VBA.

Is this right to have both Table A and Table B? If I must have 2 tables with one-to-one relationship, how to solve this proble?

Your answer will be greatly appreciated!

 

[Vie]

In the form, where are you entering the ID? Your problem should be fairly easy to remedy but it would help if you could provide some more particulars.

What I understand is that your form has something like:

PatientID (tblA)
PatientName (tblA)
PatientDOB (tblA)
TerminationInfo1 (tblB)
TerminationInfo2 (tblB)
TerminationInfo3 (tblB)

Do you enter the actual number of the ID or do you select it from a combobox by the patient's name? Also, I assume you only want one record to show at a time, yes?

 

[CCSC0208]

SELECT [TERMINATION].*, PAITIENT.NAME, PAITIENT.DOB
FROM PAITIENT INNER JOIN [TERMINATION] ON PAITIENT.ID = [TERMINATION].ID;


ID (tblB)
=> If the ID is from tblA, it says that I am creating a duplicate value in the primary key.
Name (tblA)
DOB (tblA)
TerminationInfo1 (tblB)
TerminationInfo2 (tblB)
TerminationInfo3 (tblB)

I open a blank form to enter ID into a Text Box, and I only want to see one record with the matching ID.

Thank you for your help!

 

[swaybright]

You're almost there. We need to make sure the textbox is unbound, change your Select query to a parameter query, and requery everytime you change the value in the textbox.

First, Let me suggest that you change your textbox to a combobox; this isn't critical, but you can use that to make sure that the ID you enter actually exists in the DB. Use the combobox wizard to do this, choose the table and ID field you want the choices to come from, and make sure you DON'T make it bound.

Second, Put a critera into your query for the field ID that is equal to this unbound combobox(or textbox). The syntax should be Forms!YourForm!YourCombobox. Then right click in the gray area of the QBE pane and select "Parameters" and enter that same syntax there.

Last, let's put some code behind the on change event of the Combobox(textbox). In the form properties dialog under the event tab, click on the build button (...) and select Code Builder. You will enter the VBA dialog window. Type in the following code:

 Me.requery

Close the VBA window and you are all set.

Post back with any questions

Shane

 

[GingerR]

sorry i didn't get back sooner. went on vacation--
anyhow, i still vote for putting all the info into one table. just because some people don't have info for table B - i don't see that as being an issue. If you really MUST have two tables, i have another question cause I'm not totally clear: you say you are going to enter info because someone is withdrawing from the program. so doesn't that mean that perhaps they don't have any info in Table B yet? if that is so, then the query that the form is based on really wouldn't be valid. I think instead--and I may be wrong--you want to base the form solely on Table B. Then, as Shane says, make the ID field on the form be a combo box, and make it's recordsource the ID, Name and BirthDate and anything else you need from Table A to make sure you're choosing the the right person.

hope this helps--g

 

[CCSC0208]

Thank you, G and Shane. The problem has been solved.

 

[danvlas]

SELECT [TERMINATION].*, PAITIENT.NAME, PAITIENT.DOB
FROM PAITIENT LEFT JOIN [TERMINATION] ON PAITIENT.ID = [TERMINATION].ID;

will display existing data for those 'terminated' and patient name and DOB continued with blank fields for those 'still kicking'

No combo, no code...and the form can contain all the info as if there was just one table.


HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html