Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Form to open based on current record of current form

    Im trying to open a form based on another forms current record. The forms are running off of two queries (a detail query and a summary query). I need the summary form to open the detail records. I've tried the following code and its not working, it brings a pop up box that says NotDemand and asks me to enter criteria.

    The TaskGenerationNbr is a field on both forms that will show either "NotDemand" or a number such as "R000000000001"

    Code:
    DoCmd.OpenForm "frmCurrentPlans", , , "[TaskGenerationNbr]= " & Me![TaskGenerationNbr]

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are getting a message requesting you enter a parameter then
    either
    there is no value in TaskGenerationNbr
    or
    more likely there is no variable, control or column called TaskGenerationNbr in the current context

    it could also be because if the TaskGenerationNbr column is a string/text then it shoudl be delimited with a pair of " or '

    of course if you put a breakpoint on the code and examined the values of your statement you'f remove most of the guesswork
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    How do i test the code.

    I can get docmd.openform "frmCurrentPlans" to work no problem. How do i test the where command?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    test?
    well either it works or it doesn't, I dont' see how you can test it outside UNLESS you add the where clause to a select.

    but you can find out what you are actually sending to the SQL engine by debugging the code. prove that you are sending what you think you are tot eh SQL engine. If you haven't used the debugger then now si a very. very (and I do mean very) good time to start learning how to use it.

    the basic premise is very simple prove your code is doing what you think it is
    then if it is AND it still isn't working then work out why the way you envisaged the solution isn't right

    Access has a very powerful in line debugger. its one of the great things about Access that makes it stand out miles above any comaprable file server DB I've seen. its inherited from VB6 and in my books anyone using code in an Access project needs to understand how to debug
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    test?
    well either it works or it doesn't, I dont' see how you can test it outside UNLESS you add the where clause to a select.

    but you can find out what you are actually sending to the SQL engine by debugging the code. prove that you are sending what you think you are tot eh SQL engine. If you haven't used the debugger then now si a very. very (and I do mean very) good time to start learning how to use it.

    the basic premise is very simple prove your code is doing what you think it is
    then if it is AND it still isn't working then work out why the way you envisaged the solution isn't right

    Access has a very powerful in line debugger. its one of the great things about Access that makes it stand out miles above any comaprable file server DB I've seen. its inherited from VB6 and in my books anyone using code in an Access project needs to understand how to debug
    Completely understand. My code is one line right, docmd.openform form name with where clause. Im using the immediate window and docmd.openform "frmcurrentplans" works without any issue. When i put the full code in with the where command I get the same error as if i just hit the button. So, I want to dissect it. However Im not sure how to test the where clause in the docmd function.

    The original code is telling FormA to open FormB where generationNbr on form B = GenerationNbr on form A. I've made sure that both queries who drive these forms have the generationnbr spelled the same way. Both queries show results with the generationnbr which tells me theres an issue with this part of the code ""[TaskGenerationNbr]= " & Me![TaskGenerationNbr].

    I try DoCmd.OpenForm "frmCurrentPlans", ,,"TaskGenerationNbr = notdemand" and it asks me for a criteria for notdemand. How would i try this as a string.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If the column being inserted, compared is string / text then delimit even if the value is numeric. Delimit with ' or ", doesbt matter which, although the SQL STANDARD IS '

    Eg
    where mynumericcolumn = 123.456

    Where mystringcolumn = 'abc'
    Or
    where mystringcolumn ='123.456'

    WHERE mydatecolumn = #2014-10-21#

    Just be aware that when you supply the value from a control or variable you need to frop in and out of vba

    ...."taskgenerationnr = '" & mycontrol & "'"

    Dates should be iso (yyyy-mm-dd) or us (mm/dd/yyyy) formats and delimited with #
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •