Form w/combo box output to report

[thesven]

I have been at this all week now and I need some help if possible: (using Access 2010 and not a web database)
I have a form that I dropped a combo box on, I need it to display two columns as choices in the drop down, the Week End date which is defined within my table and the Client Contact which is also defined.
I utilized the Form Query Builder for the combo box and stated to show the two columns in ascending order and placed the criteria of the Client Contact as "Is Not Null" as I only want to display those records that contain data in this column as a choice.
If I run the query within the form it displays all records that contain a Client Contact.
Here is where my problems begin: (maybe they began prior to this and I just don't know it)
At this point I go back to the form and need to run a report I have built to only display these records but actually I want the report to only populate the selected record from the combo box within the form.
What is the process I need to follow to get this working? Can I call/reference the query built within the form somehow or do I need to call a different query or can I call the report to be created from the combo box selection directly?
Your help is greatly appreciated.

 

[vbaInet]

Have a read:

http://www.baldyweb.com/wherecondition.htm

The same process applies for opening a report.

 

[thesven]

Thanks for the point in the baldy direction. My struggle begins I suppose as I have zero knowledge of VBA. Maybe I can find some samples...

 

[vbaInet]

So tell me what you've tried and we can go from there.

The code should go in the After Update event (i.e. Code Builder) of the combo box and you substitute OpenForm for OpenReport.

 

[thesven]

Okay here is what I have:

On the form:
Unbound Combox = Combo84
Row Source = SELECT TimeSheets.[Week Ending], TimeSheets.[Client Contact]
FROM TimeSheets
WHERE (((TimeSheets.[Client Contact]) Is Not Null))
ORDER BY TimeSheets.[Week Ending], TimeSheets.[Client Contact];

Command Button83 = generate print preview of report
OnClick = Event Procedure
Private Sub Command83_Click()
Dim stDocName As String
stDocName = "Client Name TimeSheet"
DoCmd.OpenReport stDocName, acPreview, , "Client Contact='" & Me.Combo84.Column(5) & "'"

End Sub


Client Name TS Query Criteria =
WHERE (((TimeSheets.[Client Contact])=[Forms]![ClientSearchForm]![Combo84]) AND (([TimeSheets]![Approval Status])="Approved"));

Client Name Timesheet report record source =
WHERE (((TimeSheets.[Client Contact])=[Forms]![ClientSearchForm]![Combo84]) AND (([TimeSheets]![Approval Status])="Approved"));

I get a blank result set when I run the query as well as a blank report.

 

[thesven]

So tell me what you've tried and we can go from there.

The code should go in the After Update event (i.e. Code Builder) of the combo box and you substitute OpenForm for OpenReport.

If I don't use a command button and place the code in the After Update event I get the same exact results.....a blank record.

 

[vbaInet]

First of all use this:

Private Sub Command83_Click()

    DoCmd.OpenReport "Client Name TimeSheet", acPreview, , "[Client Contact]='" & Me.Combo84.Column(5) & "'"

End Sub

Are you sure Client Contact is in the 5th column? Columns are zero based so 5 is referring to the 6th column. If you want the 5th column, you should use 4.

 

[thesven]

Thanks for the update!

I tried columns 4, 5, 6 but still blank results.

In the ClientSearchForm I have the Query Builder on the unbolund Combo84 set to:
SELECT TimeSheets.[Week Ending], TimeSheets.[Client Contact]
FROM TimeSheets
WHERE (((TimeSheets.[Client Contact]) Is Not Null))
ORDER BY TimeSheets.[Week Ending], TimeSheets.[Client Contact];

And I do get a result set listing the records with a week ending date and a Client Contact (in a datasheet view) and I also get the combox to display only those records with a Client Name (not showing a week ending date though which I have to address at some point) , so I assume this portion is working.

I have the Command Button (Command83) set to"
Private Sub Command83_Click()
DoCmd.OpenReport "Client Name TimeSheet", acPreview, , "[Client Contact]='" & Me.Combo84 & "'"
End Sub

and also tried it with multiple columns with no results showing.

Here is the entire SQL set for the Client Name TS Query:

SELECT TimeSheets.[Week Ending], TimeSheets.[Employee Name], TimeSheets.[Team Selection], TimeSheets.Client, TimeSheets.[Client Contact], TimeSheets.Billing, TimeSheets.[Number of On Duty Days], TimeSheets.[Number of Per Diem Days], TimeSheets.[Number of Cell Phone Days], TimeSheets.[Number of Camera Days], TimeSheets.[Number of ATV Days], TimeSheets.[Number of Computer Days], TimeSheets.[Weekly Summary], TimeSheets.[Sun - WO#], TimeSheets.[Sunday Hours], TimeSheets.[Sunday OT], TimeSheets.[Mileage - Sunday], TimeSheets.[Sun - To/From], TimeSheets.[Mon - WO#], TimeSheets.[Monday Hours], TimeSheets.[Monday OT], TimeSheets.[Mileage - Monday], TimeSheets.[Mon - To/From], TimeSheets.[Tues - WO#], TimeSheets.[Tuesday Hours], TimeSheets.[Tuesday OT], TimeSheets.[Mileage - Tuesday], TimeSheets.[Tues - To/From], TimeSheets.[Wed - WO#], TimeSheets.[Wednesday Hours], TimeSheets.[Wednesday OT], TimeSheets.[Mileage - Wednesday], TimeSheets.[Wed - To/From], TimeSheets.[Thurs - WO#], TimeSheets.[Thursday Hours], TimeSheets.[Thursday OT], TimeSheets.[Mileage - Thursday], TimeSheets.[Thurs - To/From], TimeSheets.[Fri - WO#], TimeSheets.[Friday Hours], TimeSheets.[Friday OT], TimeSheets.[Mileage - Friday], TimeSheets.[Fri - To/From], TimeSheets.[Sat - WO#], TimeSheets.[Saturday Hours], TimeSheets.[Saturday OT], TimeSheets.[Mileage - Saturday], TimeSheets.[Sat - To/From], TimeSheets.[Weekly Mileage Total], TimeSheets.[Total Hours Worked], TimeSheets.[Total OT], TimeSheets.[Approver Comments], TimeSheets.[Approval Status]
FROM TimeSheets
WHERE (((TimeSheets.[Client Contact])=[Forms]![ClientSearchForm]![Combo84]) AND (([TimeSheets]![Approval Status])="Approved"));


The Client Name Timesheet report points to the Client Name TS Query.

I'm dumbfounded...........

 

[thesven]

As a test I have switched to a List box and I am now able to see the Week End Date and the Client Contact as one selection in the List Box.

My issue of not being able to display the individual record still exists though.

 

[vbaInet]

Upload a stripped down version of your db and tell me the form and report you're working on.

 

[thesven]

I can appreciate your request however the data is live on the table and confidential.

One caveat here is that the form/report/query is running against a SharePoint 2007 list which is being treated like an Access table. The list resides on the server and I am just making calls to the list.

So the when I'm done I will create an accde file to run on the desktop. Thsi is not making use of a web databse or Access Services.

 

[vbaInet]

I'm not asking you for your confidential data. If you want to get help with this you can create a sample db with the relevant table(s), query and report and some bogus data for testing.