Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Put the date into the record source query so that it is calculated prior to coming in to the form.

  2. #17
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    WOO HOO!!!!

    Thanks so much for all your help. This is awesome

  3. #18
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by ranman256 View Post
    build a query. It doesnt get the syntax wrong.
    then you can either keep the query or copy out the SQL,
    you want an outer join, and show all REPs, and under accounts , set the criteria: acctname is null.
    (this says show all accounts that are null, not having contacted.)

    trying to go a different way that before. I have a query that works and returns the uncontacted accounts by rep but when I try and use the sql i still get a syntax error.

    The query sql is
    Code:
    SELECT tblMain.*FROM tblMain LEFT JOIN tblActivities ON tblMain.Account = tblActivities.Account
    WHERE (((tblActivities.[Activity Create Date]) Is Null))
    AND tblMain.Employee_Number = Forms!Logon_Form.cboEmployee;
    and the on click event of the button is
    Code:
    Private Sub Command395_Click()
    DoCmd.GoToControl
       DoCmd.ApplyFilter , "SELECT tblMain.* FROM tblMain " & _
                    "LEFT JOIN tblActivities ON tblMain.Account = tblActivities.Account " & _
                    "WHERE (tblActivities.[Activity Create Date] IS NULL) " & _
                    "AND tblMain.Employee_Number = Forms!Logon_Form.cboEmployee;"
    End Sub
    Is there somethign I can do to make this work from the sql?

    Thanks

  4. #19
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think your problem is the same as here https://www.accessforums.net/showthread.php?t=74705 where I posed a solution in my posts 4, 5, 7 if I got that right. You might benefit from reading more of it though. You just can't dump sql into vba when the sql references things like form controls.

  5. #20
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Micron View Post
    I think your problem is the same as here https://www.accessforums.net/showthread.php?t=74705 where I posed a solution in my posts 4, 5, 7 if I got that right. You might benefit from reading more of it though. You just can't dump sql into vba when the sql references things like form controls.
    That was a lot of very cool information.

    So If I understood it this is what my onclick now looks like
    Code:
    Private Sub Command395_Click()Dim sql As String
       sql = "SELECT tblMain.* FROM tblMain LEFT JOIN "
       sql = sql & "tblActivities ON tblMain.Account = tblActivities.Account "
       sql = sql & "WHERE (tblActivities.[Activity Create Date] IS NULL) "
       sql = sql & "AND tblMain.Employee_Number = "
       sql = sql & [Forms]![Logon_Form]![cboEmployee] & ";"
       
    DoCmd.ApplyFilter , sql
            
    End Sub
    I do get an error and it looks like it is adding something to the end of the string that I can't seem to locate where it is coming from. The query itself look s good though in terms of it bringing in the right employee number

    This is the error
    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	26.5 KB 
ID:	36419

    Thank you for your time

  6. #21
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    While I'm no mathematician, I'd say employee number cannot be numeric if it starts with e as even numbers expressed in E notation would not start with e. Therefore, employee ID number is actually text? Then you missed the part where the posts said numbers are not delimited but dates and strings are (strings being text, including numbers that look like numbers but are in fact text).
    Try
    Code:
    ...
       sql = sql & "AND tblMain.Employee_Number = '"
       sql = sql & [Forms]![Logon_Form].[cboEmployee] & "';"
    Oddly enough, vba sql doesn't need the semicolon at the end but no harm in including it. Also, personally I avoid using ! between form and control. When dealing with such code behind a form or report (i.e. not in a standard module) if the control is named Something2 and you write Forms!frmLogon!Something3 then the error won't be caught when compiled (e.g. when you compile from the vbe toolbar). The error will raise it's head at run time, and only if the offending part is evaluated. If the error is in a standard module, compiling won't catch it at all, so IMHO, the best approach is to avoid using it altogether in that position. For more info on the ! bang operator, here's something I have bookmarked for frequent reference https://stackoverflow.com/questions/2923957/bang-notation-and-dot-notation-in-vba-and-ms-access

    EDIT
    is your employee combo bound? That is, does the control have a record source or just a row source?

  7. #22
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Micron View Post
    While I'm no mathematician, I'd say employee number cannot be numeric if it starts with e as even numbers expressed in E notation would not start with e. Therefore, employee ID number is actually text? Then you missed the part where the posts said numbers are not delimited but dates and strings are (strings being text, including numbers that look like numbers but are in fact text).
    Try
    Code:
    ...
       sql = sql & "AND tblMain.Employee_Number = '"
       sql = sql & [Forms]![Logon_Form].[cboEmployee] & "';"
    Oddly enough, vba sql doesn't need the semicolon at the end but no harm in including it. Also, personally I avoid using ! between form and control. When dealing with such code behind a form or report (i.e. not in a standard module) if the control is named Something2 and you write Forms!frmLogon!Something3 then the error won't be caught when compiled (e.g. when you compile from the vbe toolbar). The error will raise it's head at run time, and only if the offending part is evaluated. If the error is in a standard module, compiling won't catch it at all, so IMHO, the best approach is to avoid using it altogether in that position. For more info on the ! bang operator, here's something I have bookmarked for frequent reference https://stackoverflow.com/questions/2923957/bang-notation-and-dot-notation-in-vba-and-ms-access

    EDIT
    is your employee combo bound? That is, does the control have a record source or just a row source?
    The employee field on the login form is an unbound field (cboemployee) and the employee field on the frmMain is tied to control source Employee_Number in tblMain.
    I tried the change you made and still get the same syntax error even though the resulting query looks fine to me.
    Also if you are not teaching this stuff you should be - very good at explaining - just helps if the leaner can read
    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	26.9 KB 
ID:	36430

  8. #23
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by mindbender View Post
    The employee field on the login form is an unbound field (cboemployee) and the employee field on the frmMain is tied to control source Employee_Number in tblMain.
    I tried the change you made and still get the same syntax error even though the resulting query looks fine to me.
    Also if you are not teaching this stuff you should be - very good at explaining - just helps if the leaner can read
    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	26.9 KB 
ID:	36430
    If I debug and copy the sql statement from the immediate window and run it it returns exactly what it should and it highlights DoCmd.ApplyFilter , sql as the issue. I think the query is ok but the applying is not...make any sense?

  9. #24
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    then the 3075 error is no longer but code falters on the apply filter line?
    Neither the apply filter method nor the form filter property can use a full sql as a filter. Sorry I didn't notice that was to be the end result, having focused on the lack of delimiters.
    Here's info about each subject - not sure if you could set your form recordsource to the sql as an alternative because it's not clear which form this code is on.

    https://docs.microsoft.com/en-us/off...lter(property)

    https://docs.microsoft.com/en-us/off...md.applyfilter
    You might notice that the parent pages for these links are for the entire Access vba object model. Should be your bible going forward.

    It appears the main form needs the employee data from the logon form, which means the logon form must be open (can be hidden) otherwise it will error. I wouldn't expect the error you posted if it's not; rather one about missing parameter or an object (the form) that cannot be found.

    Thanks for the compliment. I used to teach basic photography in continuing education at the local college and took a lot of satisfaction from doing so. Oddly enough, they weren't too receptive when I suggested they supplement their Access courses with vba as they had none of that at all.

  10. #25
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Micron View Post
    then the 3075 error is no longer but code falters on the apply filter line?
    Neither the apply filter method nor the form filter property can use a full sql as a filter. Sorry I didn't notice that was to be the end result, having focused on the lack of delimiters.
    Here's info about each subject - not sure if you could set your form recordsource to the sql as an alternative because it's not clear which form this code is on.

    https://docs.microsoft.com/en-us/off...lter(property)

    https://docs.microsoft.com/en-us/off...md.applyfilter
    You might notice that the parent pages for these links are for the entire Access vba object model. Should be your bible going forward.

    It appears the main form needs the employee data from the logon form, which means the logon form must be open (can be hidden) otherwise it will error. I wouldn't expect the error you posted if it's not; rather one about missing parameter or an object (the form) that cannot be found.

    Thanks for the compliment. I used to teach basic photography in continuing education at the local college and took a lot of satisfaction from doing so. Oddly enough, they weren't too receptive when I suggested they supplement their Access courses with vba as they had none of that at all.

    No sorry for confusion - the 3075 error still happens bu the applyfilter is what is highlighted in yellow when I debug. I will take a look at the links you sent and see how I make out.

  11. #26
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, I'm getting lost here, between this one getting long and being involved in other places as well. I think you're saying if you output the sql to the immediate window, copy/paste into a new query (and the necessary form is open) that the query runs fine. Then the 32075 error doesn't make sense unless:
    I tried the change you made
    Visuals speak louder - suggest you post what you tried or we have to assume your implementation is correct.

    Regardless, you can't use that sql as a filter anyway. Not sure why you don't just run the query from code if it works anyway as long as you're OK with having a form control reference in it. Some don't do that; I don't have too much of an issue with it as long as steps are taken to ensure a) that form is open and b) the referenced controls have data in them.
    but the applyfilter is what is highlighted in yellow when I debug
    Pretty sure that's because you can't do what you're trying to do, per the links.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #27
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Micron View Post
    Well, I'm getting lost here, between this one getting long and being involved in other places as well. I think you're saying if you output the sql to the immediate window, copy/paste into a new query (and the necessary form is open) that the query runs fine. Then the 32075 error doesn't make sense unless: Visuals speak louder - suggest you post what you tried or we have to assume your implementation is correct.

    Regardless, you can't use that sql as a filter anyway. Not sure why you don't just run the query from code if it works anyway as long as you're OK with having a form control reference in it. Some don't do that; I don't have too much of an issue with it as long as steps are taken to ensure a) that form is open and b) the referenced controls have data in them.
    Pretty sure that's because you can't do what you're trying to do, per the links.

    I have the button click running the query to return null modified dates with the form control reference in it. When I run the query by itself it works, when I run it from the button it does nothing...OpenQuery query name datasheet view. How would you do this type of thing it it was your database. Seems like a fairly simple idea to pull back uncontacted records but seems difficult to implement.

  13. #28
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have the button click running the query
    I don't see that. I see a button click procedure to run sql. The query object and sql in code are not the same thing. If it were my db, I'd be OK doing it either way, so there has to be something about all the advice given that you're not understanding and/or implementing.
    I think it's time for you to post a zipped copy of the db along with info on what to do to replicate the issue.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 02-24-2015, 11:19 AM
  2. Filter form records with a combo box
    By Exmark1 in forum Programming
    Replies: 5
    Last Post: 02-09-2015, 07:18 PM
  3. Replies: 3
    Last Post: 11-27-2012, 07:20 AM
  4. Filter specific records on sub form
    By foxtet in forum Forms
    Replies: 5
    Last Post: 06-05-2011, 12:06 PM
  5. Filter Form records with Combo Box????
    By jgelpi in forum Forms
    Replies: 0
    Last Post: 05-19-2009, 07:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums