Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78

    Opening a table and getting a value to use on the if statement

    hi,
    I got a problem and i need help.I m trying to get from a table the value of a record and compare it on the if statement for it to enable a textbox and the color
    This is the code i m using


    Dim dbsStock As DAO.Database
    Dim rstMinimum As DAO.Recordset


    Set dbsStock = CurrentDb
    Set rstMinimum = dbsStock.OpenRecordset("minimum")
    If Me.Texte15.Value < rstMinimum Then


    Me.Texte15.Enabled = True
    Me.Texte15.Text = "Red"
    Me.Texte15.Value = vbRed
    dbsStock.Close
    rstMinimum.Close
    End If

    me.Texte15 is a value on the form and minimum is the minimum stock value each record has on stockTable.
    let me paint you a picture,on a form there are textboxes and there is one when the minimum is reached,it enables the texboxe and change the colr to red

    This code does not want to work.
    Please help on to fix them with the rigth syntax

    Regards

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well for starters, you're just referring to the recordset. You have to specify a field:

    If Me.Texte15.Value < rstMinimum!FieldName Then

    I'm guessing you'd also want to refer to a specific product, so you'd open the recordset on an SQL statement that did that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    it s ginving an error saying that minimum is not recognize. minimum belongs to the stock table,are you sure that the syntax im using is the rigth one.Pbaldy i have sent you a mail where i have attached the db

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You need to open the recordset on the table, not the field. Also, like I said you'd need to specify the product, or you'll just get the first one. I think you'll find a DLookup() easier than the recordset:

    DLookup Usage Samples
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    so how do the code will look like please i do not know how to use the DLook

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If Me.Texte15.Value < DLookup(...) Then

    using the syntax from the link.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    thanks a lot,by triing to find the solution on your website i came across the solution of one of my roblems.it concerns the auto fill.Now i can save the chooden item from a combo box on a table.but there is another pb now.i got an update button that update the stock level but now it does not update no more but the saving part is working. Please have a look at my codes as i am new in this
    Dim strSQL As String

    strSQL = "UPDATE Stock Set [produitQuantite] = " & Me.Texte15 & " WHERE [produitId] = " & Me.Modifiable13




    CurrentDb.Execute strSQL, dbFailOnError
    me.modifiable13 is the combo box with the name,qty and price and its the one displaying the productName that is getting saved
    Help please stuck there for days,but one pb solved

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not sure what you're describing, but that button just worked for me in a brief test.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    Thanks a lot your website help me to find solution at a problem i had from long. I m working on the other one.I ll keep posting

    Regards

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    It s me again and sorry to bother.I ca see that im closed but there are so few pb.After solving one another came out. This is the case scenario
    There is a table stock and table invoice,a form Invoice and two button save and update On the table stock there is productId and productName and productprice.
    The invoice table has InvoiceNumber,productId(For the relationship) and product name.
    Me.Modifiable13 is a combo box containing the productId,productPrice and productQuantity.
    On the invoice form i do not display the productId,Me.Modifiable13 display a name.( But while updating the table and saving,the value of productname is a number and on productId there is a zero.). This is fixed but now the update on the table stock does not happend
    Those are the codes
    Codes
    strSQL = "UPDATE Stock Set [produitQuantite] = " & Me.Texte15 & " WHERE [produitId] = " & Me.Modifiable13


    CurrentDb.Execute strSQL, dbFailOnError
    Me.Texte15 = ""
    Me.Texte17 = ""
    Me.Texte19 = ""

    REGARDS

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not sure what to tell you. I chose Primus and put 7 in the textbox, and this code updated the table:

    Code:
    strSQL = "UPDATE Stock Set [produitQuantite] = " & Me.Texte15 & " WHERE [produitId] = " & Me.Modifiable13
    
    
    CurrentDb.Execute strSQL, dbFailOnError
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    yes it does but on the FactureOutside it does not save the name but the index number. Try to check it out

  14. #14
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    when i put the code Me.Modifiable13 = Me.Modifiable13.Column(1) on the after update of the combox as you said its gives an error and then by adding selected(0) at the end of modifiable13 on the update codes,it saves the name of the product but does not update the stockstrSQL = "UPDATE Stock Set [produitQuantite] = " & Me.Texte15 & " WHERE [produitId] = " & Me.Modifiable13.selected(0)

    CurrentDb.Execute strSQL, dbFailOnError

  15. #15
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This code doesn't make any sense:

    Me.Modifiable13 = Me.Modifiable13.Column(1)

    You're changing the numeric bound field to the text description, which would certainly cause an error. This is the code I have in the after update event, which I think I gave you earlier, perhaps on another thread:

    Code:
        Me.prixvenduO.Value = Me.Modifiable13.Column(2)
        Me.Texte15.Value = Me.Modifiable13.Column(3)
    
        If Me.Texte15.Value < 10 Then
            Me.Texte15.Enabled = True
            Me.Texte15.BackColor = vbRed
        End If
    I think I remember also deleting the code from the textbox change event.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Opening a table
    By lloyd5v5 in forum Access
    Replies: 2
    Last Post: 02-21-2015, 08:58 AM
  2. Opening a pivot table
    By crowegreg in forum Forms
    Replies: 3
    Last Post: 01-08-2014, 07:44 PM
  3. If Statement Condition - Opening Report
    By geraldk in forum Reports
    Replies: 3
    Last Post: 07-10-2013, 10:26 AM
  4. Replies: 1
    Last Post: 10-15-2012, 02:41 PM
  5. Table opening by default
    By rod147 in forum Access
    Replies: 1
    Last Post: 07-07-2009, 10:01 AM

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