I have two date fields that I am using a form to show the open and close date of an investigation. The opened date field is controlled by a textbox elsewhere on the form, but the date closed is controlled by the vbCalendar control. What I am trying to do in coding is to evaluate whether or not the closed date is before the open date. If the date closed is before the date open I would like a message box to open that prevents the user from moving forward and reminding them that the date closed is earlier than the date open. Is it possible with the vbCalendar option being able to change its focus continually.
Access 2010 project I am trying to develop for my workplace.
I want to be able to show a chart, a graph, of how many open orders we have had during the last month (showing per day) and during the last year (showing per months). In order to do this I am trying to create a set of queries that would provide me with such information in the form/structure;
And after that I would create a chart I have 1. TblOrders [OrderID] Autonum [CaseID] Foreign primary key [DepartmentID] Foreign primary key [DateOrderMade] Date (yyyy-mm-dd) [DateOrderAnswerd] date (yyyy-mm-dd)
2. TblDates [ReportDate] Date (yyyy-mm-dd)
The evaluation in a query that runs all [ReportDate] logically would say;
Code: Count(IIF([ReportDate] BETWEEN [DateOrderMade] AND [DATEORDERANSWERD]);1;0)
I would like to have the Switchboard I created open when the database is selected. I used Tools/Startup to set this up. It works to a point. The database window opens and THEN the Switchboard opens (on top of the database window). There are some who I wish not to see the database navigation window). Also, is it possible to OPEN the switchboard again when a Form is closed?
Is there a way to make the shutter bar be closed on database open?
I already have an autoexec macro to take people to a main menu, but would like to ensure the shutter bar is closed for aesthetics and to keep people away from clicking on something.
Evening All! (Old enough to remember Dixon of Dock Green (stupid enough to mention it!))
I'm working on a small database for a small team of support workers with a client base of 60 clients at anyone time, although the turnover is quite substantial.
I have done some searches on archiving records but am concerned that once archived a record would be difficult to re-integrate in to the live database, particularly if there have been changes to it?
I have considered that I may be just as well slapping a big label over every record that has a date in the [CloseDate] field and if a closed case is re-opened, the closed date is removed and the label disappears.
I'm still open to being convinced that the archiving is the way to go, but in the mean time I hope someone will be able to help with the expression needed in the form on_current procedure to make the label visible. If it was just a tick box I'd be flying by now, but I can't get my head around getting a populated [CloseDate] field to initiate the CloseRecordLabel.
Please have a look at the following code (I am not good at VBA or SQL).
Me.[txtBox] = DLookup(“[SNum]”, “[Table1]”, “[RecID]) If Me.txtBox =>2 Then Condition if true Else Condition if false End If
I type a value in my [txtBox], a number. I want to evaluate if the typed value meets the condition or not. [SNum] is serially numbered and [RecID] is autonumber Primary Key. The above code is not working. How do I achieve this? Please help.
I have query with a parameter [Category]. Based on this value i wish to select team data like if [Category]="Sale" then (team) in (1,9,5), if [Category]="Purchase" then (team)="7" else (team) in (1,9,5,7).
Both the fields team and category are from same table called 'rawdata'
Report displays the data datewise but it is grouping according to team,which is not what i required;
Date CntValues 12/1/2013 1 [team 1] 12/1/2013 3 [team 9]
I am trying to evaluate a field in an access database. I have entered iif([fieldx]="Contributor",1,0). The field that I am evaluating has the word Member or Contributor in it, but my formula always returns a zero (0). What am I doing wrong?
I'm working with a table of financial transactions. I need to know the date a record relating to cash received actually arrived in our bank, but the software doesn't allow the user to enter this, so I've asked them to enter it into a text reference field, e.g. 'Other Text XX 16/04/14', and then I've got a formula - CDate(Right(Trim([RefField]),8)) - which takes the date from this reference.
This works fine, but it's really important I don't miss any of these records due to the date not being entered, so what I'm trying to make is a formula which will give me the date from the ref field as in the formula above if there is one, but if that formula gives an error (so the date has been missed out or incorrectly entered) then I want to take the date from another field, called [DateAdded] as a failsafe.
I can't work out a formula that won't give me an error, i've tried loads. First I tried :
But that gives an error, and I think from looking at forums that Access evaluates both parts of an Iif function so it'll do that.
It seems like other people are saying that you need to use Nz, IsDate or IsNumeric or something along those lines instead of IsError to evaluate the field, but I can't get that to work as it's not a field, it's a portion of a text field.
I have a bunch of reports that need to be printed, but only if they include a certain date. I want all of the dates to show up though.
For example:
There are 3 reports with a bunch of different dates in the bill date column.
Reports A and B include the date 04/30/2012, along with a bunch of other dates. Report C does not include the date 04/30/2012.
i want reports A and B to open since they include the date 04/30/2012, and I want all of the other records with other dates to show up on the reports along with records with a date of 04/30/2012. I do not want Report C to open at all.
Record Primary Key: ID_Wells..The TxtFedStCo has Fed, St, Co, ... in one field - and a Dt_Apd_Sub (date submitted) .There can be zero or many dates submitted for each Fed or State.
Objective: If A Fed has (1 or many) date submitted AND A St has (1 or many) date submitted Condition is TRUE
How do I get to the next step? The reason for doing this in SQL is to prototype in Access, then move this over to TSQL later.
I manage a fleet of vehicles at work with drivers assigned to these vehicles. Each driver may opt to pay a contribution for private use of these vehicles.
This can normally be done on an excel database, however, the drivers sometimes go on leave, so I need to put their payments on hold. At this time, other drivers may take over for that period or the vehicle is left at the office. Also, the drivers change their level of contribution from time to time, so I need to adjust that accordingly too.
When the driver takes over a vehicle they complete a form that has the contribution level (there are 4 to chose from) and the dates they will have it from. They always provide a start date, however, sometimes the end date is left open.
Sometimes, driver A will say something like 'From 1 Jan 2012 To Open' then driver B may take it for a week and say 'From 1 July 2012 To 1 August 2012'.
I want to know if Access is the right tool to produce the report with the current driver, and that it will revert back to the Driver A after 1 August?
I have a search from that has an option group, text fields, and a checkbox where the users selects a variety of option that generates a query. If the query return at least on record a split form (form on top/datasheet on bottom) is displayed and the form has an Edit and Close button. If the query returns no records the form is still displayed except the buttons are not visible.
If I knew the result of the query and then made a decision whether to open the form or release control back to the search box that would be great. To open the form I am using VBA with the DoCmd.OpenForm(,,"MyQuery","criteria") command.I thought about using the DLookup command and evaluate the return value for null to be used to control the program flow.
I've been stomping around in the forums for an hour now and I have to ask for some help because I really don't know what it is I am looking for.
I am interested to know if there is a way to write VBA code that reopens my menu form if all other forms have been closed. The database window is hidden in my app and normally when all windows close this will exit Microsoft Access too.
If there's not an easy, established way for this, I'm not really in a spot that requires me to use this, I just wanted to know for future forms.
I guess my real question is how to you build events for the entire database, not just a single form or report?:confused:
I am stumped as to how to get a form to update/refresh. Heres what I have! My main form:[Daily Log From] is opened, with a subform:[DailyDSQry subform] populated by a Query. This form has a command button that opens another form: [NewDSLog] in "acAdd","acDialog". What I need is: when the [NewDSLog] form closes, the [DailyDSQry Subform] needs to requery/update so that it displays the new entry.
I have tried the following in a command "CLOSE" button in form [NewDSLog]: docmd.Runcommand AcCmdRecordSave Me!Forms![Daily Log]![DailyDSQry subform].Refesh Me!Forms![DailyDSQry subform].[Query].!Requery doCmd.close
but no LUCK.....Keep getting errors Any Ideas Thanks all, I do appreciate the help Enviva
I have a DB with 20 forms. All of the forms are closed. I opened a new form and added a button. I would like to do the following when the button is clicked:
Check the "Tag" property of each form and count whichever is not null.
I have a form which is associated with a table. When I put some values in the fields which are connected to the database and try to close the form, it gives me an error that "Can't insert record the primary key might be duplicated"...! and then it closes.
Does the form store a record in the table while closing??
If so is there a way to prevent it?? Please reply..
Hi, I have code for a command button that allows me to save the form information to 7 different tables primary key (the same primary key), owever when i close the form it resaves the information and i get a duplicate error msg. Is there anyway to get around this? Can I change what happens when the form is closed. i tried to apply this code to Form_Close() but to no avail.
Hi all. I have a form that opens up another form when you click on a button. The button does more than just open the form, it does other checks. I want to wait until the opened form is closed to continue with the subroutine. Is this even possible in VBA? I was wondering if there was a frmFormName.IsOpen or some kind of flag that would tell me if a form is open or not. Any help would be greatly appreciated. Thanks in advance.
Sub open Checks things Checks things checks things
Opens form(frmDetails) Wait for form to close (THING I DON'T KNOW HOW TO DO IF POSSIBLE)
I am running Access 2010. I am building a form that is finding the "Docket Numbers" from three different forms. In order to get these numbers, I put in three text boxes with these statements:
Text box1 =[Forms]![Jobslist]![Docketnumber]
Text box2 =[Forms]![Jobslist2]![Docketnumber]
Text box3 =[Forms]![Jobslist3]![Docketnumber]
However, not all three forms will necessarily be open at one time so I am trying to use an Iif statement to check if the form is open. I can't seem to figure out the function to check if the form is open. I am looking for something along these lines:
Text box1 =iif([Forms]![Jobslist] = closed,"",[Forms]![Jobslist]![Docketnumber]
What i need to figure out is what to used instead of "= closed".
