Show only current record in report from form/subform

[stelioshania]

Hello everybody!

I try to make a database for my surgery.
I have 2 tables: tblPatients (key; Patient ID) and tblVisitMain (key: VisitID) (one-to-many relationship)
I made a form that has my patients details (frmPatientsMain3 ) and a second form for patients’ visits (frmVisitMain3).
The second form (frmVisitMain3) is a form/subform which is made from ‘frmPatientsMain3’ and the subform ‘subformVisitMain3’ which contains data from ‘tblVisitMain3’. They are linked (master/child) by ‘patient ID’ key.


First, I tried to make a report (rptRecipe) in order to print the recipe for the current visit. The result was a report containing all Recipies from all visits and therefore I could not print a single recipe


Then, I made a form/subform (frmRecipe) as a recipe. This form is like frmVisitMain3 but it contains only the necessary details (surname, name, medication, date, etc)
The button that open frmRecipe contains this code
Private Sub Command129_Click()
On Error GoTo Err_Command129_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmrecipe"
stLinkCriteria = "[PatientID]=" & Me![PatientID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command129_Click:
Exit Sub
Err_Command129_Click:
MsgBox Err.Description
Resume Exit_Command129_Click
End Sub
This button opens the frmRecipe for the current patient but it does not chose the current visit prescription and it prints all patient visits recipes.


Is there any way to print only current visit’s recipe?

(I did this in a strange way. From subformRecipy Properties- Data,- Recordsource- Query builder, I chose sort Descenting by VisitID. By this way when FrmRecipy opens it appears the last visit record. When I print I choose page1 and it prints the last recipe. But it is complicated)


Is there a way to print current recipe from OR REPORT frmVisitMain3???

 

[SpentGeezer]

You can filter your receipt report by the current visit ID. See the attached DB for filtering a report demo.

 

[stelioshania]

You can filter your receipt report by the current visit ID. See the attached DB for filtering a report demo.

Thanks for your reply
According to your example I used this code
Private Sub Command163_Click()
On Error GoTo Err_Command163_Click
Dim theRecord
theRecord = VisitID.Value
Dim theFIlter
theFIlter = "VisitID = " & theRecord
Dim stDocName As String
stDocName = "rptRecipe"
DoCmd.OpenReport stDocName, acPreview, , theFIlter
Exit_Command163_Click:
Exit Sub
Err_Command163_Click:
MsgBox Err.Description
Resume Exit_Command163_Click
End Sub

When I Try this a small window asking VisitID appears (enter parameter value)!
Any suggestion????
(The button is in the sub form/ this subform has PatientID and VisitID boxes)

 

[SpentGeezer]

In the filter it has to be whatever the field is that is bound to the Visit ID textbox:

Replace this part:

[I]theFIlter = "VisitID = " & theRecord[/I]

with this (replacing <fielname> with your field name:

[I]theFIlter = "<fieldname> = " & theRecord[/I]

 

[stelioshania]

In the filter it has to be whatever the field is that is bound to the Visit ID textbox:

Replace this part:

[I]theFIlter = "VisitID = " & theRecord[/I]

with this (replacing <fielname> with your field name:

[I]theFIlter = "<fieldname> = " & theRecord[/I]

I did it and the result is exactly the same as before.
The code now is:

Private Sub Command163_Click()
On Error GoTo Err_Command163_Click

Dim theRecord
theRecord = VisitID.Value
Dim theFIlter
theFIlter = "VisitID = " & theRecord


Dim stDocName As String

stDocName = "rptRecipe"
DoCmd.OpenReport stDocName, acPreview, , theFIlter

Exit_Command163_Click:
Exit Sub

Err_Command163_Click:
MsgBox Err.Description
Resume Exit_Command163_Click

Thanks for your interest. Do you have any other idea?

Thanks

 

[SpentGeezer]

The field that you are filtering has to be in the report source table or query. Is it?

 

[SpentGeezer]

Can you list the fields that are in the report source table or query please?

 

[stelioshania]

Can you list the fields that are in the report source table or query please?

Thanks again!
The form part (Which in fact is a small part of the 'main' FrmPatients where I edit patient details and then navigate to form/subform VisitMain3 ) of the form/subform ''frmVisitMain3 has these fields from tblPatients:
PatientID (Primary key)
First
Surname
FathName
Gender and many other similar fields

The subform part (SubformVisitMain) of form/subform 'frmVisitMAin3' has these fields from tblVisitMAin:
VisitID (Primary Key)
VisitDate
PatientID
Reason,
Symptoms,
ClinicalFindings,
Diagnosis,
Note, and many other similar fields

Form and subform have Linke Master/child fileds : PatientID

Thanks again!
You are my last hope!!!!

 

[vbaInet]

Have a look at this link from pbaldy:

http://baldyweb.com/wherecondition.htm

It's for opening a form but the same principle applies when opening a filtered report, just a matter of changing OpenForm to OpenReport.

Or you can look at this sample from boblarson:

http://www.btabdevelopment.com/ts/samples

The link is titled "Open Generic Report with Various Criteria (simple sample)"

 

[stelioshania]

I tried this code according to the example you showed to me but rhe result is that 'enter parameter value-=--visitID''
Now the code is:

Private Sub Command163_Click()
On Error GoTo Err_Command163_Click
DoCmd.OpenForm "FrmRecipe", acViewPreview, , "[VisitID]='" & Me.VisitID & "'"
Exit_Command163_Click:
Exit Sub

Err_Command163_Click:
MsgBox Err.Description
Resume Exit_Command163_Click

End Sub


I am at a dead end.
Is there any way for you to check my database?
Thanks so much for your help

 

[vbaInet]

Two things:

1. Why are you opening a form? I thought your intention was to open a report?

DoCmd.OpenForm "FrmRecipe", acViewPreview, , "[VisitID]='" & Me.VisitID & "'"

2. The field [VisitID] must be a field name in the report's Record Source and it must also be a field in the detail section of the form.

 

[stelioshania]

Two things:

1. Why are you opening a form? I thought your intention was to open a report?


2. The field [VisitID] must be a field name in the report's Record Source and it must also be a field in the detail section of the form.

I make tries with both report and form Recipe. The result is the same.
The field VisitID is in both subformVisitMain3 (where the button to open the report is) and report(or form) recipe. In fact when I add this field it appears as tblVisitMain.VisitID Control source.
I continue seeing Enter parameter value
Is there any chance that the problem has to do with tblPatients and tblVisitMAin3 Relationship??? It is one to many with reinforced Referential Integrity (cascade update related fields) and join type -2nd option

 

[vbaInet]

If it's called tblVisitMain.VisitID then it means that VisitID exists in two or more tables in the record source.

Change the NAME of the textbox that is bound to VisitID (on the form) to txtVisitID. Then use this code:

DoCmd.OpenForm "FrmRecipe", acViewPreview, , "tblVisitMain.[VisitID] = '" & Me.txtVisitID & "'"

 

[stelioshania]

I found some errors.
All the forms/subforms had their fields on Header not on Detail section
I made the database almost from scratch. I named the new forms frmVisitMain, frmPatients, frmRecipe ( I did not use number (3) any more).
Everythink else is the same. The button for the recipe is in the subform part of frmVisitMain and has the following code:



Private Sub Command165_Click()
On Error GoTo Err_Command165_Click
DoCmd.OpenForm "FrmRecipe", acViewPreview, , "[VisitID]=" & Me.VisitID
Exit_Command165_Click:
Exit Sub
Err_Command165_Click:
MsgBox Err.Description
Resume Exit_Command165_Click

End Sub

The problem is that I tried again all your previous suggestions and it appers the first visit recipe (fortunatally from the correct-current patient.
The visitID field exist in the detail section of subform recipe (part of the frmVisitMain) as well as in the suborm and form part of frmRecipe. (It has not the name tblVisitMAinVisitID any more)
Any way to have the current visit appear?

 

[vbaInet]

I don't understand all of this -->

The problem is that I tried again all your previous suggestions and it appers the first visit recipe (fortunatally from the correct-current patient.

Do you get an error when you run the code?
What is the data type of VisitID?

 

[SpentGeezer]

I am discombobulated

 

[SpentGeezer]

OP, you are going to have to post your DB....it is hard to follow you now. Make a copy and empty out all the tables. Then post it.

 

[stelioshania]

OP, you are going to have to post your DB....it is hard to follow you now. Make a copy and empty out all the tables. Then post it.

I am sending the database.
I did not remove the tables- names and visit details are not real.
Open a patient, go to his visit- navigate to the second, third or last visit, and down at the form there is the command button to open the recipe form or report.
All I want is to open this form using current visit and print only this visit!!!!
It now opens all visits and prints all of them.

Sorry for being tiresome but it is because of my poor knowledge in access.
Thank you very much.

 

[SpentGeezer]

Sorry dude, I only have Access 2003. Can you convert (or can someone with 2007 solve this small problem?)..

 

[stelioshania]

Sorry dude, I only have Access 2003. Can you convert (or can someone with 2007 solve this small problem?)..

Sorry, It does not allow me to save to an erlier version .
Anyone else willing and able to help me?