Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set Yes/No field by code (Access 2000)

    I want to set the field of the last order of the table orders to True in case the Toggle Button TglAudit in the form
    is set to true.If we make an auditing in the warehouse,i want by pressing the button Audit to mark the field of the last order number as orders.receipt >=True
    How can i do it ?

    I know how to make a query based on the last order,which i have with the help of Hans from the present Forum.
    It works excellent and it is as follows :

    SELECT orders.orderid, orders.orderdate
    FROM orders INNER JOIN Customers ON orders.customerid = Customers.Customerid
    WHERE (((orders.orderid)>=(SELECT Max(orders.orderid) FROM orders WHERE orders.receipt=True)));

    I do not know however how to set by code the Yes/No button to True for the last order,if Me!TglAudit is set to True.
    I will be very grateful for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Set Yes/No field by code (Access 2000)

    Which field do you want to set to True?

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Yes/No field by code (Access 2000)

    Thank you for your reply. If you remember, i have built my excellent query entirely with your help and i am very gratefyl for that.
    I want now to set the field orders.receipt of the last order to True. I must somehow find the last order and on this last order to set the filed Receipt to True.
    For example, the last order number in my table orders is number 1247. If i set the field receipt of this order to True, it will trigger my query
    and i will have displayed all orders greater than 1247. This information is very improtant for me ans i part of a greater task to calculate input and output of the warehouse.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Set Yes/No field by code (Access 2000)

    Try this:

    Private Sub tglAudit_Click
    Dim strSQL As String
    strSQL = "UPDATE orders SET receipt=TRUE WHERE orderid=(SELECT Max(orderid) FROM orders)"
    CurrentDb.Execute strSQL
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Yes/No field by code (Access 2000)

    I hurry to let you know that works now perfectly. How amazingly simple some complicated solutions may look !

  6. #6
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Yes/No field by code (Access 2000)

    Dear Hans,

    As i have informed you, the code works excellent and is a little wonder.I would like now to introduce the following refinement in my code.While setting the receipt = True for the last record i would like to reset all the previous True values to False.I would like to have a True value only for one order.
    Is it possible to do it?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Set Yes/No field by code (Access 2000)

    You need two separate SQL statements:

    Private Sub tglAudit_Click
    Dim strSQL As String
    ' First, set receipt to False
    strSQL = "UPDATE orders SET receipt=False WHERE receipt=True"
    CurrentDb.Execute strSQL
    ' Then set receipt to True in the last record
    strSQL = "UPDATE orders SET receipt=True WHERE orderid=(SELECT Max(orderid) FROM orders)"
    CurrentDb.Execute strSQL
    End Sub

  8. #8
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Yes/No field by code (Access 2000)

    Dear Hans,

    Could you help me further with your brilliant suggestion about setting the Yes/No field? Actually the query i have built is valid only if i have only one customer in question, namely:


    SELECT orders.orderid
    FROM orders
    GROUP BY orders.orderid, orders.customerid
    HAVING (((orders.orderid)>=(SELECT Max(orders.orderid) FROM orders WHERE orders.receipt=True)) AND
    ((orders.customerid)=118));


    However, when i introduce another customer, with ID = 119, then the query is valid only for this customer. If i introduce both customers,namely 118 and 119, the query for custoemer 118 is blank,does not show anything. I have to build queries for 12 customers,with numbers 118,119,120,122,124,960,1008
    These are fixed customer numbers.

    I use the above queries for auditing in the warehouses.Each number has an warehouse.So i will have 12 queries.
    I need to avoid the conflict between the queries.Even though i have specified with HAVING the customer number, somehow the
    queries are in conflict.
    I am appending a short database with an example

    Thank you in advance

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Set Yes/No field by code (Access 2000)

    I don't know why your query is a totals query and why you have "greater than or equal" in the criteria.

    I wouldn't build 12 separate queries. Since they are identical except for the customerid, maintenance will be difficult. Instead, use a form to select a customerid and refer to that in the query.

    See the attached version. Open the form, select a customer, then open the query.

  10. #10
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Yes/No field by code (Access 2000)

    Dear Hans,

    Thank you very much for your reply.I tried to send you a very shortened database but i missed some important
    points.My query is a totals query since i must sum up all the products in the warehouse.
    Greater means all the records after an auditing in the warehouse.
    For example for client Nr 118, option 2 in the form.
    When we make an audit,the field receipt is set to Yes.The warehouse is set to null, and after that all the receipts
    greatet than this record increase the quantity in the warehouse.I used your suggestion
    >=(SELECT Max(orders.orderid) FROM orders WHERE orders.receipt=True
    to follow the records after the auditing.I consider this suggestio as very clever.

    Actually the database works excellent,provided i use only one customer.For example customer 118, which is number 2 of the options.If i make a second revision or auditing, for example with client 119, which is the third option,then only the thrid option works, but not the second.
    The same is with the queries.qryVainput is blank,but if you remove the Yes for client 119,(order 5622) then it is OK.


    You can see in the example that the second option is not working.But if you remove the Yes option for client 119, which is the thrid option, then it is OK.
    I think the problem is with the yes/No values in the tables,and i am asking for your help

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Set Yes/No field by code (Access 2000)

    As I tried to indicate in my previous reply, you must include the customer number in the criteria for orderid. As it is now, you select the maximum orderid where receipt=True for ALL customers. If you want to look at one specific customer, you must take the maximum of orderid where receipt=True for that customer only. See the modified versions of qryBlInput and qryVaInput in the attached database.

  12. #12
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Yes/No field by code (Access 2000)

    Just to let you know that all our problems are solved now!!!! Thank you for your patience with me. Please accept
    my appreciations and i wish you all the best.

  13. #13
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Yes/No field by code (Access 2000)

    Dear Hans,

    Perhaps i should use the same thread since it concerns the same problems.
    I thought I have finished,but again I have a problem.The problem is similar but again i cannot see the final solution.After i have solved input query i have to solve now the output query.This time the value depends not on the customer id,but on the affiliate number, i.e. afid. I have tried to enter the afid number in the query, but Acces get errors and cannot stop.
    As you can see from the attachment, if orders 65880, for affiliate 2 and 103028 for affiliate 3 are checked with Yes, I cannot open thee query qryVainput. If however I set the order 103028 to No, then the query appears.
    Obvisouly I have to define the query depending on the affiliat number but somehow I cannot do it.
    Could you help me?

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Set Yes/No field by code (Access 2000)

    In your database you have not included the query qryVainput.
    However if you mean to use query qryVAOutput then you will find that there is no product for order 103028.

  15. #15
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Yes/No field by code (Access 2000)

    Thank you for your attention.
    Yes,i mean qryVaoutput.You write that there is no product for order 103028. This order however is for query qryBlOutput. The number
    of the product is 236, and the order is from affiliate number 3.
    All my orders are tied up with affiliates.In my example :
    order 65880 is for affiliate 2 ( the ID number is afid - 2). The query is called qryVaoutput
    order 103028 is for affiliate 3( the ID number is afid = 3) The query is called qryBloutput

    If you want to see what qryVaoutput contains, you have to uncheck order 103208 and then you
    will see that qryVaoutput contains the products 213,236,175,242,and 243.This is exactly the question i have.If i have more than one query,in this case 2 queries,i have a problem and only one query is valid.And i have 12 queries.I am attaching the variants to see better my problem.
    The first variant is called Va, where only order 65880 has the Yes/No field set to Yes.
    In the second variant both orders are checked, i.e. Audit set to Yes.In this case the query qryVaoutput is blank, and you may think thatthere is not product for it.
    I think the solution is somehow to relate the query to the afid number,but i cannot do it.

    regards

Page 1 of 2 12 LastLast

Posting Permissions

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