Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34

    Macro doesn't work 100%

    Good morning.

    I'm writing on the behalf of my company. First we would like to apologise for our colleague's behaviour but there has been a huge misunderstanding. We are building a very large database in Access 2010 and we are dealing with some serious deadlines. It's the first time we are using it so we would appreciate some assistance.

    We have created a table in which we imported an Excel file (it included all the info we needed). We have also created a query and a form. We used the Split Form, so far so good. We added a Text Search Field and a Search button. Then we chose the Search button, clicked on it, chose macro but somehow we couldn't make it work with two statements in Where Condition.

    For example: the code we wrote in Where condition was [COMPANY] Like "*" & [Forms]![qr-BWTS]![Text87] & "*"

    When this code was by itself it worked great but when we added a second statement it ignored the second one and didn't show any results.

    For example: [COMPANY] Like "*" & [Forms]![qr-BWTS]![Text87] & "*" Or [STATUS] Like "*" & [Forms]![qr-BWTS]![Text87] & "*" ignored the "Or" statement and didn't return anything when we typed something from the STATUS field...

    We did one more thing to test it which was that: we deleted the second statement and we left the first one, only we replaced COMPANY with STATUS. It didn't work either!!!

    The macro was: [STATUS] Like "*" & [Forms]![qr-BWTS]![Text87] & "*"

    COMPANY is the second column (first is the ID) and STATUS is the 12th column... Something wrong with the macro or the columns ?

    Sorry again for the mess that has been caused, good faith is above all so any help would be really much appreciated.

    Thank you in advance.

    Nikos Platis


    IT Manager

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Apology accepted, I have just responded to your colleague, bridges can be mended.

    the implication of this

    We did one more thing to test it which was that: we deleted the second statement and we left the first one, only we replaced COMPANY with STATUS. It didn't work either!!!

    The macro was: [STATUS] Like "*" & [Forms]![qr-BWTS]![Text87] & "*"
    is that the STATUS field is a lookup field in your table - so you are seeing text, but it is storing a number. If this is not the case, please provide some example data to illustrate the problem.

  3. #3
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    What do you mean by saying "lookup field" ? I know it must be a small detail but can't figure it out. The STATUS field is not a number, it includes text "In Service" and "On Order"

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    What do you mean by saying "lookup field"
    go into table design, click on the status field. Under data type does it say text or number? Then click on the lookup tab in field properties if it doesn't say Text Box, then it is a lookup field. Lookup fields are a bad idea so if you have not implemented them, don't. If you have, change them back to normal fields.

  5. #5
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Got it! STATUS field is Text in Data Type and Text Box in Lookup. I have also tried with another field which has numbers and still no success. The other field is mDWT field. It says Number in Data Type and Text Box in Lookup. Only COMPANY field works in the text search box...

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    OK so we should be comparing like with like. On the face of it your criteria should work, so implies there is a different problem.

    Please can you provide some example data you are searching and an example of the what you are searching for. Also you are talking about macro's which I don't use, but could be terminology (VBA functions in excel are called macros). Please provide all the code you are using rather than just the criteria because it is not clear whether you are using this as a criteria in a query, or openform/report command or as a form filter or whether some other part of code is cause the problem. If text code (VBA or SQL), copy and paste to the post and surround with the code tags (highlight and click the # button. If macro, do a screenshot. Would also help to have a screenshot of the form.

  7. #7
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    OK, so here are 5 screenshots, I will explain it somehow.

    Screenshot 1: shows the database form what it looks like.

    Screenshot 2: shows the results when i search via COMPANY. Works great!

    Screenshot 2a: this is the code that works great for COMPANY field. The macro code goes here: Forms -> Design View -> choose Search button, on Property Sheet I select the Event tab, then I select On Click, Embedded Macro and click on the three dots. Finally I put the code in WHERE Condition as shown on Screenshot 2a.

    Screenshot 3: shows the Search Button selection in Design View.

    Screenshot 4: I add the second statement in Where Condition using "Or".

    Screenshot 5: code doesn't work for STATUS field.

    Click image for larger version. 

Name:	Access 2010 Example (Screenshot 1).jpg 
Views:	16 
Size:	135.4 KB 
ID:	26380 Click image for larger version. 

Name:	Access 2010 Example (Screenshot 2).jpg 
Views:	16 
Size:	138.6 KB 
ID:	26381 Click image for larger version. 

Name:	Access 2010 Example (Screenshot 2a).jpg 
Views:	16 
Size:	75.6 KB 
ID:	26382 Click image for larger version. 

Name:	Access 2010 Example (Screenshot 3).jpg 
Views:	16 
Size:	186.0 KB 
ID:	26383 Click image for larger version. 

Name:	Access 2010 Example (Screenshot 4).jpg 
Views:	16 
Size:	86.4 KB 
ID:	26384

  8. #8
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Screenshot 5

    Click image for larger version. 

Name:	Access 2010 Example (Screenshot 5).jpg 
Views:	16 
Size:	92.4 KB 
ID:	26385

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    OK but I am confused as to why you say this works

    [COMPANY] Like "*" & [Forms]![qr-BWTS]![Text87] & "*"

    but this doesn't

    [STATUS] Like "*" & [Forms]![qr-BWTS]![Text87] & "*"

    the reason why it doesn't is one reason why the OR doesn't either

    As I mentioned before, I don't use macros so not sure if the string construct is correct - it looks like how you would construct for a query, but if you changed to VBA code it would look like this

    Code:
    Private Sub Command88_Click()
    
        me.Filter="[COMPANY] Like '*" & [Text87] & "*' Or [STATUS] Like '*" & [Text87] & "*'"
        me.filteron=true
    
    End Sub
    Suggest create a new button, go into VBA code for the click event and copy/paste the two lines of the above code and see if it works

    You can also convert your macro to VBA and see how the two filter strings compare.

  10. #10
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    WOW! It worked like a charm! Thank you very much!!! I also added a "number" field and it works great!!! It seems VBA code is more accurate!!!

    I don't understand why the macro code didn't work though. I watched a video on youtube and I did it the exact same way the guy did...

    Must stick to VBA code as it looks like.

    Thank you very very much, really appreciate it!!! You saved me lots of time B-)

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    VBA is much more flexible and is much easier to debug, maintain and document - but can't be used in a web app which also have much fewer events that can be triggered. It is also easier for others to help you since code can simply be copy and pasted.

    I note you said your application is large so do be aware of a few things:

    1. your data is not normalised - looks like you should have separate tables for company details and contract details as a minimum, probably more
    2. I'm guessing you do not have any indexing except for the ID field
    3. Using an initial * in the Like clause negates the use of indexes

    All of the above will have a profound effect on the performance of your db unless you address them now. Leave it until later and you will have a much bigger problem to resolve.

    Access (or any db) is not a bigger Excel. With Excel, data and presentation are typically in the same view (a worksheet), with databases, data is stored in tables whilst views are created using queries, forms and reports. In Excel, data is stored 'short and wide' whilst in db's it is stored 'tall and thin'. In Excel you get 'the whole thing', in databases the objective is to just bring through what is required for the task in hand.

    In many respects an access application is closer to a web application than to excel and should be designed to work in a similar way - forms should fit within the width of the screen (no horizontal scrolling required), consideration given for the amount of network traffic generated, easy to get from one part of the db to another, etc. Unfortunately Access provides some tools which very quickly create your forms - but in turn those forms are often inefficient in their design.

    An example is the split form you have provided. I don't have a problem with the basic concept of the design - but when designed, it brings through all the table data which you then filter. So Access has to bring all the records from your database to do that. It would be better for your user to either provide the search string in a different, unbound form and you then pass the filter in the docmd.openform parameters - then only the filtered records are brought through. So instead of 100,000 records, you bring through perhaps 50.

    Alternatively, a little trick using your existing form is to do the following:

    1. change the recordsource to your form to

    SELECT * FROM BWTS WHERE True=False

    which will return zero records

    2. then in your search button control put

    me.recordsource="SELECT * FROM BWTS WHERE [COMPANY] Like '*" & [Text87] & "*' Or [STATUS] Like '*" & [Text87] & "*'"

    and the required records will be found

    It won't be particularly noticeable on small datasets, but once they get larger and you split the db for multi user use you will see a difference in performance

  12. #12
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    So it's like waiting for a web site to load, heavier the site, more time to load... Thank you very much for this advise, will keep it in mind and use it.

    Code:
    1. change the recordsource to your form to
    
    SELECT * FROM BWTS WHERE True=False
    
    which will return zero records
    Where in the form or query will i add this code ?

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    in the form properties recordsource (under the data tab) - it probably just says BWTS at the moment

  14. #14
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Got it!

    I wrote
    SELECT * FROM qr-BWTS WHERE True=False

    When I save it to exit there's this error "The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parenthesis"

  15. #15
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    OK, I found it, had to put a parenthesis (SELECT * FROM qr-BWTS WHERE True=False). Now there's this error:


    Click image for larger version. 

Name:	Access 2010 Example Screenshot.jpg 
Views:	15 
Size:	109.3 KB 
ID:	26386

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

Similar Threads

  1. Why doesn't this DCount() work?
    By tim_tims33 in forum Access
    Replies: 1
    Last Post: 12-16-2014, 07:14 AM
  2. Replies: 5
    Last Post: 04-14-2014, 12:37 PM
  3. Just doesn't seem to work!
    By txmmoore in forum Reports
    Replies: 9
    Last Post: 01-16-2014, 11:39 AM
  4. Replies: 1
    Last Post: 05-02-2012, 11:40 AM
  5. Can Grow doesn't work
    By gg80 in forum Reports
    Replies: 6
    Last Post: 05-13-2011, 07:14 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