Modules & VBA :: Where To Place Code That Run Constantly Without Event Such As A Click
May 26, 2015
I'm currently trying to make the call answering screens a bit better in the call centre that i work in. i'm trying to make a caller i.d. for incoming calls. i have a reference library from the telephone software people in vba that lets me dial and hangup the phone, now i'm trying to place a text box on the form that will update to show the number of an incoming call which will enable me to search a table of contacts and display the details of the caller if they are available. i'm guessing that i need to create a loop that will run until there is an incoming call but i don't know where to place the code without it needing a mouse click or mouse over to trigger it. i just want it to run constantly.
In the Main Form , I have few buttons and I am writing the following code to open the right form when a button is clicked but unfortunately the code isn't doing anything. No form is opening. I am using MS office 2010.
Code: Private Sub BtnOption_Click() DoCmd.OpenForm "Form1", acNormal, , , acFormAdd, acDialog End Sub Private Sub Form_Load() 'Me!Label1.Caption = "Welcome " & Environ("username") & "!"
I have a button on a form and I want the On Click event behind it to check whether Shift is also being pressed when the user clicks the button (or Ctrl, or Alt, in fact any damn key will do).
I can't believe that there isn't a function in VBA like the old 1980s BASIC 'inkey' function (etc) that just simply returns the code of any key being held down at the time of the scan. I shouldn't have to mess around programming other events like KeyDown and KeyUp when I don't particularly need the key-press to trigger an event per se.
I want to handle the event when I right click and choose "Paste" to pop up a message to the user that he cannot paste; disabling the pasting functionality.
I understand that the Button parameter refers to right or left button , but on which event to best use (mouse up ,mouse down , mouse move - as it seems to be a mixture of the three) and the code for that event.
Code: Private Sub List_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) ' See if the right mouse button was clicked If Button = acRightButton Then 'DoCmd.CancelEvent 'do event here End If End Sub
I don't want to cancel after there is a right click , but rather after right clicking and choosing "Paste".
I have been looking into populating a tree-view with folders and files, and have found some great examples for doing this, How ever...I cannot seem to work out how to show files/pictures in a form based on the click event of the tree-view.
I have managed to do an onclick event for the files in the tree-view, but I cannot seem to get the onclick event for folders in a tree-view.
Here is an example which I am working on at the moment, but get the error Object variable Or with block variable not set.
Code: Private Sub tvw_Click() Dim tClient As String Dim tParent As Scripting.folder Dim fd As Scripting.folder Dim fNode As MSComctlLib.Node
I have a form (about patients) containing a listbox (list of visit dates) and a subform (showing medications at that date). When you click on the listbox, the subform is re-queried with the selected date as a parameter to show the medications that were in use on that date. This is working fine.
However, when you move to a new record (patient), the subform is initially blank because the listbox hasn't been clicked on yet. This means that it looks like the person isn't on any medications, which is causing some confusion.
I need a way to have the subform show the "top" (most recent) date because that is the current medication list, and the thing most people want to see first. It is always the top of the list, so I would like to do something like this (pseudocode)
Code: Private Sub Form_Current() Forms![MedSearch].SelectDateBox.Requery Send MouseClick Event to line 0 of SelectDateBox End Sub
I have some code for a button on click event to open a form and select the record which corresponds to a value in an unbound text box. The code is:
Private Sub Command25_Click() On Error GoTo Err_Command25_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmOpenPatientRecord"
[Code] ....
This works fine when I put in a 10 digit NHS number but opens a blank record when I enter a four digit or six character/digit PatientNumber. Both patient number and NHS number are text fields in the underlying table.
I work for a company that manufactures home appliances & electronics. When those products fail within the warranty period, we are obligated to repair the units for the customers.
We have around 200 factory technicians who make those repairs. Each tech is able to make approximately 8 repairs each day. We have a dispatching system that assigns the repairs to the techs based on their availability each day. But the problem is that we have to manually enter and adjust the schedule for all 200 techs every single day, and this takes a lot of time. And of course technicians get sick, take vacation, etc, so we have to adjust the schedule so no techs will be assigned calls when they're off.
The problem: For example, currently when a technician takes off for 2 weeks, the user has to enter 14 individual records for the tech, which is somewhat time consuming. What I need is to program a button click event to determine the two dates (startDate & endDate) and append multiple records from one single entry in the form for each date in between and including the two date fields.
I know I'll probably need to create a loop that will loop through the two dates on the form and append a record for each day, so I can then cross reference the dates to the master schedule dates to make sure that no availability is opened for the techs taking time off.
Visual Studio IDE environment and I have been working with Access VBA for almost a year now.
I am very used to generating my form controls in runtime and being able to create some cool user interface interactions by being able to say btnExample.OnClick += MyDynamicClickFunction
Often I use Labels as buttons due to the fact I can colour them the way I want, and use the on click event to trigger code. The code below however works for a command button, but not a label button.
DoCmd.OpenForm "frmdatetime" Do While Forms!frmdatetime!OKFlag.Caption = "False" DoEvents Loop
When this code is run by clicking on a command button, it works fine. If run by clicking on a label, frmdatetime opens, but the mouse will not work on either of the 2 open forms unless you go down to the windows task bar, jump onto another window, and back onto frmdatetime. If I remove the loop with the DoEvents in it, then the problem does not occur.
Can anyone enlighten me as to why this behaviour occurs.
Hi again, This might be a simple answer, but I have tried several event properties, the last in the OnCurrent of the main Form. But most of the calcs work most of the time, and occassionally I get an 'Error 2001'. I have a couple of years VBAing, so still a teenager?? I am curious as to wether I am placing the calcs in the right event. I really only want them to do the calcs when the leave tab is selected (see enclosed image) and/or if a new employee record is navigated to. I will also attach code of calcs. If it needs to go into the Tab control what is the best event? NB pink fields on form are visible=No and for troubleshooting
Coding: ' Run Calcs here for Leave other than Sick Leave. If IsNull(EmpStartDate) Then DaysWorked = 0 Else DaysWorked = DateDiff("d", EmpStartDate, Now) End If ' Calc number of days between Employee Start Date and Now
TotalLeaveAlloc = DaysWorked * (Me!AnnualLeaveDue / 365) ' Divide Allocated days by 365 and multiply by number of days worked
If IsNull(DSum("[DaysTaken]", "[qryLeaveRecords]", "[LeaveType]<>'Sick' And [tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]")) Then TotLeaveRecorded = 0 Else TotLeaveRecorded = DSum("[DaysTaken]", "[qryLeaveRecords]", "[LeaveType]<>'Sick' And [tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]") End If ' Calculates the number of leave days taken from leave records in database based on qryLeaveRecords ' Used IsNull to check if there are no records found - set calc result to zero
Me.Taken01 = TotLeaveRecorded 'visible option = no ' Display this on screen
If IsNull(Me.LeaveAccrued) Then LeaveBalance = TotalLeaveAlloc - TotLeaveRecorded Else LeaveBalance = TotalLeaveAlloc - (TotLeaveRecorded + Me.LeaveAccrued) End If ' Check if there is any Accrued Leave [reduces the need to load historical Leave Records] and subtract them
Me.Bal01 = LeaveBalance ' Display Balance
' Run Calcs for Sick Leave here. If IsNull(DSum("[DaysTaken]", "[qrySickLeaveRecs]", "[tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]")) Then TotSLeaveRecorded = 0 Else TotSLeaveRecorded = DSum("[DaysTaken]", "[qrySickLeaveRecs]", "[tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]") End If ' Calculates the number of leave days taken from leave records in database based on qrySickLeaveRecs ' Used IsNull to check if there are no records found - set calc result to zero
Me.Taken02 = TotSLeaveRecorded 'visible option = no ' Display this on screen
I'm working with a database with employess info in access, When I click on an employees name in a form, I want to fire off another form with there info, what I have now it does nothing.
I have a list box control on a form and i'd like to set up a right-click property. Can this be done at all? When I open the Events tab on the property window..there is no right-click property :confused:
How can you disabled a click event on a text box ? I don't want to use ENABLE = false because the background of the texte box comes gray. So, any alternative solution?
I'm trying to code an event that would occur when a user double-clicks on the Record Selector of a subform. I want it to then open a form that would provide additional details about the record selected in the subform. I can't find documentation to tell me (1) where to put the code, i.e. subform double-click event vs. subform detail double-click event, etc.; (2) how to reference the record id of the selected record for use in the filter for the form that is going to be opened (the underlying query contains the ID for the record). Can anyone help or point me to documentation?
I have a form which has a tab control on it. The form itself is based on 1 table (tblCustomers). The tab control has 2 pages.
Page 1 shows 6 fields from tblCustomers.
Page 2 of the tab control shows 2 embdedded forms. Those forms are based on queries created from tblCustomers. one shows records with a date entered into one of the fields, the other shows records that have no dates.
What I want to be able to do is when I see a company name in one of the ebedded forms (Page 2), I want to be able to double click that record and it switches me back to Page 1 and shows the the related details of whichever company I double clicked.
I have changed the double click event in the emdedded form and using the builder to create something. I also added a setfocus command as I read about that somewhere (but i have no idea what it does). Through this I managed to get it to switch from Page 2 to Page 1 when I double click, but it only ever takes me to record 1 regardless of which company i double clicked.
i may have this deisgned totally wrong so please feel free to point me in the right direction.
I'm calling a Public Click event on a Form, from a Delete Record button on another form. Debugging shows that the code executes correctly line for line... but the 'Event' doesn't function as it should. The main feature I need to to iterate over the controls on the second form to disable them, to prevent users from typing in, causing relationship error. Why is this?! Even after the execution of the code... I can click on the refresh button myself, which will disable the control, but not if I call the event from the code.
Here is my code...
Code: Private Sub DelGroup_Click()On Error GoTo Err_DelGroup_ClickDoCmd.SetWarnings FalseDim msgString As StringmsgString = "You are about to delete a Lessee/Group." & Chr(13) & "This will also remove all related records in the..." _& Chr(13) & Chr(13) & Chr(45) & " Reviews Tab" & Chr(13) & Chr(45) & " Facility Details Tab" & Chr(13) & Chr(45) _& " Chronology Tab" & Chr(13) & Chr(45) & " Asset and Insurance Tabs." _& Chr(13) & Chr(13) & "Are You Sure?"If MsgBox(msgString, vbInformation + 4, "Confirm Delete") = vbYes Then DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70Dim frm As Form_ReviewsSet frm = Forms!Reviewsfrm.SelectLessee.Requeryfrm.SelectLessee.Value = ""frm.SelectAsset.Requeryfrm.SelectAsset.Value = ""DoCmd.SelectObject acForm, "Reviews", False 'Don't even think i need this line, but put it in for good measure. Tried withoutfrm.Refresh_Click 'It now jumps to the Refresh Click Event and runs as expected...ElseExit SubEnd IfDoCmd.SetWarnings TrueExit_DelGroup_Click:Exit Sub Err_DelGroup_Click:MsgBox Err.DescriptionResume Exit_DelGroup_ClickEnd Sub
If you click on the main form called frmpatient you will see 2 text boxs one called Primary Insurance the other Called Secondary Insurance
Above those text boxes are to hyper links which open the list box
What I'm trying to do is if a user double clicks the name of the Insurance From the List Box it will insert that Name of the Insurance Into either The Primary Insurance or Secondary Insurance Text Fields
I do not Want to Use combo Boxes because I have about 1,000 Insurances in my database.
I have a form with a combo box that is populated by (1) a union query, and (2) a make table query from that union query. The reason I have both is because I want to have an autonumber and I can't do that directly in the first query.
So any time I update any of the tables that are in the union query, then go back to the form, i want the the combo box to update the list which means I have to run (1) and then (2) to repopulate the combo box with the updates.
I have a textbox which I need to refresh when my Query is refreshed. Currently I only have the ability to requery with a Button but when that button is pushed I also want a text box to be refreshed. This is what I have currently in my On Click property of the requery button:
=[SearchSubformNew].[Requery]
Is it possible to have 2 On Click events? If so I just wanted to add the line to refresh my textbox which is named SummaryText.
I will be updating a table in my database every week, via imports from a xls file. What I have going on now is a very inefficient way of updating. What I do is import into a table (x) and just keep adding to the table through the import wizard saying add to exsiting table. Then do a make-table query where I do "group-by" to get the unique ones and then run it to get the new table. I am sure there is an easier way to do this. Possibly, find duplicates in the import and then just append to the main table? Don't know how to do that one? Also I wanted to create a button that will ask for the xls file and then do the appending and clean up once the file has been asked for...any help please!
I have managed to populate a treeview with some menu items I required and it looks great but I now need to get it to do something. How do I get a tree view value (selection) to act as a variable in routine. e.g. if my tree look like this.
Menu ¦ - Compaint management I I----My compaints
When I "double click" on My Complaints I want it to a open a specific form. One I know how to do one action I should be able to figure the rest out for myself.
I have a continuous form, with data entry fields in both the form header and the detail section.
I've attached an "On Exit" event to the ProdQty field in the detail section. The prime function of the event code is to run an update query on the table (which is the data source for the form).
The code executes properly (update query runs successfully and form refreshes) when I Tab out of that field. It also executes properly when I use the mouse to click on another field in the detail section.
However, when I use the mouse to click on a field in the form header or form footer sections, the On Exit code does not execute properly.
Here's the On Exit Sub:
Private Sub ProdQty_Exit(Cancel As Integer) On Error GoTo ProdQty_Error
If (Int(Me!ProdQty) <> Me!ProdQty) Or ((Me!ProdQty / Me!LDU) < 1) Or (Int(Me!ProdQty / Me!LDU) <> (Me!ProdQty / Me!LDU)) Then Me.ProdQty.BackColor = 255 Me.ProdQty.ForeColor = 16777215 Beep MsgBox "The quantity you entered is invalid for this product." & vbCrLf & vbCrLf & "Please check the LDU (least divisible unit) and enter a new quantity.", vbExclamation, "Quantity Error" DoCmd.GoToRecord acActiveDataObject, "Order Form", acPrevious DoCmd.GoToRecord acActiveDataObject, "Order Form", acNext DoCmd.GoToControl "ProdQty" Exit Sub Else Me.ProdQty.BackColor = 255 Me.ProdQty.BackColor = 16777215 Me.ProdQty.ForeColor = 0 DoCmd.SetWarnings False DoCmd.OpenQuery "Update Product Qty in Current Order", acViewNormal, acEdit Me.Requery Me.Refresh Me.Repaint
