Combo Box creating multiple unwanted pages on a report

[chapmajm]

Hi and thanks for your interest,
I'm creating a database to produce transcriptions in a medical office instead of having to go through a transcription service.

I have a main form with multiple subforms(4) and tabcontrols(2). The subform I use to enter medications is based on a control box listing medications from a "medications" table. It's a list that you type the first few letters of the medication and the medication pops up without having to type the complete name. I have a report that is actually like a letter, so the report prints only for the record on the form. I have 2 questions:

1) Even though the report shows only the record on the current form, it shows multiple pages because of the medications. 10 medications = 10 duplicate pages. How do I fix that?

2) The subform containing patient's medications, "pt to med details" is also on a tab control. The second tab is to add the medications to the "medications" table, if it's not on the list. It works, but only updates the list if I exit the form, then re-enter. How do I make it so it updates automatically without having to leave the form?

Thanks in advance for your genius replies.

 

[llkhoutx]

(1) The medication list needs to be a sub-report of your letter with the sub-report sized for one row and with its Can Grow property set to Yes.

(2) Requery the list when a new med is added.

 

[chapmajm]

Thanks for your help, but I tried a subreport and the records don't show up on the report. Even though the records show up on the report if I run the subreport alone.

I also tried the requery thing, but it didn't work either. Ugh, this is hard. I've tried help, but that's hard because I don't know the correct verbiage to use... terms for things, etc. I'm sure I'll figure it out though... I always do somehow.

Any other suggestions out there?

 

[llkhoutx]

You're doing something wrong.

There has to be an index in the main form related to one in the subform, whether on to one or one to many. The main form index field can be used as a filter of the subform's recordsource.

Alternatively, set the subform's Master Link property to the main form index and the subform's Child Link property to the related subform recordset index.

Alternatively still on the mainform index AfterUpdate Event (or the Main form's OnCurrentEvent) set the subform Filter property to
"[SubformIndexName]=" & forms!MainFormName!FormIndexName
and set the subforms FilterOn property to True
forms!MainformNam!SubFormName.FilterOn = True

After each of the above execute
Forms!MainformNam!SubFormName.requery

Maybe you're requerying doesn't match the above or you're requerying at the wrong time.

 

[chapmajm]

Ok. I'm going to give it a shot! Thanks. I'll let you know how it turns out.

-Jenny

 

[chapmajm]

Oh my gosh. I'm so frustrated with this now. I'm going call the geek squad. I believe my relationships have errors. Your recommendations were way over my head. Thanks for all your help.

 

[chapmajm]

My Solution

If anybody in the future ever has this problem, here is the answer.

Private Sub Command90_Click()
On Error GoTo Err_ConsultReport_Click

Dim Counter As Integer
Counter = 0

DoCmd.SetWarnings False

DoCmd.OpenQuery "que-DeleteRecordsFrom-TempConsultInfoTable", acViewNormal
DoCmd.OpenQuery "que-ConsultReport-B", acViewNormal

'Making Medications Table for my Recordset code
DoCmd.OpenQuery "que-PatientMedications-Report", acViewNormal

Dim db As DAO.Database
Dim rs3 As DAO.Recordset

Dim Medications As String


Set db = CurrentDb
Set rs3 = db.OpenRecordset("tbl-Temp-PatientMedications-ConsultReport")


With rs3
    Do Until .EOF
        
        Counter = Counter + 1
        If Counter <> 1 Then Medications = Medications & ", " & rs3![MedicationDescription]
        If Counter = 1 Then Medications = rs3![MedicationDescription]
        
        .MoveNext
        
    Loop
    
End With

DoCmd.OpenForm "frm-Temp-MedicationConcatenationForm", acNormal
Forms![frm-Temp-MedicationConcatenationForm]![MedicationBox].Value = Medications
DoCmd.OpenQuery "que-Update-MedicationsOnTempConsultTable", acViewNormal
DoCmd.Close acForm, "frm-Temp-MedicationConcatenationForm", acSaveNo
DoCmd.SetWarnings True

Dim stDocName As String

    stDocName = "que-ConsultReportForUse"
    DoCmd.OpenReport stDocName, acPreview, "", "[VisitID]=[Forms]![frm-VisitInformation]![VisitID]"
   
Exit_ConsultReport_Click:
    Exit Sub

Err_ConsultReport_Click:
    MsgBox Err.Description
    Resume Exit_ConsultReport_Click
    

End Sub