Modules & VBA :: Assigning Users To Projects?

Dec 16, 2014

I have two tables. The first is a list of users. The second is a list of projects. I need to loop through the user list and assign each one to a project. At the end of the user list it will just start over at the top until all the projects have someone assigned to it. What is the best way to do this?

Table1 Users

User1
User2
User3

Assignments

ProjectAssigned To
Project1
Project2
Project3
Project4
Project5
Project6

After processing.

ProjectAssigned To
Project1User1
Project2User2
Project3User3
Project4User1
Project5User2
Project6User3

View Replies


ADVERTISEMENT

Modules & VBA :: Counting Value And Assigning Numerical Value?

Aug 15, 2013

I am working on an app, that has a field named "Name". I have everything set up with a module, which, for example, the name "John", it assigns 1. when it sees "John" again it assigns 2. Then "Jane" It assigns 1 again. I want the module to see every instance of "John" to assign the same number, 1, then all instances of "Jane" 2, etc. Here is the code in the module I am using, but it is assigning the values wrong. My final plan is to use the numbers for conditional formatting, so all johns one color, all janes, a different color. I can't use the conditional formatting wizard because these names pop up at random, and the names populate at random.

Option Compare Database

Global GBL_Category As String
Global GBL_Icount As Long
Public Function Increment(ivalue As String) As Long
If Nz(GBL_Category, "zzzzzzzz") = ivalue Then
GBL_Icount = GBL_Icount + 1
' MsgBox icount
Else
GBL_Category = ivalue
GBL_Icount = 1
End If
Increment = GBL_Icount
End Function

View 1 Replies View Related

Modules & VBA :: Assigning Index To Variables

Sep 11, 2014

How can I assign some type of index to the below variables (data representation) ? I'm using VBA to build a .RFT file for MS Word.

"Line # 1 data"
"Line #2 data"
"Line #3 data"

. . . .

"Line #7 data"
"Line #8 data"
"Line #9"

. . . .

"Line # 22 data"
"Line #23 data"
"Line #24 data"

View 14 Replies View Related

Modules & VBA :: Export Report As PDF And Assigning Name At Same Time

Nov 15, 2013

I'm trying to export a report as PDF giving it a name at the same time. I'll then email this out.

The email bit I'm fine with, but I'm struggling to get the report to save as a PDF

Dim outputFileName As String
outputFileName = CurrentProject.Path & "Mail_MergesTemplatesBulkEmail Quote From Dialler" & "Quote" & [Quote_URN] & ".csv"
DoCmd.OutputTo acOutputReport, "Digital Service Cover Quote", acFormatPDF, outputFileName

View 3 Replies View Related

Modules & VBA :: Assigning New Values To Blank Fields

Dec 19, 2013

I got one months table containing a reporting_month ,timeperiod and an Index column ID with data type Autonumber.Basically I want to search through the table whenever the User types in a new reporting month or timeperiod over the dialogue.Now I want to realize the following options:

1. The user types in a new reporting month, when a record in the months table exists with a timeperiod and a blank reporting month field, it should be assigned there. For example the User types in reporting month = 032014 it should be assigned to the Time_Period Value = 042014-032015

2. Vice versa, the user types in a time period. This value shuold be assigned to the blank field beneath the existing reporting month.

View 14 Replies View Related

Modules & VBA :: Assigning Control (sources) To A Form

Oct 3, 2014

My scenario is that I am using a one time table that is a copy of my real transaction table (called GLAcTranLine) to enter general ledger transactions. Both my form and my sub-form are unbound. The form is called GLFI50 and the sub form is called GLFI50TranSub on disk and inside the main form. That is, both the sub form and its description inside the form (in the label that appears when I build the sub form with the wizard) bear that name.

So in the load for the main form I carry out these job steps.

1. I take a "No Data" copy of the transaction table GLAcTranLine, to get the structure. That works fine. The name I give to it is prefaced with GLFI50 and ends with a random number. That works fine too.

2. I insert a blank record into the new table. That also works fine.

3. Assign the control source to the Sub form and to its individual objects. That just dies in a crumpled heap. Neither the form not the individual columns code works.

The error I get (in both cases) is the Run-time error '2465' Application-defined or object-defined error.

I believe this means that it just cannot work out what idiotic error I have made. The code all passes the compiler test.

Here is the SUB.

Private Sub Form_Load()

'STEP 1. I take a "no data" copy of Create the Work Table and assign it as a control source to the sub form
'================================================= ======================

'(Copies the structure of the source table to a new target one. It isn't a temporary table so I need to
'find a way of assigning a temporary name and then using that name on this prog.
'The True on the end says "Do not copy the data)"

'GLFI50Work is the Global variable that holds the temporary table name.
Dim RandomInt As Single

[Code] .....

View 6 Replies View Related

Modules & VBA :: Assigning Value To Variable Causing Error

Jul 19, 2013

Is the following possible? If so - how?

I have a series of several hundred variables being assigned values.

If the value assigned to the variable results in an error, I would like to assign a default value to the variable which caused the error.

How do I reference the variable, or line (or any info I can work with), which caused the error?

View 2 Replies View Related

Modules & VBA :: Assigning Reference Numbers To Records Where Same Customer Appear

Nov 21, 2013

I'm trying to put together a DB for creating notifications to customers. The source file is pulled out from another system as an Excel file, which is then uploaded to Access via VBA. The user runs a query, and then sends the results (customer information) to an Excel file where we have set up a mail merge document (the notification itself).

A customer may appear in multiple records (having multiple accounts), but will only receive one notification.

What we're trying to do is give each notification an unique ID that will become its document reference number once the document is created. The number will need to be the same for all the records in which the customer appears, and they will need to be consecutive, as the documents themselves will be stored in PDF form later on in the process.

So I would like to assign an unique ID to each customer, each time it shows up when querying the uploaded source file (the query is done in a form, that has a sub-form for showing the results). Then, when exporting the query results, they will be also copied and stored to another table, along with the assigned reference number, for monitoring purposes.

The Access file itself will be split and used by multiple users (up to 9 users at a time). In theory, each user will see their own customers (each customer account is assigned to a certain user), but the number assignation will need to also depend on when the query has been run. For example, if user 1 queries the DB at 9:30AM they will get records numbered 1000 to 1050; user 2 (who queries the DB at 9:31AM) gets numbers 1051 to 2100 etc.

View 2 Replies View Related

Current Db Projects

Jun 13, 2005

Just curious as to what others are working on. Here is what I'm in the middle of and what the basic purpose is:

It's a db to track parts we swap with our customers. It's primary table is on a sql sever so that the anyone in our company can view only the data via DAP's. The other tables are in the .mdb on the file sever. I plan on having it up and running in the next week or so. It will be used (add/edit,etc data), by approx 8-15 users in three different buildings off a file server share folder...

See screen shot attachment.

View 8 Replies View Related

Modifying Projects - See Example

Sep 8, 2006

Hello, So far you guys have been helping me through my database that i am building. Thanks so far!But at the moment i need help on one more aspect of this Database Job. Currently i have a database that supports and holds records for jobs that are under contract. That means we have certain numbers and statistics of Jobs and there properties.what i have right now is a table with a couple fields. (there are more than this but this is just for example) project number "primary key" aerial units buried units planning units ICGS unitsThat part works fine with the current table. But since jobs are under contract the number of units might change further on during the design and construction. There may be more than one mod.Now the part that im stuck on is the most important. I need a table/query/form/report that i can input the changes to particular units. Like i said before there might be more than 1 mod. So i need to make it show ALL the mods ive done to a project.This is a huge part, and any help would be very much appriciated. ~Thanks so muchJon

View 2 Replies View Related

Access Projects With SQL Server

Oct 26, 2006

I need to be able to add a feature to an Access project that will select a group of records based on an area code( user will have to be able to select a desired number like 100, or 1000), mark those as selected, and create a single csv file...The area codes are stored in the SQL SERVER database...right now they just export groups of #'s based on area code to excel select the amount they want and cut and paste into a flat file for another application to use...they would like to be able to select a number from a list box or type it in...like 100 ( which means take a 100 #'s from all different area codes) then have them automatically noted in database as being selected..and then export all selections to one csv file... should I do part of the work in the Sql Server behind the scenes..the format of the csv file also needs to be specific in that the first row is the columns separated by commas and then the data follows all text fields enclosed by qoutes..
Thanks for anyone's help ..Im very inexperienced with VB and Access

View 1 Replies View Related

How To Input New Orders For Old Projects:

Apr 14, 2005

How to input new orders for old projects:

I have an orders form that looks up data from 3 tables (projects, orders and orderDetail) with a subform that looks up data from a query.

The form has several unbound comboboxes relating to the project that synchronise with VBA code

Projects are setup in another form and this form is only used to lookup or add orders to existing projects, so project data will remain the same on the form

At the moment when I press the 'add new order' button, it does not clear the unbound combo boxes and all the data in bound fields and subform is cleared.

When I press the 'Add new Order' button I want the form to clear all the data on the form and subform relating to the order_table, but to retain, or automatically update(with the same data), the data in the fields relating to the project table.

How do I make this work?

Any help is greatfully recieved.

Thx.

Kev.

View 1 Replies View Related

Question About Access Projects For A Beginner

Dec 21, 2005

I have a general question about Access Projects which I plan on learning soon:

I will be using SQL Servers for the databases.

Are Access Projects very different from ordinary Access and VBA (.mdb)?

I have a good grasp of Access Databases using Access as the back end. How much would this help with Access Projects?

View 1 Replies View Related

Access Projects - Query Designer - Update Queries With Joins Cannot Display In GUI

May 26, 2005

Hi, Big Jim here:

I am really not sure where to ask this one.....

My boss and I are in a jam. We have been using Access to run a reporting process, but one of our tables will exceed the maximum fields allotted this month. Our thought, dump the table into SQL Server and use the GUI interface provided in Access Projects.

Unfortunately, the query designer seems to have a few drawbacks. The one that effects us the most is in using UPDATE queries where more than one table is used to determine records to be updated. In attempting it, we get the message: "The designer does not graphically support the Optional FROM clause SQL construct".

Now I know we can manually create Update Queries, but we often need 1,000+ in a short period of time. Manually punching in all the fields involved and other code just isn't timely.

Question: Is there some alternative, service pack or anything else that would allow us to graphically create these Update Queries using Access Projects or even SQL Server 7.0? I would hate to have to scrap all the work we did over something that seems so minor.

Thanks in advance!

Big Jim


Set Up:
Windows XP
Office XP
SQL Server 7.0

View 4 Replies View Related

Forms :: Pivot Chart To Show Progress In Multiple Projects Over Time

Aug 30, 2013

I'm aiming to create a stacked area chart to display the progress of multiple projects over time, so we can review and forecast project load over time.

The data comes from this table:

tblProject
* ProjectID
* DateProspect
* DateConfirmed
* DateStarted
* DateComplete

A project passes through the statuses Prospect, Confirmed, Started, Complete. The current status of each is calculated in qryStatus from the dates in tblProject. The past and forecast statuses for each project will probably be calculated each time the chart is created (or maybe not necessarily so?)

To create the stacked area chart, the x-axis will be months (e.g. Sept 2013 - July 2015). On the y-axis will be the count of ProjectID, and the series will be the different project statuses.

The sticking point is getting from the Date* fields to the past and forecast statuses in each month. After that I imagine it'll be reasonably straightforward to put into a pivot/chart.

View 3 Replies View Related

Tables :: Database With Updating Dates - Track Current Projects For Team At Work

Dec 27, 2013

I am creating a database that tracks current projects for my team at work.

Some projects are only due once (e.g., mailed brochures due on 1/1/14) and some are due at scheduled intervals (e.g., status report due monthly, quarterly, etc.)

Ultimately, I'm hoping that my end result will allow us to click on a form and look at what everybody has due that day, in the next 7 days, and so forth.

View 3 Replies View Related

Forms :: Automatically Filter Combo To Show Only Projects That Match Training Type

Sep 13, 2014

I've attached a rudimentary example db to give an example of what I'm trying to achieve.

There is a form on the db called Resourcing with several combos.

When I choose a Training_Type from the first combo it automatically filters the Project_Title combo to show only the projects that match that training type. When I choose a Trainer_Name the text box for team automatically completes.

The bit I'm struggling with is Trainer_Name. As you will see from the table Course_Details not every trainer can deliver every Project_Title. So what I want is to display only the trainers associated with the project title chosen rather than displaying the whole team in the Trainer_Name combo.

Someone suggested a junction table but I'm not sure how this would answer my question. I'm thinking some sort of Dlookup but don't know how I would write it when it has to include data from four fields.

View 14 Replies View Related

Modules & VBA :: How To Define Accessibility For Different Users

Oct 28, 2014

I have a multi-user database. I would like based on the logged-in user, some buttons and controls in different forms be disabled and the others be enabled.

There is a login form that gets the username; I made also a function to define authorities for users in it.

My plan is that when user enters to the database, on load of the main menu the function calls and disables the defined controls in different forms. I tried to use this kind of codes:

Function User_1()
[Forms]![frmMainMenu].[cmdUpdateDatabase].Enabled = False
[Forms]![frmChooseReports]![cmdOrdersFollowUpReports].Enabled = False
End Function

Function User_2()
[Forms]![frmMainMenu].[cmdChooseCharts].Enabled = False
End Function

But this code only works for the forms that are opened at the moment and if one of them is closed the system gives Error.

View 2 Replies View Related

Modules & VBA :: Mail Merge From Multiple Users?

Jun 11, 2014

I know this is possible (e.g. opendatasource) but search as I might I can't get the exact code.

Scenario: Multiple front ends, in different folders, linked to single back end.

When mail merging, a temp table is created in FE - thereby different source for each user.

The Word doct is linked to a source (via Mail Merge) and I want to change that source according to the logon of the user, which is held on an open form.

Ideally like:
Case Mary use C: Db1 emptable
Case Tom use C:Db2 emptable

View 4 Replies View Related

Modules & VBA :: Identify Users With Database Open

May 1, 2014

I'm wondering if there's any way to find out who has a database open.

I've saved the file as an ACCDE for the users and kept the ACCDB file as my working file. Whenever I make changes to the db I need to save a new ACCDE file on top of the existing one. In order to do that, I need to ensure no one has the db open. Half the time I need to track down 1 of 15 people to find out who it is that has it open so I can kick them out and save.

I don't have any login features, but could use the POD # that each cubicle has associated to their computer if Access allows that...

View 8 Replies View Related

Modules & VBA :: Filters Not Working For Multiple Users

Mar 9, 2014

I have created a module, where based on various selections (form), the output is thrown in the table for editing various fields. This works fine with single user. But once I have placed the same database on the share drive for multiple users, the users are unable see the data in the text filters. I don't know what is the issue all about. Also if i use me.requery, the text filters becomes blank. Below is the code :

Code:
Option Compare Database
Option Explicit
Public Function SelRec(shDate As Date, ATMID As String, City As String, Depots As String, Vendor As String) As Boolean
SelRec = False

[Code] .....

View 12 Replies View Related

Modules & VBA :: Users Connected To Split Database

Sep 30, 2013

I have an project with a split database. The data is stored in the database_be.accdb

I want to know which users are connected tot this database.

View 4 Replies View Related

Modules & VBA :: Separate Users And Admin On Login

Jul 8, 2014

I need to separate Users and admin on login so that the admin can go to admin form and users to specific form using vba access. Here is my login code

Private Sub cmdLogin_Click()
'Check to see if data is entered into the password box
If IsNull(Me.cboUserName) Or Me.cboUserName = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.cboUserName.SetFocus

[Code] .....

View 8 Replies View Related

Modules & VBA :: Users With Different Mapped Network Drives

May 13, 2015

I need to make sure that all users on the network can access a specific network drive folder.

Most users don't have a problem but some users have the network drive mapped differently so when running the feature required their system can't find the file at that specified location.

I have the location of the files hard wired into the VBA code.

E.g. Z:INVDATACOMPANIESCompanyAASSETS Pty LtdPrecedents 2015STAGE 3A - COLLS-DEMS

The location is outside of the project address.

I need to be able to make sure the address of the folder is the same for all users.

View 3 Replies View Related

Modules & VBA :: Login Form And Users Permissions

Mar 30, 2014

I have been searching for info with users & permission levels. I have ready a lot about ULS (User Level Security) which doesnt seem to be my answer as I do not have admin rights and I believe it is not suitable with Access 2010.

Basically I have an 2010 Access database which is used by my department as a project management tool. It has various tables, forms, queries & reports, I have created a table with user names & passwords and a login form. So when the databases opens the login form is launched & the users selects their user name & enters their password, then the main menu is shown.

Everything works really well, but as the moment there is no difference between all the users. All I want to do is to be able to make one of the Logins read only, ie if the Reviewer is logged in they can see & read every form and run every report but they can not make any edits.

View 5 Replies View Related

Modules & VBA :: Sending Message To Users Logged Into DB On Different Servers?

Jul 23, 2013

I have some code that when a check box in a table is ticked, sends a pop up message to the users logged in (message is on a hidden form), then it closes the DB after a pre-dtermined period of time.

The problem I'm having is that we are a large site with a about 25 differnet servers that our users log onto via Citrix, when a user is on a different server it isn't displaying the message nor closing the DB down.

trying to achieve this? Or is there a better way to see who is logged in and get a message to them?

I'm using Access 2003 btw

Code I'm using below

Code:
Option Compare Database
Private Sub Form_Load()
Me.TimerIntderval = 10000 ' change to 300000 for about 5 minutes
End Sub
Private Sub Form_Timer()
DoCmd.Close
Application.Quit
End Sub

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved