This is my first question posted on this site. I have created a survey with questions to be answered. The database is already populated with records of the people's names who were getting the survey. What I need is when the data entry clerk brings up the customer's name on the form, is that they are forced to enter the surveys answers for certain questions before they click the "Save" button. The answers are set up as radio buttons on the form (ie Less than 6 Months, 6 Months, 8 months and 1 Year).
Not sure if a Before or AfterUpdate macro is needed?
Any suggestions would be appreciated. Please email me directly if you have code.
I am fairly new to access but so far I have been able to get what i need from it, until now. I am trying to find a way of comparing two sets of data to find out an employee's average productivty.
Table 1 -Hours Worked (by day) - contains 'name' 'date' and '# of hours' worked - an employee would enter the hours here on a daily basis
Table 2 - Contracts Keyed (by month) - contains the number of contracts worked that is derived from seperate system - this is entered on a monthly basis (so for example: John keyed 30 contracts for the month of January) - the system i am pulling this info from does not have the ability to pull a daily count of contract per employee, only a range of dates and it then provides the sum for that range (unless I ran a query for each day, for each employee which would take me hours) - employees dont have access to this system to enter their own # of contracts keyed on a daily basis. - for entry, so far i have just been putting the first of the month and then the # of contracts.
In a nutshell, this is the calculation I am trying to create:
(Sum of "# of hours" for the month) / (total "# of contracts keyed" for the month) = employees average hourly productivity.
I have tried to do this with various types of queries and reports but with no luck, I get a prompt saying that access can't compare the 2 fields.
Is there a way to compare the data that is entered daily with the data i would enter monthly?
Hi I have designed a database for a small organization. It is a ver small database. can any one verify and tell me the mistakes in my design. It hardly takes 20 min for the people like you. This is my first step in designing. I am attaching the zip file which contains dbm file and business requirements. Complete physical data model is there.
especially in "category_tests" table i feel that i have done some thing wrong.
Is there any way user can verify the data that its enter correctly, for example I want a button on user form, after every 14 entries when user hit that button it bring the sum of 14 entries.
I have a form with several tabs on it, where the user enters various info.. I want to have the user hit the save button and have all info saved to a single table. Is there an easy way to do this. Please excuse my ignorance but I am fairly new to Access. Thanks in advance!
I have a form where users enter data in various ways - combo boxes with drop down selections and text boxes where users manually enter some data. I also have several text boxes that return "answers" based on calculations from user entries mentioned above. The form is bound to a table where all the entered data is stored. I'm having trouble saving the calculated information to the bound table. It's easy in all the non-calced boxes, I just put the column name of the bound table in the control source, but in the calculated fields, I already have the formula in the control source. How can I identify the proper column to save the data in this case.
1. I'm in need of some way of preventing a user from opening form B unless he has entered information in form A.
Right now it is possible to open form B without entering data in form A (form A being the date/time info and form B being the case technical info) due to which a record is created for clientnr 0 (which should be a non existing client). Querying for clientnr 0 from time to time and deleting those records is easy enough, but who knows what kind of relevant data could be stored accidentally in a record that doesn't belong there.
This happens by the way, even though referential integrity is enforced throughout the dbase. Maybe I should say, because of that. I want to keep it that way of course to prevent orphans roaming around. But having orphans for clientnr. 0 is also not a good idea.
2. What is the wiser thing to do: keep all information concerning one case for one client (date/time info per case, techinfo per case, maybe even products sold info per case, payment info per case) in one table and have the data needed for each thing entered in seperate forms, or have all data split up into seperate tables like I have now. One for clients, one for dates and times, one for the technical info for that case etc.... ?
It is starting to seem a bit ehrm... useless to have all that data for one case floating around seperate tables. I don't know... it seems so much more complex (having to create multiple relations, multiple PK's per table etc.).
Where do I draw the line? For example the image attached (relationship2.jpg): now I have one PK in the table holding the clients (clientnr), two PK's in the table holding the case date/time info (clientnr and casenumberdatetimeinfo), three PK's in the table holding the techinfo (clientnr, casenumberdatetimeinfo and casetechinfonr). And what's next? Four PK's in the table holding the sold productsinfo? Five PK's in the table holding the invoice info? And what about the relations between those tables? Right now I can still comprehend.... but when I start thinking ahead, I'm starting to get dizzy. So some advice on this would really be highly appreciated.
3. Which build up of the relations between the tables in the dbase is better? The one in relationship.jpg or the one in relationship2.jpg?
I am not sure where this thread should be in forms or queries. I have database which has 3 linked tables by ID in each table. I have built a query on the 3 tables, then created the form.
Now when I enter data on the form and save it, when I open up the said form again , the data is not there. Yet the data is in both the table and query
If I only use 1 table, then there is no problem. The record source for the form is the query, and the query uses the 3 tables.
I have a form that the user enters employees daily duties. I have code to run sql after the BoxNum field has data in it. I want it to look at the BoxNum field and the dropdown box for the task to see if the entered box number has already been completed for the task selected on the form. Here is what my code looks like now. The code runs but gives me the msgbox no matter if it is a made up box number and I also get a syntax error 3075.
Code:
Private Sub Text13_BeforeUpdate(Cancel As Integer) Dim rs As DAO.Recordset Dim strSQL As String
Can someone go through my forms and help me figure out why I cannot submit the entered data. Whenever I try to test my system and enter in inspection information I get an error that says....
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data and try again."
Steps:
1.) Type the given sample account number in the blue box 2.) Click "Search Acct #" button 3.) Click "Test" button 4.) Enter random info on the test form 5.) Read error message 6.) Help Ken_C :D
I am developing a form where students have to enter their ID number but I want my form to check through a table of students and their IDs and flag up if the ID number does not exist in the table.
I have a parameter form I used to enter data for a query. I want it to clear any previous data entered when it opens. I have tried the below without success. I also have the fields defaults set as "" and the form set at yes on data entry.
Private Sub Form_Open(Cancel As Integer) Me.BldgCmb.Value = "" Me.StateCmb.Value = "" Me.CityCmb.Value = "" Me.AddCmb.Value = "" End Sub
I am attempting my first access database which tracks blood components in our medical facility. When a component is issued to a patient there is a button to push which links to a report that runs a query. The user must enter the unique key for the component at a promt, which ensures the report generated will be for that component only. My question is if there is a way to cpture and use that key automatically when pressing the button. If I need to clarify something, please let me know--I may not have the Access developer lingo down yet...
I have a main form titled "projects" with a subform titled "inventory". They are linked by two fields "project" and "fiscal year" with a one to many relationship. These fields are also the primary keys in the main form. I also have the referential integrity option activated.
Whenever data is entered in the subform inventory, it duplicates the main form and all the other subforms linked to the main form. (I have about 11 other subforms linked to the main form). So if I enter 2 records in the inventory tab, there will be 2 records for the same project and fiscal year in the subform and all the other linked subforms with the same data, so it is almost like it is seeing the inventory subform as the main form. I have tried deleting the duplicate record from the main form, then it deletes the 2nd entry of the subform. I have attached a pdf with a print screen of the 2 duplicate records with the same fiscal year and project name.
I have another form called Notes that has a similar relationship, but doesn't have this problem. I think it might have something to do with the conversion. I originally created the database with Access 97 and we are now using Access 2007. Am I missing something that needs to be done in Access 2007 to link the 2 forms, so I can enter records in the subform without duplications?
Also, the database is split so there is a back end and front end. I have all the tables and relationships set in the back end.
I want to check if data entered in a form field is existed
The form is bounded to a table
I used this code
If DLookup("Telegram_Number", "tbl_Violation_Of_Building", "Telegram_Number Like " & Forms!frm_Add_Violation_Building!Telegram_Number) Then MsgBox ("number existed") Me.Telegram_Number = "" Else End If
everything is ok but if the data is existed the database show the message and clear the field but i'm getting a Run-time error
'-2147352567(80020009)': the macro or function set to the beforeupdate or validationRule property for this field is preventing [ISF] from saving the data in the field
I guess the problem because the form is bounded to a table so he will save automatically
My solution is to unbound the fields and save the data via vba but is there any solution with a bounded form???
I have a database (split into front and backend). Users populate the table using a form on the front end. Recently, it has been pointed out that some of the data entered into one specific textbox is being changed on the table. The data entered is always has a minimum of a letter and number value i.e. "A1", "A2" etc. Upon examining the table, this has changed into numeric values i.e. "1291", "1061", "852"... etc. Looks like it is translating them into both 3 and 4 digit numerical values by the looks of things. Where more complex data is entered such as "2(A1, A2)" these seem unaffected.
The field is set to text, I have set no validation rule, format or input mask. Just can't see why access is changing these values. It seems to be translating them, but I can't see a pattern.
I have a form that request the User to enter a Parcel ID number (99-9a-99-99aaa-aaa-aaaa). The sub form asks for the subdivision number, block number, and lot number. The subdivision number, block number and lot number are normally part of the Parcel ID number but sometimes they can be different. I want the sub form to fill in the Subdivision number, block number, and lot number as a default but allow the user to change the value if needed. currently the sub form will only show what is entered into the table itself if a change is made on the main form the sub form does not reflect the change.
I currently have two froms, "add record" and "add record cont." The reason I have two seperate forms is because when clients create a new record information needs to be saved to two different tables and when creating one from with fields from both tables I ran into many problems. The two tables are named : tblMain, tblFileLoc Currently there is a textbox on both forms named "fileID" the FileID in the first form is from tblMain and is the primary key for that table, the FileID on the second form "Add Record Cont." is just a normal field. When clients enter in the new FileID in the first form "Add Record" and then move onto the next form "Add Record Cont." i need access to bring the entered FileID from the first form and Fill it in the FileID field in the second form. Currently I have tried making the control source for the textbox on the second form = the textbox on the first form but it brought up an error.
I've got a form with a series of combo boxes which are dependent on each other, however i keep finding that after ive entered some data and left a record, when i then return to the record, some of the information ive entered has been deleted and the combo box is blank.
I think it must be requerying the combo boxes everytime the record opens but i dont know why, i have only put requerys "OnChange" because that is the only time the dependent combo boxes should be requeried.
Once the info is entered and ive moved on to a new record, i need for it to stay that way when i return to the record and only change again when i edit it.
I have a field in a database that needs the data entered without any spaces and I'm trying to figure out how to set either the validation rules or the masks so that the system will either automatically delete any spaces or warn the user not to enter them.
My preference would be for them to automatically be deleted.
Any ideas, suggestions?
I can figure out how to make either all numbers or all letters, but the data is both so that doesn't work.
My Edit info form has several fields. The two of interest are: Filestatus - text - open or closed Dateclosed - date format When the file is created, the filestatus field is set to "Open". What I would like to do is when a date is entered into the dateclosed field, automatically change the filestatus to "Closed".
I have a form that student grades on. I use a listbox to pick the names. I also have a pop up form for me to enter new students that are not in the pop up "query" form. I can go to my form with the grades and hit a refresh button and the listbox is updated. I would like for my data to be requeried and up to date when I close the pop form. Any suggestion.
I have a form where the user can add as many rows as he wants - it's usually only 1 or 2 but that's not the point.When he has finished editing he clicks a Save and Exit button.In here I want to save data from the last row he has entered. I have this working but only if the cursor remains on the last row or on the 'new' record row. If he manages to, say, put his cursor on the penultimate row then I save the data for that.
eg.
Col1.........Col2..........Col3..............Col4 AA...........AAA...........AAAA.............AAAAA BB...........BBB...........BBBB.............BBBBB CC...........CCC...........CCCC.............CCCCC DD...........DDD.........DDDD.............DDDDD EE...........EEE............EEEE.............EEEEE NEW LINE
So I for instance want to save Col3 on the last line filled in. Lets say the control is called Col3 then
If the cursor is on NEW LINE I have got the code so that Me.Col3 "EEEE" If the cursor is on the last line (the 5th) I have got the code so that Me.Col3 "EEEE"
If the Cursor is on the first line i get "AAAA", "BBBB" for the 2nd etc. How do I always pick up the last line?
For my work I have created an incident reporting database based on the Desktop Issue tracking database that can be downloaded in MS Access 2007 from the MS website.
I have made a fair few changes to it and it seems to be working well. Managers now want their staff to be able to enter their own issues but have them approved by the manager before being saved into the database. How do I go about this?
The DB does get the username of the person who is logged into the pc.
