I created a report database that rely's on both default data and user entered data. Unfortunately the default data is going to be different for each office and needs to be easily updated as needed. So I created a "default data table" and named it DefaultData_tbl; and has these field names in it:
Office
Phone
Address
Fax
The personnel Main form is run from the Main_tbl with the exception of the items above (which reside on the DefaultData_tbl). This default data then needs to be automatically applied into the Main_tbl, via the Main form, which has Main_tbl as it's data source. Then the user does not have to keep filling in these textboxes for each personnel entry (unless it is different for a particular reason; which they should be able to change as needed and then automatically revert back to the DefaultData_tbl for any future personnel inputs). A personnel report (to file in their personnel records) is printed and will also need to pull from this table the items listed above (unless it is changed by the user for that one person they were entering in). The report name is NIPRNet_rpt.
For instance:
Say the Office is "Marketing"...
Everywhere on the reports, tables and forms that asks for the office should automatically put "Marketing" in the spot.
Now, if I get someone who is working in Recieving today, I should be able to update those office boxes with "Receiving" (just this one time) which will also have to be updated on the Main_tbl and NIPRNet_rpt (but should NOT update the DefaultData_tbl).
Then it should automatically revert back to "Marketing" when I pull up a new user
Putting this information in the default data under properties works fine, but I am sending this out to all the offices and want them to be able to easily enter in their particular default data. I don't want to have to create 30 databases all with default data set in them already. Plus, if something changes, like their phone number, I want them to be able to update this in their default data form and it be updated for future use.
I tried putting this in the Office textbox on the Entry Form:
=[Tables]![DefaultData_tbl]![Office]
All I get in the textbox is "Name?"
I would make it a Label instead of a Textbox to associate it, but the user has to have the ability to easily change this default data inside the form as they are filling it out. This updated data is also needed to update the Main_tbl and the NIPRNet_rpt.
I am racking my brain trying to figure out what I am missing here. The DefaultData_tbl will not have more than one record in it; as all it is doing is storing the "initial setup" default data to be used in reports and forms. Anybody know what else I can try to get this to work?
I'm trying to use a form to store some usefull data on my database.
I'm using the DefaultValue property of the TextBoxes in this code:
Code: Sub Comando17_Click() Testo4.DefaultValue = """sasso""" Testo6.DefaultValue = """sdr""" Testo0.DefaultValue = Testo0.Value DoCmd.Save End Sub
Comando17 is the name of the button to run the macro Testo4, Testo6 and Testo0 are the names of my TextBoxes sasso and sdr are the values I want to set as Default
When I run this macro it changes the DefaultValue property on VBA local variables and the Value property, but the DefaultValue on the form structure remains unchanged.
I have set it up and now need to create an invoice section. An invoice needs to be issued every month and is worked out on a percentage of works complete. For example in one job there may be 10 items of work and for 8 of these iems 25% of the work is complete and the other 2 no work has started. Therefore my invoice needs to be able to calculate 25% of the value of the 8 items and work out a total price to be invoiced. Each time an invoice is created I need to be able to store the invoice value and the percent value of each item complete. Does any one have any ideas as to a good way of doing this. That is if you can undersatnd what I am asking for. Thanks for any help.
I have a handful of misc. data that is unique an really doesn't fit anywhere... I need it to be easily updateable, as it will and needs to change from time to time, What should I do with it?
Some of the data is a "Common Footer" that prints on ALL reports and DOES change periodically. I decided to create a common footer table with a single value and can access it from all of my reports... it works great!
However, I now have a bunch of other misc. data that I need to store, like the "Working Year" (I am going to use the field to dictate which year of info to pull the data from) "Common Header", etc. Additionally, I am considering storing formatting data such as font size (still considering this).
So with all this explained, to sum it all up, what is the best method for handling all of this misc. data? I didn't think creating a separate table for each piece was the best idea.
Sorry if this is an elementary question, but here goes.
What I want to do is have Access run a query based on the user name "taken from a login screen". That query will return some values such as what team the Supervisor is responsible for, what days off his team has etc...
This information will be used to display all other queries that particular sup runs. When another sup signs in obviously the results will be different
And onto the problem
I have the query running and displaying the result in a datasheet view. I created this to see if the queries work. Now what I would like is to do away with this query (it pops up at startup) and have access save the user name in memory instead of running this query. I also need to figure out how to reference this "user name" piece of data, that will be in memory on the queries.
would appreciate any help you can give on this topic. I have been trying to solve this issue for a loooooong time.
I would like to know if I can control what field the check box enters the -1 value in my record depending on a value from another field in that record. In my table called workbook I have fields called, phone type which is a text field, wall and wm entry which are both yes/no fields. I want to put one check box on my form to check when a wall mount is needed for the device. The thing is there are two different wall mount sizes. So I need to check the phone type field on my form, if the value is ENTRY then I need to have it enter -1 in the WM Entry field. If the field anything other than ENTRY then the -1 value should go in the WALL field. Can this be doen and how do I code it?
I am trying to add an attendance records to my database but cannot figure out how best to do it..I already have a 'children' table were all the kids info is stored and have created a 'roll' table.
i want to be able to open a form and search first and/or last name from the 'Children' table, then be able to save both first and last names and the date into the 'Roll' table. (then open reports etc later based on dates)how to pull data from one table and store it in another.
I'm thinking of 2 different ways, but not sure how Access will handle them.
1) A table that maintains the start and stop date of the relationship (i.e. employee has a job title from a start date to an end date).
This is the ideal, but I'm concerned about the number of records. The database will store 3,000 employees and I'd estimate around 2000 changes a month can occur to the employee data (transfers, hires, promotions, terminations and all cascading changes on dependent information).
2) A different database for each month/year. (i.e. Employees_March2013, Employees_April2013)
I don't have concerns about the number of records, but I'm not sure how the front-end will work with multiple back-end databases. Is there an easy way to setup a form to choose which "effective date" of employee information you'd like to choose and have it link to the correct back-end at that point before running a query/report?
I have 2 tables one, Contacts, primary key ContactID this table contains names, addresses etc and one group called Form primary key Form ID, foreign Key Contact ID. I want to store the related contact ID in my form but display firstname ad surname from Contaacts list. This works fine when I am selecting name. List box shows 2 columns with correct data but when I recall the form after making other entries it only displays the firstname. The correct ContactID is stored. Why does this happen.
If I can get this working I would like to concatenate the Firstname and surname to make it more readable but don't know where to begin.
How to insert a timer in the PropertyFrm Form in the Access Database attached to this message. When a user opens the PropertyFrm form I want the timer to record the number of hours, minutes, and seconds each user spend time on a record including the current dateand have the data stored in the Timer table.
When a user goes to another record, I want the timer to reset and store the hours, minutes, seconds, and current date on another record in the Timer Table and so on. If the fields and data types in the Timer table is not created properly.
I do not want the time and current date to be displayed on the PropertyFrm Form. I want the information to be stored in the Timer table.I do not want the user to see the hours, minutes, seconds and current date information on thePropertyFrm Form.
My department handles all two-way email communication with our customers. We have 8 different email addresses that we use depending on the customer service issue. I'm looking for a way to continuously collect 3 pieces of data from every email that is received: 1) Date Received, 2) Time Received, and 3) To Field (which of the 8 email addresses it was sent to).
As you can imagine the collection of this data to look for trends to assist with staffing needs, as well as analyze build reports for the company to review. I've been working with my IT department on this but they are not sure where to start. My guess is that I need to have them collect the data as it is coming into the email server, right? I'm good with VB and I've built some VBA scripts recently that collect Outlook information, but these only work if the computer I'm using stays on all the time which is not always practical. We'd like to have this database stored on one of our shared drives which collects this data indefinitely from our email server.
I am writing the following code that will first of all display column headers dynamically using "Headers" field data from Access table and then find out the sum(volume) using column header and first column values. The following code works fine to display headers dynamically in Excelsheet from Access table but doesn't display sum(volume) in all the corresponding cells. As I can't attach the Access table so I have stored data from Access table to sheet named "Access Data" as attached. The sheet2 named "Report" should populate total volume .
Code: Public Function Inputdata() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim r As Long Dim i As Integer
I have a very large Access application and use a hidden "main switchboard" to store data such as the current customer ID. Prior to the advent of tempvars, I embedded a key control into many query's underlying combo boxes, list boxes and forms criteria:
"Forms![main switchboard]![currentcustomerid].
The problem arises when the user quits the application on with forms using this in their underlying query(ies). As far as I can tell, the application closes forms in the order in which they were opened, so the "main switchboard" closes first. Then, for some reason, the current form has a requery and the user is presented with a prompt to enter the parameter "forms![main switchboard]![currentcustomerid]. I tested and found I could replace the criteria reference to a tempvar to "fix" the problem. I wonder if there would be a way to open every query in design view and do a search and replace on the criteria statement.
How do I make sure that for every record entered, a certain field automatically has a number 1 in it? I thought that it might be that you set the Default Value to 1 by typing "1" in the bit where it says "Default Value" at the bottom of the page. But this doesn't seem to work. :o
I am working on a database for my boss that stores the results of monthly file audits and generates reports. Each month about fifty files are checked against a check list and the results are to be entered into the database. Reports are generated quarterly. I have a date field that holds the date of the review. I would like the default value of this field to be what ever was entered in the previous record. That way this field can be tabbed through until the next month. When the data in the field is changed that will become the new default for the following records. I don't have any formal VB or VBA training. I just learn what I need for each project and save it for later use.
I've made a data access page which is in data entry mode. One of the fields is a date field which I have set the default value as =Date(), but it only inserts the default value when you move to a new record it doesn't put it in on the initial record.
The database was developed on a computer with only English input setup. The user group is a combination of computers with only English input setup and both English and Chinese input setup. For those computers with both English and Chinese input setup, when they use the form to enter the records, the data input is in Chinese input. The users are very annoying to change to English input before data input. It is not the worse case and the most worse case is that when go to next new record, the input is automatically set back to Chinese and the user needs to manually changes to English before continue the data input. Is there any way to set the data input to English by default and if need, the user manually change to Chinese?
I have a basic invoicing setup, with a Form (Invoices) and subform (InvoiceDetails).When in the subform, i have a combo box to choose a Product Code (saved in table as PCode).I want now to auto fill in the NettPrice and (Product Description) PDesc fields in the subform row - by looking these up in the Products Table and entering the data into the relevant fields on the Subform. This lookup will be based on PCode.
I tried all sorts of methods and the one i favour, if i could get it to work, is setting up a Function then calling this function from the Default Value property of each field involved.So, for the Product Description field (PDesc), i created a Function as follows:
Function GetDesc () As String GetDesc = DLookup ("[PDesc]", "[Products]", "[PCode] = " & Forms!InvoiceDetails!PCode) End Function
Then i try to call by entering =GetDesc () into the Default Value property for the PDesc field.I seem to have a syntax problem with my function code.I know some of my values like NettPrice need not be fields on my Invoice Details table, but the prices change and I also need to be able to overwrite prices etc when typing invoice.
I have a form that has number entries. I changed the Default Value for these numbers to Null because I dont want 0s (they aren't being used for numeric calculations, they just hold a number). I want there to be nothing in the input field until I put it there.
My problem is, when I start to enter info into my form, access automatically places 0s into all my number entries! Is there a way to avoid this? Or should I just make the fields text instead?
I am a beginner working with access (2013). I am looking for a way to retrieve data from one table and store these values in a other table. The tables are in the same database.
For example: Table 1 Input [OrderNr] Input [Nr] (linked to Table 2 [ID] Input [Amount] From Table 2 Get [Product]
Get [Unit] Get [Price] [TotalPrice] =[Amount] * [Price]
The result should be wiewed as a datasheet.
Problem 1 I have made the form and the links between the tables and it works alright on screen. Except the sum-function It does not work. I can only get the amount of items in the summery field for column TotalPrice. Is it because it is a column for calculated values? Other columns including not calculated numeric values works alright with the sum-function.
Problem 2 The data from table2 will not be stored in table 1
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 have a form with a combo boxes and a table with relevant list and additional field, fldDefaultDrive (Yes/No Field).
Currently in order to set the default value, I have used the following code for each default;
Private Sub Form_Load() Forms!frmMediaLabeller!CboDriveName.DefaultValue = """D"""
End Sub
However, I want users to be able to go into the table and change the default value if thier CD player default Drive is anything but D: Drive. I have tried to replace the D above with an SQL statement but with no success.
Private Sub Form_Load()
Dim Drivename As String
Drivename = SELECT tblMediaDrive.fldDrivename FROM tblMediaDrive WHERE (((tblMediaDrive.fldDefaultDrive)=-1));
