I have a split db and there's a form which has a combo box and i type the name into the combo box and it looks up that record. this whole process is taking a long time. i have about 18700 records, but i thought that if you split a db then it's supposed to run a lot faster. currently it's a good ten seconds before it finds the person and then another ten after i hit enter before it brings the record up. any ideas?
View RepliesHi All,
I'm having trouble with access. Basically it has been very slow to load
(over 30 seconds when not opening a database) and when opening a database it just crashes.
Has anyone any idea what could cause this as it worked fine before ?
also would a reinstall help ??
-Elfman
I've developed a multi user DB. When a user is keying in a record I want to be able to give them the ability to click on a button and the system automatically looks at two value from the previous record they keyed. There is a text box that contains their user ID so I can use this as a reference to look up their last record (I don't want them to take other users last records).
The fields are StartTime and EndTime. So I want the startTime and EndTime from their previous record to be dropped into the current record(without physically going backwards on screen).
Cheers,
Recall
I have a form with a Lookup method. I have it set as this:
Code:
SearchStr = FilterSearch & "(tblCalls.CustFirstName LIKE " & "'" & txtSearchBox.Value & "*' OR tblCalls.CustLastName LIKE '" & txtSearchBox.Value & "*')"
This will allow the user to search for a record by first or last name. But if they try and search with both it will not return anything. I need to to be searchable by first, last, or both.
I am trying to lookup a record in a recordset. If no match is found then run an append query. I am having trouble coding the findfirst syntax.
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim WO As String
WO = Forms!frmdsh_workorder!txtWO
Set db = CurrentDb
[Code] .....
When I run the Function, it throws a RT error #3077 on the .findfirst line. (Syntax Error (Missing Operator) in expression. )
I have a table with some combo selections with row source No;Yes, bound column 1, column count 1, and default value "No", limit to list set to yes, required = yes, allow zero length = no.
When I create a new record though, all of the combos show 0, and I have to select No or Yes manually. The requirement is that No is the default value..
Lots of thanks in advance,
Aidan
I have been editing the service call management database that comes with office XP.
I have a table called "Workorders" with fields:
WorkorderID (primary key)
CustomerID
And another table called "Workorders by Customer" with fields
CustomerID (primary key)
I want to be able to have an input form where I input a workorderID which then opens the "Workorders by customer form" by customerID which relates to the WorkorderID in "Workorders" which is typed in the input box.
Im only a novice and its the code part that im struggling with.
Any help would be great.
I've got stuck in preparation of this sales query. The primary sales table contains a mix of Canadian and the US detail invoice sales records. All sales records are in their native currencies The secondary lookup table contains daily US/CAN foreign exchange rates (FX).
I need to multiply every $US sales record (marked with U) with the FX rate in order to convert it to the Canadian currency. Unfortunately, this FX lookup table is missing over 50 daily rates in the last two years and as a result I cannot match them date-by-date with the sales table.
As a workaround, it is acceptable to use the previous FX rate in the table. The previous rate can be one or more days before the transaction occurred.
Attached is a sample database with the query that I've already built. The query contains two sample US records that are missing the FX rate on Jan 6. The FX rate of $1.0639 that needs to be applied to it should come from Jan 3rd entry.
New Datesales matcode curtype trans newrate
1/3/20141225.61281173224U R187611.0639
1/3/2014344.70361173260U R181731.0639
1/6/20142520 0022691U R19841
1/6/20145400 0022692U R19841
Any way I can set a table up with an intrinsic limit, specifically that only ONE record at a time (this will vary) can possibly have value X selected from a lookup field containing assigned values X, Y and Z. Is this possible? If so, how do I do this?
View 2 Replies View RelatedI want the lookup to be performed with criteria based off of the same row that record being selected is on.
I wrote typed out the Columns and rows that my queries return, and attached a screenshot as well.
You enter your phone number which provides you with an indexed location. Based off of that location only certain products are available and I want them to be filtered accordingly. I have it working to some extent, here is an example below:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521| KNNRLABR
The lookup displays products available where the indexed products match the same location. Here is what the query returns:
| Location |Products Available|
| KNNRLABR| Product 21 |
| KNNRLABR| Product 9|
| KNNRLABR| Product 7 |
| KNNRLABR| Product 3 |
This is the affect that I want to achieve but, if I add another location like so:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521 | KNNRLABR |
| Store 2 | 3609061624 | VANCWA01|
The lookup returns all products available for all the records for each location entered rather than based off of the selected record.
| Location |Products Available|
| KNNRLABR | Product 21 |
| KNNRLABR | Product 9 |
| KNNRLABR | Product 7 |
| KNNRLABR | Product 3 |
| VANCWA 01 | Product 31 |
| VANCWA 01 | Product 8 |
| VANCWA 01 | Product 4 |
| VANCWA 01 | Product 3 |
| VANCWA 01 | Product 1 |
Is there a way I can make the lookup run off of the location specific to the current row that I am working on?
P.S. Here is a copy of the SQL statement I'm using right now.
SELECT DISTINCTROW [DSL Product CLLI's].CLLI, [DSL Product CLLI's].Speed, [DSL Product CLLI's].CLLI
FROM [DSL Product CLLI's], [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet]
WHERE ((([DSL Product CLLI's].CLLI) In (SELECT DISTINCTROW [Location] FROM [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet] WHERE [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet].[Location]=[DSL Product CLLI's].[CLLI])));
Which Returns the Following in my lookup field for Products Available.
I have a form (Student Details) that has an unbound lookup box using a query to select a student. The lookup has the hidden Student ID autonumber key field plus Class Number, Last Name, First Name, Status, sorted by Class Number (descending), Last Name, First Name.
The form record source is a similar but unsorted query.
There are nearly 1000 student records. The form has pages (tabs). The lookup box is in the form header. There are dozens of fields in the 1st Page (General) including an picture (attachment, not hyperlink) field holding a photo of the student, the other 6 pages have less.
When you select a student via the look up, at least one sometimes more students "flash" before it displays the one you want, which seems to display twice before it stops.
I deleted the picture field to see what difference that made, because it seemed to do the most flashing, and indeed, without that field you go right to the record. I looked at the Student table indexes and it seems that field type is not index-able, which is understandable.
Is there any way to improve this with still leaving the photos as attachments? Some thoughts:
It improves if it is displayed first as an Icon or Paper Clip, and probably would if a hyperlink field, but we like to have the picture displayed and not have to open the link to see it nor worry about the location changing invalidating a link. It seems that if the record was displayed with the picture field only filled after all the other fields were filled it would make for a better impression.
Can an On Load form event VBA or Macro be written that either hold the picture back until the rest of the fields are displayed or if an icon or changed to a hyperlink to open. Would it work if the pictures were attached to a separate joined table?
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
Hello,
I wonder if anybody can help me.
I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,
I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.
I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.
Is this possible?
Regards
Nathan
I want to create a different rowsource-query for a lookup field (field1) in each record in a subform. The rowsource changes dependent on the value in another field (field2) in the same record. How can this be done?
- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- I think I have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query.
Something like:
Lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);
me![field2] however does not function
Hi,
My DB is very slow when it's used by more than 1 person at a time. It's divided into FE/BE. The BE is in a shared server and a copy of the FE is in each person's machine.
I don't know why it takes ages to respond. Any help will be very much appreciated.
Regards,
B
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
Hi all
I have an Access 2003 db that works extremely well as a stand-alone application but I am keen to “split” it so that many users can operate it together.
Accordingly I did the split and placed the tables on our server, and tried running the front end locally.
Much to my horror, it takes about a minute to open, and many seconds to do anything after a button press etc.
I have been testing it on my wireless home network and the same thing happens, except now I can monitor network activity from the “network working” light.
What I see baffles me and would welcome comments, in particular, what I can do to speed it up by a factor of 100!
1.It takes forms about 50 seconds to open; however, this is due to about 40 seconds of network activity BEFORE the form’s load event fires!
2.In DESIGN mode, any minor change to a control for example, results in about 40 seconds network activity.
3.All queries upon which forms are based take well under a second to run over the network.
4.Making an mde file has no effect.
I thought the idea of splitting was to keep the forms/queries/code etc, local to a machine so I don’t understand what all the network activity is about, particularly in design mode. Also, given the actual queries are so fast, just what is Access doing??
Any comments please?
Skeletal
I have a form with a subform (there is no child parent relationship) , when I open the mainform it causes the subform to slow down.
ie If I open the suform by itself - it opens quickly
If I then open the main - it runs quickly
If I then open the main again it runs slow or if I open the subform again - it runs slow - it continues to run slow - until I have opened the sunform by itself.
So something in the mainform is causing the subform to slow down.
Help! Thanks
I am using Access 2003 but it is to slow, what can I do?
View 1 Replies View RelatedHi,
I have a database for booking equipment in and out.
I have the backend stored on a shared drive on our LAN, and the front end on each client machine.
The database has been working fine until just now, it runs very slow and my DCount functions have stopped displaying data.
I have no idea why this has happened, please help?
hi all,
i'm create a aplication in ms access 2000 developer edition. this app comunicate with sql 2000.
we have problem with slow connection provided by access. in headquater wher people use access app it working quickly. sql 2000 is localted at headquarter too. but if i run app in warehouse it working very slowly.
VPN connection doesn't slow. but access can't use all vpn connection capacity but only 1/5 from this capacity?
how can i do something that acces can use all capacity that i have?
example: link is 512 kbps but access use only 100kbps and work very slowly.
I developed a sales database that contains around twenty forms, (in addition to tables, queries, reports and 2 modules). In these forms, I'm setting recordsets, select statements and filters. We recently hired a person to set up a Virtual Private Network. After spending a couple of thousands on a server and countless hours reprogramming the database to have multiple users access the database out in the field, (via Verizon broadband network card), we were unsuccessful to use the database since it was taking too long to download the data or perform any calculations. The database is split. The users have a front end.
The person setting the VPN has suggested that I get rid of all the queries that I have in the forms (which is impossible), or use Visual Basic for the front end instead of Access with VBA.
When I set up the recordsets I use the following setting:
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
I don't know what to do. This program is very complicated and I don't want to have to reprogram the whole thing with Visual Basic, and I don't know how the front end is going to work if I eliminate all the queries, select statements and filtering.
Any ideas, comments or suggestions would be appreciated.
Maritza
At the moment, I have a very simple Access 2007 database with 3 small tables, and 4 small forms.
In two of the forms' headers, I have a text box with contents '=Nz([FirstName] & " " & [MiddleName] & " " & [LastName],"Untitled")' which I took from Microsoft's Marketing Projects sample database.
When I open the two forms with this text box, the forms displays instantaneously except for this text box -- this text box doesn't show its contents for almost a full second. The part of the form that displays right away includes the fields FirstName, MiddleName, and LastName, so it's not that Access is taking forever to pull the row of data...
The database isn't too large. It's about 800K.
It isn't the computer, it's an Intel Core2 Duo E6600 with 4GB memory.
Any ideas on how to speed this up? Is there a totally different way I should be doing this?
As you can problaby tell, I'm relatively new to Access. I've read a few books, but am at a loss why this function is running so slowly. Before, I've focused on web development however this client requres Access for this project. Any help would be much appreciated!
I have a very simple table with about 20 fields. The thing is it has about 360,000 records in it. It has always been a little slow to open, although using a query works pretty fast. There is just this one table, no forms, no reports, no saved queries. Simple database with one table, but it takes forever to open now, seems to have gotten worse. I have compacted and repaired many times and it does not help. The database is stored on a server.
When I click to open the table up it says RUNNING QUERY at the bottom of the screen and then runs and runs for a LONG time. I cannot rely on queries since we want to keep this real simple for users of this data. Just go in, do a FIND or a FILTER and that is it.
Any idea why this is running so slow, or why it says RUNNING QUERY when I attempt to open it, is that normal?
Thanks.
Hello All,
Yesterday only i designed my first form in VB.I designed a tab form as i have to place two kinds for data from the same table separately (for user convience).i was succesfull in that.But the problem is the form is very slow to load........very very slow......
i want to know whether Tab form are slow or i have made any mistake..
any solution please.....