auto populated text boxes in form not popuating table (1 Viewer)

lizcliff

Registered User.
Local time
Today, 11:20
Joined
Feb 23, 2013
Messages
38
I am creating a database to capture new referral information. So I have a patient table and a referral table to capture the data.

I have created a form to set up new patients to add to the patient table. Patient number combo box has a control source of patient number from the patient table and the row source is based on a query to a linked table pulling all the relevant patient details through. The patient number saves to the patient table but the other details I am pulling through into the form using text boxes control source =patientnumber.column(2) etc. are not? am I missing something...

my next part would be to open referral form to log referral information on that patient - how do I get the patient number to automatically open up on the referral form so I get that link?
thanks for your help

I have set up the patients table with a primary Key of patientID - autonum and referrals table with primary key RefId autonum, including field patientID to link to patients table so I can add them as relationships
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:20
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you saying patientID (autonumber) and "patient number" are not the same thing?
 

lizcliff

Registered User.
Local time
Today, 11:20
Joined
Feb 23, 2013
Messages
38
No Patient ID is just an autonumber to give a primary key
patient number is a number like CC00123456 although I will only want 1 patient number in the table I maybe don't need this ID? but as I am linking to our national patients table I needed a table of just our patients in this service but I wanted to data entry clark to choose rather than type a patient number in to save data quality as I could show all relevant details to that patient as a safety check, i,e, name, age etc.

1 patient could have many referrals So I think I have talked myself into having the patient number the primary key :banghead:

even with that though I still am struggling to understand how the populated text boxes can save in table.

Patient Number = control source combo box
row source = SELECT Qry_PatientDetails.hospitalNumber, Qry_PatientDetails.NHSNumber, Qry_PatientDetails.surname, Qry_PatientDetails.forename, Qry_PatientDetails.postcode, Qry_PatientDetails.Sex, Qry_PatientDetails.dob, Qry_PatientDetails.Desc, Qry_PatientDetails.OrganisationName FROM Qry_PatientDetails;

all these are then set up in text boxes : =[PatientNumber].[column](2)
but these aren't pulling through into table

thanks, sorry I am new to forms, always had the data usually not had to capture it before.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:20
Joined
Sep 21, 2011
Messages
14,046
I would be using bound controls and setting them in the afterupdate event of the combo.

I believe you are using unbound controls as you are assigning the values in the control source.
Code:
Sub Combo_AfterUpdate()
Me.PatientNumber = Me.combo.column(2)
End Sub
Note the column index is zero based.?

However you should likely only be storing the PatientNumber and retrieve all those other details when you need them, as in this case.?

HTH
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 11:20
Joined
Oct 29, 2018
Messages
21,358
No Patient ID is just an autonumber to give a primary key
patient number is a number like CC00123456 although I will only want 1 patient number in the table I maybe don't need this ID? but as I am linking to our national patients table I needed a table of just our patients in this service but I wanted to data entry clark to choose rather than type a patient number in to save data quality as I could show all relevant details to that patient as a safety check, i,e, name, age etc.

1 patient could have many referrals So I think I have talked myself into having the patient number the primary key :banghead:

even with that though I still am struggling to understand how the populated text boxes can save in table.

Patient Number = control source combo box
row source = SELECT Qry_PatientDetails.hospitalNumber, Qry_PatientDetails.NHSNumber, Qry_PatientDetails.surname, Qry_PatientDetails.forename, Qry_PatientDetails.postcode, Qry_PatientDetails.Sex, Qry_PatientDetails.dob, Qry_PatientDetails.Desc, Qry_PatientDetails.OrganisationName FROM Qry_PatientDetails;

all these are then set up in text boxes : =[PatientNumber].[column](2)
but these aren't pulling through into table

thanks, sorry I am new to forms, always had the data usually not had to capture it before.
Hi. No worries. Thanks for the clarification. In database design, you would want to avoid storing the same information in more than one table. For example, let's say you have a patients table and a referral table. So, you might have a patient with a patient number of CC00123456 and a name of Jane Doe. Now, if Jane has a referral, you might be tempted to store the patient number and patient name in the referral table. However, if the patient table gets updated, for example, let's say Jane got married or divorced and changed her name to Jane Smith, then your referral table information of Jane Doe will be incorrect.


The proper way to approach this is to only store the patient number in the referral table and every time you want to know the name for a particular patient number, you would "look it up" from the patients table. This is usually done using a query joining the two tables together on the patient number field.


Hope it makes sense...
 

lizcliff

Registered User.
Local time
Today, 11:20
Joined
Feb 23, 2013
Messages
38
Thanks.

Do you think I should just have a referrals table, with a lookup to my patient pool to find the patient to add to referrals and not have a patients table.

this is all based on referrals, all other forms (the referral could go to an outpatient form or an inpatient form or a therapy form or have multiple events under one referral) would be linked to the referral ID, and in time I would report on patients who have multiple referrals but I could query this off the referral table.
 

Users who are viewing this thread

Top Bottom