If there are any aspiring `idiot´s guide` writers out there, now is the time to put some practice in!
I have almost completed my database, I just need to get the reporting section done.
I need to produce a monthly report on our casework which basically means totalling certain fields each month, which are exportable to access so I can produce comparison graphs.
I know that you cannot ´total´ text fields except for when you produce a form based on a pivotable. Is there anyway of producing a pivotable from a user-selected date range, and then being able to export that pivotable to Excel for the monthly report? Also, can I put a user-selected field filter in the pivotable?
So for example, my user wants to know how many cases there were for each subject in the month of March i.e. how many health enquiries, how many benefit enquiries, how many pension enquiries and so on. Each of these different types of enquiry are coded in a field-list called ´subject´. Therefore, can I produce a pivotable based on the selection of a date range and the field called ´subject´?
I am against a desperate deadline to get this done now. If anyone can spend time spelling this out in little words for me that would be great. This is the first time I´ve used Access and I ain´t done half bad, but this is completely stumping me!
In trying to write code to automatically hide database window I have completedly stffed up and now have code that closes the database window everytime I startup, hence closing the database.
Does anyone know how to disable VBA or somehow keep the database open??
Recently I've been having a problem with Access (2003) that I can't seem to get around. If I click on "NEW" to create a new query, do some editing, then go to close, it prompts me as to whether or not I want to save, which would be ok, if it would let me choose NO!!
I only get OK or Cancel options. I've NEVER had this problem before. I have not done any updates added any addons. Is there some hidden trick here or some way to get around this cause its really annoying.
I am building a database to help my unit in Iraq. Here are the fields I have so far.
Field 1: Date Field 2: Time Field 3: Location Field4: SIGACT (Significant Act)
Well that is the basics look like. I would like to be able to count the number of times a type of SIGACT occured in durring the day, week, and month. I would like to have this come out in a report I could then take to Excel and graph. The SIGACT has a couple different options like IED, and SAF. Thanks for any help you can provide
"relationship must be on the same number of fields with the same data types" data types are same, numbers.
Primary table has just jobnoID that are all unique (primary table) ie 6907, 6908, 6909 etc
second table "jobs by order" (secondary table) has unique orderno(order numbers) in column one say 69071 and 69072, 69073 and jobno in second coloumn say 6907, 6907, 6907 for all the above.
trying to link JobnoID primary table (one to many) to Jobno secondary table (many)
Also need to note. This database is a purchase order database. so am trying to load in all past data from excel. data is in already. so primary table above with jobno is an auto number so will generate the new jobno for us. but had to copy and paste blank fields into table to get records upto the number we are upto now, ie job number 7112. I have only put data in secondary table for jobno's 6885 through to 7112, and even some of these have blank spaces.
Any ideas? Also how do i do a screen capture and dump in here so you can see relationships etc, which would be a whole lot easier to explain. thanks heaps Alastair:rolleyes:
I have a table (Master) that contains columns such as MsgLocation, MsgName, MsgScript, and MsgTxt. I want to create a form where the user would have an area to type in a 'string' and when they hit enter (or select a button), a (background) query would execute a lookup on master "where MsgTxt LIKE %string%"... For example, if a user wants a list of all MsgTxt values that contain "Please hold" they would type in a box "Please hold" and the query would use %Please hold% in the search criteria.
I don't have a clue on the first thing to do to get this done. And I'm new at this...
I am trying to create a Switchboard of my own that will do everything the database window will, but restrict certain users from accessing certain areas of the switchboard. The only part i am needing assistance on is Adding, Deleting, Modifying and Running my database items from the form.
I already have used that MSysObjects code to create list boxes for my Tables, Queries, Forms and Reports. Now, I'd like to setup 4 custom command buttons on the form that say:
Code:Private Sub AddTable_Click () [listTables.Value = New Table, prompt user for standard New table wizard]End Sub Private Sub DeleteTable_Click () [listTables.Value = Delete this table (with normal "Are you sure" prompts)]End Sub Private Sub ModifyTable_Click () [listTables.Value = Open this table in Design view]End Sub Private Sub OpenTable_Click () [listTables.Value = Open this table in Datasheet view]End Sub
Now, I know that this code is by far not the correct code, but I hope it helps you understand what I'd like to do. Basically, I want the button to correspond with whatever is selected in the list box for that item (except for the add button, it really doesn't matter what the listbox says when the add button is pushed). As far as my list boxes go, i'd like 2 functions for it to do:
1. If a table/query/etc. is double clicked, that item is opened.
2. Allows multiple selections to delete. (i.e. the user can select multiple tables and click the Delete button, and all the tables that are selected are deleted.)
Let me know if all of this can be done from my form. It would be great to get all this working by tomorrow. I appreciate all of aspfree's users help. You guys rock!
I am trying to add a 'keypad' to an access form. The aim is to have an entry system similar to the tills you get in some pubs where when you click on the relevant number it enters that number in the selected field on the form.
I know this is probably a basic question for most of you but your help would be very much appreciated.
we run a family business and i do all the quotations.
i have an access database in which i record these quotations and whether or not we got the job or not, and i have to input all the data manually.
i wondered if i could set a word document so that it would automatically export certain areas of the quotation (like the date, who it was to, what work we are quoting for, how much it would cost, etc) into the correct fields in my database.
It basically is used to create, manage and track Customers, Parts, Work Orders and Suppliers, payments and employees.You simply create a Customer and then create a Work order that contains a list of parts and labour items.
What I want to do is to create a "Kit" of parts so that the user does not have to select each part individually when they create a Work Order. For example, if I use a mechanics workshop as an example, the mechanic may sell his "Signature 1916cc Engine" that consists of 40 different parts and some other items such as labour and other services. In this scenario, instead of the user individually selecting 40 different parts, all he needs to do is select the Kit called "Signature 1916 cc Engine" from a drop down list and all of the 40 parts and other items would automatically populate the correct Work Order Parts/Labour tables.
I would also like the user to be able to select another "kit" that would either replace all parts and labour records or append to what is already there.
I have changed the form called "Workorders" to have 2 buttons. One called "Load Kit" the other called "Save Kit".If the user presses "load Kit" a form will be displayed that enables them to select a "kit" from a list and whether they want to replace all of the existing parts and labour records or if they want to append to the existing records.
In order to create a new kit, the user can simply manually create a new work order by individually selecting each part and labour item and then pressing the "Save Kit" button. The user will then be asked to provide the short name of the Kit and the long description (I have created table called tKit). The new kit will then be saved.
It appears that the "Entered by" field that is linked to the Employee table must not be blank other wise the Workbook is NOT saved....I have tried to figure out why this is happening but can't!
The Work Order Parts are stored in "Work Order Parts" and the Work Order Labor is stored in a table called "Work Order Labor". I have created 2 extra tables called "tKitWorkOrderParts" and "tKitWorkOrderLabor" that I assume will contain the items that make up the kits..
I have a form in my Access database that after trying to add another field to it today, I get the error "Microsoft Access can't create any more controls on this form or report."
I'm guessing I reached some type of limit that I was not aware of. How can I get around this?
I read somewhere that you can have more than one row of tab comtrols on a form. I have searched the forum for any threads or posts on the issue but can't find anything. Does anyone know how to do it?
is it actually possible to place a tab control within another tab control? every time i try to put one on it ends up beneath? would be good to have because even with one tab control my forms still look very busy!!
I have placed a tab control within a tab control (which I have done before). The problem is that the sub tab control appears on every page of the main tab form (encountered this before). I fixed it in the past by placing code on the on open event to make the sub tab control not visible unless the appropriate tab is selected. That worked fine.
I have tried this in the new program and find that when I set the value of the main tab form to 4 (the page index of the tab) the sub tab form will not display at all. However if I set it to zero it will display regardless of which tab is selected. Confusion has now set in :confused: . Any thoughts out there on what I have done incorrectly.
[/CODE]Private Sub Form_Open(Cancel As Integer)
'Me.OrderBy = "RankNo desc" 'Me.OrderByOn = True If TabCtl44.Value = 4 Then TabCtl144.Visible = True Else TabCtl144.Visible = False End If End Sub[CODE]
I have a main form which shows customer personal details including their budget and a subform which shows the costs for each course they book. The sub form has 4 boxes which I use to capture course fee, travel fees,subsistance and other expenses (along with other details such as course code etc). These cost details are stored in the table study_leave_recs in the respective 4 fields. I show on the subform the total spend for each course being booked using a calculated control. On the main form I use four dsum statements added together to show the total spend per customer. =DSum("[COUR_ACT_FEE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_TRAVEL]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_SUBSISTANCE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")++DSum("[COUR_ACT_OTHER_EXPEN]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")
It works ok but is there a better way of doing this? Also is there any way I can use the resultant output to calculate / show the remaining budget (the budget is held in the general table "people"? I'm having trouble using the above statement to subtract the calculated spend from the budget box shown in the main form!!
I would appreciate any help, I seem to have spent ages on this on! Thanks in advance. Regards Peter
I wanna know if it's possible to use an access 2002 form to create some controls(icons) on top of a picture (a map) so that i can click those icons to display some info. The trick part is that those icons positions are generated by x,y coordinates from the db itself. Plus a zooming control wold be a plus...
I have a pretty standard form to enter data about contracts. Depending on the type of contract, the user will answer a yes/no type question, and the result will determine what set of information needs to be entered next. Since the variables are grouped intrinsically, I would like to make them visible or not depending on what is necessary. I know how to do this with just one control, but not several at a time. Is there a way to treat them as a group, and have the form make the group appear/disappear as needed, or do I have to list out each control everytime i need to do this? Any help would be much appreciated.
I am currently using a popup Calendar in Access "Calendar Control 9". I mousedown on a field and the Calendar pops-up. I select the date and it populates field1. So far, so good. After I select the date I need I would like for field2 to populate with a date that is exactly six months out from the date I selected for field1. Any suggestions. I hope this can be done as it would help immensely.
Recently I designed a form with several controls including both TextBox and Combox. Before I submit data by clicking the button, I use VBA to validate the values of those controls (textbox and combox). If the value is not valid, the system will eject a msgbox and automatically set the focus on that component. In order to tidy the code, I use a GoTo statement, which refers to a group of code to display the message due to the value of parameters
But it seems VBA doesn't recongize the control I set through the parameter.
The code as the following:
private function validation () as boolean
Dim ErrorMessage as string Dim ErrorComponent as String
...... ErrorMessage="Please select the shop Name" ErrorComponent="ShopName" // ShopNameis a combox
GoTo ExitFunction .....
...... ErrorMessage="Please set the start date" ErrorComponent="StartDate" // StartDate is a Textbox GoTo ExitFunction .....
As I tested for seveal time, I'm sure there is something wrong with " msgResult = MsgBox(ErrorMessage, vbOKOnly, "Error Message") Me(ErrorComponent).SetFocus "
I want to use a tab form where the user can click add, search, and edit records all from 3 tabs. The problem is I only want to be able to add data on one form, edit data on another, and just read only on the search. This seems to cause a dilemma, how can I specifiy them to add, edit, and read only on one form using tabs?
Hey, I was messing about with tools>startup because I wanted a form to appear whent he database was opened.... I unchecked far too many options in there, and now I cannot design and modify my database anymore... it just goes straight to the form, but i cannot get anything back in design view.. is there a way to fix this??
In my program I have a tab control with three tabs in it, how can I tell which tab currently has focus (which tab is activated) in VB? I checked all the properties of the tabs, and there's no GetFocus or Value or anything like that, so how do I do it?
I have searched the forum on this, but I can't find an example of this problem.
I have a data entry form with a two-page tab control. One is for company info and the other for contact info. The recordsource is a query based on the two tables. Initially I had the page set up as a rather large form with a subform and decided to convert to the cleaner looking tab control (I used copy and paste to transfer the controls).
This form worked initially, but now I have changed some setting so that it doesn't work anymore: I can enter data properly on the first tab, but not on the second.
I have tried changing the various form properties but I must be missing something obvious. All my tab-less forms work the way I expect them to.
What I don't understand is why one tab works and not the other.
