Results 1 to 10 of 10
  1. #1
    trent.bourne is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    3

    Wink Help with Query and IF Statements in VB

    Hey All,



    I have connected our salesforce platform with our access system so we have read only access to the SF data and run a whole heap of things out of Access instead of having to pay heaps of money for Salesforce Apps then we can also keep the costs low on licences and then we also pump this data out to our clients.

    I want to be able to scan a barcode on one of our letters and the scanner put the value in a text box called "Scanned_Textbox_Value"

    Then I want Access to change the colour if the query "Duplicate Check" returns more then one value to the form turning "Angry" red... Then if there is only one record turn to Happy "Green" and if it comes back with nothing then "Confused" Orange.

    I get that I have to put the macro or VB code into the form or field in the "When Updated" option for the text box but I cant find a way to get the count of a query

    Can someone please help

    Also if I have posted this in the wrong thread please be nice I am a noob here.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Code:
    iCount = Dcount("*","qsMyQuery")
    
    select case iCount
       case 0
                  Me.Detail.BackColor = RGB(252, 230, 212)
       case 1
           Me.Detail.BackColor = vbgreen
       case else
           me.Detail.backcolor = vbRed
    end select

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum....


    Quote Originally Posted by trent.bourne View Post
    <snip>
    Then I want Access to change the colour if the query "Duplicate Check" returns more then one value to the form turning "Angry" red... Then if there is only one record turn to Happy "Green" and if it comes back with nothing then "Confused" Orange.
    With you up to here, then I got lost.

    WHAT do you want to change color when the query "Duplicate Check" is executed?
    How are you executing this query?
    If you have code, would you post it?

  4. #4
    trent.bourne is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    3
    Hey All,

    I am confused with the code provided.

    In short we have a whole heap of mail article that Australia Post will barcode depending on the street address will a 40 number code and these are printed as barcodes on our letters 600,000 of them.

    What I want to do is run a query that I have already made to look at these post ID numbers and then report how many in our database there is (this part is done)

    Now we are in the process of folding them ect however I want a way to be able to pull the duplicates out (more than one match in the Account Query)

    I want it simple and Visual for our more "basic" minds to use...

    If there is 1 record.. Turn the form background to green - this can go out.
    If there is 0 record.. The system has not found it put it to the side
    If there is more then 1 record then place it in a rejection bin for another inspection based on the company data. (like a quality check)

    This is going to be the quickest way for us to verify all the letter we have already printed without the QA checks.

    So the process would be Scan barcode with the field active...
    Insert the 40 character barcode
    Run the Query
    Then change the form based on the above requirements.

    The return back to the text box and clear the value waiting for another input value

    I hope that makes sense.

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is your existing query returning the duplicate records (so lets say 5 rows for ID 1) or a count (ID:1, Duplicates:5)? If first use ranman's code (maybe add a Me.Repaint after) in the AfterUpdate event of the text box where you place the scanned code. If you query is not returning the duplicates for just the current ID you will need to add criteria to the dcount. If your query returns counts you need to replace dcount with dlookup and retrieve the value like so: iCount = DLookup("[NumberOfDuplicates]","[qsMyQuery]",[ID] =" & Me.ID).

    You can add code to reset the form on the click event of the scan button or OnCurrent event of the form (check if Me.NewRecord=true then reset background).

    Cheers,
    Vlad

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by trent.bourne View Post
    <snip>
    I want to be able to scan a barcode on one of our letters and the scanner put the value in a text box called "Scanned_Textbox_Value"
    <snip>
    I get that I have to put the macro or VB code into the form or field in the "When Updated" option for the text box but I cant find a way to get the count of a query
    There is no "When Updated" event, but there is an after update event - for the control and for the form.

    Not saying this is the best event because I don't know how you are are initializing the scan and 600,000 pieces of mail to scan sounds daunting!
    But lets say you are using the after update event of the control "Inv_Quantity". The code would look something like:
    Code:
    Private Sub Inv_Quantity_AfterUpdate()
        Const vborange As Long = 10403577
    
        Dim r As DAO.Recordset
        Dim iCount As Integer
    
        'default value
        iCount = 0
    
        'open query in a record set
        Set r = CurrentDb.OpenRecordset("Duplicate_Check")
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            iCount = r.RecordCount
        End If
    
        'set background color
        Select Case iCount
            Case 0 'not in database
                Me.Detail.BackColor = vborange
            Case 1 ' found one!!
                Me.Detail.BackColor = vbGreen
            Case Else ' Uh-oh...More than one found!!!
                Me.Detail.BackColor = vbRed
        End Select
    
        r.Close
        Set r = Nothing
    End Sub
    Note I replaced the space with an underscore in the query name. You should never use spaces in object names.

    Also, the query "Duplicate_Check" needs to have the criteria (for the bar code field) looking at the control "Inv_Quantity" to be able to check for matching record(s).

    And somewhere/some event you should also reset the background to white so you can tell when the recordset/query does or does not find matching records.

  7. #7
    trent.bourne is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    3
    Ok I am clearly being a noob here...

    I have added the code and created the query and have created the form.

    Then I have checked that the query works and brings up the appropriate records when there is details in the form so that's exciting

    Then I move on to updating the records and i am getting an error in VB when we hit the following part of the code

    Code:
    Dim r As DAO.Recordset
    I am getting "Run Time Error '3061' - Too Few Parameters. Expected 1"

    I have uploaded an modified copy of the table and the query and have removed all other forms on the file and uploaded it to my google drive as a public document - you can access that here - https://drive.google.com/open?id=1gK...DBNbilh0AhFosI

    Here are some "Green Codes"
    1301012002200221220012310031111221113
    1301012100111202210102330200100021313
    1301011012201130203022302122023303113
    1301013002102120023010300022223020013
    1301013022221101021130333303232300113
    1301011220113021201030330130223302013

    Here are some "Orange Codes"
    1401012002200221220012310031111221113
    1101012100111202210102330200100021313
    1401011012201130203022302122023303113
    1701013002102120023010300022223020013
    1801013022221101021130333303232300113
    1901011220113021201030330130223302013

    Here are some "Red Codes"
    13000000000000000000003003013023103113012012022023 13

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was getting the same error, no matter what I tried using the query. Still haven't figured it out. (I noticed that you have A2013 64 bit and I have 32 bit...???)

    But I got it to work by using SQL in code.

    You should do a "Compact and Repair' once in a while to shrink the size of the dB. It went from 41,856 kb to 3,284 kb.
    Attached Files Attached Files

  9. #9
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi guys,
    have a look at the updated file, this is my go to approach when using recordsets based on queries with parameters, seems to be working OK. I have also added a bit of code to reset the color on the current event as that red was painful to watch .

    Cheers,
    Vlad
    Attached Files Attached Files

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @ Vlad
    Now that you mention it, I seem to recall that I had to the same thing for a project. (It was a long time ago and I have a brain like a sieve)
    Good solution!!

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

Similar Threads

  1. 15 IIF Statements in a Query
    By hiker8117 in forum Queries
    Replies: 9
    Last Post: 05-31-2018, 05:11 AM
  2. Query on IF Statements MAX Date
    By hinkwale in forum Queries
    Replies: 1
    Last Post: 01-04-2015, 06:42 PM
  3. How to use multiple IIf statements in a query
    By jabadoojr in forum Queries
    Replies: 4
    Last Post: 12-17-2012, 11:05 AM
  4. Query iif statements
    By beckkks in forum Queries
    Replies: 1
    Last Post: 04-20-2012, 03:03 PM
  5. Query for IIF statements
    By SpotoR1 in forum Queries
    Replies: 2
    Last Post: 08-26-2009, 06:57 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