If textbox entry matches an entry in a subform

C

Colman

Registered User.
Local time
Today, 08:17
Joined
Sep 5, 2011
Messages
37
I am looking for a fast way to identify if a text box entry matches an entry in a subform query. This is for an If statement in an 'after update' macro on the textbox.

I currently use DCount on the query but this function is now unbelievably slow after I upgraded to 2010. (from 97)

Can anyone give me a faster solution? I have no vba knowledge at present. I hope to change this soon.

Thanks in anticipation. I hope I can return all the help I am getting one day.
 
I have seen those resources and can't see what I might be doing wrong. I will revisit though.

The story goes, the database was running as a single file (not split) for many years in Access 97 with multiple users accessing it. Since converting it to 2010, parts of it were running very slow. They occasionally run fast but not for long. I split the database after researching this speed issue. The split works okay but still has the same speed issue. In one form the user ticks checkboxes in a subform to indicate inspected items. An 'after update' macro on the checkbox, amongst other things did a DCount to display the number of inspected items on the main form. Ticking an item was taking a couple of seconds to process. When I removed the DCount function it became instantaneous. This is why I am blaming the DCount for everything.

Re. the subject of my thread - this is the condition in the macro on the text box [Text13] 'after update' property;

DCount("[serial number]","possible children","[serial number]=Forms![select children]![Text13]")=1

Thanks for your help
 
Well, hopefully you have brackets around *"possible children"* because of the space in the name but neither that or that DCount should be causing the problem.

1. Is the database in a Trusted Location?
2. What do you have set for References?
3. When was the last time you ran Compact & Repair? (Please make a back-up as there have been reports of lost data and/or tables)
4. I see you split it but does everyone have their own front end?
 
Hi again,

1 Database is in a trusted location.

2 The two main tables are 'batches' and 'numbers'.

'batches' records are created to define a batch of product serial numbers. Its primary key is an auto-number [batch ref] field. The only indexed field is [batch ref] (no duplicates).

'numbers' records are serial number records which are generated to relate to a specific 'batches' record via its [batch ref] field. Its primary key is an auto-number [ref] field. The indexed fields are [ref] (no duplicates), [serial number] (no duplicates), [end product] (duplicates ok) and the related field [batch ref] (duplicates ok).

3 I did compact and repair before and after I did the split.

4 I have only tried running one FE. The BE remains unaltered at present, so people are accessing this in an un-split way at the same time as the new FE is accessing its tables. As I am writing this I am sensing that this may be significant? (I am having to avoid too much disruption of production during these changes and database is continually in use)

Hope all this makes sense. Thanks again for helping.
 
2. You are telling me the tables not the References. For example of what I am talking about see...

Repair (or Remove) Broken References


4. Oh, well, everyone accessing the same front end will cause a problem right there. So let's fix that. Have a look at...

http://www.kallal.ca/Articles/split/index.htm
http://allenbrowne.com/ser-01.html

You may also want to take a look at MVP Tom Wickerath's article…
http://www.accessmvp.com:80/TWickerath/articles/multiuser.htm

To handle the update of multiple front ends see…
http://www.autofeupdater.com/

If each user needs Access you can install the Runtime…
Access 2007
http://www.microsoft.com/downloads/...d9-9dc6-4b38-9fa6-2c745a175aed&displaylang=en
Access 2010
http://www.microsoft.com/downloads/...cd-5250-4df6-bfd1-6ced700a6715&displaylang=en

AND you should *never* try to make changes to a front end will your Users are in it. You are just asking for trouble (and taking a chance for corruption). One of the many benefits of giving each User their own front end is so you can work in peace!
 
2 - I am learning all the time here. Now I know what you mean by references. Never looked here before. I have the following items checked;

Visual basic for applications
Microsoft Access 14.0 object library
OLE automation
Microsoft activex data objects 2.1 library
Microsoft visual basic for applications extensibility 5.3

Nothing appears to be missing or broken. i.e. no prefixes in the table.

4 - Looks like I am going in the right direction but only done half a job.

Okay, so my plan is as follows;

i) Study the performance improvement tips again and study Tom Wickerath's article.

ii) Create a persistent link table in the BE and have it permanently open in a hidden form in the FE (I kind of assumed that my existing FE main form which is permanently open would do the trick as it has a subform based on a query on the linked tables - is this not so?)

iii) Turn my one FE into an accde and distribute copies to each user (They all have 2010 runtime)

iv) Consider using autofeupdater
 
You missed a part on the link I posted... at the bottom there is a Conversion Alert. Remove the checkmark next to...

Microsoft visual basic for applications extensibility 5.3

...this might eliminate your problem. If not, you can move along to your other ideas.
 
Many thanks. I will report back at the end of the week after I've had a chance to work on this.
 
Okay, I'm not sure what it was that did the trick or if it was a combination of all of them, but things are running a lot quicker and smoother now. I'm afraid I didn't have the time to test after each stage.

First of all I made backups of the FE and BE databases in case things went horribly wrong.

I then un-checked the reference 'Microsoft visual basic for applications extensibility 5.3' and compiled. I did this in both the BE and FE. Not sure if I needed to do both?

I went through all the BE tables and set their 'subdatasheet name' property to 'none'.

I created an empty table in the BE and made it permanently open in a hidden form in the FE.

I created 17 accde copies of the FE and distributed them to each user, updating their desktop shortcuts.

All indications are that this has made a vast improvement. One or two of my macros are still a little slow, but they are loaded with DCount expresssions and I think I can make them more efficient. (One more thing I did was buy a book on vba for Access 2010 !!).

Thanks Gina for steering me through.
 
Well, you were busy! Glad that all worked and yes, you probably want to look into those DCount() functions but all in all good job. (Oh, and yes, you needed to remove that Reference in both the front end and the back end.)

Good luck on your project!
 
Well maybe not quite there yet.

I am working on a copy of the database at home, so both FE and BE are on my desktop. - I re-created the table links using the linked table manager, and did a compact/repair and things are working.

I have a strange issue however with one of my queries. It is a very simple query and runs instantly about 7 times out of 8 but occasionally takes about 7-8 seconds to run. I get a momentary "not responding" indication at the top of the database just before it finally runs. This is baffling me.

The query uses two tables, both of which open in the FE instantly every time. One table has just two records, the other about thirty. They are joined in a 'show all records from one and show only matching items in the other' fashion. There is a simple criteria on one field that selects records with a match in a textbox entry on a form. This field is indexed in both tables, and I have tried creating a relationship between the tables using the same joined fields as set up in the query with no improvement.

I did try deleting the query and re-creating it again but still have the same issue.

Hard to see any pattern. Sometimes on opening the database the first few attempts are ok, but sometimes the first attempt is delayed. It seems that once a delayed query has occurred, repeating the same query is always okay until the criteria textbox entry is changed. Then the delays are possible again.

I'm using a brand new pc, running windows 7, so plenty of RAM etc. PC has no other issues that I have seen.

Anybody have any clues please?
 
Gina, so good to hear from you. I am having so many problems with 2010. I wish I'd stayed with 97.

The anti-virus is I believe Avast but will need to check that. I should explain that I am only on site one afternoon a week. I am trying to provide support to this company, for a range of databases that I developed when I worked for them in a full time quality role.

I have built a new little database using 2010 to handle one of the functions of one of the old databases, that has become too slow since the 97 to 2010 conversion and subsequent database split. However, the new database, is just as slow on the network, although it is okay when tested with a copy of the BE on the local machine.

This new database uses a BE table of serial numbers (on a network server). The user scans a serial number barcode and a macro checks to see which of two queries returns a matching serial number record (some serial numbers are parent items, others are child items). If it is a child item serial number, it runs an update query to update one field of the scanned serial number's record. This used to take less than a second in 97, now takes about 8 seconds. The queries themselves take several seconds to open from the navigation pane, although the table links open instantly. I think the DCount in the macro condition accounts for the remaining delay.

Anti- virus? Is this something the IT man needs to look at?
 
You must log in or register to reply here.

Similar threads

Bobcat42
Solved Problem filling a textbox (format RichText) with Richtext
Replies
3
Views
760
Bobcat42
Bobcat42
Z
DCount with multiple criteria in subform
Replies
5
Views
663
zebrafoot
Z
J
Match the primary Key in the Main form to the foreign key in the Subform
Replies
9
Views
345
MajP
MajP
C
Subform Footer Textbox wont update
Replies
9
Views
1,016
Gasman
Gasman
G
Strange behaviour from a combo box on a form when trying to evaluate data in another column
Replies
6
Views
693
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom