I have a form in my database that opens by default. I want to trigger a yes/no (yes would run a query, no would end) popup based on the value of a field in a table or query (i.e. if "now" is fifteen days past date in the table/query).
Any help would be most appreciated.
-Eric
I have a query, TonerAudit, that audits toner inventory based on the [number of cartridges in inventory] - [cartridges installed in labs]. Data is grouped by the cartridge reorder number.
When the db opens I would like the switchboard form to run an On Open event that evaluates if [TonerAudit].[Expr1] < 2 and if so return a message box saying that toner needs to be reordered.I tried doing this using a macro but I ran into the problem that my if/then procedure will not access individual columns in TonerAudit.
G'Day:
I have tried numerous things to try and create a pop up message or form to alert users of a timing conflict in a scheduling program.
I am not real familiar with codes and am not sure how to handle this.
I used Jason Browne's web site to help me construct a query that will list a result of time conflicts (ie some project is scheduled to occur before another one is completed).
What I need to have happen is the alert message or form opens when this conflict occurs but does nothing when the timing is ok.
I am not very familiar with code and am having a road block mentally. All help is appreciated!
THANKS
Access front end, SQL Server backend.
----
I have a continuous subform (part of a tab control on a main form) whose name and sourceobject are both "sbfCannulae". There are about 10 fields on the subform, and all are bound to fields in a table. The primary key of this table must be generated by combining two other fields, which must happen just before the record is saved. (I know calculated data should not usually be saved in tables, but trust me. In this case it is necessary.)
Right now the user COULD enter data into the primary key-bound textbox, but since the user is me, I make sure not to do that.
When I enter data into all fields except the primary key, I get an error because the primary key cannot contains nulls...of course.
I use the following code to tell me when an event has been triggered.
Code associated with sbfCannulae:
Option Compare Database
Private Sub Form_Activate()
MsgBox "cann activate"
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
MsgBox "cann before insert"
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "cann before update"
End Sub
Private Sub Form_Current()
MsgBox "cann current"
End Sub
Private Sub Form_Dirty(Cancel As Integer)
MsgBox "cann dirty"
End Sub
When I enter data into a new record, the Dirty event is triggered, but I cannot for the life of me figure out how to trigger any of the other events. No combination of values and nulls in any fields, clicking in other tabs, clicking to other records, or using the navigation buttons will ever trigger any events but Dirty.
The only msgbox I ever get is the one giving delivering the SQL error about the primary keys not taking null values.
What am I doing wrong?
-Matt
Hello one and all
It may seem a rather simple question, though it continues to confound me -and I have searched to no avail :D
How do you trigger an event depending on what button is pressed on a msgbox?
For example, using a vbYesNo msgbox on a form, how can I assign events to either the Yes or the No option? Say, if "Yes" is clicked the focus would be set to a control on the form, and if "No" is clicked access would close down.
Any ideas?
I've currently got on the on click event of a command button (the bit in italics is fine, the bit in bold is not working!):-
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click
Dim stDocName As String
MsgBox "Please select where you wish to save the exported file", vbOKOnly, "Save To Location"
stDocName = Month.Value
DoCmd.OutputTo acTable, stDocName, acFormatXLS
MsgBox "Do you want to select another History Extract?", vbYesNo, "Do you wish to continue?"
If intresponse = vbNo Then
DoCmd.Close
Else
Month.SetFocus
End If
Exit_Command3_Click:
Exit Sub
Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click
End Sub
But whether Yes or No is selected, the focus returns to the current form.
Cheers for any ideas
As above, I've noticed that it will only trigger if there are no textboxes on the form with a higher tab index than the control the keyascii is set for. Any ideas why, or what I'm doing wrong?
I've found this a lot and never bothered trying to find a solution till now. :p
Gareth
I have a combo box in a form which simply pulls a text field from a single table - approximately 100 values to choose from. On the After Update Event, there is a basic "If null this, else that" VBA which just hides a subform if the value is null. This works fine, except there are a handful of values (I've found at least 6) that when chosen, trigger the NULL portion of the code! All the values are very similar, so I don't know what the problem is - for example, the value "D-11" works, but neither "D-10" nor "D-12" does.
View 11 Replies View Relatedi have list box which lists all active records. raw source is based from Main Query which lists all active records from current user. on click even of list box i have macro that goes on record selected in list box, which is working fine.on current event of main form function is called. Main Query order by id Desc, list box order by is Asc problem is when i click on last field in List Box which takes me to record, On Current event does not trigger on main form. if i use Record Selector on main form to go to that same record,On Current triggers.
View 2 Replies View RelatedI have a searchable form that display information on agreements other companies have with us. If a company requests an inspection we have 30 days to go out and complete it.
I am trying to create a message box that will display all inspections that are due within 5 days when the form loads. No luck so far, only broken dreams.
Is it possible for message boxes to use expressions and display information that fits the criteria?
I have a combobox with several options for a device's parts. When one part is selected, it gets added to a device table.
ComponentCmb
Generator A
Generator B
Gearbox A
Gearbox B
Blades A
Blades B
Blades C
Tower A
Tower B
I want to display an error message if an option is being selected more than once.
For example, if a gearbox has been chosen, then another gearbox cant be chosen too.
How do i display this warning message.
I have a table (ComponentT) of different device components a user can select from ( generators, gearboxes, etc.)
The table has 3 fields - ComponentName, ComponentType, ComponenentDetails
I have a query returning all the ComponentDetails values.
I then have a combobox that uses this query as a row source.
The user can select different components they want from the combobox. Once something is selected, it is added to a new table - UserSelectedComponentsT
As a device can have only one gearbox or one generator etc. I want a warning messagebox to be displayed if the user tries to select a generator from the combobox when one has been previously selected.
we have gotten in to a routine of copying, pasting and bastardizing old databases instead of creating new ones from scratch - and we've had a problem recently where we've started off with one master database back in 2011, which has then been the base for practically every single major DB we've created ever since.So this month we've copied and pasted (again) and started adding new features to what's already there, and the thing has corrupted. I've copied and pasted the same version three times and added all the new bits, and the same thing has happened every time.
So this time I've copied and pasted and tried my best to clean up, get rid of the dead weight and (where necessary) create completely new objects, split into FE and BE versions etc, and I've reduced the overall weight of the DB by about 50%.Due to time constraints I now need to crack on and get this thing working again and, for the most part, it does - but now I'm having trouble carrying the filter over from the OnClick Event of a form button to the next form it's opening.
We're using the DB to record attendees at an Event we're running later in the year. This is the code that I'm running from the OnClick Event of the button on Form1:
Code:
Private Sub btnBkg_Click()
On Error GoTo Err_btnBkg_Click
Dim stDocName, vFilt As String
Dim vBkgRef As Long
Dim vContactID As Long
[code]....
You can see I've tried to use vFilt instead of the actual code for frmBooking's Where Condition, but for some reason it does not carry over to the form whichever way I try - when I open the immediate window and type ?vFilt it returns a blank entry. Not sure how to show the 'Where Condition' in the Immediate Window?Before Update, Open and Activate of frmBooking, but I'm hoping that the problem lies with what I've posted up here, as I don't want to get into posting the frmBooking code...
It may be worth noting that the button resides in the Header of a continuous form, with conditional formatting that changes the button caption depending on whether there is a value in the BkgRef text box of the record that has the focus.
Hi,
I am wondering whether it is possible to trigger the run of a macro when a form window is closed using the close button in the top right of a standard window?
If not, then is it possible to display a form in a window without the minimize, restore and close buttons?
I am currently using a setup whereby I have a button to close a form, and when clicked a macro is executed. However, the user could just as easily close the form using the close button of the window and the updates would not be performed.
Thanks in advance
Turbojohn
I have a form which has been in use for some time now, and works extremely well for all users; as part of a recent update to that form, I added a few events to various controls; most of them are message box prompts to remind the user to do something, but one is automatic entry of text into a memo field when another field is updated, and it is really this last one that is causing an issue.
There is a command button on the form which has an embedded macro, created using the wizard - it is probably the last, or one of the last few buttons that have a macro as opposed to VBA.
Essentially, when this button is clicked, I would like a duplicate of the current record to be made (thus giving it an Autonumber ID of its own), and if possible I would like this to be done without triggering any of the after update events on the form. Is there a way to disable these events and copy a record in its entirety, re-enabling the events at the end?
I have the below VBA in a from for it to filter the records below based on the criteria:
Private Sub Search_Click()
Dim strWhere As String
strWhere = IIf(Len(Me.AssignedTo & "") <> 0, "([AssignedTo] Like ""*" & Me.AssignedTo & "*"") AND", "") & _
IIf(Len(Me.OpenedBy & "") <> 0, "([OpenedBy] Like ""*" & Me.OpenedBy & "*"") AND", "") & _
IIf(Len(Me.Status & "") <> 0, "([Status] Like ""*" & Me.Status & "*"") AND", "") & _
[Code] ....
When I click the search button nothing happens, is there something wrong with my code??
I will have a PC and a number of scanners in a production line. I will need at least 15-20 scanners and would like to plan for more if needed. When an operator clicks on a button I would like to have each of the scanners triggered and return the value. They can be done in turn or all at once (preferred for speed). I need to know which value came from each scanner. I would then compare the barcode read at each station versus what is supposed to be at that station and either pass or fail.
View 2 Replies View RelatedI have a messagebox and want to know if it is possible that the messagebox give me a date as 15/01/2015 and not 15/01/2015. Using dailog box as messagebox
Code:
Private Sub Save_Click()
Dialog.Box "Tape # : " & Me.Tape & vbCrLf & "Sticker # : " & Me.Container1 &
vbCrLf & "Book # : " & Me.Book & vbCrLf & "Date send Out : " & Me.DateSendOut &
vbCrLf & "Date to be back : " & Me.DateToBeBack & vbCrLf & "OS : " & Me.System, ,
"Saving............."
End Sub
I have attached a sample of a database.
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
I need to calculate the final date based on the begin date and the number of days.
The name of the fields are: sdatainicio; diasatribuidos; sdatafim.
Is there a way to show the earliest and latest dates of a report generated by a non-date field?
E.g. I generate a report based on Food, and it'll list the days that this food is associated with. Is there a way to show the first and last day that appears in this report (i.e. the range of dates that the report shows based on the food selected)
I have a DB with Start Date (dd.mm.yyyy), End Date (dd.mm.yyyy) and Quarters. We have 4 Quarters (Jan-March, April-June, July-Sep, Oct-Dec) and extra columns of month (xxx) and year (yyyy) too.
The problem is the front end users have access permissions to modify the dates. if they change the start date and end date then they are manually gonna change other fields like quarter, month and year too (Which they don't want). The users don't need to manually update the column values for Quarter, Month and Year
For Example,
If the user modifies Start Date to: 22.05.2014 and End Date to: 24.06.2014 (then the quarter column should be fixed to 2nd quarter and month should be June and year should be 2014).
2nd Example: We need to consider the End Date for classifying the quarters, Month and Year
If the Start Date is: 22.05.2014 and End Date is: 24.12.2014 (then the quarter column should be fixed to 4th quarter and month should be December and year should be 2014).
The solution is when ever the front end user modifies the dates then automatically the quarter, month and year columns need to be changed.
Sample piece of Access data sheet with just 3 examples. Column names Start date, End date, Quarter, Month, Year.
I have a database which stores information relating to club members, which I'm in the middle of giving a bit of a facelift and one thing I want to automate is the calculation of the expiry date.
My memberships expire annually at the end of December and the expiry date is set as a default value within the table properties. This has to be manually changed every September (every new member from then pays for 15 months) to the following December.
Is there a formula I can insert in the defaul value field that will return December the current year if todays date is between January and August, and December next year if todays date is between September and December?
I am using the following expression to calculate a completion date for a project which is based on the due date. My problem that I am running into is when I get to a project that is due on a Tuesday, it returns a date for Sunday when it should be Friday.
IIf(Weekday([Due Date])=2,DateAdd("d",-4,[Due Date]),DateAdd("d",-2,[Due Date]))
I have a vba code export a error message if the current date of a file is not correct.. I have tested the code it worked to send a error message to a error.txt file but he it doesn't look to the modified date
So instead of:
file1.txt (13-02-14)
Then
Send error message ("file not updated") to: Error.txt
IF NOT
no action requeried
This code does the following :
file1.txt (Modified date: 13-02-14)
Then
Send error message ("file not updated") to: Error.txt
file2.txt (Modified date: 14-02-14)
Then
Send error message ("file not updated") to: Error.txt
Code:
Sub CheckD()
Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
Dim objFile As Object: Set objFile = FSO.GetFile("C:Users
ameDocumentsShow5621.txt")
If objFile.DateLastModified <> Date - 1 Then
[Code] .....
So also if it is the current date he send the error message to the error.txt file, it could be possible that i did something wrong.
I manage training for a large organization and am able to get a data dump that contains class end dates and class hours. Based off of those two, is it possible to determine a start date (excluding non-business days)?
For example, a 2 day class would be 16 'Hours' with an 'End Date' of Friday the 15th. Is there a formula that would give me Thursday the 14th as a 'Start Date?'
More importantly, if 'Hours' is 80 and the 'End Date' Friday the 15th, would it be able to give me a 'Start Date' of Monday the 4th? Can Access account for holidays?
I have a DB with Start Date (dd.mm.yyyy), End Date (dd.mm.yyyy) and Quarters. We have 4 Quarters (Jan-March, April-June, July-Sep, Oct-Dec) and extra columns of month (xxx) and year (yyyy) too.
The problem is the front end users have access permissions to modify the dates. if they change the start date and end date then they are manually gonna change other fields like quarter, month and year too (Which they don't want). The users don't need to manually update the column values for Quarter, Month and Year
For Example,
If the user modifies Start Date to: 22.05.2014 and End Date to: 24.06.2014 (then the quarter column should be fixed to 2nd quarter and month should be June and year should be 2014).
2nd Example: We need to consider the End Date for classifying the quarters, Month and Year
If the Start Date is: 22.05.2014 and End Date is: 24.12.2014 (then the quarter column should be fixed to 4th quarter and month should be December and year should be 2014).
The solution is when ever the front end user modifies the dates then automatically the quarter, month and year columns need to be changed.
Need sample piece of Access data sheet with just 3 examples. Column names Start date, End date, Quarter, Month, Year.
I will see the logic and will sort it out.