open report with current record in forms

[kotechaprashant]

See attached and you will see how it was done. I think I actually covered this in my first post.

I would mention two things though:

1. Don't name your objects with the same name even though they different objects. The search query, form and report you had problems with had the same name.

2. Have a good naming convention. Here's a link to help guide you:

Lastly, your report was corrupt so I created a new one and added only the controls to the detail section so you will need to do the other bits.

VBinet thanks for the great help. I learnt a lot from the changes made by you in the database. I remain evergreatful for this help. I am not a tech person so could not understand what you and jhon mentioned in earlier post. But now i understood. Thanks to both of you.

 

[vbaInet]

VBinet thanks for the great help. I learnt a lot from the changes made by you in the database. I remain evergreatful for this help. I am not a tech person so could not understand what you and jhon mentioned in earlier post. But now i understood. Thanks to both of you.

Glad we could help!

 

[neerjakhattar]

I did the same thing in my project..kepp all the query on back of the form means on click button and put a where clause with cases and in query put where = & that string name.

make this query as public and paas this query as a recordsource on open event of report.

it worked for me perfectly.

I am showing u the example also how I did it

If IsNull(Me.Type) = False Or IsNull(Me.Status) = False Or IsNull(Me.Combo29) = False Or IsNull(Me.Combo27) = False Or IsNull(Me.Combo35) = False Or IsNull(Me.Combo31) = False Or IsNull(Me.Combo33) = False Or IsNull(Me.Text23) = False And IsNull(Me.Text25) Then

DoCmd.RepaintObject
If IsNull(Me.Type) = False Then
If Len(WhereName) > 0 Then

WhereName = WhereName + " and "
End If
WhereName = WhereName + " action_items.type = '" & Me.Type & "' "
End If

If IsNull(Me.Status) = False Then
If Len(WhereName) > 0 Then
WhereName = WhereName + " and "
End If
WhereName = WhereName + "action_items.schedule_or_not = '" & Me.Status & "'"
End If
If IsNull(Me.Combo29) = False Then
If Len(WhereName) > 0 Then
WhereName = WhereName + " and "
End If
WhereName = WhereName + "action_items_site.site_name =" + Me.Combo29 + " "
End If
If IsNull(Me.Combo27) = False Then
If Len(WhereName) > 0 Then
WhereName = WhereName + " and "
End If
WhereName = WhereName + "physician_action_id =" + Me.Combo27 + " "
End If
If IsNull(Me.Combo35) = False Then
If Len(WhereName) > 0 Then
WhereName = WhereName + " and "
End If
WhereName = WhereName + "action_items_site_personnel.site_personnel_name = " + Me.Combo35 + " "
End If
If IsNull(Me.Combo31) = False Then
If Len(WhereName) > 0 Then
WhereName = WhereName + " and "
End If
WhereName = WhereName + "action_items_mds.md_name_id = " + Me.Combo31 + ""
End If
If IsNull(Me.Combo33) = False Then
If Len(WhereName) > 0 Then
WhereName = WhereName + " and "
End If
WhereName = WhereName + "action_items_employees.emp_name_id = " + Me.Combo33 + " "
End If
If IsNull(Me.Text23) = False And IsNull(Me.Text25) = False Then
If Len(WhereName) > 0 Then
WhereName = WhereName + "and "
End If
WhereName = WhereName + "action_items.scheduled_date >=#" & Me.Text23 & "# and action_items.scheduled_date <=#" & Me.Text25 & "#"
End If
If Me.Check74 = -1 Then
Me.week.Enabled = False
Me.month.Enabled = False
Me.Text23.Enabled = False
Me.Text25.Enabled = False
If Len(WhereName) > 0 Then
WhereName = WhereName + " and "
End If
WhereName = WhereName + "action_items.scheduled_date = #" & Now & "# "
End If
If Me.month = -1 Then
If Len(WhereName) > 0 Then
WhereName = WhereName + " and "
End If
WhereName = WhereName + "Month(action_items.scheduled_date) = " & Format(Now, "MM") & _
" and Year(action_items.scheduled_date)= " & Format(Now, "YYYY")
End If
If Me.week = -1 Then
If Len(WhereName) > 0 Then
WhereName = WhereName + " and "
End If
WhereName = WhereName + "Weekday(action_items.scheduled_date) = " & Format(Now, "WW") & _
"and Year(action_items.scheduled_date)=" & Format(Now, "YYYY")
End If


End If


' error handler
' For getting records by group by. (Having a Check box button with all field whatever checkbox people will click the result will be grouped by that criteria)
If Me.Check45 = -1 Then
GroupBy = "action_items.type AS Grp_By"
ElseIf Me.Check43 = -1 Then
GroupBy = "action_items.schedule_or_not AS Grp_By"
ElseIf Me.Check51 = -1 Then
GroupBy = "Institution_list.Institution AS Grp_By"
ElseIf Me.Check47 = -1 Then
GroupBy = "IIf(personalgeneral.[first name]<>'',[first name],'')+IIf(personalgeneral.[middle name]<>'',' '+[middle name],'')+IIf(personalgeneral.[last name]<>'',' '+[last name],'') AS Grp_By"
ElseIf Me.Check59 = -1 Then
GroupBy = "IIf(Institution_contacts.[INSTCNT_First_Name]<>'',[INSTCNT_First_Name],'')+IIf(Institution_contacts.[INSTCNT_Middle_Name]<>'',' '+[INSTCNT_Middle_Name],'')+IIf(Institution_contacts.[INSTCNT_Last_Name]<>'',' '+[INSTCNT_Last_Name],'') as Grp_By"
ElseIf Me.Check49 = -1 Then
GroupBy = "medical_directors_name.mds_name AS Grp_BY"
ElseIf Me.Check54 = -1 Then
GroupBy = "employees_list.employee_name AS Grp_BY"
Else
GroupBy = "action_items.scheduled_date AS Grp_By"
End If


' Query which will print the result on teh report.
If Len(WhereName) > 0 Then
StrSq = "SELECT DISTINCT action_items.action_id, action_items.subject_name, action_items.scheduled_date, action_items.tm1, action_items.tm2, action_items.location, action_items.type, action_items.notes, action_items.schedule_or_not,action_items.timerecord, " & GroupBy & " FROM (((((action_items_site RIGHT JOIN ((((action_items LEFT JOIN action_items_physician ON action_items.action_id = action_items_physician.action_items_id) LEFT JOIN action_items_site_personnel ON action_items.action_id = action_items_site_personnel.action_items_id)" & _
" LEFT JOIN action_items_employees ON action_items.action_id = action_items_employees.action_items_id) LEFT JOIN action_items_mds ON action_items.action_id = action_items_mds.action_items_id) ON action_items_site.action_items_id = action_items.action_id) LEFT JOIN personalgeneral ON action_items_physician.physician_action_id = personalgeneral.PERGEN_ID) LEFT JOIN medical_directors_name ON action_items_mds.md_name_id = medical_directors_name.md_id) LEFT JOIN institution_contacts ON action_items_site_personnel.site_personnel_name = institution_contacts.INSTCNT_ID) LEFT JOIN institution_list ON action_items_site.site_name = institution_list.INST_ID) LEFT JOIN employees_list ON action_items_employees.emp_name_id = employees_list.emp_id where " & WhereName & ";"
Else
StrSq = "SELECT DISTINCT action_items.action_id, action_items.subject_name, action_items.scheduled_date, action_items.tm1, action_items.tm2, action_items.location, action_items.type, action_items.notes, action_items.schedule_or_not,action_items.timerecord, " & GroupBy & " FROM (((((action_items_site RIGHT JOIN ((((action_items LEFT JOIN action_items_physician ON action_items.action_id = action_items_physician.action_items_id) LEFT JOIN action_items_site_personnel ON action_items.action_id = action_items_site_personnel.action_items_id)" & _
" LEFT JOIN action_items_employees ON action_items.action_id = action_items_employees.action_items_id) LEFT JOIN action_items_mds ON action_items.action_id = action_items_mds.action_items_id) ON action_items_site.action_items_id = action_items.action_id) LEFT JOIN personalgeneral ON action_items_physician.physician_action_id = personalgeneral.PERGEN_ID) LEFT JOIN medical_directors_name ON action_items_mds.md_name_id = medical_directors_name.md_id) LEFT JOIN institution_contacts ON action_items_site_personnel.site_personnel_name = institution_contacts.INSTCNT_ID) LEFT JOIN institution_list ON action_items_site.site_name = institution_list.INST_ID) LEFT JOIN employees_list ON action_items_employees.emp_name_id = employees_list.emp_id;"
End If

If Me.Check104 = -1 Then
If Me.Check126 = -1 Then
DoCmd.OpenReport "Action_Items_Report", acViewNormal
DoCmd.Close acForm, "Action_Item_Report_Filter", acSaveNo
Exit Sub
Else
DoCmd.OpenReport "Action_Items_Report", acViewPreview
Exit Sub
End If

 

[neerjakhattar]

Now after all this just make strsq as public string and on open event of report pass this as a recordsource for a report

me.recordsource = form_name.strsq

it really works try it

 

[vbaInet]

Now after all this just make strsq as public string and on open event of report pass this as a recordsource for a report

me.recordsource = form_name.strsq

it really works try it

Thanks for posting your solution.

But let me mention a few things:

1. When you want to post code on the forum, use code tags:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

2. Control names like Me.Text23 isn't very readable. It would help you if you name them something meaningful like txtStatus. Here are some tips:

http://www.acc-technology.com/namconv.htm
http://www.databasedev.co.uk/naming_conv.html

3. The method you presented is very convoluted for this purpose. That method is mostly used for creating a search form.