Forms :: Daily Data Collection With Constant Elements
Jan 26, 2014
I have a table that is populated everyday, with following columns:
1. ward (linked to the wards table)
2. date
3. number of patients
We have a total of 18 wards, wherein the daily number of patient in each ward should be recorded. The problem we face is that we find it counter-productive if the data encoder selects a specific ward (dropdown list), then puts the number of patients, and then moves to another field repeating the process. (the date is pre-selected using a combo-box and this will fill the date fields, thus the encoder selects the date only once).
I was wondering if there is a way where we can just automatically show all the wards, so that the data encoder would just proceed on putting the figures.
I have a form that collects data from a survey. The survey has been undertaken before and the equipment being surveyed may carry an ID number and I have the original survey data in a table. If the old survey ID is entered into a field it automatically populates several other fields with the original data....neat!
But that data is not necessarily correct and for this survey I am going to great lengths to ensure the data is as "normalized" as possible so the data is prepopulated into a field with validation. Normally when something is entered into these combo fields I validate with a not in list event and ask if the entry should be added. If I prepopulate with a me.xxxxxx = oldsurveydata the entry is accepted without question. Is there some way to fire the not in list event? I've tried me.repaint, requery etc. but no success.
I have a master/child forms. in master form sale_id,collection notes are placed. and in child form all collection details like collectionnumber,date,etc., child form look like datasheet view.
When i select the record from the list depending on the sale_id all the collection records shown in datasheet view form. If no record is there ok. if more than one records are there in collections of sale_id.i need count of records in collection depending on sale_id and also if i select one row in collection, notes to be shown for that record when i clik on shownotes button.
If more than one record in collection automatically it increment. how many collections for the sale_id. collection number would be increment. How and also show notes depending on collection number and sale_id.
I'm sorry if this isn't the correct forum to be posting in, but I wasn't sure where "I'm very familiar with Access, but I need to do X and I'm not even sure where to begin"-questions go.
I need to create a database that collects monthly metrics for an entire team. So I setup tables
I've sat and stared at this for far too long and I'm no closer. Each Metric will be assigned to a Reviewer. They will be responsible for entering the data for each Metric assigned only to them on a monthly basis. Any suggestions would be greatly appreciated.
I have an unbound listbox with 3 columns. Call it PeopleBx. How can I address the item in the first row and first column. How can I address the first column in the selected row and how can I address the item in row 3 and column 2. I tried with
If I send an HTML message to an individual and they reply back on Monday, can they still reply again on Tuesday to the same message and again on Wednesday, so on and so forth? Will this be adding multiple records to the table?
Also, please consider the same situtation for an InfoPath email. Thanks.
Completing the html wizard for Access Data Collection by e-mail is straightforward enough but I need to change the instructions underneath each of the data fields prior to sending out the e-mail, to ensure (so far as possible) that the forms are completed with the correct information - which, regrettably is not always proving to be the case.
I did this some time back with another e-mail ADC form I completed but for the life of me cannot remember how!!
I want to use Access to collect data for our research department. I have created a number of forms to assist the department in collecting data. I created a query with all the fields from the relevant tables to be used to collect the data. I used the option Collect and Update data via email to generate the form for collecting the data and emailed the form to myself for testing (gmail account), however, I cannot edit any of the fields.
I have set up an access database and want to collect data using the e-mail feature. I can use the feature but the form it sends to fill out is very basic and not that user friendly. I want to put in directions to the form, is there a way to change the style of the form?
I am trying to send out request-for-quote sheets to vendors and i need to send the image of the product as well. when i send emails for data collection, the attachment is not included. how can i attach images?
I have a .mdb file (access 2003) which has ballooned in size to 1.2GB and I'm not sure why. I tried deleting some older tables (which were copies and had about 38,000 rows each) but it hasn't made a dent. Is there a way to show all the database elements tables, forms, macros, modules etc and list their sizes so I can see what has caused the size to increase?
There are 3 tables linked via ODBC which have tens of thousands of rows but as these are linked I wouldn't have thought they would have increased the size at all?
I am trying to send out data collection emails to fill in my records but it will only let me select fields from single access tables, I want to be able to get them to fill out all the fields as entered in my forms, which contain sub-forms and therefore links to multiple tables.
I have a need to convert the export of an Access report to an ASCII file.
The export needs to have a specific format - described below:
Field Position | Field Length | Field Name | Field format
1-6 | 6 | Member # | text - right justified 7-12 | 6 | Trans Date | YYMMDD 13-15 | 3 | Pay code | Alpha/ NUM - Left 16-26 | 11 | Amount | -9999999.99 (*) 64-71 | 8 | Check # | Right
general comments and questions:
This report needs to interface with some sort of transaction accounting system - No Idea what it is, but this format of the report will interface.
The field positions go from 1 to 73. I need only supply the data above, so the check # is way out of sequence. Do I need to have the space inbetween "Amount" and Check Number in the report? Will a simple text file work with all the fieds together? or will I need the space in there? Is there a way to design a report in an ASCII format in access?
Specific Questions
I think I need to change the format of the amount and date fields. I am currently using the short date format 8/30/04, but need to return the date as YYMMDD - 040830. How can I change this format to reflect the ASCII format. The form is also set with the default "=Date()" so the user doesn't have to enter the date everytime - it can also be edited for older data. I would like the date to appear the way it is on the form 08/30/04 but get entered to the table as 040830. Is there a way to do that?
The amount field is odd - I'll include the exact criteria that is desired:
"All Amount Fields must be right justified and space filled. If the amount is a credit, show the "-" sign at the beginning of the number. Payment should be entered as positive amounts. If zero, assign the fied as 0.00 (space filled)"
Is this just a mask that needs to be added? Currently, The field shows $7.00 when 7 is entered to add cent, you must type in 7.50, tab will put in the "$". I need to get rid of the $ and space fill the field?
I currently have a table that holds 2 months of data. Let's just use January/February 2004 as an example.
The field name is [DateReceived] and goes from '1/1/2004' to '2/29/2004'. I need to group my records in 7-day spans for a future query, so anything from 1/1 to 1/7 would be grouped, etc.....
I have no idea how to do this. Any help would be great.
How do I create a daily table from monthly data? I have a monthly table and want to split it into a daily table by dividing each monthly value by the number of days in that month. I need this so I can compare the new daily values to other daily values.
I am trying to transfer daily data that I get from three different queries all into one Excel sheet. I take it that you have to make one over-arching query which I have made called Awaiting Base.
I want to be able to append data programatically once daily OnClose.Although users can log-off & on as many times,but the Append should be once & after then,update subsequent records for that day automatically from table1-table2.
I am currently updating our old Access DB 2003 to 2013 and wanted to make a few modifications along the way.I would really like to allow users to leave notes for each other on a front page. This front page form does not have any underlying table to it as it is only used as a switchboard.My question is, is it possible to have some sort of memo or text box that users can enter notes into that can be easily viewed from the front switchboard and then they can delete/update the notes as required?
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?
I have a table T_RollDate It only has one field, called RollDate It only has (and only ever has) one record (being a date)
I also have a form F_RollDate that has T_RollDate as record source, containing a text box that has RollDate as the control source.
It has been suggested to me that I can interrogate the value of the record in T_RollDate as if it were a constant, and use that constant in (eg) other select queries. To be precise, I was told that forms!F_RollDate!RollDate would return the value of that record.
I cannot get this to work (assuming that it should be possible). If I refer to forms!F_RollDate!RollDate in an query then when I run the query I am prompted to enter a parameter value for forms!F_RollDate!RollDate, where I had hoped that it would select the value of the only record in T_Rolldate.RollDate.
Hi at the moment I am using a bit of VBA code like below:
Public Const currentYear As String = "0708"
Static Function GetCurrentYear() GetCurrentYear = currentYear End Function
I then call this from my ms access sql statement with GetCurrentYear() am I correct in thinking this will only need to be evaluated once (I am just thinking in terms of performance) as it is a static function and a constant or is there a quicker way to do this. I couldn't see a way to easily get the value from a constant without a function. I may be missing something though thanks for any advice.
I tried to display a variable and constant onto a text box by putting =myvariable onto the data source of the text box. However, Access seems keep threating my variable as a data field and giving me error message. What to do with this?
Can anyone help me with setting up a Visual Basic project to where the datasource I use is a Access database on my harddrive. This Access database isn't linked to a server or anything. It's just a database I created and would like for my project to open this file everytime it's attempting to pull data. How can I make this connection? If I am able to use ADO, how do I go about doing this? I would think ADO wouldn't be necessary though.
I'm trying to do the following with the code below:
1. If the file is not in your My Documents folder than goto errhandler and a message box pops up asking you to retry or cancel. 2. When the user clicks retry it goes back and trys again but if the file still isn't there a runtime error 1004 occurs.
I want it to continue to loop when the user presses retry or until cancel is pressed. I've been trying with the code below but no luck.
Code:
Public Function AddITARPicOffloadAnalysis() Dim xlApp As Object Dim wb As Object Dim ws As Object Dim Lastrow As Long Dim objFolders As Object Set xlApp = CreateObject("Excel.Application")
