#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2012
    Posts
    41
    Rep Power
    3

    Question VBA code not working


    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:

    Code:
    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
  2. #2
  3. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,620
    Rep Power
    650
    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.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2012
    Posts
    41
    Rep Power
    3
    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:
    Code:
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2012
    Posts
    41
    Rep Power
    3
    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.
  8. #5
  9. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,620
    Rep Power
    650
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2012
    Posts
    41
    Rep Power
    3
    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.

Similar Threads

  1. ASP code not working
    By rgopinathan in forum ASP Development
    Replies: 1
    Last Post: February 9th, 2008, 02:37 PM
  2. Why isn't this code working?
    By AdamsKelly in forum Microsoft Access Help
    Replies: 1
    Last Post: June 6th, 2007, 03:23 PM
  3. ASP Code Not Working
    By Big Quinny in forum ASP Development
    Replies: 5
    Last Post: April 4th, 2005, 05:29 AM
  4. ASP code working in IE not working in Netscape and Mozilla
    By nebujacob2000 in forum HTML, JavaScript And CSS Help
    Replies: 2
    Last Post: January 18th, 2005, 10:56 AM
  5. Replies: 1
    Last Post: April 11th, 2000, 03:24 AM

IMN logo majestic logo threadwatch logo seochat tools logo