Results 1 to 4 of 4
  1. #1
    JMac is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    21

    expression builder: update query

    Hi all
    Im using a simple update query on a command button.


    It just updates a field "A" in the relevant table, to a combination of 3 fields with "&" joining them, no problem here..
    This works perfectly except for the current record or rather last record which Ive just added in the form! This field "A" remains blank until I exit the DB and return then run the query again at which point it populates it correctly..

    Ideally I want the query to update just the latest record... does anyone have a solution as to how to 'build' the 'criteria' (I assume the criteria is the best method?) or at least have a solution to update the active record??
    Thanks

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    If the record is still in the form, just update the field without using a query:

    [Field1] = [field2] & [field3] & [field4]

    [Field1] does not have to be on the form, as long as it is in the underlying recordset.

    Just as a note, though - if Field "A" and the other three fields are all in the same table, that is not good database practice - field "A" can be a calculated field in a query when you need to use it.

    John

  3. #3
    JMac is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    21
    Thanks John
    Field1 is in the same table as its a unique number, because field 4 is an autonumber and I need Field 1 to be a unique number.
    Essentially I want to lock it in via a query so it can never be repeated, basically I am issuing a unique number which has some user input..

    So the click to run query serves to populate field1 then closes the form, the next time the form is opened its done as a new record.. No one can ever overwrite or change the record...

    So once closed I want to be sure all of field1 is populated?

  4. #4
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    Like John_G said, you can do it on the form, this uses less time and brainpower.

    If your form operates like you said:
    the next time the form is opened its done as a new record.. No one can ever overwrite or change the record...
    Then there is not a problem. and if you are really worried you can perform the calculation on the form close button.

    However to answer your question you can do it the hard way simply by specifying in the where clause [field1] is null

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

Similar Threads

  1. Expression builder help
    By jigg14 in forum Forms
    Replies: 1
    Last Post: 03-19-2012, 09:47 AM
  2. expression builder
    By tonyl in forum Access
    Replies: 3
    Last Post: 11-26-2011, 12:00 AM
  3. Expression Builder or VB ... Help
    By Adynn in forum Access
    Replies: 0
    Last Post: 06-03-2011, 09:51 AM
  4. Expression builder
    By PJ_d_DJ in forum Access
    Replies: 2
    Last Post: 02-24-2011, 03:38 AM
  5. Expression Builder
    By mistaken_myst in forum Access
    Replies: 2
    Last Post: 05-07-2008, 01:30 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