Modules & VBA :: Allocating Work To Individuals
Oct 19, 2013I have a database that uses a table to capture work by reference number. I would like to be able to allow users to only be able to see work they have been allocated.
View Replies
ADVERTISEMENT
Allocating A Set Number To A Runner?
Jul 26, 2006Hi all,
Only discovered Access a few days ago, absolutely brilliant tool.
Have ordered 4 books (most with the words "idiot" or "dummies" in the title!), but would appreciate some advice with a problem i can't seem to get my head around.
As you can see from my attachment, i've got a lovely database that i made.
Well, i say made.. downloaded from Microsoft templates and turned it into my own. Very proud of it!
I'm a co-cordinator for the Tresco Marathon - a full marathon run around a 2 x 1mile island all for charity (Cystic Fibrosis Trust). This database is supposed to be an efficient way of keeping track of all the entrants, if they've paid registration, if packs have been sent to them, their eventual finishing times, their running numbers etc.
I've managed to do the most basic operations in Access - a nice form that keeps allows you to add a runner, and all their details.
The problem i'm having is...
Once the few hundred applicants have been entered, we then have to assign them Race numbers... but i can't figure out how do to this.
Ideally, something like a form openable from the main form (already have a tab showing an empty box for a manually entered race number - this is showable by choosing their status as "Active") showing 1 - 130- and then whether they've been assigned to a runner already (and their name), or whether they're available.
I really am stumped on this one.
Would appreciate any help or advice in the best way to do this!
Allocating Records Per User
Oct 4, 2007Anyone any ideas, please?
I have a table of name and address data as a back end and have put a front end of each of 12 "user" computers on a workgroup-based small network. Each user can look at on twelfth of records, the range being specified in a query on each front end PC, which, of course, means that each front end has to be slightly different.
I wonder if it's possible to assign some sort of owner system so that whenever "user1" logs on he/she gets the first twelfth of the records, user2 gets the next set and so on.
As a separate issue, can I somehow work out the start and end records needed for each user by automatically dividing the total number of records by 12 (since the master table can have a different number of records each time we get new data). At the moment I am calculating (using Excel) and inserting the ">1200 <2400" for each user.
Any ideas would be gratefully appreciated.
Allocating Values To A New Field
Dec 8, 2004This may seem a bit of a strange one but I hope someone can help.
SCENARIO
1. Add a new column to a table that already has data entered in it.
2. Index this column (NO duplicates) but the field is not a required one.
3. Set it as an Auto Number.
Is there anyway of applying the autonumber to the data already in the table?
i.e. for row 1 in the table the new column gets a value of 1, the second row a value of 2 etc.
Many Thanks
Steve
Re-allocating Fields In A Tables (appending)
Jun 14, 2005Hi
Firstly let me thankyou for your help because I feel this may be a difficult question.
I have a stock control db that records transactions through purchase orders, which allocates each line item (product) in a purchase order to an employee and a project it is destined for. I have three tables, one for purchase orders, one for transactions and one for products.
I then have a seperate table for stock in and out with a two other look ups for projects and employees.
Now sometimes a product may become a stock item as it is in surplus and not used for the project.
My problem is how do I re-allocate the product as a stock item then when it is used allocated it to another project.
Basically we use an internal unique number for each purchase order and i have a query set up that will recall each product in a purchase order. The reason for this is to save time updating goods recieved when an order turns up as i call all the item in a purchase order in one go rather than going to each product one at a time to update.
I think the db may be to advanced in the set up and feel a restructure of the db is the only solution but hopefully i not. I am no means an advanced user buit have done ok so far in seeting this db up e.g. my knowledge of vba is limited etc.
This has only just come up in what is needed so it is an after thought, however is very important for the operation of the db as a stock control system.
All advice much appreciated in advance.
scott
Queries :: How To Select Individuals With More Than One Skill
Jan 2, 2014How do I select individuals who have, for example, both access and excel skills? I have a table of candidates and child table with the list of skills.
I have a form (called "search" at the moment) with a subform (called "skillslist subform"). The subform's record source is an empty table called "searchlist" with a single combo box using the master list of skills as the source. This is so I can select the skills I am looking for.
Then there is a button that (1) saves the records and then (2) opens the query, as follows:
Code:
SELECT SkillList.CandidateID, searchlist.searchlist
FROM SkillList INNER JOIN searchlist ON SkillList.Skill = searchlist.searchlist
ORDER BY SkillList.CandidateID;
Is this problem need to be solved with a subquery?
Reports :: Email Report To Several Individuals
Apr 3, 2014I want to be able to email a report to several individuals and have only the data that pertains to that person attached in the email? Is this possible?
View 1 Replies View RelatedForms :: Distinguishing Between Two Same Surnamed Individuals
Feb 16, 2015I am having difficulty distinguishing between two same surnamed individuals.
I am providing my sample which provides my provides my problem.
I have two same surnamed individuals but when I highlight them in the form the same data appears in the subform.
Email Reports Using 2 Addresses For Several Individuals
Jul 14, 2014In my database for our charity there is a field for email addresses. Most of the contacts have a single email address and we can send them their individual reports without any problem. However a number of the contacts have 2 email addresses and these are stored in the email address field with a semicolon between the 2 addresses. However the system can't send emails to these contacts! Are we storing the addresses in the wrong way? Or is there some reason why Outlook won't regard the 2 addresses as separate email addresses - does it think that it is one strange email address that it doesn't recognise?
View 14 Replies View RelatedGeneral :: Allocating Race Result Position?
Mar 31, 2013I am creating a DB for race results and have a field which calculates the elapsed time based on the start and finished times. What I want to do now is populate a field "position" with the finishing position relative to other competitors elapsed times in the race. But I cant see how to do this.
I understand I would probab;y have to use some sort of query but not sure how to create it.
Modules & VBA :: Get Work Sheet Name And Last Row?
Apr 14, 2015i have the following code to get excel sheet name and last row number it work fine with no problem but when i go to next record in my form and press the button agin it produce error 91 "object variable or with block not set"
Code:
- this code to get sheet name and last row in column A
-General declaration
Dim LastRow As Long
Dim SheetName As String
'Declare Excel file
Dim strPath As String
Dim ApXL As Object
[code]....
Modules & VBA :: Using Recordset To Work With Query
Dec 15, 2014I want to use Recordset to work with query but get the error "Run-time error '3061' too less parameters. Expected one".
Code:
Dim rst As Recordset
Dim strSQL As String
Dim krojenie As String
strSQL = "SELECT * FROM powtorzeniaDoKrojenia"
[Code] .....
Modules & VBA :: Why Does Emailing A PDF Work But Saving A PDF Doesn't
Jul 24, 2013I'm trying to figure out why I can email a report as a PDF, but not save it as a PDF using the code below? When I save, I get the dreaded error 2501.
The searching I did for this said to uninstall and reinstall printers (I've done that - seems to have no effect), or took issue with filtering the report with a where clause (but if that was an issue, why does it email a PDF just fine?).I'd just like to be able to save a report as a PDF to a specified location.
CB
Developing in: Access 2010; Win7
For use in a mixed 2007/2010 XP/7 environment
Code:
Private Sub EmailDietCardBtn_Click()
On Error GoTo MyErrorHandler
Me.Refresh
Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim stEmailMessage As String
Dim stCaption As String
Dim myPath As String
[code]...
Modules & VBA :: How To Make Array And For Each Statement Work Together
Aug 23, 2013how I can make an Array and For Each statement work together. I normally don't use either, but it would be good so I could write less code where plausible. Here is the example I have:
Code:
Dim Named As Variant
Dim Ctl As Control
Named = Array(Me.Namee, Me.ID, Me.Title)
[code]....
I am unsure how I include the array in this statement. I have tried replacing "Me.Controls" with the array name, but I get a 424 runtime error (no object). What I am trying to accomplish is for each item in the array I want to make it not visible if it has a null value.
Modules & VBA :: Switchboard Form Doesn't Work
May 27, 2014I have developed a project in Access 2003 and it works absolutely fine. There is a switchboard form too and that works fine as well. But now in our company we are moving all the projects developed in 2003 to 2010. And so I have noticed the switchboard form doesn't work in Access 2010 the way it works in 2003. The Switchboard form doesn't display all the options in 2010.
View 5 Replies View RelatedModules & VBA :: Repaint Doesn't Work Until End Of Code
Jul 31, 2015I have a process which runs lots of slow append queries, so I want to reassure the user of progress, by changing the font colour of labels to green when the relevant queries have finished. This is the code I'm using
Code:
'Update the older data if that option is selected
If Me.Menu_YearOption.Value = 1 Then
DoCmd.OpenQuery "2-10 Append FY1112"
Me.lbl1112.ForeColor = 32768
Me.Repaint
DoCmd.OpenQuery "2-12 Append FY1213"
[Code]...
Each query takes about a minute to run, as it gets data from a sharepoint server, but the labels don't turn green one at a time as expected. Instead, I get the spinning wheel until all the queries have run, then all the labels turn green together.
Is there something else I should be doing rather than Me.Repaint ?
Modules & VBA :: Unable To Get The Public Variable To Work
Mar 27, 2015I have a module that has a global variable defined as shown below
Module: initGlobals
-----------------------
Code:
Option Compare Database
Option Explicit
Public strGlobalUserId As String
Sub initvar()
strGlobalUserId = "Myuserid"
End Sub
This is invoked in a login form("frmLogin") and it displays the above value before I replace it with the one that is accepted in the form.
Here is the code from the login form
Code:
strGlobalUserId = Me.cboEmployee.Value
When the password is successful, I close the login form and open a form called "frmMainForm". In the form_open event of the "frmMainForm", I am trying to display the user id using strGlobalUserId but it just has spaces. What am I doing wrong?
Reports :: Exclude Individuals From False List If They Are In True List
Jul 15, 2013I have created my tables and form and am now trying to run reports to organize the data. I have figured out how to group the individuals by group and treatment, but can't figure out how exclude individuals from the final list if they are already in another. As background, some of the individuals are eventually excluded from the experiment, though I keep the initial data. When the exclude individual checkbox is checked ("True" on the report) I do not want the individual to be listed in the "False" list, even though there are entries for that individual when that checkbox wasn't checked (when it was "True"). This is so I can get an idea of the current totals in each group.
Implant Period
>Treatment
>>Exclude individual?
>>>Individual ID
I feel like this should be a fairly simple task, but I cannot figure it out... Maybe there is coding to exclude individuals from the "False" list if they are already listed in the "True" list?
Modules & VBA :: Get Open Args To Work While Opening A New Form
Jun 25, 2013I have tried a few times to get open args to work while opening a new form.I never could get it to work.
View 4 Replies View RelatedModules & VBA :: Transfer Spreadsheet Won't Work If Excel Is Already Open
Sep 17, 2013I am extremely new to writing code, but was able to successfully write code that transfers the data in my form to and excel spreadsheet template and saves it with a different name. The code works perfect and without issue as long as Excel is not running. However if Excel is already open the template remains blank and a warning message appears saying that the form is locked for editing. Below is the code I used.
Private Sub ExcelClear_Click()
Dim xlApp As Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
[Code] .....
Modules & VBA :: Date Filters - Work Load Criteria
Mar 25, 2015I know in Access that you can filter your work load criteria for each employee which is fine using Como boxes to filter down specific criteria for that employee, however I'm trying to achieve it with date filters between certain dates, and it works but ends up filtering the dates for every employee, I just want it to filter that specific employee .
This is what I have so far.
PHP Code:
Private Sub Date_Filter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#mm/dd/yyyy#"
If Not IsNull(Me.txtStartDate) Then
[Code] ....
Modules & VBA :: Adding Time With Work Hours Function
Sep 13, 2014In my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
Code:
Me.Date_and_Time_Complete_Package_DUE = DateAdd("h", [SLA_Time], [Date_Complete_Package_Received])
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
Code:
Me.Date_and_Time_Complete_Package_DUE = WorkHours(DateAdd("h", [SLA_Time], [Date_Complete_Package_Received]))
I have a Workhours module and it is used for a lot of things and it works perfectly.
Modules & VBA :: Passing Parameters To Report Doesn't Work
Feb 23, 2015I have a query that sums up the number of parts used. This works fine.
I want to be able to limit this query to parts used after a specific date.
I have in my report
DoCmd.OpenReport "Part Totals Report", acViewPreview, , "[Part Date])>= " & SQLDate
The report is bound to a query that has 2 group by fields, 1 count field a a further field, a date field ([Part Date], that I put a default criteria on. This field is not displayed. If I don't put a criteria on this field disappears when I close and open again.
I pass a date to the program via a form and this ultimately ends up in SQLDate. When I run this I get promted to enter [Part Date] even though I'm setting it equal to SQLDate above. I can out garbage to a proper date in here either way the report picks up the default date entered in by the query.
1. Get rid of all of the parameters off the query.
2. Then you can use the Where Clause of the DoCmd.OpenReport code to specify the parameters based on your variables.
1.Not sure what this means but when I get rid of the criteria for the parameter the field disappears (I'm setting the show field to no as I don't want totals group by date). Getting rid of the field gives me all parts used.
2.I think I'm doing this in the above but will bow to superior knowledge!!
or is it I can't pass a parameter to a report run by a query that is grouping fields together to produce a count.
Incidentally once the report has been run (albeit with the wrong parameters) and I go into design mode and look at the property sheet for the report the correct filter is there (i.e., the date that has been input) but it quite clearly ignores this.
Modules & VBA :: SQL Where Clause - Open Form To Correct Work Order?
Aug 25, 2014Windows 7
Access 2013
I've been trying to work up a where clause that is generated by a button click event on a report. The workflow that i'm trying to obtain is as follows:
1) A report is run to determine the remaining work orders that need to be processed.
2) A button that is placed on that report is to be clicked, taking the user to the form associated with that work order, so it can be processed.
What i've been able to do so far is capture the unique ID for the work order and then print that in a message box. I can then open the form.
What i haven't been able to accomplish thus far is to open the form to the correct work order.
Things I've tried : I started trying to use the macro with the search for record option and using the where clause. Not successful. I am a little more comfortable in using vba so i switched to that pretty quickly.
Code:
Private Sub btnJobEntry_Click()
'GOAL: open the work order form to the correct entry
'METHOD: store the uniqueID to a variable, then use that in the open command's where clause
Dim strJobID As String
'store the unique ID in the variable
[Code] ....
I've put the strJobID variable in both the filter and where clause sections of the DoCmd but it just opens the form to the first entry. I'm fairly confident i'm not applying the filter/where clause correctly by using the incorrect syntax.
Modules & VBA :: Application Quit Doesn't Work On Network Users
May 26, 2014My database is used by a few users on a shared drive in "read-only" mode so they can't change any data.I set a scheduled task to copy an updated version of the database (which I edit) every morning.
The problem is that when the users forget to close the database file I can't overwrite it.For this purpose I wrote a small code that quits the application at 00:00 using Application.Quit.The code WORKS when I test it on my computer, but every morning when I try to open the file I see that it is opened by another user since I also open it as "read-only" - meaning the code didn't work...getting the code to work properly OR get a better solution to be able to overwrite the file even though it's opened by other users.
Modules & VBA :: Database Audit Trail To Work With A Form In Datasheet View
Aug 11, 2014Is there way a to get this audit trail module to work with a form in Datasheet view? It works fine with a form in Single Form view; I can open the table where the audits are saved and everything looks good. But when implemented on a form in datasheet view, nothing happens. I don't get any errors and nothing is saved to the audit table.
Link : [URL] ....
Here's the vba code:
Sub AuditChanges(IDField As String, UserAction As String)
On Error GoTo AuditChanges_Err
Dim db As DAO.Database
Dim rsT As DAO.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
[Code] ...
You call it in the BeforeUpdate event like so:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Track all changes made to the record
If Me.NewRecord Then
Call AuditChanges("Asset_ID", "NEW")
Else
Call AuditChanges("Asset_ID", "EDIT")
End If
End Sub