I have a main form [mainform] with several subforms on it, two of which are on a tab.
On the second tab [page79] there is a subform [subformlist] which has like 25 checkboxes on it, any combonation of which can be picked by the user.
I need there to be at least one pick required of this subform, else the record won't show in a general query I have, since the table the subform is based on is linked to the record.
Is there anyway to make making a choice of one of at least one of these checkboxes checked a manditory thing before the user can close or move on to the next record?
I've tried making an "Other" box that on the Table has a default value of "True" which I thought would cause it to show in the query record...to no avail.
The only true way to make this work is to have it mandatory that the user pick from AT LEAST one, usually more before the record is complete.
Any help? Other than writing a MASSIVE If...Then statement...?
Or is there a way to make it so the choice isn't required for the record to show up in the query?
I'd post...but my db is too massive, even zipped.
:(
I have set up a quesry based on 2 forms 1 orders and 2 order details. i have the set a criteria on the report to print a order number
i have then set up a report based on the query. Then i have created a command button on my form up untill here it is fine the problem is when i create the order and send it to print the criterai box pops up which is ok but because i am using a touch screen it will not be viable for the user to enter the number each time he prints i would want it to pick it up from the current order id.
Is there a way to do this as i am sure all invoice systems and everything should be working like this or is there a better way to do this or am i doing something wrong i have attached a screen shot of what si going on
I'm having issues with getting UK dates to show in access.
I created a table and then undertook an insert with the following:
docmd.RunSQL "INSERT into tbl_test([dates]) VALUES (#01/06/2005#);"
Note that my machine is in UK local and the format of the field is short date. The date in access is 06/01/2005. ie 6th Jan , not 1st June as it should be
If I set up a query via the query wizard to look at this field then I see that the date is shown in US format.
I tried to use the following in the field section:
result: format(dates, "dd/mm/yyyy")
and the date still showed up at US ie 06/01/2005. argghhhh. (assume due to the fact that format returns a string and I'm looking at a date field)
I am trying build what I thought was a super simple data base, but this is driving me crazy.
I have a group of building maintenance manuals (12) each of which is subdivided into sections. I have created a list of all the section headings and respective page numbers and have imported them into a table (tblMASTER ) with these fields:
MasterIndexpkf SectionNumber (unique sequential number 1-311) SectionName PageNumber
I have a second table (tblCROSSREF) with these fields:
The two tables are linked in a one-to-many relationship on the respective MasterIndex fields.
I also have a query (qryMaster) which includes ALL the fields in tblMaster which serves as a lookup to the SectionName field in tblCROSSREF.
What I want to do is to select a section number in tblMaster and enter all the possible cross references in tblCROSSREF by selecting the available section names from qryMaster.
This seems to me to be sooo simple, but I am having a real headache!
My primary problem is that when I use the lookup function in the SectionName field (in tblCROSSREF) it returns not the Section Name, but the Primary Key Field number.
This thing is making me feel like the AFLAC duck. Can anyone give me a clue about where I have gone wrong?
I've been searching in vain for a solution to a relatively simple bit of mathematics that I’d like to Access to undertake.
It involves interpolating between data, as illustrated in the following example. Basically have several items, and let's say different "prices" dependent on the number of units purchased. The relationship between the “prices” is not a straightforward formula, therefore I need a query to return an interpolated price for a given number of "units".
DATA ItemID Price10Price20Price35Price50Price100Price 1000 1£70£135£200£315£800£6500 2£23£31£61£89£210£1500
OUTPUT ItemIDUnitsPrice(int) 117? 231?
So, for example if Units <10, Then Price(int) = Price10 if Units = 17, Price(int) = Price10 + (17-10)*(Price20-Price10)/(20/10) if Units = 32, Price(int) = Price20 + (32-20)*(Price35-Price20)/(35/20) if Units >1000, Then Price(int) = Price1000
I'm aware that IIF statements might do the job, but I have 15 different 'Price' points, and don't particularly want to nest them all.
This problem was solved previously in Excel using a clumsy set of VLOOKUP/HLOOKUP functions in conjunction with If statements.
I got a headache to changing my form's filename... i am totally a newbie in Access while in the few months ago... so i just create all those form or table name's for the way i like. But now i had already know how important of management of the filename because when u r using the module to write the code u will be headache to look for the filename that u want in the code. So that is important to add 'tbl', 'frm', 'Qry' infront of the filename.
So do anyone have a good way to edit all the file name with all match to the other in the module with a quick way rather then one by one editing (really tired for one by one) Thanks!!!
I need some help. My brain just can't grasp how to do this.
I have an Access DB. I sell vinyl records. I have an order form. When a customer orders a record, each vinyl record has a unique ID#. No two vinyl records are alike. I have four tables: Customers, Orders, Inventory, & Order Details. I have an "Order Form" form where I input all the info. It's an all-in-one form where you can view and data entry in one screen. The form has two subforms in it. Here's the problem: If a customer orders more than one item (i.e. a vinyl record), each Inventory_ID (representing one vinyl record) is associated with one Order_ID which is, in turn, related to one Customer_ID. If I have one Order_ID that has multiple Inventory_IDs (i.e. has ordered two or more vinyl records) that belongs to one customer, how can I calculate that total cost? Currently, I have a calculated field within my all-in-one form that calculates only the price of the Inventory_ID that one currently sees on the subform. The Inventory_ID item has a set price, for instance $5.00. If I scroll through the ordered Inventory_IDs, the calculated field will only display the price of the product that you currently see. It's not really calculating anything, I guess. It's just bound to that field, but I need to sum up all the price fields for all the Inventory_IDs that are associated with one Order_ID & Customer_ID whether it's a query or calculated field...I just need it done so that it'll display on this form. Any help would be much appreciated. I can show you whatever you need to see. Thanks in advance. David
Ok, here's something I think I've done dozens of times, successfully. Now I can't get it to work.
I have a Parent Form with three Subforms (based on 3 separate, linked, tables).
I have a combo box on the parent form which looks up a piece of EQUIPMENT by its unique ID and the other two forms display equipment DETAIL and SERVICE HISTORY related to that equipment. It works just great until I use the ParentForm's navigation buttons. The two Subforms change to display the information relevant to other pieces of equipment, but the combo box continues to show the original look up.
Yesterday I posted a question and rec'd good advice but as usual I don't think I explained myself very well. As the attached picture of my Form shows, I have a checkbox for each Age Group under each Team. I originally had a query that would come up for each Team's particular Age Group and then the required data copuld be input. This leads to too many querys clogging things up. I tried to copy the SQL code of one of the query's to the On_Click event of the checkbox but as I have been informed SQL is different to VB. All I would like to know is how to convert the SQL to VB coding so that I can continue to do the coding for each Checkbox. The SQL code for the first checkbox is:
SELECT tblPlayerRegister.Surname, tblPlayerRegister.[First Name], tblPlayerRegister.Age, tblPlayerRegister.[D'n], tblPlayerRegister.G1, tblPlayerRegister.SP, tblPlayerRegister.Age2, tblPlayerRegister.G1A FROM tblPlayerRegister WHERE (((tblPlayerRegister.Age)<11) AND ((tblPlayerRegister.Club)="Beaconsfield")) ORDER BY tblPlayerRegister.Surname, tblPlayerRegister.[First Name];
If there is an easier way, by all means let me know. Any assistance would be greatfully appreciated.
I have a DB in .mdb format which works perfectly and when all the code is compiled it results in no problems. However. When I convert this to a .mde file, it doesn't work at all and it suggests that there are compile errors where there weren't any before! Please can anyone suggest what the problem might be as I don't particularly want to run this as a .mdb. Thanks
I want to 'append' individual records from 2 tables and place in an archive table or within another database, whichever is the best option. I then want to be able to 'delete' the relevant records from one table.
The tables are tproperty and trents. This property paid rents but has since been sold. Therefore it no longer belongs in the database, however client wishes to keep details of the property/person/and rents paid in past, for any future ref. These tables are linked in relationships to tlessee and tbilling. I've read books/notes/looked on here for inspiration and the right direction! If I choose 'append' which seems pretty straight forward then a 'delete' query, how do I choose only one record? Is a make-table onto a different database a better option? It would appear that the whole table is copied over? Can't understand the issue about auto-numbers being copied over? Do I have to use an append/delete query for each individual property that's ever removed? I'm at a loss! :) Thanks
Debug.Print sSQL Set conn = CurrentProject.Connection conn.Execute sSQL GoTo ThatsIt ErrorHandler: Select Case Err.Number Case -2147217908 'command text not set Case -2147217865 'cannot find table Case 3021 'no records Case Else MsgBox "Problem with cmdArchive_Click()" & vbCrLf _ & "Error " & Err.Number & ": " & Err.Description End Select ThatsIt: conn.Close
End Sub
I have no problems making the following code work except if there is an apostrophe " ' " in either the txtGivenName or txtSurname . For example O'Keefe it shows an error but not O"Keefe! How do I fix this?
I have got the following query but when it actually inserts the date, the dates will be "08/01/2005". Month and day will be swapped if date is less than 12. I don't know it's happening. It's driving me crazy.
It will insert "8/01/2005" for the first date and "21/08/2005" for the second date. my date format is set to australia and i have set the same in my asp page as well. it is giving the same result even if i try direct from the query.
I have a VERY simple select statement that I am using in a pass-through query: "SELECT * FROM dbo_vReturn;", but I cannot get this to work. Each time I run the query it generates an error: "ODBC call failed - Invalid object name "dbo_vReturn" (#208)".
The dbo_vRteturn is a view in the backend. I can open this table/view from Access using an ODBC connection but when I try to run the pass-through query it falls over!
Does anyone have any idea what is causing this?:confused:
Hi all, this is my first post, I'll try and make it a good one :-)
System: Windows 2000 Office 2000
I have a small network of PC's here which have access to our central database. I have a simple form created using the label wizard which is used to print address labels for mailing lists. On most of the PC's here it works fine but on one or two (which have no obvious difference in thier setup) I get an "ODBC--Call Failed" error and it won't run the report???? I have puzzled over this for a while when I decided that I would remove the Trim function from the lines of the address fields on the label, these are put in automatically by the label wizard. This stopped the error message and the reports now run. I don't know why this is happening but I am hoping someone will.
I have a large application in Access 2003 which comprises a main program file and 4 linked data files which between them contain around 50 tables. The prog file sees the data files through linked tables. Everything works well until I do a compact and repair on the data files then unpredictable problems arise. The compaction is only ever done when all users are off the system and one file at a time. Having done a compaction this week I found that one of the tables appeared corrupt when I browsed it via the program file (the table comes from one of the files which contains other tables working OK after the compaction). The data was garbled and what could be recognised was not in the right place - appears to be moved a couple of columns out of place. There were some fields which reported errors also. However when I browse the table in its native file it is perfectly OK. I can solve the problem by re-running the Linked Table Manager in the program file but that is hard work for 50 tables! This has happened previously when I compacted and I would be better understanding the problem than just fixing it! Can anyone help please?
I now want another query which returns the row which is always the latest STATUSDATE. This is what I have done so far:
SELECT qryFirst.PROBLEM_ID, Last(qryFirst.STATUS_ID) AS LAST_STATUS FROM qryFirst GROUP BY qryFirst.PROBLEM_ID;
However, when I want to add qryFirst.STATUSDATE to the SELECT clause, I get an error „You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)“
I need to see what the STATUSDATE is for each of the records returned in the second query. How do I do this? Please!!!!
I have a database with a series of fields, all set to a String datatype, that I want to be able to fill and then use with MS Word as a mail merge data source. This function lets you query a MS Access db and use the results to fill in predefined spots attached to the various data values in the table. This is great for my needs however a large percentage of times that I have a dash in the value being queried it does not return any results. For example, 01-01 would return no entry found even though I can clearly see it in the database, however it would work fine if I queried for 0101 or any other value in the given field.
Basically I'm trying to figure out why dashes would cause problems. They are obviously fine for being stored in the access database and don't cause any troubles until they are queried for, also retrieving data containing dashes is fine so long as that value is not the one being queried for.
I basically can't avoid the dashes in the data for this setup and as such hopefully can figure out why this happens and how to get around it. Thanks for any help offered.
The ones with stars are "type" tables. All possible IP's are in the tblIP, models in tblModel, office's in tblOffice etc.
Maintenance types are just like "fuser replacement", "toner replacement", "turned on computer" (IT joke)
anyway, On my main form. If I open the form and click the button I made to go to the previous record or next, I am fine.
If I choose an office, or start a record, I get a message that I have to have a record for IP chosen, that it can't leave this new record until it has an IP because of referential integrity.
If I choose an IP and nothing else, I can leave the new record. If I don't choose IP, but choose everything else, I cannot leave the record. AHHH!!!
The thing is, all my combos on the main form are (as explained above) FK's to lookup tables, and all set up the same: all with referential integrity, 1 - many, cascade updates and deletes.
This 1 table is the only one acting this way.
I am including a print screen of my form, as a visual sometimes helps.
If anyone can help me to fix this, I would really appreciate it. I have never had this problem before.
Hey firstly im at the end of the project ive spent months on and thats a MIS datase software. with all the bits and pieces and expert advise ive managed to learn a whole lot of things through this forum.
This is the problem i am having though;
i have a form that has two subforms embedded and since i have to use link tables because i placed the database file over a network somehow the loading seems to take forever, "it actually makes access inactive for about 50 seconds"
i would like to prevent the subforms from loading with the main form until after the main form is up and then i just click a button that makes both of them "appear" and load.
I know my way around Access reasonably well but am by no means an expert. I have created a system that I use in my business for hardware/software requests, and was told to do it in Access. I did ask that we do it on SQL Server with a Web Front End, but we are where we are.
Now, I have been on a SQL query training course so I know the basics, but am a bit confused on this one since Access has been added to the mix.To make matters more frightening, this is Access 2003!
[T_Hardwarelist] ID (primary key, autonumber) Make Model Description
[T_UserHardware] AT Reference ID
[Request] is inner joined to [UserHardware] on the AT Reference column. [UserHardware] is inner joined to [Hardwarelist] on the ID column.
The select query I have is basic and does just what it says on the tin; it shows who has what hardware.The query:
Code: SELECT T_UserHardware.[AT Reference], T_UserHardware.ID, T_HardwareList.Make, T_HardwareList.Model, T_HardwareList.Type, T_HardwareList.Description FROM (T_Requests INNER JOIN T_UserHardware ON T_Requests.[AT Reference] = T_UserHardware.[AT Reference]) INNER JOIN T_HardwareList ON T_UserHardware.ID = T_HardwareList.ID WHERE (((T_UserHardware.[AT Reference])=[Forms]![F_Request]![AT Reference]));
However, when it comes to adding NEW hardware to the requestI have a form called F_Request.I have a sub form called SF_Hardware.The SF_ Hardware subform runs the aforementioned query, and shows what hardware is assigned to the parent request.If I add new hardware via a dropdown on the form, it adds it in to [T_UserHardware], but it also adds another value on the [T_Hardwarelist] table. E.g. if I add a "HP Compaq 8200 Elite" (which is stored in [T_Hardwarelist]) it adds it to the [T_UserHardware] table correctly, but creates a second (third, fourth, fifth) entry on the [T_Hardwarelist] table for the same kit.
I think its confusing because of using a select query and might have to run a 2nd query on save or something like that, I had a working version before that had the make, model etc in both tables and didn't have a 3-way relationship. It'd be nice to have the request table with the high level info, a hardwarelist table with our catalog of kit, and a userhardware table just containing the ID and Reference for the hardware/request rather than duplicating the information.
I've been experiencing a very frustrating problem recently.
One of my databases crahes randomly, and I'd like to know if some of you have tips to helps me.
The database is splited in front-end and back-end, both on the server. I know keeping the front-end on the client PC will improve performance, but due to a high number of users (about 60 to 80) and the need of doing frequent updates, installing the front-end on each machines is not an option for now.
Users are not all directly connected to the server. Those who are directly connecte4d to the server (in building A) have no problem. Those who don't (building B) have extensive lags on opening (about 30 seconds), and on about anything that calls an heavy query, most often leeding to a crash. Access display the message telling that the datbase needs to be repaired and restart.
I tryed several thing to improve my queries, like eleminating most of GROUP BY and UNIQUE clauses and avoiding the use of functions.
I have a lot of conditional formatting in the forms, but I don't think this is the problem, since it is executed on the local machine (as far as I know).
My theory is that the lags cause some call to the DB to timeout. I just dont know how to fix that. Is there an option to higher the timeout delay?
My other theory is that the problem is caused by dropped packets during the connection to the DB. I tryed downloading large files (100Mb to 250Mb) from the server several times and never had a problem. Although the connection is quite slow, the ping is very small, like below 10ms to 20ms. Is there better ways to test a network connection, if possible without installing software (limited rights on the machine)?
Would using Replicas be a solution? What are the pros and cons of it? I never used them and know very little about it. My idea would be of putting a back-end on both servers, but I guess the data would not be as live.
BTW, I am using Access 2002 (2000 file format) on an XP SP2 PC.
Thanks to all of you patient enough to read all of this! I Hope some of you have some awnsers, tips or tricks!
I am creating an IF CASE statement in SQL that basically takes the 2 last characters in a field and if they happen to be 'HX' then a special rate is used to calculate the cost whereas if their is no 'HX' as the last 2 characters of the field then a different rate is applied.
(Right([BOARD SPEC DATA],2)) has always worked flawlessly in SQL to render similar solutions. However, unfortunetly the weekly extract that comes into us permits for massive amounts of blank but real spaces after the data in [BOARD SPEC DATA] field so what ends up happening is that (Right([BOARD SPEC DATA],2)) yields a (SPACE SPACE) value even thou many of these values should be 'HX' as they are the last 2 real non-space values at the end of the field.
If there any easy way to fix this keeping in mind that this is how we get the extract everyweek so a search/replace on spaces won't work ... is there any way I can use a Right command but ignore spaces so that it simply renders the 2 real characters at the end of the field as oppossed to blank spaces?
