Running Code When Data Changes In A Table
Oct 25, 2005
Hi
Ive made a booking system in Access and need to convert it so it can handle multiple users on a network. The problem i have is that when someone selects a block of time on one computer i need the other systems to lock out those blocks on their screens. I have a table which records each user's activities so i could always use the On Counter event on the form to repeatedly check if there is any blocks to be locked out according to data in that table. The obvious problem with this is the huge overhead.
Is there any other way round this? or is there any way of detecting changes to data in a table so that i can run code to lock out the blocks only when theres a change?
Any help or suggestions much appreciated! :)
View Replies
ADVERTISEMENT
Feb 4, 2014
I have a bound form with a few fields. I would like to run a sub after some of these fields are modified, but not others. I would like it to run only once, after user finishes his work on the form.
When using the Unload event - the form on the form is not there anymore (or did I get something wrong?).
Is there a way to trap Data on form just before the form closes?
View 4 Replies
View Related
Apr 11, 2005
I am getting an error 'Run time error 2001, You canceled the previous Operation'. I don't know where I am makin a mistake in this code. I hope you guys will figure it out.
Private Sub Form_Load()
'On Load of the switchboard check Jobs table for any uncompleted jobs
Dim intStore As Integer
'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[ID]", "[Sheet2]", "[Follow up date] <=Now() AND [Appointment Reminder] =0")
'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
If intStore = 0 Then
Exit Sub
Else
If MsgBox("There are " & intStore & " uncompleted jobs" & _
vbCrLf & vbCrLf & "Would you like to see these now?", _
vbYesNo, "You Have Uncomplete Jobs...") = vbYes Then
DoCmd.Minimize
DoCmd.OpenForm "Reminder", acNormal
Else
Exit Sub
End If
End If
End Sub
View 4 Replies
View Related
Sep 29, 2006
Hi,
I get this error when trying to rung a piece of code:
The expression On Load you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.
I have the EXACT same code working on other forms using different fields and I just can't figure out why this particular one isn't working, it's driving me nuts!
My code is this:
Private Sub Form_Load()
Dim strReason As String
strReason2 = Nz(Ref3Poor_Reason, " ")
Select Case strReason3
Case "Not Known", "Unwilling to Give"
Poor1NavRef3.Visible = False
Poor2NavRef3.Visible = True
Case Else
Poor2NavRef3.Visible = False
Poor1NavRef3.Visible = True
End Select
Dim Ref3PoorCheck As Boolean
Ref3PoorCheck = Ref3Poor_Reference
Select Case Ref3PoorCheck
Case "True"
Ref3Poor_Reason.Visible = True
Case "False"
Ref3Poor_Reason.Visible = False
End Select
End Sub
Can anybody tell me where I'm going wrong please?
Thanks,
Dan
View 1 Replies
View Related
Nov 29, 2006
Ok - really need some help here. I have searched and searched but i think my problem is slightly different to the ones i am finding.
Basically i have a form and i want to force users to fill in one out of 4 tick boxes. If none of them are ticked when they press the close button i want the form to stay open and a message box come up.
I can get the message box part to work but the form still closes down.
The reason for this is that when the form closes it goes to another form that is linked to it by a unique ID number. This works by the following code.
Private Sub BTN_INVIS_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm closure"
stLinkCriteria = "[issue ID]=" & Me![Issue ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_BTN_INVIS_Click:
Exit Sub
Err_BTN_INVIS_Click:
MsgBox Err.Description
Resume Exit_BTN_INVIS_Click
End Sub
What i have tried to do is insert a piece of code into the above to generate the message box so it now looks as follows:
Private Sub BTN_INVIS_Click()
If [Transport_Delivery_issue_] = False And [Process_issue_] = False And [Design_issue_] = False And [Supplier_issue_] = False Then
MsgBox "You must complete the liability box before you can close this issue"
Cancel = True
[Design_issue_].SetFocus
End If
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm closure"
stLinkCriteria = "[issue ID]=" & Me![Issue ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_BTN_INVIS_Click:
Exit Sub
Err_BTN_INVIS_Click:
MsgBox Err.Description
Resume Exit_BTN_INVIS_Click
End Sub
This code checks the boxes and generates the message box if none are ticked - that works great. However it doesnt stop the second part of the code (the move to the next form) from executing if the boxes arent ticked.
Would really appreciate it if someone could help me - i'm fairly new to coding so the whole database is a bit cobbled together (mostly with help from this forum!).
View 3 Replies
View Related
May 19, 2014
I found code at this website that is supposed to determine if a specific program is running on your computer. I have a couple questions about the code here. One, when I run it just the way it is and call it in the immediate window with the ?fIsAppRunning("word") command, it tells me "True" whether I have Microsoft Word open or not.
My other question is this: After the code is working properly, how can I add new programs to it? There are two programs the end user might need open to complete a process. One is called Extra and one is called tn3270 (this is how they show up in the Processes tab of the Task Manager). I think in order to add them to this code, I need to know a "class" to associate with them.
View 1 Replies
View Related
Mar 20, 2015
So in my database i'm creating a live form that will open up a list of patients and when they are seen. one of the bits is a button that before they arrive says "Not Arrived" and after they arrive it says "Arrived". The code works fine:
Code:
Private Sub Command68_Click()
Dim C1 As String
Dim C2 As String
[code]...
The problem is after clicking the button on any of the records, it changes the text on all records, to wit: I tried to attach images or links to images, but my post-count is too low. The before-click screenshot is /3QnBkgG on imgur. The after-click screenshot is 9VZWzzp on the same site.
Bear in mind I've only clicked the button (it's white, so it doesn't look like a button) on the top record.The form is set to continuous forms, not sure if that has anything to do with it.how I can get the code to run on individual records, rather than applying the first-record's results across all records?
View 12 Replies
View Related
Aug 28, 2013
I have a report with several sub reports that run and fill a table conditionally. The third sub report displays the records from this table. It works perfectly.
If I print a batch of records, it also works perfectly - but only for the first record. Previously, I had another report with several sub reports where the visibility of objects were controlled - this worked fine in a batch. Every subreport had the correct visibility settings.
In this case, I have a similar design, but instead of setting visibility on the various subreports, I am filling a table based on data in the subreport. As stated it works great for the first one in the group, or any single print.
View 4 Replies
View Related
Feb 12, 2005
I’m working on an Access 2002 project where I need some help with coding. I’m a neebie to Access and Visual basic so a few pointers to get me on my way would be much appreciated.
The project is dealing with logging vehicles on and off site. I have one database that contains a list of all regular traffic in three fields – Registration Number, Company Name and Type. After the last update this contained about 300 records. I called this MAINDATABASE.mdb.
I have another database that runs as a user input screen and front end called WORK.mdb. This contains the above three fields and three others, Time In, Number of Occupants and Time Out.
I have already done the coding for Time In and Time Out where the user just double clicks the field and the system time is entered. The important field is Registration Number. What I need is the following:
When a vehicle comes on site the user double-clicks Time In on the form which inserts the present time (done). The user then tabs to or clicks on Registration Number. An input box comes up where the user types the reg number. I need the code to take this input, search the MAINDATABASE database and if the reg number is found return the values for Registration Number,Company Name and Type in that row of the database and then insert those values into the corresponding rows in the WORK database. If the reg number isn’t found then a message box comes up telling the user to enter details manually and input the string entered by the user into Registration Number field on the WORK database and leave the user to enter the values for Company Name and Type. I have the code for the Input Box and the Message Box at the end!:
Private Sub REGISTRATION_GotFocus()
InputBox("Enter Reg number...")
……..
MsgBox "Registration number not found. Please enter details manually"
End Sub
I just need the stuff to do the work in between!!
Any pointers are very welcome……
View 4 Replies
View Related
Mar 26, 2015
I have a form I use for data entry, it needs to generate an id called RO Number and i need it be generated by access starting at number RO129036 and then keep adding sequentially, so RO129037 etc etc.
as i already have data in my DB that i need i cannot just reset any fields
having a table with just one field - the numeric part of the RO number so first one would be 129036 - so i need the form to pull this field, add 1, and then add "RO" at the beginning of it? i have really been struggling with this database
View 3 Replies
View Related
Mar 6, 2014
I need to create a message box or a form or something to flash on the screen to tell the user that a piece of "Update" code is running. the update code will be updated reports from marketing returns, but the 3 branches who use the information are separate so I am creating an update form to download and update the table.
The code for the update is already working, but can take a while, so I thought a message or splash screen would be useful as the update runs on start up.
It would have another use, I have a report which is made mainly of calculated fields on an onPrint event and also takes a while to work it out, so a similar screen would be more useful than my current spinning circle and blank screen.
View 1 Replies
View Related
Jul 16, 2014
I have prepared a table in a database which includes a yes no box to one of the fields.
The idea of the yes/no field is that this is recording if the record is awarded. What i would like to achieve is that if the yes no box is checked "True" i would like to be able to copy some of the data from this table into a new table and that the form associated with this table opens up to enable the user to populate the remaining fields.
I believe i need to run this as an on click event procedure but cannot identify the code to enable the data to be copied accross to the other table.
View 14 Replies
View Related
Jun 11, 2014
I have a table called "EquipmentRequired" which is populated by 4 append queries,
5000BaseReq, 6000BaseReq, 6000IFBBReq, EquipmentReq which get some of their information by counting fields in another table but all have the same field names.
The queries contain all data that is initially used to append new records to the table and this works fine.Unless some information changes or a record is added then I would like to add a button to a form and call it "update equipment" behind which would run a vba code firstly to delete all the records in "EquipmentRequired" table then run the 4 queries without the warnings and re-populate the table.
View 9 Replies
View Related
Jan 12, 2015
I'm opening forms to get data then closing using VB code.
Is there a way to stop the screen showing these forms as the code runs
something like Display refresh off then display refresh on.
Just to hide my workings.
View 1 Replies
View Related
Jun 21, 2006
If a user "x'-es out of a form, is there a way to run some code to do a little process before the form is totally shut down?
If there is a way to do that, would someone please advise how it's done?
Thanks a million! You guys and gals are the best!
View 3 Replies
View Related
Aug 31, 2006
I have a form that has a start date field and an end date field and a client ID field. When I click a button to run a query I want the query to take the start date and end date and filter the query on those dates and also only supply data for the client ID
at the moment when I run the query i manually type the start date and end date and then scroll down the client ID to the one I want.
any ideas how to do this from the form
many thanks
View 2 Replies
View Related
Feb 28, 2014
I have some queries that pull data from one table and other queries that pull data from two related tables. In both cases, how do I prevent the data from being changed after the queries results are displayed? I know I could make a form and lock each field so it couldn't be changed, but I was hoping to avoid having to do that.
View 6 Replies
View Related
Jul 29, 2013
i have a table with health facilities (A,B,C,D) . each health facility has data from several months (Jan, Feb, Mar etc). the table has 2 fields (New Patients) and (Cumulative Patients) . Cumulative Patients is a total of New Patients for current month plus the total patients for the previous month. In the Facility Footer of the report if i create text boxes with data =Sum([New Patients]) and =([Cumulative Patients]) works well.
However in the Report Footer =Sum([New Patients]) works but =Sum([Cumulative Patients]) totals everything. if i try =([Cumulative Patients]).
View 8 Replies
View Related
Apr 13, 2006
I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:
View 3 Replies
View Related
Jan 16, 2014
I have vba code that creates the following SQL:
SELECT SubscheduleID, EventID, WeekOrder, DayID, StartTime, EndTime, Priority, CanJoin, PatientTitle, PatientNickname, IncludesPatient, IncludesAftercare, Letter1
FROM [qryScheduleCombinedDetails]
WHERE (SubscheduleID = 1 AND IncludesPatient = -1 AND DuringAftercare <> "AC only" AND (WeekOrder = "All" OR WeekOrder = 3 OR (WeekOrder = 1 AND Letter1 = "XYZ")) AND DayID = 2 AND StartTime <= #8:00:00 AM# AND EndTime >= #8:30:00 AM#);
When I try to run it, I get a "data type mismatch" error. When I put the same code into a query, I get the same error. However, it will run if I delete either condition from within the (WeekOrder = 1 AND Letter1 = "XYZ") pairing. I can't figure why it can run with either of those, but not both together.
WeekOrder is defined as String. Letter1 is calculated as Cstr(Nz(IIf(Letter,"XYZ","ABC"))) within [qryScheduleCombinedDetails], because I wanted to make sure that it would be recognized as a string.
View 4 Replies
View Related
Aug 27, 2013
I have a larget transaction data set in access with Datetime column/filed.
I have been running pivot queries to excel to do analysis of the data but the datetime field is returning too many unique values for the pivot table to run.
What is the best way to reduce the datatime field to date only and where should this be done?
i.e. should I have a calculated field that trims datetime or should I set someohting up in Powerpivot?
View 7 Replies
View Related
Mar 14, 2005
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
Many thanks in advance.
Peter
View 2 Replies
View Related
Jan 27, 2006
Hi all,
I am trying to update a table in a database (Lets call that DbNew). I want that when DbNew form is loaded it automatically updates its underlying table (lets call it TblNew) by running a make table query (called "coversheet") that is located in another database (call that DbOld). DbNew and dbOld are all on a network and I dont have total access on DbOld (but I have created the make table query "coversheet in DbOld).
I should also state that DbNew is bound to TblNew and I am not interested in keeping the old table in DbNew. Information is frequently updated in DbOld and all I am interested in is loading the most current info in DbNew so the current table can be deleted upon loading DbNew and a new one created. Here is what I tried doing and I have it in the form load event of DbNew. This however returns an error (Runtime Error 3010. That TblNew already exists)
Dim db As Database
Set db = OpenDatabase("\networkdbOld.mdb")
db.Execute ("Coversheet")
DoEvents
Set db = Nothing
End Sub
P/S: I am not good at access in anyway I should state.
Thank you for taking the time to read.
View 8 Replies
View Related
Nov 22, 2013
Im trying to sum a value within subform able before I even start I get ####Error as I have put in the control source the rule to create the sum
Code:
=Sum([TempTable].[hours])
I then have a function when another value is added it requires the text box which holds the sum value.
View 1 Replies
View Related
Apr 16, 2013
I have now a database where I made a query that will show me all clients I can charge when there is a end date and an account number mentioned. I have also in a table where I put if the client is already charged. This is a check box that gives me yes or no. Is it possible to run the query and once those selected show up, to also add a yes to the table so next time I run the query the ones charged will not be charged again.
View 5 Replies
View Related
Nov 12, 2014
I have a running total query that seems to run but when I try to total the query results then Access will be "Not Responding". I tried to change it to a Make Table query because I need to use the running total result in another query. So I created a table but when I try to run the make table query it just says "Run Query" at the bottom. Here is the query:
SELECT [OTMissing].[Employee], [OTMissing].[AsOf], [OTMissing].[HRsEarn], (SELECT Sum(OT1.[HRsEarn]) FROM [OTMissing] As OT1
WHERE OT1.[Employee]=[OTMissing].[Employee] AND OT1.[AsOf] <=[OTMissing].[AsOf]) AS RunningTotal, [OTMissing].[RemainPP] INTO OTGenerated
FROM [OTMissing]
ORDER BY [OTMissing].[Employee], [OTMissing].AsOf;
My OTMissing query is 47061 rows. Does that have something to do with it? The only other thing it might be is that most of the records have 0 although I'm not sure why it would be a problem I thought I would at least mention it.
View 2 Replies
View Related
