I currently have a continuous form in my database that has 24 records in it. every record is blank except for a time (I will post a screenshot)
Our employees are instructed to enter various information every hour in this form. coding a button that, when clicked, will create 24 new records in the table that this form is based on, insert times in those 24 records, and then display those 24 records in the form. It would have the illusion that the end user it "resetting" the form at the end of his shift and preparing a clean form for the next person.
In the spreadsheet that we currently use, there is a button that, when pressed, saves the workbook with the date in its file name and then clears all of the end-user's data so that the next shift has a clean spreadsheet to use. I would like to mimic this button as closely as possible in MS Access
I understand that this database design is not exactly ideal in the world of database engineering; however, I was told to make this a perfectly seamless transition from the excel spreadsheet we currently use (there is a folder with 900+ workbooks from over the years. This obviously makes the information useless - we need all of the information in one file - this database)
I am creating a program to allows us to figure out where our personnel are. I am using a continuous Form that shows the individuals name, phone numbers etc. and infront of that is a checkbox, and a combobox that shows "Leave", "TDY", "Contacted" etc. I also have two fields that fill up with what time they were contacted or when they reported in. The default status is "Not Attempted and the checkbox is not checked, and the time fields are blank.
I attempted to use this code:
Dim ctl As Control For Each ctl In Me.Controls Select Case ctl.ControlType Case acCheckBox ctl.Value = False Case acComboBox ctl.Value = "Not Attempted" End Select Next ctl
but it will only clear the one line (it doesn't clear the times yet, i figure i'd add that once I figure out to clear one). This seems to work if this was a single form, but not for continuous. How can I convert this for continuous forms?
I've attached my access file...if anyone could help I would appreciate it very much!
If you click on the Export & Email tab on my form, there is a button which describes what i want to do in the caption.
Basically...
When the button is clicked i want a warning message box to appear saying do you want to continue Yes/No. No would just cancel it.
Yes would do the following:
Export an Excel file of "Magtable" and save to C:Magazine BackupMagazine Counter.xls and W:Magazine CounterMagazine Counter.xls
Then (i dont think the order of the following matters)
Email the excel file to two people.....if its easy to do this with no data input needed by the user (ie: not having to type the subject, who its too, body or press send.....if this is too complex then that is fine..having as much in as possible would be good though)
Then I want the values in Magtable, Amount to be reset to 0 - this may have to be done before the email is sent?
BTW the company i work at uses Lotus Notes only as an email program....As long as it pops up a new email in lotus notes with the excel file as an attachment then that would probablly be enough.
Hope this makes sense :S Any help would be VERY appreciated!
I need to create a production forecast form based on previous sales history.The history is based a sales and grouped by month & year
So on the form, which needs to be a continuous form, I want products to show as rows and months as columns The sales history per month needs to be displayed as well as a field allowing to user to enter the production forecast.
I can write the sales history to a temp table.However I never know how many months history the user is going to want displayed at run time. Could be 3, 7, 12 or 15!
Attached spreadsheet shows what I am trying to achieve. Is this possible and if yes, how would I do it?
I have a form based on a table with just sales quotes and dates. I am using the salesquote as a combo box to select corresponding records in a subform.
Code: Private Sub cboSalesquote_AfterUpdate() Me.Filter = "[salesquote]=" & cboSalesquote Me.FilterOn = True End Sub
Works fine. But not the mgr says his users get confused when they are making changes in the subform which record they are changing so they want when they select a record from the combo box and the records appear in the subform; they want the combo box to go blank. I am not sure how to do this because if I reset the combo box to go blank; the subform records will go blank as well.
I have a combo box, 'Type' that has two values - "Instrument" and "Equipment".Another combo box control, 'Criticality', has an Enabled = False value set OnCurrent.
When the user selects "Equipment", Criticality goes Enabled = True and when OnUpdate to "Instrument", Criticality goes Enabled = False.The Criticality field correctly displays and switches off if someone selects Instrument.
However, if someone puts a value in whilst Type is set to "Equipment" and then Type is reset to "Instrument" and in the meantime the user has selected a value for Criticality, the value stays 'stuck' in the Criticality box, despite the field being disabled. It is a bound field.How do I purge any contents stored in Criticality when the field is disabled? I presume it's something like an SQL statement to set the value to null, but I'm not quite certain.
I have a SQL Server 2005 backend table with a varchar(1) field which allows nulls. The field is populated from a combo box in Access 2012. The field can be set to 1,2 or 3 via the combo box or left blank in which case its value will be null. Sometimes I need to reset this field to null. How can I do this via a combo box? I know I could just set this to an empty string but this would mean re-writing a load of queries which a looking for a number or null.
Something like 1;"First";2;"Second";3;"Third";Null;"Blank"
I am self-taught on Access and have designed a quite basic database for a friend who owns his own company. This is a client contact database designed to hold information about his clients and their companies, but also to send reminders to Outlook when they haven't been contacted for a while - ie. to make my friend more proactive in his business.
This was a project which I originally agreed to work on for a fixed price. Needless to say, I undersold myself and probably because I am self-taught, with the number of hours I have put in, I probably got paid 2p an hour!
The project is drawing to a close, but he wants to pay me per hour for any further development to the system, and for any similar work he wants doing, problem is I haven't a clue what to charge him per hour?
Any ideas, without divulging your well-earned salaries of course!
On my unbound form I have a combo box with a record source type set to value list and the record source with two choices in it. I would like the combo box to reset back to either blank or a the phrase "select one" once a record is saved. If it shows "select one" I do not want the user to be able to actually select that. As I said the form is an unbound form and I have used the coding of "insert into" to save the new record to a table. I have been able to clear the text box on this form when the new record is save by resetting it's value to "" but have been unable to reset the combo box to blank.
how do I reset the combo box through code after a save to display nothing - or is there a way to add a non-selectable choice to the list and have it display when the form is refreshed.
I have a login form that closes automatically after a specified time if you do not login with user name and password. It also keeps track of how many times you login and whatever value you set for a password reset it forces you to change when that value is met.
Problem: If you do not login within the allotted time even though a password change has been scheduled, my code resets the login count back to 0.
This is what i want to get around if I can. Either I have to pause the timer or stop the reset.
After you enter the password is when the login count is fired.
And also when it checks if a new password is required.
I'm asked to do a specific task, a database concerning of:Tables: 25Forms: 25Reports: 40-45Build from ground up, multi-user, accounts, account-restrictions, has to be linked to an already existing database, will need a fair amount of VBA coding.I would like to know specifically two things:--> what is your common hourly rate --> what would your common total-price-estimate for a project like this.
I have the below code to reset my subform after I have filtered it using some combo boxes. One of my fields in the underlying table had a calculated field so I'd get an error when clicking the reset button. I edited the query to update the 'type' field and deleted the calculated field in the table but now get another error. The exact error is 'Run-Time error '3327' Field 'Type' is based on an expression and cannot be edited.' It appears it will not reset my form and show all records as long as this field is based on an expression.
Code: Private Sub cmdReset_Click() 'Purpose: Clear all the search boxes in the Form Header, and show all records again. Dim ctl As Control 'Clear all the controls in the Form Header section. For Each ctl In Me.Section(acHeader).Controls Select Case ctl.ControlType
We recently have had a change to our hourly rate that we pay and now the Totals calculation is not adding up the numbers correctly.
In the query that the report is based from:
ExtendedTotal: CCur([Hours]*[Rate])
In the footer of the report: (Provides a Total for each Day)
Sub-Total: "Control Source" is: =Sum(CCur([ExtendedTotal]))
Report Footer: (Provides a Grand Total for the Month) Total: "Control Source" is: =Sum([ExtendedTotal])
Problem comes in with the odd dollar rate time partial hours. For example:
$9.73 x 4.5 hours = $43.785 (Rounds to $43.79) which is fine...
but when you have a whole column of these rounded numbers, the totals are coming out off by pennies which add up to a good bit at the end of the month. I cannot match up the totals with the invoices that are coming in either.
I have an unbound form with a combobox which needs to be validated before the control is updated.In the Before Update event I check that the value is valid and need to reset the value back to the previous value if not valid and then change focus to another control.I thought that the following code would work but the selected value remains in the control and I cannot move the focus to another control.
Code: Private Sub cboYears_BeforeUpdate(Cancel As Integer) Dim dteNewDate As Date dteNewDate = DateSerial(Val(Me.cboYears), Month(Me.txtCalendarHeading), 1)
I created a small database with a user login form, along with a password reset form. Running it in my sandbox as an accdr file, the password value resets just fine, but on my test machine with the Access 2010 runtime installed, the password reset form doesn't reset the password. I will try to describe the state of things as of this point.
1. The LoginF presents with a combo box for the user to choose their name, and a text box to enter their password. The code below is on the After Update of the password text box.
Code: Private Sub txtPassword_AfterUpdate() 'Check if user has been chosen If IsNull(Me.cboUser) Then MsgBox "Please Select User Initials", vbInformation, "Warning"
[Code] ....
2. The password reset form has two text boxes for the new password and to re-enter the same, as well as a command button with this code.
Code: Private Sub cmdResetPassword_Click() If Me.Password = Me.PasswordTest And Len(Me.Password) & "" > 0 Then DoCmd.RunCommand acCmdSaveRecord DoCmd.Close DoCmd.OpenForm "LoginF"
[Code] ....
As I say, this works as an accdr file, but not in the actual runtime environment.
New to MS-Access and I'm trying to help our HR with creating a production tracking table.
This is just a small simple table with all 12 months of the year and a total production column for summing up everything that has been produced for the year. But being "very" new to access I have zero clue on how to do this. I would appreciate any Access for this dummy help I can get . Thanks in advance.
I have a database which I use for scheduling production. I would like to know if there is a way to "push" the schedule dates. I have multiple products scheduled for production but at times I need to adjust the production dates by pushing all the schedule dates ahead by one day. I don't want to re-enter all the dates to do this adjustments.
I've got a single table with multiple fields, three of which are a date field ('DDate'), a time field ('TimeET') and a unique identifying field ('Unique Call Key'). I'm attempting to write a query in the QBE that will allow me to count the number of instances of 'UniqueCallKey' for each hourly time interval (7:00:00 AM - 7:59:59 AM, 8:00:00 AM - 8:59:59 AM, 9:00:00 - 9:59:59 AM, etc) for any selected date range (BETWEEN 'DDate'(1) AND 'DDate'(2)). When I try to simply use the Count function on 'UniqueCallKey' as an Expression and 'TimeET' with 'Like '7:*AM' as a Where criteria then do the same with another instance of 'UniqueCallKey' and 'Like 8:*AM' as the criteria for a second Where criteria for 'TimeET' the query returns an empty set. What I'm trying to accomplish would be a column of dates, a second column that counts the number of instances of 'UniqueCallKey' at the 7AM interval for each date in column 1, a third column that counts the number of instances of 'UniqueCallKey' at the 8AM interval for each date in column 1, etc to a final column for counts at 7PM.
I keep the production version of my database on a server, and a copy of it for development work on my desktop. Today I've made several changes to the development version, which didn't work. I then opened the production version held on the server in a separate MS Access window to compare the queries/results to see where the problem was and have found that the changes have automatically updated in the production version. Can anyone explain this? They shouldnt be linked at all. The 2 versions both link to a datawarehouse through an ODBC link, but shouldn't be linking to each other (I'm using Access 2003).
My boss threw a curve at me this morning and I need some guidence. I created a commission tracking db for our industry (automotive). Each technician will have a base commission that they'll get regardless of their production. Should they exceed their production by X, we would like to give them a bonus of 2% of all of their work. Should they exceed their production by X+Y, we'd like to increased the bonus to a total of 4% for their work during this pay period.
I need help with the table stucture, to start. My db currently contains a variety of tables including tblTech. tblTech contains information specific to each tech including their annual PERSONAL income goal (not to be confused with Bonus scale). Depending on a number of factors (ie. how long they've worked here, how much other responsibility, etc.), I'd like to assign a bonus tier to each technicial. ie. Technicial A = Joe. Joe is promoted to manager and therefore needs a greater bonus for the same amount of production. I'd like to assign him to Tier 2 where he makes more then a Tier 1 one employee.
I'm having a hard time getting focused. All suggestions are appreciated.
old Access database that's been upgraded through to Access 2000 or 2003 (probably 2000), but not beyond. Was rebuilt in Access 2003 format around 2006/2007 (by someone else who no longer works here). Being used now in Access 2007 and 2010. Uses DSN to connect to SQL Server backend. Last week it was SQL Server 2005, moved the back end on Saturday to SQL Server 2012. Changed the DSN when moving to the new server. But I don't think this has to do with the DSN (it's getting data just fine).
There's a data entry form. User enters a Generator ID, event triggers it to look for the details for the generator and load them into a generator subform. After it loads the information into the subform, and before the user does anything else, it throws "This connection cannot be used to perform this operation. It is either closed or invalid in this context."It triggers post update of the Generator ID:
Code:
Private Sub txtGeneratorID_AfterUpdate() On Error GoTo Handle_err Me.txtGeneratorID = UCase(Me.txtGeneratorID) Call FillHandlerSubform(Me.subGeneratorInfo, Me.txtGeneratorID.Value) If GetGenStat(Me.txtGeneratorID.Value) = "N" Or GetGenStat(Me.txtGeneratorID.Value) = "OB" Then MsgBox "Warning Generator Status! " & UCase(Me.txtGeneratorID.Value) & vbCrLf & _ "This Generator has a status of N or OB!", vbCritical, "Bad Generator Status!" Cancel = True End If
[code]....
It doesn't throw this error in the development copy of the database, dev copy works just fine. It only throws it from the production version. They are located on the same network, just in different folders. They are pointing to the same database on the same SQL Server using a DSN file located inside the folder where the .mdb file is located (this is a change in the front end, prior to this it was using a DSN on each individual machine, but I changed it to stop that).
I want to release an Access program to the production environment for users. I remember there being a file extention that can be used so the users cannot access the DB or Code.
I have a production application in which i have a table named daily_production with fields as ( prod_id, date, productname, qty ). Now I want a daily production query as
date : xx-xx-xxxx productname | qty | monthlysum(for thsi product) | daily average |
I want this on a single query so that i can make a report out of this .
