Small Code In Need Of Review
Jun 24, 2005
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
Help is appreciated.
Here is the code that I was thinking of, I don't think its right but it might be a start.
Dim Count As Variant
Dim CountImplementedTotal As Variant
CountCost = 0
CountImplementedTotal = 0
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordSet("ECM Details")
Do While Not rst.EOF
If rst![AUDIT ID] = Forms![audit info]![AUDIT ID] Then
If rst![Has Measure been Selected] = "YES" Then
Count = Count + rst![Total Measure Cost]
End If
End If
rst.MoveNext
Loop
Text47.Text = Count
View Replies
ADVERTISEMENT
Feb 5, 2006
This database is for a car event that's held once a year. It has been a work in progress for about 15 weeks now and is basically finished. There is only a couple of errors and not sure where to start. The database is so big it is getting a little out of reach for me to organize everything.
Idea of the database is that I can enter entrant's information, there car details and any merchandise they want.
Main errors I’ve noticed are;
In email form if you select one user it still emails everyone.
In the car details form the induction box does not show the value list I created in the car table.
when entering a t-shirt in the products form you select the shirt size. Is this the best way to lay this out.
In the Order Form what is the best way to add a freight box between Sub-Total & Total where you can type the freight in and it's automatically added to the Total Box
Have a look over the db and let me know what ya's think.
Thanks
Dean
View 14 Replies
View Related
Feb 16, 2006
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load()
Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
If InputBox("What is the password?", "Password") = "2" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
End If
End If
End Sub
View 14 Replies
View Related
Jun 5, 2013
I'm having some problems with VBA code I'm writing that references a subform. I'm pretty new to VBA and am not sure what the problem could be. The parent form (TrackingForm) tracks dates different tasks were completed. The subform (Child2) tracks exceptions that also need to be marked completed. I need ALL dates to be marked as completed before the entire tracking record can be marked as completed.
I have the following code entered into the OnFocus event of the tracking records Me.Completed_Date field:
Private Sub Completed_Date_GotFocus()
If IsNull(Closing_Date) Or IsNull(Package_Received) _
Or IsNull(Upload_By) Or IsNull(Initial_Review_Date) _
Or IsNull(Me.Child2.Form![Date Exception Completed]) Then
Me.Completed_Date.Locked = True
MsgBox "Completed Date cannot be entered - outstanding items.", vbOKOnly, "Warning!"
Else: Me.Completed_Date.Locked = False
End If
End Sub
What ends up happening is that, if there is more than one exception record on Child2, the code only seems to care if one of the records (usually the first record) has something entered in the [Date Exception Completed] field. I can't seem to make it look at any successive records in Child2 to ensure they are also completed prior to unlocking the Me.Date_Completed field. I have googled this extensively over the last day and the only response that seems to recur involves using a Recordset function that I have never used and am unsure how to.
View 4 Replies
View Related
Nov 3, 2005
This is what I have so far
Def: IIf([30 day notification]<"1/1/2005","h")
What I need to accomplish is, any records less than 1/1/2005 show me "H". It does show up in some but not others. This is a date field. What is it that Iam doing wrong.
Doing my best at learning code. I don't know how you people learn this stuff
Thanks
View 2 Replies
View Related
Oct 1, 2013
creating a small loop to run code.For each record from AREA with PLANTPROGRAMID = VARIABLE Then...In this example, my table is called Area, my field is called plantprogramid, and my variable is defined earlier in code.
View 5 Replies
View Related
Nov 19, 2007
Could someone offer suggestions on how to improve the database or the general structure of it?
All suggestions are welcome!
(Its an online retail project selling DVD's, CD's & Games)
View 9 Replies
View Related
Nov 20, 2007
Hi,
I'm doing a database for a CD, DVD, Game website could you take a second to review it and give me suggestions to improve things.
I've included my old and revised database to see what if done
View 8 Replies
View Related
Aug 28, 2007
Hi
I have a database for maintaining procurement contracts which includes a start date for each contract and a frequency of reviews required (monthly, qrtly, bi-annual, yearly). I need to be able to generate a report that will tell me when the next review is due for each contract, taking today's date into consideration.
This is the setup so far:
tblRegistry - contains field 'Commence' for the start date of the contract and 'Review Req' to indicate how often a review is required (monthly, yearly etc).
tblReviewReq - contains field 'Review Frequency' (monthly, yearly etc) and 'Days' to assign the number of days eg monthly = 30, yearly = 365 etc.
Next I have a query that includes the above tables using the fields 'Commence' and 'Days' and have created a calculated field to give me the next review date ie contract start is 1/08/07, review freq is monthly (=30 days), therefore the next review is 1/09/07. "NextReview:[Commence]+[Days]"
But how do I get it to tell me the next review date after the 1/09/07? Ultimately I'd like to be able to pull a report at any stage which will tell me all the reviews that are coming up.
Thanks
View 3 Replies
View Related
Nov 6, 2005
Hello,
This is my first post.
I have built an access database (Access 2002) which contains around 370 records.
It would be useful to be able to click a button on a form to review which records (forms) have been amended or what records are new.
This could be presented by either (1) sorting the records so the newest/or latest changed records came first, followed by the next most recent etc etc, or by (2) presenting a list of record names which could be followed manually.
I have tried several text books but have been unable to find a solution so any help from the experts would be very welcome.
Thank you ~ David.
View 3 Replies
View Related
Sep 23, 2005
I'm trying to create my own events (issues, action items et al) log database for project management.
I have attempted to have the events interrelate to each other. I am inviting comments, suggestions and the like.
Thank you in advance.
View 14 Replies
View Related
Apr 18, 2008
Hello everyone,
Using Acess 2007
This is what I have:
I have one Query with the following:
Main.ID, Main.First Name, Main.Last Name, Main.Assigned Date
I created a Daily Report using this Query to tell me when I need to meet again with these people.
I have a Report that has the following:
Main.ID, Main.First Name, Main. Last Name, Main.Assigned Date, [Assigned Date]+30,[Assigned Date]+60,[Assigned Date]+90,[Assigned Date]+120,[Assigned Date]+150,[Assigned Date]+180
What I would like to do:
The current report shows ALL records with the requested information. How can I have the Query or Report filter out the records that do not have any of the review dates falling on today's date?
I hope I explained myself. If further explanation is necessary let me know.
Any help is greatly appreciated.
René
View 2 Replies
View Related
Jul 18, 2013
I have a form with date fields I need to calculate review dates for employees. Example 45 day 60 day 90 day what would be the best option to do this?
View 1 Replies
View Related
Aug 3, 2005
I am working on a database to keep track of meter readings from several meters. Am at the table design stage and have come up with two seperate table designs but am wondering what would be best.
Each individual 'Meter' tracks more than one 'Fuel'. All of the regular meter readings should occur on the last day of the the month. The difference between the current month's reading and the reading from the previous month is the usage for the current month.
Though rare, there are occasions when there could be adjustments to the meter. A meter may be reset to 0 or to any other number due to a number of issues (replacement, maintenance).
The usage for a Meter-Fuel combination calculated by the current meter reading & previuos meter reading is assigned to current readings' date, As readings should onlt be taken at the end of the month, all other readings should be adjustments ONLY!
Here is my one version;
tblMeter
MeterID (PK)
strMeterDesc
tblFuel
FuelID(PK)
strFuelDesc
tblMeterReadings
MeterReadingID (PK)
dtMeterReading
MeterID (FK)
FuelID (FK)
dblMeterReading
tblMeterReadings
MeterReadingID (PK)
dtMeterReading
MeterID (FK)
FuelID (FK)
dblMeterReading
tblMeterAdjustments
MeterAdjustmentID (PK)
dtMeterAdjustment
MeterID (FK)
FuelID (FK)
dblMeterReadingOLD
dblMeterReadingNEW
My other idea was to incorporate meter adjustments into tblMeterReadings and not have a sepereate table for meter adjustments
tblMeterReadings
MeterReadingID (PK)
dtMeterReading
MeterID (FK)
FuelID (FK)
dblMeterReading
blnAdjustment
The I would create a multi-field index of dbMeterReading, MeterID, FuelID, blnAdjustment.
So data with an adjustment may look like this
dtMeterReading, MeterID, FuelID, dblMeterReading, blnAdjustment
01/31/2005, 1, 1, 900, False
02/15/2005, 1, 1, 1000, False
02/15/2005, 1, 1, 0, True
02/31/2005, 1, 1, 100 , False
I think that the second version is the way to go, since I would still have to create a query to join data from both the reading and adjustment tables in the first design that would look like the data above.
Here what I consider to be the drawbacks to either table structure
Version 1
The first version requires more complicated queries to calculate the difference between meter readings.
Version 2
The second version will require some coding/querying to make sure that there are always two adjustment records for one date and determining which is the pre-adjustment and post-adjustment meter reading since it may not be true that the meter is reset to zero; it may even be set at number higher than the pre-adjectment reading.
Any thoughts on which design is better? Or any problems I could run into later with designing forms/queries/reports that perhaps I have not thought through?
THANKS!
View 1 Replies
View Related
Nov 11, 2013
Is there a way to review / edit the actual SharePoint workflows in SharePoint created by Access Services after a web database has been published?
View 2 Replies
View Related
Sep 27, 2012
I've got a database that allows all users to review an address register. If they find errors or mostly get updated information they have another form they can fill out to make the recommended changes. Only I have access to the main DB to make the change permanent.
The question I have is there a way for Access to send me a notification that someone has requested the change?
I know that sending an email is doable - in Access. However, that's not an option for our network here. I was thinking something along the command prompt "Net Send" command. However I've not been able to get that to work by itself - let alone within Access...
I have used VB code in the past to store what PC is using the database, I could modify that to alert me whenever I open the DB up from my PC. But I was trying to get away from having to open this particular DB up every day and check for changes.
View 2 Replies
View Related
Dec 30, 2004
I have tried the count(field name) and the sum(field name) but neither of them are giving me the grand total only the count/sum of that row. Here is my code, please look - I'm sure its something small and stupid
SELECT [qry_Refi_Yield].[LOAN NBR], [qry_Refi_Yield].[BORROWER], [qry_Refi_Yield].[PURPOSE], [qry_Refi_Yield].[FUNDED STAMP], [qry_Refi_Yield].[HSS], [qry_Refi_Yield].[UNIT TEAM], [qry_Refi_Yield].[Cycle Time], [qry_Refi_Yield].[Total Yield], IIF([Cycle Time]<=30,1) AS Yield
FROM qry_Refi_Yield
GROUP BY [qry_Refi_Yield].[LOAN NBR], [qry_Refi_Yield].[BORROWER], [qry_Refi_Yield].[PURPOSE], [qry_Refi_Yield].[FUNDED STAMP], [qry_Refi_Yield].[HSS], [qry_Refi_Yield].[UNIT TEAM], [qry_Refi_Yield].[Cycle Time], [qry_Refi_Yield].[Total Yield]
ORDER BY [unit team], [hss], [funded stamp];
past tries: count([qry_Refi_Yield].[Total Yield])
count(borrower)
View 7 Replies
View Related
May 10, 2005
Hi,
I was using access 2000 to build a small office app. It will be on a network and will generally be used by only 2 or 3 people(max). I was wondering if the default jet technology would be good enough for this. If it isn't, what should I use? I looked at other options such as msde and ado but it's a bit confusing when you're new to this stuff.
Thanks,
scratch
View 2 Replies
View Related
Aug 29, 2007
There are times when it is just necessary to have a little rant, a canniption if you will.
For the last few weeks I have been working off-and-on with a new database for a salmon hatchery. One of the forms I designed contained a subform in continuous form view that flickered inordinately when first loaded. Now, there's a lot of code going on behind controls etc to run sql updates or deletes or appends depending on what the user clicked. There was also some code to alternate the row color of the continuous form. I spent literally days trying to find what part of my code was causing the problem.
I swore a lot.
I searched.
I eventually gave up as other priorities began to arise at work. I resigned myself to my fate.
Then I discovered, quite by accident, that the problem arose by the use of some unassociated labels being present on my form. I removed them, replaced them with labels associated with a control, and presto: the form flickering/reloading magically vanishes.
Great.
But what a colossal PITA for something so bloody stupid. There's absolutely no reason why this should occur IMHO. Honestly: it's hard enough to learn VBA, SQL, and relational db design without having to deal with poorly documented glitches like this. There are days when I think I'm finally starting to get the hang of Access, then something like this comes along and I want to send a lynch mob to redmond! :mad:
Ok, end of rant :) Hope the solution to my problem comes in handy for someone else someday.
View 3 Replies
View Related
Mar 22, 2006
Hi,
What i want to do is have a field called "monthlyhourcount" in my query that totals all the hours in a field i have in a table, the field in the table is called "hours worked" were a figure is entered, i need my query to give a total of all the figures entered. so far in the expression builder i have :
monthlyhourcount: [JobListMonthly]![Hours Worked]
when i run the query with this it gives me the seperate values, i need one total value.
any ideas guys?
Thanks
Conor
View 1 Replies
View Related
Nov 21, 2006
I'm fairly new to Access (2000 is the version im using), and I'm not sure how to do several things within a form:
1) make cursor in a text box jump to the front (left) when clicked on
2) Lock (and grey out) a text box once data has been entered into another
3) assign a field to correspond with another, and to have this shown on the form (ie: having a list of names and their corresponding phone numbers assigned to each; then on the form, having a combo or list box with the names that a user can select- once a name is selected, their phone number appears automatically in another text box)
4)Locking screens or subforms when moving on to another (either by pressing a button to open another form, or by selecting a field in another subform)
thank you in advance for your help and advice
View 1 Replies
View Related
Sep 15, 2005
Hello,
This will probably generate a few good laughs but here goes!
I have been given an Excel file with 6 fields about 20 chars in each field.
This file is a whopping 250 records. I would like to be able to add, change, delete and search records.
The DB would need to be shared by 2 users on a home network. Both are using Windows XP.
What would be(heres your chance!!!) the easiest solution for this?
This is for someone who wants to keep it simple. If it was simple though, I guess I would not be posting this!!! Haha.
They best part is they have MS-Works on their machines.
Any help or better yet a completed solution would be much appreciated.
Thank you
Take Care
Tom
View 1 Replies
View Related
Nov 25, 2005
hello all
i need a small thing to change in a module but am not too familiar with vb so i reached a dead end, here's the story:
i have a 'dog pedigree' database that i was working on, now there's a report where a dog's parents, their parents, and their parents need to be shown
that turned out to be out of my league so i paid a guy to do it for me, it worked except he didn't do exactly what's need and i can't get to him now :S
anyway, in the report page,
you can notice that the name of each parent is shown, but i want the registration number of the parents to show as well,
i tried modifying the module but it didn't work (am not a vb user), so can anyone help me add the reg number to the name of the parents?
also, in the report, the dog's color is appearing as a number instead of an actual color so if that can be fixed as well ...
to understand better:
record 1 in dogs, click the preview diploma button
you'll notice "tat" as dogname and "ry245" below that as registration number
now sire is "pat" and dam is "titu", i want their registration numbers to show as well (and same for the rest of the parents)
i uploaded the file here for a closer look:
http://www.designcrafts.org/dogs1.zip
thanks :)
View 14 Replies
View Related
Oct 24, 2006
Okay, brief overview.
We currently have 2 MDE front ends to and oracle box. Database is about 25Gigs and have been minimising the traffic by optimising the queries. However we have always noticed some very strange problems now that our user base is about 150. They seem to become more apparent when the MDE files reach a certain size. I have implimented an autoshut down and inactivity shut down protocol into the access front ends but I would really like to know why the MDE files are getting so large. They are normally about 5MB but get to in exccess of 130MB. The autoshut down is forcing everyone out which compacts it overnight!
When these databases reach critical mass, peoples data seems to be getting mixed up and one persons form is mixing data up with anothers!!!!!! What is casuing this? We are using the front ends over citrix too, is this the root of my problems. I am going to be copying the MDE file to each citrix box to speed it up and reduce the number of users on the mde. Ideally I would prefer an mde for each user but that is impractical.
Really my question is this, Why does an MDE file with no tables except linked via ODBC increase in size so dramtically? Is there a leak or somthing somewhere?
Thankyou in anticipation of any replies!
Marc
View 5 Replies
View Related
Jun 27, 2007
I made an Access DB program in my PC. I have 17 inches monitor and so I designed everything to fit in that screen. When it was deployed in the user's computer, it did not fit in his screen as he has only 14 inches monitor. The command buttons and other things were not visible and were out of view of the screen. I changed the screen resolution but it made the fonts look much smaller. I have used 12 pt fonts in my Prg. Is it possible to correct my forms so that it automatically fits in all sizes of screen?
View 2 Replies
View Related
Aug 29, 2007
Hi Access World Forums,
I'm an audio/video technician at a college and wish to use Access for a simple camera booking system, but do not have time to go through all the extensive help menus and tutorials about this program, and none I have viewed seemed to help.
I want four columns in the table. First, the user inputs a student ID number. Now my first problem is that I want the student name to appear in the 2nd column after entering their number. I don't know where to create the list of names or how to link them to their student ID numbers.
My second problem is I would like the 4th column simply to display the date seven days from the current one (as in whenever the db is used) for the return date without having to input it manually.
Any help towards these 2 problems would be most appreciated, and thanks in advance.
Edit: I'm using Access 2003.
View 4 Replies
View Related