Solved Can we update multiple record in multiple rows of subform using a combobox on main form? (1 Viewer)

[ZKHADI]

hi buddies i am attaching an attachment look over there.

here is a laboratory system
i want update the CBC related tests in subform rows with one click. is it possible?

there is a combobox on main form every time need clicks to add tests related CBC
if we put the citeria is as the cbc related tests go into their rows. for example CBC have 4 sub tests.
thanks in advance

 

[June7]

You want to 'batch' create 4 records for the CBC tests? First, need patient ID. New record needs to be committed and then need to retrieve that new patient PID value for use in creating the dependent records.

Does combobox have hidden ID field in RowSource? Show the combobox SQL statement.

CurrentDb.Excecute "INSERT INTO PatientTests(PatientID, TestID) SELECT " & intPID & ", TestID FROM Tests WHERE TestType = '" & Me.cbxTests.Column(2) & "'"

How to get value for intPID variable for new patient is tricky part.

 

[arnelgp]

you need VBA to check if the second column of the Combo is CBC then add it to the subform:

Private sub Comboname_AfterUpdate()
If Nz(Me!PID, 0) <> 0 Then
   If Me.comboBox.Column(1) & "" = "CBC" Then
        CurrentDb.Execute "Insert Into yourTable(PID, TestName) SELECT " & Me!PID & ", TestName From yourTestTable Where MainTestField = 'CBC'"
   Else
        CurrentDb.Execute "Insert Into yourTable(PID, TestName) SELECT " & Me!PID & ",'" & Me.comboBox.Column(1) & "'"
    End If
    Me.SubFormName.Form.Recordset.Requery
End If
End Sub

 

[XPS35]

you need VBA to check if the second column of the Combo is CBC

The same seems to apply to UrineR/E tests. So maybe better check whether the second column is Null or not Null?

I wonder what to do when the user selects two CBC tests? Then 8 tests will be generated!? A unique index could prevent this, but then you have to deal with the fact that in the course of time a patient may need to do the same test multiple times. So........

 

[ZKHADI]

its not working .
file attached kindly check the errors in code which i copied from here

The same seems to apply to UrineR/E tests. So maybe better check whether the second column is Null or not Null?

I wonder what to do when the user selects two CBC tests? Then 8 tests will be generated!? A unique index could prevent this, but then you have to deal with the fact that in the course of time a patient may need to do the same test multiple times. So........

i like your idea. but first let do this then we will think about this idea too

 

[jdraw]

zkhadi,
Please provide an overview description of the "business process(es)" you are trying to support with this database.
Your data model/relationship diagram is incomplete.

Seems you have
1 or more Labs (Testing Facilities) where
1 or more Patients may visit
a Patient may have 1 or more Tests on a Visit
there are a number of possible Tests
a Test involves a test Type/Patient/TestDate and a Result

 

[ZKHADI]

this for my own practice nothing else

zkhadi,
Please provide an overview description of the "business process(es)" you are trying to support with this database.
Your data model/relationship diagram is incomplete.

Seems you have
1 or more Labs (Testing Facilities) where
1 or more Patients may visit
a Patient may have 1 or more Tests on a Visit
there are a number of possible Tests
a Test involves a test Type/Patient/TestDate and a Result

 

[jdraw]

Well, for your own practice, I suggest you identify the "business rules" ina the area under study.
Identify the Entities involved, identify the attributes of each Entity. Use your Business rules to establish relationships between entities. Set up some test data- both good and bad -Try to stump the model. After resolving your stump the model exercise you'll find you now know more details of the requirements and business. Now, start design of the physical database and interface.

I recommend that you open the Database Planning and Design link in my signature. You'll find a reference to RogersAccessLibrary.
Download and review the Class Info System tutorial material
It has Problem, Steps to Do, and Solution. It will take about 30-45 minutes to complete since you have some database experience. What you experience and learn can be used with any database.

Good luck with your project.

 

[ZKHADI]

i will. could you setup my file. i will learn

Well, for your own practice, I suggest you identify the "business rules" in the area under study.
Identify the Entities involved, identify the attributes of each Entity. Use your Business rules to establish relationships between entities. Set up some test data- both good and bad -Try to stump the model. After resolving your stump the model exercise you'll find you now know more details of the requirements and business. Now, start design of the physical database and interface.
Good luck with your project.

 

[jdraw]

I have revised my last post. It now has additional info. Try the tutorial --you will learn by experience.
The best 45-60 minutes you'll spend re database design!

 

[ZKHADI]

I have revised my last post. It now has additional info. Try the tutorial --you will learn by experience.
The best 45-60 minutes you'll spend re database design!

thanks

 

[June7]

Some changes I recommend:
1. Should save ID, not TestName. Remove TestName field from PatientTestDetailsT. Change TestName5555 to TestID_FK. Recommend also use PID_FK name instead of PID.
2. I see no need to check if test type is CBC or not. Put some value in all records for Particular, such as HepB, HepC, etc.
3. No need for Unit, TPrice, ReferenceRanges fields in PatientTestDetails unless this is to prevent changing historical data if info is changed in TestListT. If you decide not to save, modify code as appropriate.
4. Should save patient birthdate, not age - age should be calculated
5. Don't allow Zero Length String in fields (table design)
6. Use patient form to view existing as well as add new.
7. Numerous other changes not listed - enjoy discovering them

I did not address: As structured, each patient can have only one visit and one set of tests. Doubtful in real world. If you expand to allow multiple visits and testing, VisitingDate and ReportingDate should be in VisitsT and possibly linked to PatientTestDetailsT, unless you don't care about history and just want the most recent data saved.

See revised db attached.

 

[XPS35]

its not working .
file attached kindly check the errors in code which i copied from

I think there are indeed errors in the code if you use in your database (like the name of the combobox is different (Combo30) now).

But I also think your table design is not right. In my opinion you should correct the design first before thinking about codes to add records. Here is my suggestion.

 

[XPS35]

While writing mine June7 posted his reaction, which is partly reflected in my design.

 

[ZKHADI]

Some changes I recommend:
1. Should save ID, not TestName. Remove TestName field from PatientTestDetailsT. Change TestName5555 to TestID_FK. Recommend also use PID_FK name instead of PID.
2. I see no need to check if test type is CBC or not. Put some value in all records for Particular, such as HepB, HepC, etc.
3. No need for Unit, TPrice, ReferenceRanges fields in PatientTestDetails unless this is to prevent changing historical data if info is changed in TestListT. If you decide not to save, modify code as appropriate.
4. Should save patient birthdate, not age - age should be calculated
5. Don't allow Zero Length String in fields (table design)
6. Use patient form to view existing as well as add new.
7. Numerous other changes not listed - enjoy discovering them

I did not address: As structured, each patient can have only one visit and one set of tests. Doubtful in real world. If you expand to allow multiple visits and testing, VisitingDate and ReportingDate should be in VisitsT and possibly linked to PatientTestDetailsT, unless you don't care about history and just want the most recent data saved.

See revised db attached.

thanks bro you did it. i appreciate it.