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 units

That 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 much
Jon

View Replies


ADVERTISEMENT

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

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

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 8 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

Modifying A Replica

Jul 21, 2005

I am still new to Access and am loving the learning process. I am stuck on a problem though. I have serached the forums here and various other places and haven't found the info I am looking for. I was wondering if there is a way to have a relpica (or a seperate database that can syncronize) that has a modied design. I want the main one to have everything on it, reports/forms/ add/delete/ect, but I want another one that is simply a data entry form, with only the option to input data. I would love to do this and avoid system security measures with usernames and passwords. From my reading it doesn't seem like I can do this, but I still hold in my heart a glimmer of hope. Please help, and if you can explain the process to me, all the better (its how I'm learning).

View 1 Replies View Related

Modifying Security Using Sql

Jan 16, 2006

Is it possible in access to modify user permissions with SQL?

View 3 Replies View Related

Modifying A Query

Jan 10, 2007

I'm far from being an Access expert, so forgive me if I don't make perfect sense. I have a database where questions and pages are entered on a daily basis. I have a table with three columns: data, pages, and questions. I have a query where I can sumarize the data by Month. (I created a query by using the wizard and I chose to summarize by Month). This returns all of the data, summarized by Month. I want to limit it further by year, so that it would return 12 months of data. I am at a loss! I've tried typing [Type Year] but I get no responses. I was able to create a limiting one where I type in the month and year [Type Month and Year] that works great, but it doesn't translate into the year only.

Thanks for any help you can lend!
Michele

View 3 Replies View Related

Modifying Error Messages

Mar 14, 2006

Hi to all. I need to modify some existing error messages in my form so that I can create messages that will be more user friendly. I know that it is possible but searching for this issue was a bit confusing. How do I do this?

Thanks in advance

View 1 Replies View Related

Modifying An Icon's Properties

Mar 21, 2006

I have an Access database split into FE/BE. For the front end, each user has his own directory on the lan. The directory is named with the UserName.

I've created an installation program in Access. It basically installs the icon on the user's desktop using Environ("UserName"). It also installs an Excel file the program needs on the hard drive.

Here's the problem...Right now, the installation program grabs the shortcut off the lan and installs it, but that shortcut has one user's UserName hard coded as the target. That one piece of information needs to change for each person. Is there a way for the program to modify the target based on the username of the person running the installation program? Without this piece, I need to visit every user after he runs the installation program and modify the properties of the new icon to point to his specific directory.

Thanks for any help you can provide!!

View 2 Replies View Related

Help Modifying Export To Excel.

Sep 1, 2007

Hi guys, me again. Have a few functions that I would like to put into action.
1)
I currently have the following code that exports multiple queries to excel. All works great I am happy to say. Currently the date criteria are hard coded into the queries. Well now my users would like to enter date ranges instead. Currently when I click on my toggle button, the users are asked if they are sure they want to start export to excel. I still want that to happen, but how can I change my code to prompt for startdate and enddate before running the export. I would rather use an input boxes to enter the date range instead of using a form. Would this be possible?

2)
Right now excel is displayed on the screen while the export is in process, how can I hide excel and then have it appear after its done with export. If the user clicks within excel during export, it sometimes freezes. And how can I apply my module for the progress bar to display the progress of the export. I have provided the code of the progress bar that I am using, that I found on this forum.

Many thanks for any suggestions.



Option Compare Database
Option Explicit

Public Function ExportDataExcel()
Dim strFilePath As String
Dim strFileName As String
Dim strFileTemplate As String
Dim strMacroName As String


If (MsgBox("You are about to generate the LAR Monthly Report. Are you sure you wish to continue? You cannot cancel this procedure once started.", vbOKCancel) = vbCancel) Then
Exit Function
End If

'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Fill in the following with your files and path
strFilePath = "R:Call CenterCall Center DepartmentsMortgage DeptMortgage Statistics & Tracking"
strFileName = "BigLarOutput.xls"
strFileTemplate = "BigLarTemplate.xls"
strMacroName = "DeleteBlank"
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''

'This deletes the old file
Kill strFilePath & strFileName
'This recreates your file with the template
FileCopy strFilePath & strFileTemplate, strFilePath & strFileName


'This is a custom function I built to set Excel as an object and you can access/export
'to a workbook programmatically. 'openexcel' is stored in a module called Functions.
'This will open the new file that was created previously

openexcel strFilePath & strFileName

'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Export data is another function that will export your data.
'Update the query Names to your real Query Names
'Update the Sheet Names accordingly, with the Query it is assiged to
ExportData "qryHoeqDotApproved", "HOEQ DOT APPROVED"
ExportData "qryHoeqDotReceived", "HOEQ DOT RECEIVED"
ExportData "qryHoeqDotDenied", "HOEQ DOT DENIED"

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''


xl.ActiveWorkbook.Save
'The Application.Run will run the Macro(s) that you saved in your spreadsheet
xl.Application.Run "'" & strFileName & "'!" & strMacroName
xl.ActiveWorkbook.Save

'Uncomment/Comment these to close out the workbook
'xl.ActiveWorkbook.Close
'xl.Quit

Set xl = Nothing

End Function


Private Function ExportData(strQuery As String, strSheet As String)
Dim intR As Integer
Dim rs As Recordset

'After you open that Object/Workbook, you refer to that workbook now as 'xl'. You will
'use it later, but now you have to access your queries through this code and to do so
'you need to use a recordset.

'strQuery is the name of the Query that you passed with the Function. You can also
'use an SQL string.
Set rs = CurrentDb.OpenRecordset(strQuery)
rs.MoveLast 'moves to the last record
rs.MoveFirst 'moves back to the first record

'You can use record count to make sure there are records in your Query/Recordset
If rs.RecordCount < 1 Then
'There are no records
MsgBox "There are no records for " & strQuery
Else
'There are 1 or more records. Now Select the sheet that you will be exporting to
xl.Sheets(strSheet).Select

'Now you need to loop through the records. 'intR' was dimmed at beginning of this
'function and will now use it to create a loop or 'For, Next'

'Starts with record 1 and gets the count of records in the recordset so it knows where
'to stop.
For intR = 1 To rs.RecordCount
'Now we need to export the recordset/query to the workbook/object we opened earlier.
'Remember 'rs' refers to the recordset & 'xl' refers to the workbook

'xl.cells(ROW,COLUMN).VALUE = rs.fields(INDEX).
'This is how you will fill in the value of a cell on the workbook. For the ROW you
'will want to add + 1 if you have Headings on your sheet. The INDEX for rs.fields
'refers to the columns of the recordset/query. The first column of the recordset
'starts with the index of zero.

xl.cells(intR + 3, 1).Value = rs.Fields(0)
xl.cells(intR + 3, 2).Value = rs.Fields(1)
xl.cells(intR + 3, 3).Value = rs.Fields(2)
xl.cells(intR + 3, 4).Value = rs.Fields(3)
xl.cells(intR + 3, 5).Value = rs.Fields(4)
xl.cells(intR + 3, 6).Value = rs.Fields(5)


'If there are more columns to add than this just follow what I have above

'Moves to the next record
rs.MoveNext
Next intR 'Loops back to For and enters data for the next row

'Once the export is done, this just puts the cursor to A1 on each sheet
xl.range("A1").Select

'Clears the recordset
rs.Close
Set rs = Nothing

End If

End Function

' Code inside progressbar module

Option Compare Database
Option Explicit

Function RunProgressBar(lLth As Long)

If IsLoaded("frmSplash") Then
Forms!frmSplash!Box20.Width = lLth
End If

End Function

Function IsLoaded(ByVal strFormName As String) As Boolean
On Error GoTo Err_IsLoaded

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

Exit_IsLoaded:
Exit Function

Err_IsLoaded:
MsgBox Err.Description, , " Service Operations"
Resume Exit_IsLoaded

End Function

Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause

Dim PauseTime As Variant, Start As Variant

PauseTime = NumberOfSeconds
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop

Exit_Pause:
Exit Function

Err_Pause:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Pause

End Function

View 7 Replies View Related

Modifying Report Changes Information...

Mar 16, 2008

I have a report I am generating with the design wizard. It uses 1 Query and all the information I want and order is correct when its finished. However, there is more information then what I want to display (I choose it to sum up everything, but I want to get rid of the thing that says 'summary of blah : 3 records) and I want to move the sum label to the far right and change it to total.

However I am finding out that any attempt to modifying the report in any way is changing my information. When its correct and working the report is 24 pages, each client has 3 sub types that come up and shows totals. However, if I make something invisible, move a label, change the text in a label or ANYTHING, then my report is completly screwed up, it drops to 3 pages, and loses all but 5 clients and only 1 sub type appears.

Why is modifying the report doing this?

Edit: its access 2003

View 4 Replies View Related

Forms :: Modifying Charts With VBA

May 6, 2013

I have a bunch of charts in my forms, and my boss wants to be able to adjust axes. I have added a context menu that brings up a little form and takes values that are then thrown into the graph by way of a function. URL....Here's the current code:

Code:
Public Function AdjustTREFAxis()
'See form fTREFaxis
Dim objChart1 As Object
Dim objAxis1 As Object

[code]....

notes:
-ignore the global vars, they are a temporary measure.
-"Graph19" I know, not good. I never expected to call it in code though , I'll fix it later
-the while loop keeps the rest of the code from running until the axes have been submitted.

Ok, so this code works for the x and y1 axes. I assumed that Axes(3) would iterate to the Y2 axis, it does not...I also found that after you change the axis the changes are persistent for the graph even upon repaint/requerying it. This is problematic and I need a way to set an On Load event that resets the graph to "autoscale"

Lacking that, how to access the Y2 axis, or how to reset the axes to autoscale that would be keen.

View 1 Replies View Related

Modifying Data In A CrossTab Query

Mar 17, 2006

Is it possible to modify data values in a cross tab query?

I tried and it won't let me delete or type anything.

Thanks.

View 1 Replies View Related

Need Assistance Modifying Date Query

Nov 19, 2006

I have a query that is based off of a union query

SELECT clvpertech.clvtech1 AS Technician, Sum(clvpertech.CLVCode) AS CLVs
FROM clvpertech
GROUP BY clvpertech.clvtech1;


This is returning a sum of all the procedures a technician does for the entire database.
This is working.

I need to break this down by day, week , month, and year base off of the datecomp1 or datecomp2 or datecomp3 or datecomp4 fields having a date in them.

I made this query to get the daily results and it works
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=Date$())) OR (((clvpertech2.datecomp2)=Date$())) OR (((clvpertech2.datecomp3)=Date$())) OR (((clvpertech2.datecomp4)=Date$()))
GROUP BY clvpertech2.clvtech1;


How can I add different queries to get the results by breaking down the date field for year, week, and month?

I tried this for the month but I get Invalid Procedure Call when I try to run it???

SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=DatePart("mmm",[datecomp1]))) OR (((clvpertech2.datecomp2)=DatePart("mmm",[datecomp2]))) OR (((clvpertech2.datecomp3)=DatePart("mmm",[datecomp3]))) OR (((clvpertech2.datecomp4)=DatePart("mmm",[datecomp4])))
GROUP BY clvpertech2.clvtech1;

View 6 Replies View Related

Modifying Database From Excel File

Nov 4, 2004

I have a a database with a large number of email addresses in it.
I also have a list of email addresses stored in an excel file.

I would like to take the excel file and use it to check for the same email addresses in the access database and if they are present in the database add content to another field in the database to flag it.

Can anyone help?

Thanks.

View 4 Replies View Related

Tables :: Modifying Data Using Another Table

Aug 19, 2014

I have a table that has a field that holds a total. Through historic reasons the total in a (relatively) small number of cases is wrong. I have analysed and worked out what the difference should be and have created a table with the value that is missing. I basically want to add the 2 together.

So I have

Table1 Field1 Value1
Table2 Field2 Value2

I want table 1 to be

Table1 Field1 (Value1+Value2)

Programming wise I would just have
Value1 = Value1 + Value2

How do I go about doing this in a query or some other whizzy way?

View 2 Replies View Related

Queries :: Modifying Query Output

Feb 13, 2014

I am trying to modify the output to only show discontinued items. While the query is in datasheet view, the text is read as "YES" or "NO" under discounted items. So, I tried entering "YES" in criteria while in design view, but keep getting an error message stating, "Data type mismatch in criteria expression".

View 5 Replies View Related

Query Execution And Record Modifying

Dec 8, 2014

whenever I run/execute a query in Access it is modifying the first record on the table in which it is calling the data from.For example if the first record might contain the following:

Record A: John Doe, Oct, 2014, Account is Active

And lets say I am running a query to pulling records from Nov 2014.The Month and Year Fields in the above example for Record A gets modified to the query search parameters for Nov.Is there some of of record lock or controls that I need to adjust to prevent this from occurring?

View 1 Replies View Related

Modifying Query Fieldname & Criteria Thru Code

Nov 30, 2004

Hi,
Self learning trying to modify a query fieldname and criteria thru code.

Have a small form with a button making a copy of a query/s (eventually making about 50 copies).
Once these have been made, would like to open the query up, which I can do, then modify both
the fieldname and the field criteria to suit my needs from parameters set in the form.

How do I do this if it can be done ?

Thanks in advance

Ian T

View 3 Replies View Related

General :: Modifying Record And Saving In New Location?

Dec 4, 2013

I'm working on a database that will be used by two different groups. Group A provides a list of items to be investigated by Group B.

I'm wondering if it is possible to:

i) have Group A create an excel file of items to be investigated (let's call the file tblNEW)

ii) open a record in the file through a form (let's call the form frmINPUT) to allow Group B to input investigation findings

iii) save the modified record into a separate table of all the work that has been done (let's call this table tblWORK)

iv) delete the investigated record in tblNEW once it gets saved into tblWORK

View 1 Replies View Related







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