Results 1 to 7 of 7
  1. #1
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32

    Changing the value of A field Name of a Querry with the push of a button

    Hello my Assignment is in the final stage so I have one more problem to solve before it is finished. Right now I have Created A querry that Selects all the records in the Table when a certain field and its value is Equal to "Uitgeleend".


    Now this Querry is linked to a form with a combo list and choice list for viewing the entire list of Loaned articles. This form will also have the function to deliver Articles Back to the system, therefore changing the value status from Uitgeleend to "Beschikbaar", and also two other fields back to Blank.


    The Querry in Question that is the source of my form is the following:
    Code:
    SELECT BIB.AANWINSTNR, BIB.STATUS, BIB.UITLENER, BIB.UITGELEENDOP, BIB.O_I_II_III, BIB.STANDNR, BIB.KOP, BIB.TITEL, BIB.AUTEUR, BIB.EDITIE, BIB.PLAATSUITG, BIB.UITGEVER, BIB.JAAR, BIB.PAGINERING, BIB.ILLUSTRA, BIB.REEKS, BIB.ANNOTATIE, BIB.ISBN_NR, BIB.HERKOMST, BIB.KEY_WORDS, BIB.[UITGESCHREVEN OP]
    FROM BIB
    WHERE (((BIB.STATUS)="Uitgeleend"));

    Click image for larger version. 

Name:	Return System.png 
Views:	35 
Size:	25.6 KB 
ID:	38895

    So the following has to change:
    BIB.STATUS to "Beschikbaar"
    BIB.Uitlener to Blank
    BIB.Uitgeleendop to Blank

    How do I do this in VBA?

    Edit: So far this is the following code:

    Code:
    Private Sub Knop13_Click()
    If "Query6Update.AANWINSTNR = '*'" & "Me.Keuzelijst9 = '*'" Then
    Query6Update.STATUS = "Beschikbaar"
    Query6Update.UITLENER = vbNullString
    Query6Update.UITGELEENDOP = vbNullString
    End If
    End Sub

    I get Error 13 Types do not match.


    Last edited by ThunderSpark; 06-26-2019 at 07:44 AM.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Read about update queries, you need to create one then run it from your button's OnClick event.

    Cheers,
    Vlad

  3. #3
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32
    I created an Update Querry called UPD_STATUS. Which already does what I want however I need to make it that not only it executes the Querry but only for the exact "record" selected.

    Code:
    UPDATE BIB SET BIB.STATUS = "Beschikbaar", BIB.UITLENER = Null, BIB.UITGELEENDOP = Null;
    In VBA this is already translated to=


    Code:
    Private Sub Knop15_Click()
    
    If MsgBox("Is dit het artikel wat u terug wilt inleveren?", vbYesNo + vbExclamation) = vbYes Then
            DoCmd.OpenQuery "UPD_STATUS"
    End If
    End Sub
    Now I just need to it to make sure it only runs that Querry for that specific Article from the choice list. Also is it possible to make sure the two warning boxes that comes up, that warn you of the Update Querry can be customized/Disabled? If not then I will just create another message box saying they have to click yes two times.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can add a criteria to the query that points to the form. On the warnings:

    http://www.baldyweb.com/SQLWarnings.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Also, Re Update queries:

    See https://www.w3schools.com/sql/sql_update.asp for details --particularly this note.

    Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

  6. #6
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32
    Quote Originally Posted by pbaldy View Post
    You can add a criteria to the query that points to the form. On the warnings:

    http://www.baldyweb.com/SQLWarnings.htm
    So you directly edit the Querry itself then instead of the VBA Code.
    Edit there is a problem even though I clearly refered to it, I still need to enter the value manually.

    Click image for larger version. 

Name:	Querry Command.png 
Views:	12 
Size:	43.0 KB 
ID:	38955

    Code:
    UPDATE BIB SET BIB.STATUS = "Beschikbaar", BIB.UITLENER = Null, BIB.UITGELEENDOP = Null
    WHERE AANWINSTNR=FRM_BIB_DLG_UITGELEENDE_ARTICLES.Keuzelijst9;

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your reference to the form is invalid. You can right-click and use the Build feature, or see here:

    http://theaccessweb.com/forms/frm0031.htm

    You want the "Not in these forms" syntax for a query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Push text box(es) down
    By leesider in forum Reports
    Replies: 5
    Last Post: 03-06-2018, 09:57 AM
  2. Replies: 3
    Last Post: 04-24-2014, 03:09 PM
  3. Replies: 0
    Last Post: 04-14-2012, 07:36 PM
  4. Push information from one DB to another
    By Cheshire101 in forum Programming
    Replies: 1
    Last Post: 05-13-2011, 09:01 AM
  5. Changing Field Values with Command Button
    By avarusbrightfyre in forum Access
    Replies: 1
    Last Post: 08-22-2010, 12:48 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