VBA code not working

[Vaulcul]

Hello,

Up until recently I've only used macros to do the dirty work (I understand that most use VBA)... It seems I'm hitting their limitations now and am in need of assistance in getting VBA code to work.

I have a single report that shows employees and the accounts that they are being paid from for a single pay period. The report is fed information from a form.

I have a pull down menu on the form that contains the numbers 1 through 26... these numbers indicate which pay period is being selected and pushed through to the report. The report is supposed to then pull correct account information for the selected employees (also selected from the form). However, I've not gotten the account information to pull successfully with VBA. When using a macro the first record for the pay periods will get pulled and be reported for all employees (i.e. all employees are reported as being paid from the same account as the first listed employee)... I could create report for each individual pay period... but I'd rather not have 26 reports to maintain.

Here is a snippet of the code that I'm having an issue getting to work:

Private Sub Report_Open(Cancel As Integer)
If (Forms![Front End Navigation Menu]!NavigationSubform.Form!PayPeriodsCombo = 1) Then
            DoCmd.SetProperty "PPData", , DLookup("[pp1]", "rm_ranged_room_q", "PY_Employee_Info_T_Emp_ID =" & "[PY_Employee_Info_T_Emp_ID]")
        ElseIf (Forms![Front End Navigation Menu]!NavigationSubform.Form!PayPeriodsCombo = 2) Then
            DoCmd.SetProperty "PPData", , DLookup("[pp2]", "rm_ranged_room_q", "PY_Employee_Info_T_Emp_ID =" & "[PY_Employee_Info_T_Emp_ID]")
        ElseIf (Forms![Front End Navigation Menu]!NavigationSubform.Form!PayPeriodsCombo = 3) Then
            DoCmd.SetProperty "PPData", , DLookup("[pp3]", "rm_ranged_room_q", "PY_Employee_Info_T_Emp_ID =" & "[PY_Employee_Info_T_Emp_ID]")
ETC. ETC.

End Sub

This code pretty much repeats up to 26.

Thanks for all of your help in advance

[June7]

Don't enclose variables in quote marks. Reference to form field/control is a variable.

"PY_Employee_Info_T_Emp_ID =" & [PY_Employee_Info_T_Emp_ID]

I don't understand use of SetProperty nor the purpose of the DLookup. Please clarify what this is supposed to accomplish.

Do you have multiple similar name fields (pp1, pp2, pp3, etc)? This is an indication of non-normalized data structure.

If you want to open a report filtered to a single pay period for a single employee, this can be done with DoCmd.OpenReport and its WHERE CONDITION argument.

However, if you want selected employees all on one report output, will need some criteria to filter the report's recordset to just those employees. Such as a check field in table.

[Vaulcul]

Hi June7 thanks for your reply.

Don't enclose variables in quote marks. Reference to form field/control is a variable.

I've now removed the quotes around all of the references to field controls. I now get an error message stating that "can't find the field 'PP1' referred to in your expression'. I suppose that's progress since before it would just open the report and and then show all of the employee records with blank information for the pay period field.

I don't understand use of SetProperty nor the purpose of the DLookup. Please clarify what this is supposed to accomplish.

Truthfully... I don't understand the significance of "SetProperty" either... at least in the VBA world... it just seemed like the most appropriate command to emulate what I was doing with macros (since it is similarly named on the macro side). Namely, I had an unbound text control that the value would be set by the Dlookup code... the Dlookup code I provided worked if I had it bound to a single box... but then it would cause me to create 26 reports for this one field. Putting the code into a macro worked... but only for the first record (and then it would repeat the data from the first in every subsequent record .

To better interpret what my macro said I'll give you kind of the break down I had for it:

If  [Forms]![Front End Navigation Menu]![NavigationSubform].[Form]![PayPeriodsCombo]=1  Then
SetProperty
     Control Name    PPData
              Property    Value
                     Value   =DLookUp("pp1","[rm_supervisor_room_q]","Emp_ID =" & "[PY_Employee_Info_T_Emp_ID]")

The Dlookup is supposed to basically give me the account information for a single pay period (as determined by the choice in the drop down menu on the form) per employee. I've gotten this to work with a macro on a form... but for some reason it won't work on a report.... thus I'm converting over to VBA (it had to happen sometime I guess).

Does that help with answering that part of the question?

Do you have multiple similar name fields (pp1, pp2, pp3, etc)? This is an indication of non-normalized data structure.

Yes, the fields go from pp1, pp2, pp3,... all the way to pp26. I understand that there are more normalized ways to handle the data (i.e. a "PP" column and an "Account" column)... But, for the purpose of this database I believe it would create LOTS of duplication of data, as there MUST be an individual entry for each pay period... this would make it so that ALL employees have 26 entries in the database (even if they aren't being paid they have an entry)... rather than 1 or 2... This would increase the amount of work that those responsible for the data entry would have as well...

Thus my decision in how the database functions.

If you want to open a report filtered to a single pay period for a single employee, this can be done with DoCmd.OpenReport and its WHERE CONDITION argument.

However, if you want selected employees all on one report output, will need some criteria to filter the report's recordset to just those employees. Such as a check field in table.

I mentioned earlier that I have a form with a drop down menu that controls the data for a pay period... this form also controls the set of employees listed. This is a report listing the a range of rooms that employees sit in and it includes the accounts that they are being paid from within the report.

Hope that clarifies things for you... I'm always willing to provide more clarification if necessary... as best I can (which may require a bit of patience on your end).

Thanks again.

[Vaulcul]

hmmm... no edit function

I mentioned earlier that I have a form with a drop down menu that controls the data for a pay period... this form also controls the set of employees listed. This is a report listing the a range of rooms that employees sit in and it includes the accounts that they are being paid from within the report.

All of this information is currently pulled from the form in the front end and is working... with the exception of the account info.

[June7]

Yes, result of an expression in textbox ControlSource will be repeated for each record.

What is name of field in table - Emp_ID or PY_Employee_Info_T_Emp_ID - you show both in the DLookup examples.

One DLookup can serve:

DLookUp("pp" & Forms![Front End Navigation Menu]!NavigationSubform.Form!PayPeriodsCombo, "[rm_supervisor_room_q]", "Emp_ID =" & [PY_Employee_Info_T_Emp_ID])

Instead of in code, this could be in the textbox ControlSource. I think the code was intended to set the ControlSource property anyway.

Are you using Navigation type form?

I still can't visualize this structure and the desired behavior. If you want to provide db for analysis, upload to a fileshare site such as box.com and post link. Make copy, remove confidential data, run Compact & Repair, zip.

After member 30 days and 30 posts, will gain privileges like editing post, attaching files.

[Vaulcul]

What is name of field in table - Emp_ID or PY_Employee_Info_T_Emp_ID - you show both in the DLookup examples.

The name of the field is Emp_ID in the table... however this is referring to a query... and the name should be PY_Employee_Info_T_Emp_ID

ne DLookup can serve:

DLookUp("pp" & Forms![Front End Navigation Menu]!NavigationSubform.Form!PayPeriodsCombo, "[rm_supervisor_room_q]", "Emp_ID =" & [PY_Employee_Info_T_Emp_ID])

This worked!!! I feel kinda dumb though... I had done this in another control on the same report to make the report heading give the pay period number.... Hours of frustration, brooding over something that was in front of my nose the whole time.

.... As for analyzing the DB, I don't mind uploading screenshots of the forms right now (w/o sensitive data) if you're still curious I will gladly PM you links to a few... I don't feel comfortable sharing the actual database outside of the business it's intended for... sorry.