Programming Challenge/minor Brain Movement Anyone?
Mar 31, 2005
Hi,
Ok. So, here's the task
There are multiple employees on a table, each with a period of time for which they worked. I am trying to gather these times together to see how many complete records I have for the time period of 4/1/1999 to 12/31/2004. So, for example,
Employee 1 works from 4/1/1999 to 12/31/2000.
Employee 2 works from 1/1/2000 to 12/31/2004.
(From these two records, there will be 1 complete record of 4/1/99 to 12/31/04 AND 1 remaining record for the year 2000 left over.)
Employee 3 works from 1/1/2000 to 12/31/2004.
(From these three records, 1 complete record + 2 2000 + 1 each for the years 2000-2004.)
Employee 4 works from 1/1/2001 to 12/31/2003.
(= 1 complete + 2 2000 + 2 2001-2003 + 1 2004)
Employee 5 works from 4/1/1999 to 12/31/1999
(= 2 complete + 1 2000 + 1 2001-2003)
And this goes on for all the employees. I need to find out the end amount of how many complete records and remaining dates all the employees have worked. I hope this was at least, somewhat clear. I remember doing something similar to this ages ago in programming class, but since then, my mind has degraded. =P Any help, insights, or advice would be greatly appreciated on how to get this done. =)
I have a lookup form that is basically one combo box that holds my user names (Combo0). I need to use the afterupdate event to open my form (frm_tc1) and filter that users records. If no records are found I need it to prompt me to add a record. If yes is selected I need it to open frm_tc1 in add format, and if no is selected I need it to go back to the switchboard menu.
Here is the code that I have so far:
Private Sub Combo0_AfterUpdate() 'On Error GoTo Err_combo0
Dim stDocName As String Dim stLinkCriteria As String Me![Combo0].SetFocus DoCmd.FindRecord Me![Combo0], , True, , True If MsgBox("User Record Not Found. Do you want to add a new record?", 4) = 6 Then DoCmd.OpenForm "frm_tc1", acViewNormal, , , acFormAdd, acWindowNormal Else DoCmd.OpenForm "frm_tc1"
End If
End Sub
What am I missing? It is always prompting me to enter a new record, even if there is already one in the db.
I have a combo box (cmboType) and the options are (let's say) Cat1, Cat3, Dog4, Mouse5 (but there are about 30). If I select cat (any type)I then (manually) tick a Yes/No box.(chkYes)
How can I make the box picked ticked automatically whenever I select a cat in the combo box.
I am trying (on the afterupdate event of the combo box) something like
If cmboType is like "Ca*" then chkYes = true
Obviously this doen't work - can anyone help me with the correct code?
I have managed to set up a cascading combo box by following this guide: http://support.microsoft.com/default.aspx?scid=kb;en-us;209576
I have two boxes - [BusinessUnit] and [Location]. When I load the form or select one of the [BusinessUnit] I get a box which prompts me to enter a paramter value - [BusinessUnitID]. If I just ok it without entering anything I get to the form and my location select box has the correct locations listed.
What have I done wrong and how do I get rid of this prompt?
I have a date field on one of my forms that gets populated automatically when a new record is created. I also have a subform on the form that lists the tasks that still need to be accomplished this month. Some problems have popped up as people got new computers with new versions of Access:
Access 2002(10.6501.6825) SP3 - No problems. Access 2002(10.6771.6817) SP3 - The Date field isn't recognised on a new record (#Name?), but it's fine on the old records (displays date). Access 2002(10.6771.6825) SP3 - The reminder subform is blank (white), as if it doesn't exist, but the date field works fine.
Any ideas why this might be/how to fix?
Thanks! This is really frustrating. If it didn't work for anyone, I would find a way to fix it, but because it works for some people, I don't know what to fix...
Desperately need a kick in the butt to restart the cognitive functions.
Am attempting to prepare a FY (1 Jul 05 - 30 Jun 06) rollup of services provided. Have done this for years with very little stress. Lost my notes in a melt-down and now it seems I'm lost.
Situation:
Properly normalized recap of various (about 15) services provided to clients over the one-year period. Created a cross-tab query that sums each of the services and displays columns for each service (represented by ServiceID, e.g. 2019, 2020, etc.)
The cross-tab works perfectly, just now need to turn it into a report. Try to use the Report Wizard, based on the cross-tab. Problem is, it returns no fields to select. Changed strategy and attempted to turn the cross-tab into a make-table query, which, I understand would return a spreadsheet-style table. For these purposes, that's exactly what I need and have examples going back 5 to 6 years how this worked wonderfully.
The cross-tab -> maketable works, but it reverts to the original normalized structure, which is not what is needed.
If someone can help to put me out of my misery, I offer my first-born as a small token.
Few days ago I found some VB code which autometically move the cursor to the left of the field (with input mask) in a form to begin with... I mean whereever you click the cursor in the field it autometically comes in the beginning (left)...
I remember i have tried it with a sample form... but now today i can't find it...
I think i lost it...
Today i have searched the forum for this but coudn't retrive it...
I wanted to create a Stock Movement Report, but I am not quite sure how.....I need some help.....
I have these tables: M_Stock (Stock information) T_GRN & T_GRN_Detail (Good Received information) T_IIS & T_IIS_Detail (Internal Issue information)
I need to do a report stating the opening balance, the activities (ins & outs), and the closing balance....like this:
StockCode: A01-001
DocNo Bal B/F In Out Bal C/F ------------------------------------------- I98999 10 0 2 8 56557 8 4 0 12 22222 12 0 1 11
Can someone lend me a hand on this? I have tried to build queries for this, but the running balance keeps counting from the first opening balance (10).....thanks in advance
My report has in each row at least 1 picture which is about 1.5" square when open; however, when trying to scroll up or down the pictures dazzles on and off while the pausing the scroll which moves slowly. If the pictures are removed the scrolling movement is normal.
How can this report scrolling be made more efficient?
I am new to these forums, and am an idiot in Access. i work for a small nonprofit and manage a project through our state Division of Rehabilitiation Services in providing Assistive Technology Services tp their clients (DORS is an agency which provides services to people with disabilities to gain meaningful employment, increase their independence or be successful in pursuing educational goals).
I have been trying to create a database to track the authorizations/clients I serve, some of them have worked but are a bit too complicated in which i am sure can be put together easier (more efficiently) and basically I am afraid to implement it as it would probably fail. Mainly a merger between a number of templates avaliable for free.
What my question is, is how much does it cost (in general) to hire someone to create the database for me. I am basically looking for a database that will be able to store client information (name, contact etc... information about their disability and their goals (employment, education etc...) who their counselor is and their contact information (multiple clients per counselor).
Now each client may have multiple authorizations (I.E. An Authorization for an Evalutation and then for training). So I need service records related to the specific authorization and then the ability to store invoices and reports related to that authorization as well. I would like it to link to a report as I create them in word and save them on a server. I would also like the ability to have the reports sent to the counselor via e-mail from the database.
Hope this makes sense, if anyone would have a generalized going price for somthing like this in terms of hiring someone to complete it that would be GREAT. Thank you,
I have inserted two combo box in the form with lookup in the table. Now I want to select values in these boxes and filter the records having those values on click of a command button placed on the form.
How can it be done without using VBA? Is it possible with use of macro?
I am new to programming. I have to design a form in access which contains basic text fields, a combobox and some datefields. I need to use this form to input data to access table and and retrieve data from access table. Can anyone help please.It is very urgent. :confused:
I want to perform in this query that Opening balance + Dr - Cr = Closing balance.And the next day my opening balance will equal to the date() -1 closing balance. And when todays movement DR and CR take changes it will also take affect on closing balance.Here I have two questions 1st is how can I built a qry that will make todays opening equal to last day closing . Can I have to built two queries or make another table that store last day closing then create relationship and again built a query that perform.
The following code is only for testing the usage of control variable. I want to use set statement to assign the textbox object reference to the varialbe myControl.
When I clicked the button, the system always tells me 'the object reference missing'. It seems the set statement doesn't work, but I don;t know the reason. could anyone of you please spot it out for me?
Many thanks
Option Compare Database Public myControl As control Public frm As Form Set frm = Forms!Form1
Sub Command4_Click()
Set myControl = frm!Text0 myControl.SetFocus
End Sub
Sub Command5_Click() Set myControl = frm!Text2 myControl.SetFocus End Sub
I am new to Access programming. I read in some web page on googling that Access is for developing Desktop database. Books inform that Access projects can connect to Server database.
I would like to know if Access can also be use for Server side programming.
Hi, I am just about to start the implementaion of my A2 computing project, hopefully using both access and pascal to create a bar ordering and stock control system. My question is how and how easy would it be to link both the pascal side of the system and the access application so that they are sychronised? The access application would be used as a front end to my system, and a store of inforamtion where as the pascal code would be used to carry out the necessary calculations.
I know this topic has been discussed, but i could not find one thread on the general purpose of exporting an excel table/form to excel.
I would like to place a command button on my FORM, and let the user click this button and export the data into excel.
I tried using the transferspreadsheet method, but could not corrrectly, if at all get it working, and the help section within access2003 onlu covered importing into access from excel.
Please, if anyone has any links to sites/tutorials/ threads i misssed actually covering this topic/or thier own help, it would be greatly appreciated.
I would imagine that reading through someone else's normalization problems is as exciting as watching paint dry.
But I have seen some brilliant answers presented here so I'll give it a shot. I'll try to be brief and concise. I'm hoping someone can read through this and tell me if I'm on the right track.
I have a table full of employees. A few of these employees are supervisors. I was going to add a field called Supervisor to identify who each employee's supervisor is. I thought about just entering the Supervisor's SSN into that field. (SSN is the key field)
But every time a supervisor is promoted and replaced (quite often), every employee's Supervisor field would have to be edited. Sounds like I ought to create a seperate table with a list of supervisors, so I can replace the SSN just once.
I THINK this new table would have two fields, let's call them CrewCode and SupervisorSSN. So there might be, say, 15 employees in a particular supervisor's "crew", and all these employees' "CrewCode"s will point to their supervisor in this new table. When the supervisor is replaced, you just type in the new guy's SSN once.
Sound correct so far?
Here's where I am overcomplicating things for myself. I don't want it to be necessary for someone to arbitrarily create unique "CrewCode"s, because there are hundreds of Crews in this company but also because it feels wrong to do this arbitrarily when we already have data in other fields to uniquely identify the employee's crew.
Consider this:
The employee record has the following fields to identify which crew they work in. (I'll provide some possible values for these fields, to help put things into context.)
Region = {SE | E | NE} Division= {Sawmill | Woodlands} Site = {Atlanta | Charlotte | Axton | Foxboro} Department = {Sawmill | Planermill | Treeplanting | Harvesting | Trucking | SupportStaff} Crew = {1 | 2 | 3 | 4}
So if I string these values together, employee "John Smith" might work on a crew which is uniquely identified as:
SE.Woodlands.Atlanta.Treeplanting.2
No other crew in the company has that unique string of characters. (I would add the dots myself for readability.)
So my first question is, am I on the right track by trying to link employees with their supervisors using a table like:
That seems like a more elegant solution than arbitrarily assigning a CrewCodes of "00001", "00002", etc.
Second question: when the time comes to actually look up an employee's supervisor, the two tables would have to be JOINed in some manner. Is it possible (and advisable) to perform a JOIN using a collection of FIVE fields in the employee table? Or would I have to combine then into a new single field first, then JOIN on that field? I'd really like to avoid doing that.
I want to retrieve some parameters from file, So for that i want to implement File Open Dialog in One of the form. File Open Dialong is available in Micrososft Common Dialog controls.
When i choose Microsoft Common dialog control from extra toolbars, it gives me error saying that "You don't have licence for using Common Control ActiveX.
I have a form which gets information from the user and upon pressing a button I produce address labels for the user. The form calls a report which does the job. This works. Now the user would like to have parameters like fonttype, fontsize and fontcolour together with margins, row and column space so that the output can be better tailored to his needs. I have inserted combo boxes on the form to get these parameters from the user, but how am I going to insert these parameters in the report layout through programming. What should be the approach?
Here's a tricky little problem I am trying to figure out. I'm sure someone out there should be able to help me without breaking anything. Heres the problem.... I have a form that has an underlying table attached. I have a combo box pull in a pull down list of locations from that table. What I would like to happen is, I would like it so the user will select a location from that combo box, and then click a command button. When they click that button, I need whatever they chose in the comobo box to copy over to a NEW table, which will be referenced later by my program. Say the table being pulled from is called "table1", and the table I need the info to go to is "newtable". I guess it doesn't SOUND hard, but I can't seem to figure anything out on it. I'll let you experts tear it apart and tell me how dumb I am for not knowing. Be kind... I'm still a noob. :D
I have been thinking about this since two day and tried everything, but no luck.
I have a form with recordsource a query. A listbox contains all references and if you choose a reference, everything else on the same form gets updated. Now i want to avoid that two users edit the same record at the same time. So i thought i would make an extra field in my table with a checkbox "yes/no" wich says if the record is in use or not. Now whenever a user edits a complaint i want that when another user is trying to edit the same record a message pops up saying that the record is in use. I can get it to work, but only have the problem that when a user decides to change reference number in the listbox and call upon another record, the value needs to get to fals again on the record he was editing.
Maybe there is another way to now if a user is working on a record.
Anyone that can help with an easy solution on this?
I am building an app which will be used to administer a small company which has several mobile service engineers.
Everything is fine. A fairly standard sort of job until the client asked for a screen showing each engineer for a given day with a bar graph showing planned start and finish times for each allocated job, together with the location! As an added challenge, I need the user to be able to click on the "Chart" and so open another screen showing full details of the job clicked on.
My first approach was to use a chart. Couldn't do it:confused:
The approach I have now used is to create a series of labels on the form "On the fly" to represent each job and the unallocated time in between jobs. I can also run a macro to display the full job details when the label is clicked upon.
HOWEVER, this is a very cumbersome way of doing things to my mind. In addition, sizing of the controls in each day's bar is far more difficult than I expected.
Anyone got any ideas as to how I might achive this task in a more polished way?
