Inserting data from combo box to table (1 Viewer)

[Kuleesha]

Hi,
I have a patient related database where there are 2 tables - tblPatient (primary key Patient ID) which includes patient details like name etc and tblVisit (primary key VisitID) which includes data recorded at each patient visit like visit date and blood test results. To join these 2 tables I have created another table with PatientID and visit dates (primary key VisitID) which is connected with one to many relationship with tblPatient via PatientID and one to one relationship with tblVisit via VisitID.
I have created 2 queries one for for patient details with tblPatient and another for visit details with tblVisit.
I have 2 forms one created using each of the queries - frmPatient and frmVisit.
I need to have a combo box on the frmPatient form to select the date/dates (one patient could have many visits) that the patient has visited so that I can see the respective details from frmVisit. I also need to be able to insert a new date value to the combobox and transmit this to tblVisit so that I can insert visit information like blood test results into the tblVisit.
Is this possible using a combobox or is there a better way to go about this?
Thanks in advance.

 

[theDBguy]

Hi. Welcome to AWF!

I am not sure you needed the third table. You should be able to simply add a Patient ID field in the Visits table and use a form/subform setup to assign multiple visits to each patient.

 

[The_Doc_Man]

Viewing this another way, it is VERY RARE that you would ever actually use a one-to-one relationship (as you described for your "junction" table). If something is 1/1 with another record and they both are defined by a common key, they belong together; the division that you see is illusory. The ONLY time you use 1/1 relationships is if some of the data has different security/viewing issues than the rest of the data, or when you are breaking apart tables so large that they require more than 255 fields (the Access limit for table sizes).

As to adding new information when you are adding a visit, there are two ways I've seen this done. One is to allow data entry into the combo for lookup purposes and to include a NotInList event. This event would then use the DoCmd I describe below.

ComboBox.NotInList event (Access)

Office VBA reference topic

docs.microsoft.com

The other way would be to add a command button (rather than the NotInList event) to force the related form to move to a different record.

Once you are ready to create the record, do so using:

DoCmd.GoToRecord method (Access)

Office VBA reference topic

docs.microsoft.com

In either case, the different record would be specified with acNewRec (new record).

 

[Kuleesha]

Thanks a lot guys. I reduced my tables down to 2 according to your advice and managed to get the same result as having 3 tables which simplified things a lot.
@The_Doc_Man I created a combobox on the frmPatient form that references to the visit date field in the frmVisit form. It now shows the visit dates. I would like to be able to click this date and open the relevant record in the frmVisit form in edit mode. I wrote following code and added to on change event of combo box.
Private Sub cmbVisit_Change()
DoCmd.OpenForm "frmVisit", , , "VisitDate=" & cmbVisit.Value, acFormEdit
End Sub
This opens the frmVisit form but displays a blank record. Could you please help as my vba is pretty poor.
Thanks

 

[The_Doc_Man]

Probably your "VisitDate=" & cmbVisit.Value is at fault.

What is the data type of VisitDate in the record that feeds frmVisit? If it is REALLY a date then that might need to be

DoCmd.OpenForm "frmVisit", , , "VisitDate=#" & cmbVisit.Value & "#", acFormEdit

because dates have to be bracketed with octothorpes (a.k.a. pound sign) in certain cases. This is likely to be one of them because you are using concatenation, which WILL produce a string. The odds are practically 100% that whatever is presented by cmbVisit when you select it will be an "unquoted" date, whether as a string or as something else. By the way, you don't ever need .Value on any control that HAS a value because .VALUE is the default property in those cases. BUT - if the bound field of cmbVisit produces a traditional date in dd-mmm-yyyy format, those # signs are needed. If the bound field of cmbVisit produces something other than a "traditional date" then more work might be needed.

 

[Kuleesha]

@The_Doc_Man

Yes the VisitDate is of Date variant. I tried your code with # signs but get an error - Runtime error 3075. Syntax error in date in query expression 'VisitDate=#33'

I mananaged to get it to work with following:

Private Sub cmbVisit_Change()
DoCmd.OpenForm "frmVisit", WhereCondition:="VisitID=" & cmbVisit.Value
End Sub

It seems cmbVisit.Value delivers the visitID rather than the date

Thanks loads

 

[The_Doc_Man]

That would be because of which column is the "bound" column of the combo box - but that is a perfectly valid approach. IF the date had been returned, you would have needed the # signs due to that substitution's effect on presentation of literal date values. But since it is not a date, you don't need special delimiters.

 

[Kuleesha]

Sorry to bring this up again. I thought it was working earlier but now it's not.
I have a combobox (search visit dates) in frmPatient form which contains all visit dates for a particular patient. I want to be able to open up the frmVisit form for the relevant patient for the relevant visit date when i click a date in the combobox.
I have the following code set up:

Private Sub cmbVisits_Change()
DoCmd.OpenForm "frmVisit", acNormal, , "ID=" & Me.cmbVisits.Value
End Sub

However the frm visit form opens up to a new visit ID and not the one I specified.
I cannot for the life of me findout where the bug is.
I have attached the DB below.
Any suggestions would be most welcome.
Thanks

 

[The_Doc_Man]

A form (not its individual controls) has certain .Allowxxxx properties. See if you have .AllowEdit = No (or False) in the misbehaving form. It is theoretically possible to declare a form that is for Insert-Only operation, which would cause that symptom.

 

[Kuleesha]

A form (not its individual controls) has certain .Allowxxxx properties. See if you have .AllowEdit = No (or False) in the misbehaving form. It is theoretically possible to declare a form that is for Insert-Only operation, which would cause that symptom.

Allow edits are set to yes in the form

 

[Gasman]

I would have thought you would need the date as well?, else you would just get the first record for that ID?

Have you walked through the code to see what is passed as ID ?

 

[Kuleesha]

I would have thought you would need the date as well?, else you would just get the first record for that ID?

Have you walked through the code to see what is passed as ID ?

The visit ID which is the bound column in the combo box is unique for each patient+visit date combination. I need to be able to open the frmVisit form to the relevant patient+visit date combination (which is already saved in tblVisit) when I click on the relevant record on the combobox

 

[Gasman]

Sadly, your DB is too new for me to open, so I will leave it to other members.

 

[Kuleesha]

Found the problem. The data entry property of the 2nd form was set to yes and setting this to no solved the issue.
Thanks for the replies.