Forms :: Using VBA To Populate Date / Time Fields On Subform
Jan 10, 2015
I have a form that contains a subform. On the main form, there are 3 fields: [IncidentNumber], [OpenDate] and [OpenTime]. The subform, [CtrlLog Detail] , contains the log entry details for each incident number. Every incident number can have many log entries. Two of the fields on the subform are [EntryDate] and [EntryTime]. The fields work correctly and the forms are fine.The issue Im having is when a new incident number is created the [OpenDate], [OpenTime] and [EntryDate], [EntryTime] MUST be equal because of some filter queries for statistics. Right now the user must physically type in the date/times in these 4 fields when they create a new incident number, which means I'm having lots of data entry errors.
Using default value on either [EntryDate] or [EntryTime] on the subform doesnt work because it doesnt create a primary key for the underlying table and the subform will not allow any other entry (due to some other linked values) until that PK is created. The forms are based on tables linked by [Activity_ID] as the primary key. I want to create some VBA code to see that if the Incident number is a new number (meaning the user must type in the new incident number creating the primary key for [Activity_ID]), that access will automatically populate the [EntryDate], [EntryTime] fields to match the [OpenDate], [OpenTime] fields on the main form. The user will manually populate the [OpenDate], [OpenTime].
If the number is an existing number (and the user is simply adding a log entry to the incident number) then I want [EntryDate], on the subform to default to the system date/time ([EntryTime] should remain blank for user entry) while keeping [OpenDate], [OpenTime] unchanged. When the [EntryDate], [EntryTime] fields are populated on creation of a new incident number, I want the PK for the underlying table ([Log_ID]) to be generated.
I have a table called Neutron2015. I want the current date and time to complete when entries are made in two other fields. So I am looking for this date and time to populate in EXLStart when an entry is made in Processed By, and also the current date and time to appear in EXLEnd when an entry is made in Completed By with the bold word being the field names.
I'm currently building my first database for a research project. When we enroll a participant, we need to have an enrollment date/time as well as an activation date/time (and they are not allowed to be the same because, rules). I was able to figure out how to auto-populate the current date/time when we've marked a person as enrolled. What I'm wondering is if it is also possible to auto-populate another field with the date/time, ten minutes in the future?
I have built a form that holds details of training records. What I want is when a drop down is selected in the main form, that it will populate some of the fields in the subform. I have this working at the moment, that for example, when a certain course is selected, that their modules will appear in the subform. Where my problem arises is that I have a relationship between two tables that I want to appear on the subform, so that details can be filled in on the subform against the list of modules that automatically appear.
I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?
Im having a problem adding the date and time to a new record using a form. The 1st method i used was by inserting the default value set to Date() & Time() functions, this worked fine for over a year, but now on some computers on the network the following error pops up "function is not available in expressions in table-level validation expression".
The 2nd method I tried was to add a macro with 'setvalue' commands to the After Update event of one of the first field's that was entered, this entered the same information into the Time & Date field once the first field was updated. This now brings up the macro fault screen and the 'Halt' button has to be pressed to clear the error.
This only happens on the older computers that access the database, the new computers don't bring up any errors.
In my form I have a calculated field that works out the time elapsed for a job, worked out as the QTY/RUN RATE which gives me the time in hours. However I couldn't format the box as a date/time, as it is a calculation and the times may go over 24 Hours which results in the date/time giving me something like 31/12/1899 16:00 for example.
I've got around this now and I've formatted the results as HH:NN:SS using a public function in a module, but the problem is this is now stored as a text field. I've tried setting this to a number field but the formatting doesn't carry over. I've tried setting a custom format in the table as HH:NN:SS but then all I get is just a bunch of 0's, no calculation results.
So how to format this as a number field properly?
Also for any extra information:
- I know you're not supposed to store calculated results, but this is slightly different, the calculated field is a standalone text box and the bound field is a formatted text box. - The reason I have to store the calculated results is because my boss wants to eventually see a chart of the total hours of jobs each day, hence why I'm asking if this can be done as a number field.
I have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.
SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName FROM Staff INNER JOIN myTable ON Staff.ID = myTable.StaffID WHERE myTable.myDate >= #3/2/2014# AND myTable.myDate <= #3/3/2014# AND myTable.myTime >= #8:00PM# AND myTable.myTime <= #11:00PM# ORDER BY myDate desc
In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.
I have a main screen that has a tabbed form in it. Each of those tabs has a sub-form in it that displays information and allows some information to be updated.
In one tab I have a query based sub-form that returns information related to the project. This information cannot be manipulated or changed as it comes out of a company managed database. I would like the analysts to be able to add more granular information to a new table by inputting information in a dropdown field for one of the new table fields, but utilizing two of the existing sub-forms fields as identifiers in the new table.
I have a Company form, a typical form with fields for capturing company names, address, phone etc.
The two fields I have an issue with are: CompanyName: a regular text field for full company name ShortName: a regular text field for same data as above but with spaces/blanks removed (to be used as a 'quicksearch' field)
I want the ShortName field to be automatically filled when the CompanyName is entered (on a new record), but allow the ShortName field to be overwritten by the operator.
I would then like to "lock" the ShortName field, such that any subsequent amendment (accidental or otherwise) to the CompanyName will NOT automatically update the ShortName field, but there will still be occasions when legitimate changes to the ShortName field will be required.
Removing the spaces is not a problem; I have a problem determining how to control the automatic updating and subsequent updating/prevention of updating.
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
From a info sheet on RTB Using the Add, Clear, and Remove methods, you can dynamically populate the control with embedded objects at run time. Can those 'embedded objects' be controls, e.g. list boxes, labels etc ?
I've got an access front-end database running on Access Runtime on a Terminal Server with a back-end on a data server both of which are hosted by a remote company and accessed via Terminal Services. All is well and the database is working fine but because this is a cloud system I need a way that I can easily delve into the tables on the back-end without copying it down locally from the cloud each time (I've set up an "administrator" option so that this would be available to limited users). I've got the front-end forms working as I'd like but I have quite a few tables and I'd like to avoid creating a separate form for every table. I've been able to populate a combobox on my form with the list of linked tables and I'd like that when I choose a particular table from this list that the whole table is then visible within a subform below in case I need to manually intervene with the data for any reason.
I want to append/populate all data from one table to subform?
It is possible?
I managed to populate data from one table to another but my relations between form and subform isn't good, because record that is supposed to be filled as the "link master fields" is left blank.
Relationship: Customer ID is the primary key in Customer and is a foreign key in order table.
I need to create a Form with contains all the customer info from the customer table, as a drop down list. Once the customer is selected, all their orders should appear in a subform. At the minute I can get a form to work which shows all the orders but you have to go to the next record so see the order. For example it will say John smith and one order. The next record will be John smith again and their second order in the sub from.
I have used access in the past and I am fine with creating everything apart from the drop down. I am just a bit lost with the structure.
I'm working with a linked table that has a seperate field for the date, and a seperate field for the time (which show in the linked table as '11/30/1899 9:46:00 AM'). I need to calculate the time difference between 2 entries.
How can I merge the 2 fields into a combined date/time field for my calculations?
I have a continuous subform with an unbound Concat textbox and would like to populate another field for each record with the results with a main form button onClick event.
I have attached a portion of my database. if you go into the form called frmtest, select a branch from the very top drop down, select a detail and click the Edit button.how to have the information populate all the fields for editing. The Cost and Quantity fields work but my combo box fields don't properly work. The top combo box populates but when the record is updated it doesn't save the id. The second combo box doesn't even put the data in the data box.The main table where IDs and fields are is the BRANCH_EXP table, it's a linking table which links tables with many to many realationships.
I am creating a query with criteria for a dates column. The column cotaining dates in my Table has many empty fields. I want to limit the criteria for my dates to >#1/1/2004#, but I don't want Access to exclude all the empty fields because I want all the data displayed for the purposes of my report.
What do I do? I'm not terribly familiar with Access, so please explain as completely as possible. Thanks in advance!
I'm combining two date/time fields in a query to an integer. The first field has the date, the second the time. I'd like the resulting integer to be without the opening 0. How can I do that?
That is, these are the two fields: 2006-09-14 (date/time) 15:00:20 (date/time)
And I'd like those two combined to be 60914150020 (integer) in the third field in the query.
I have a table in which each record includes a field showing the time that an event started and then another field which stores "how long did it take", these two fields are both defined as a short Time. I then have a query that runs through the "how long did it take" fields to produce an average time for the whole sequence of events. Two problems:
1. The average comes out as a decimal like 39553.367942 instead of several hours and minutes.
2. When I try to access this value using DLookup on a form I get "#error"
I created a Contact database where I have the date and time as two separate fields in one of the tables. I have them defined as such, but would prefer that the fields wait until I enter data into them and not auto fill with the current information. Is there a property of these fields I can set so that these fields do not get auto filled? Again this database is too large to send, so I hope that you can help without it.
You veterans have probably heard these questions many times, I've had a search around but couldn't find quite what i was after.
This is my first database.
I have 2 tables, one for customer details, and the other is products (holiday packages)
I have made a product form for staff to input new holiday packages. And for the customer form this is also the order form, so a staff member picks up the phone and gets customer details, name, ph, etc and then asks what holiday package they want. I want this to be a drop down list that always fetches all the packages availiable form the product table and when you choose a package it shows all the details on that package.
I need this all to be on the 1 form, if the staff member has to swap back and forth between customer form and product form to get info on a product this will severly hamper productivity.
If my question is abit hard to understand i can take screen shots of my tables/forms to get a better idea. And if someone can really help me finish this database off i'll pay them somethign for there trouble.
