Having trouble setting form record source via code 1

[ajaeger]

On my menu form, I have a command button that takes me to a data entry form, frmTime. Via code, I am trying to open up frmTime so it displays one record/row for each day of the current month for the employee who is logged in. The form would look something like:

[In the header]
Employee: Bill Smith
DATE TIME
[In the details]
7/1/2011 2 hours
7/2/2011 .75 hours
7/3/2011 1.5 hours
...
7/31/2011 1.25 hours


I have the code for strSQL working (ie, when I put it directly in a query, it pulls the correct records and I can update the time). However, I'm having trouble getting the record source of the form to update with strSQL. Here's what I had working on Click for the command button on the menu form, but now it's not changing the record source:

strSQL = "SELECT..."

DoCmd.Close

DoCmd.OpenForm "frmTime"

Forms!frmTime.RecordSource = strSQL

In fact, in testing, I created and saved a query, qryEmployeeMonth, with the same code that I used above. Then I set the Record Source of the form to qryEmployeeMonth. Even this didn't open anything in my form. Could I have changed a setting in the form that is prohibiting this from changing?

Thanks!

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[misscrf]

What it looks like you are doing is setting the strSQL on the main form and then trying to pass it as openargs. I would look up openargs if that is what you want to do. Another option is to have the form onopen or onload have this in there:

 Dim Mysql As String
 Dim strQuery As String
 strQuery = "...[default query with no filters]"
 Mysql = "SELECT..."
 CurrentDb.QueryDefs(strQuery).SQL = Mysql
 Me.frmYourFormName.SourceObject = "QUERY." & strQuery

Then you are putting the code in the form it affects.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Is the details a subform?
I would probably try send the strSQL in the OpenArgs to the new form. When the new form opens, you can set the record source based on the openargs value.


Duane
Hook'D on Access
MS Access MVP

 

[ajaeger]

No, this is not a subform. I also have the same process on two command buttons on frmTime. One is cmdPreviousMonth and the other cmdNextMonth. The strSQL is the same, it just pulls the time records for that employee for the previous or next month. So my solution needs to be something I can run and have the records source refresh when the form is open and one of the command buttoms are clicked. I will read up on OpenArgs. Thanks.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[ajaeger]

Will OpenArgs work with a WHERE clause that includes multiple fields? Also, will it work if you need to return multiple records? My WHERE clause is:

WHERE ((tblEmployees.EmployeeID)=1) And (12=Month(tblTimeLog.DateDay)) And (2011=Year(tblTimeLog.DateDay))

This will return a record for this employee for each day in July - ie 31 records.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[TheAceMan1]

How are ya ajaeger . . .

[blue]Will OpenArgs work with a WHERE clause that includes multiple fields? ...[/blue]

OpenArgs is used to pass a string. That is its function. Your SQL statement is a string! Just load the recordsource from the [blue]On Open[/blue] event of frmTime:

[blue]   Me.RecordSource = Me.OpenArgs[/blue]

There's a small problem to take care of if frmTimer is still in its design stage. If you open frmTime independently (say for testing) there's no way to input a string in OpenArgs and the recordsource will be loaded with an empty string ... hence no records show. The following will circumvent this and allow you open independently as well. Replace your previous code (in the frmTimer [blue]On Open[/blue] event) with the following:

[blue]   If Me.OpenArgs = "" Then
      Me.RecordSource = "YourDefaultRecordSourceHere"
   Else
      Me.RecordSource = Me.OpenArgs
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ajaeger]

Thanks AceMan! That makes more sense to me now. Will give that a try.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[Gammachaser]

You don't need to go through all of that.

Make the query (with no WHERE clause) your record source on the form and use:

Docmd.OpenForm "frmTime",,,"((tblEmployees.EmployeeID)=1) And (12=Month(tblTimeLog.DateDay)) And (2011=Year(tblTimeLog.DateDay))"

"WhereCondition" is one of the arguments you can pass directly when you open the form. Eliminate a step and don't pass it as OpenArgs.

That way if you open the form without using the main form, you have records (all of them) and you can apply the filter from the Immediate Window to test your results using

Forms!frmTime.Filter = "((tblEmployees.EmployeeID)=[i]x[/i]) And ([i]xx[/i]=Month(tblTimeLog.DateDay)) And ([i]xxxx[/i]=Year(tblTimeLog.DateDay))"

The nice thing about the immediate window is you only have to type once and then edit to change the results.

BTW -- I have never seen anyone use "12 = Month..." as a parameter before -- it is normally "Month... = 12" but I guess whatever works for you is good.