Report not Working

Hi running32,

pass the value of lngPatientId to  new textbox in your Form,
then run the query, referening to this textbox

I hope this helps.

jaffer

I'm sorry can you explain more for me.  I'm a little lost.

thanks

Textbox 5 has the lngPatientId value

Did it work for you?
OR am I missing somethig?

I'm still trying to get it to work.  

you said:
>Textbox 5 has the lngPatientId value

One way to test it is, Don't Run the code, but:
while the Form is open and Text5 has the value in it,
run the query:
In the query, in the lngPatientId field, place the following in the Criteria:
Forms![Your Form Name]![Text5]

This should give you the right Records.


jaffer

I wasn't linking to a query. Would that be best?

Come back Enter Parameter Value

Now I am lost :o(

When I try and add to the criteria Forms![frptMHCIntakeAll]![Text5] I get the error DataType error in expression

is your Form Open and Text5 contains a value?

Yes it has a number

and does the query show the correct Records now?

Nope is just keeps saying one of your parameters is invalid

can you zip your mdb and email it to me to llok at,
you will find my email in my profile, click on my name to see it.

I wish I could but I cannot.

Could you just save the part that is causing the problem and bits of the Table, save them as mdb and send them.
The problem is that I cannot understand fully what is the problem.
OR
I can put together a sample mdb and send it to you, but you will have to tell me the structure of your adp and some data, so that I can mimic it and put it together.

I don't think the problem is in solving the problem, but I think it is in the communication/explaination.

jaffer

Is the name of your textbox, [Text5] or [Textbox 5]?  You must reference the correct name of your textbox in the criteria jaffer gave you.

Can you post the *actual* SQL that you are using?

Here is the code being called.  This code will work if I want to call by txtIntakeId  but I want to call by lngPatientId.  thanks

 Case Is = 16
        If frmMainsubBtm.SourceObject = "frmMainsubIntakeMH" Then       'print all Intakes for MHC client
            DoCmd.RunCommand acCmdSaveRecord
            Set cnn = CurrentProject.Connection
            Set r = New ADODB.Recordset
            r.Open "tblMHCIntake", cnn, adOpenForwardOnly, adLockReadOnly
            r.Filter = "lngPatientId=" & lngPatientId
            If r.EOF = False Then
                lngId = Forms!frmMain!txtIntakeId
                Do Until r.EOF
                    Forms!frmMain!txtIntakeId = r.Fields("lngIntakeId")
                    DoCmd.OutputTo acOutputReport, "rptMHCIntakeAll", acFormatSNP, strPath & lngPatientId & "-" & txtIntakeId & "-MHC_Intake.snp", False
                    DoCmd.OpenReport "rptMHCIntakeAll", acViewNormal
                    r.MoveNext
                    Forms!frmMain!txtIntakeId = lngId
                Loop
            Else
                MsgBox "Sorry, no client contacts to print.", vbInformation, "No Data..."
            End If

            r.Close
            cnn.Close
            Set r = Nothing
            Set cnn = Nothing
        End If

and here is the sql query
SELECT tblMHCIntake.*, qselApplication.strPatientId, qselApplication.lngHouseId, qselApplication.strLName,
                      qselApplication.strFName, qselApplication.strMName, qselApplication.strGeneration, qselApplication.strWPhone,
                      qselApplication.strOPhone, qselApplication.strGender, qselApplication.dtmDOB, qselApplication.intAge,
                      qselApplication.strSSNum, qselApplication.bytEligibility, qselApplication.dtmEligExpires, qselApplication.strAddress,
                      qselApplication.strCity, qselApplication.strState, qselApplication.strZip, qselApplication.strMAddress,
                      qselApplication.strMCity, qselApplication.strMState, qselApplication.strMZip, qselApplication.strHPhone,
                      qselApplication.intCat, qselApplication.intHousehold, qselApplication.bytHomeless, qselApplication.strHomeless,
                      qselApplication.ysnHomeCall0, qselApplication.ysnWorkCall0, qselApplication.ysnOtherCall0,
                      qselApplication.ysnHomeMsg0, qselApplication.ysnWorkMsg0, qselApplication.ysnOtherMsg0
FROM      qselApplication INNER JOIN
                      tblMHCIntake ON qselApplication.lngPatientId = tblMHCIntake.lngPatientId
WHERE  (qselApplication.lngPatientId = ?)

So you have a ? in your query? If so, when you replace the ? character with the Forms!MyForm!MyTextbox you get an error message?

I get error in where clause near ! unable to parse query text.

While thanking you for the points and the grade, running32

Can you please tell us what happened that my older comment is suddenly working for you?


jaffer

Actually it was a combination of all your comments and me poking around in the report.  I should have put  Forms![frptMHCIntakeAll]![Text5]  in the properties of the report along with in the sql statement.  I was missing that one step.

Thanks for your help!