Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33

    Unanswered: Using form variable in query criteria

    Hi.

    I have an append query that adds new deliveries to tblDeliveries when a button is pressed on frmNewDelivery.
    On Load of frmNewDelivery, I have a function that returns the DriverID. This is stored as an Integer variable by the name iNewDriverID. In my query, all of the data to be appended is displayed in textboxes, combo boxes etc, therefore I have been using "Expr1: Forms.frmNewDelivery.lblBookingDate2.Caption" etc as my 'Field' criteria. However, under tblDeliveries, I have a DriverID field. This is where I would like to store the value of iNewDriverID. Since this is not stored in a textbox but instead as a global variable on the form, I don't know how to reference to this through my query criteria. I cannot simply put "Expr1: Forms.frmNewDelivery.iNewDriverID" as Access doesn't allow it.

    How can I reference to this variable?
    I have a workaround, whereby, you insert the variable into an invisible textbox, but I am very clinical as a designer and like to create 'clean' forms

    Any input much appreciated!

    Many thanks,
    Tom

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I suggest assigning it to a non-visible textbox and use that in you query designer, like all the others.

    HTH


    MTB

  3. #3
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    I have a workaround, whereby, you insert the variable into an invisible textbox, but I am very clinical as a designer and like to create 'clean' forms
    Any other suggestions??

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Sorry Tom, I was a bit quick on the trigger, should read ALL the post first.

    I do think you are being overly fussy though.

    The only other suggestion I can come up with, which you will probably like less, is to build your append query as an SQL string in code and use

    CurrentDB.Excute SQL or whatever

    Any good??


    MTB

  5. #5
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    Yea, had that originally and then decided to extract all my queries from my code and build them in design view so it was easier to find.

    Will probably just resort to the invisible textbox idea as an exception.

    Thanks for your help anyway!

    Tom.

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    create a table called global that has 2 fields Name as the PK and Value.

    then use an update query to change the value then reference the table
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by stibily
    I cannot simply put "Expr1: Forms.frmNewDelivery.iNewDriverID" as Access doesn't allow it.
    I would imagine it does if you create a new property for your form and return the value through that. Not worth the effort though IMO - dump it in a textbox.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I imagined wrong....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    Lol, Ok. Thank you all for your thoughts!
    Looks like text box is the way to go

Posting Permissions

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