Results 1 to 6 of 6
  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151

    using a Query to set a variable in VBA on a form

    I have a field in a table which is not an autonumber. This is usually entered as a consecutive number by the user. I created a Query which shows the Max number from this field and displays on the form so the user knows what the last number used was.

    I wanted to have a button on another form which opened this other form for data entry and would populate this field by adding 1 to the result of this query.

    Is there a way to set a variable to what the result of the query is. I tried by referencing the form

    Dim lt as long

    lt=forms!Fdata_Last!LastNumber

    .... openform

    nmb = lt +1



    However there was an error that it couldn't find the form referenced. It does exist so I am assuming because it is running a query this didn't work.

    Any solution would be appreciated.

    Thanks,

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is the form open at the time this runs? It would have to be. An alternative would be a DLookup() against the query, or a DMax() eliminating the query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you really care about the numbers being consecutive and there can be more than one logged in user, performing the calculation on form opening can result it two concurrent users generating the same result. One could cancel the record creation, causing a gap. If the field is unique, and the first commits the record, the second user will not be able to save their record, and cannot alter the number, thus will have to cancel and start again.
    Suggest you determine the number in a BeforeUpdate event, which should generate the number immediately prior to your making its commitment.

  4. #4
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Thanks for the suggestions. I will try the Dmax. The truth is that only one person will be using this so I won't have an issue with multiple numbers. I would have just used autonumber. The problem is that I took over this database and I can't change it to an autonumber as it is already populated. I would have to create a new field, but this current field is already linked to other tables. So I think in this case keeping the format as number and automating the input through the Dmax may be the best.

    Thanks,

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Dmax solved my problem.

    Thanks,

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-27-2015, 02:40 PM
  2. Replies: 1
    Last Post: 03-28-2013, 07:54 AM
  3. Passing Form Variable to Query
    By allenrickson in forum Reports
    Replies: 10
    Last Post: 06-27-2011, 07:33 PM
  4. Using Form Variable in Query Criteria
    By Greg.Terry in forum Queries
    Replies: 3
    Last Post: 04-01-2011, 08:06 PM
  5. Refering to variable form names inside a variable
    By redpetfran in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 01:39 PM

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