I have access tool with front end and back end parts.My user works with forms in order to add new records or edit records.I wish to "catch" the following events - if the user modify field's value (which is not NULL) I wish to display a notification message and update a comment field / log table with old field data, new field data, time and user name. If the user update field's value from NULL to other value I do not need to log it.
Can I do it in the form level and not for each field ?I wish to discover the modified fields names and analyze their values before and after the update.
Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.
What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).
I have a onclick tied to a label (for decoration purposes) that when clicked it launches VBA that essentially updates a form. All that part works except it will not recognize any changed value of the field I was last in?
Just to try to explain best as I can what happens.
- Form gets opened - I change field (quantity field) - I click the Label - It reverts to pre-existing value.
if I click off of the text field first then do the onclick - it recognizes just fine.
and wanted to change this. I have this on the On Current event and it gives me an error because the data is not in the record source when the form opens. How can I change the me. to the formname? The form gets dynamically created so i have
Dim CurrentForm As String CurrentForm = Me.Form.Name
then i put
GBL_Tab05ID = Forms(CurrentForm).Form.Tab05ID
but that did not work...what is the correct syntax?
I have a form that is being used for sample submission. Ideally, the user will be able to put in some information (3 or 4 fields) and the number of samples that they're taking, click the arrow and it will insert that number of samples into a temporary table that is then displayed on a continuous form. From there, they can edit samples, add more samples, and generate a report to submit to the genotyping group. Once their sample list is final, I will append it to the master list and clear the temporary table.
I've got the basics working. A looped "INSERT INTO" using a counter inserts the relevant information. I cannot, however, seem to get the numbering field to work.
Ultimately, the numbers need to look something like MySample-001, where MySample would be one of the other fields. Incrementing the 001 is the problem. In another situation in the database, I can successfully use DMax+1 to assign a single number on a single form, but I can't get it to increment the sample numbers.
Ideally, I'd like it to happen when the form is generated. However, I'm also open to having them assigned when the sample list is finalized, before inserting into master table.
If I have a field in a tabbed form (the form has pages), can this field be moved from a tab to another programatically?
The form is called "ProjectMain". The field is called "Lenght" and the page (form tab) it is on is "specs1". Sometimes I need to have this field in the page (form tab) "specs2"
How do I reference a subform DS field to open up a tabbed form on another subform.
Here's what I have.
field one on the Sub DS opens up tab one on another form, as does field two on DS open tab two on another form.
but..
how can I click on "any" field on the DS, run an IF statement, and it checks if that record on the Datasheet, contains information in regards to, IF field one OR field two contains any data, and opens the tabbed form respectively on the other form?
I created a form that applies conditional formats depending on a field. It works fine, until you enter data into one of the conditionally formatted fields, then all hell breaks loose. Conditional formats totally go away and the cells go to #error.
I'm trying to lock certain fields on my form based on the value in a particular field. It seems to work the first time but applies itself to all other records in the database from then on!
The starting point is a command button that sends an email and makes Me.DPLLock = 1. The corresponding table entry is formatted as a number.
The code is...
Private Sub Form_Current() If Me.DPLLock = 1 Then Me.OR_Name.Locked = True Me.OR_Sales_Order.Locked = True Me.OR_WO_No.Locked = True Me.OR_Qty.Locked = True
I am using Access 2010.Most of the time a tenant pays his exact rent. When that happens, I currently type in the payment (taken form the Rent field) and date of payment (current date) into a form. The date should not change.
I would like to place a checkbox into the form. If checked, the rent would be entered into the payment field and payment date (current date) would be entered automatically. Otherwise, I would just enter in the payment and date manually.
I have a data entry form [Resources] and I would like to display some information about holidays in the form footer. Once the user has picked a combination of Trainer_Name and Start_Date I would like the 'On Change' or 'On lost Focus' event (not sure which would be the best) to perform a datediff calculation.
The datediff calculation would compare the difference between the start date entered on the form and the most recent past Start_Date on a query called [Hours Holiday_P1].I could adapt the same code to also look for the difference between the End_Date on the form and the next Start_Date on the Query.The idea is that when resourcing trainers I know how long it is since and how long it is till their next holiday.The sql for the query is
Code: SELECT Resourcing.Start_Date, Resourcing.Trainer_Name, Resourcing.Duration, Time.Hours, [Hours]/7.4 AS Days FROM (Resourcing INNER JOIN Employees ON Resourcing.Trainer_Name = Employees.Trainer_Name) INNER JOIN [Time] ON Resourcing.Duration = Time.Time WHERE (((Resourcing.Start_Date) Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1) And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3,31)) AND ((Resourcing.Activity) Like "Holiday*")) ORDER BY Resourcing.Start_Date;
I have created a form with 3 subforms on. i was just wondering is it possible to display/ hide these subforms based on a Yes/No field in the form. as the subforms would only be valid if the field is ticked as yes.
I want to check if data entered in a form field is existed
The form is bounded to a table
I used this code
If DLookup("Telegram_Number", "tbl_Violation_Of_Building", "Telegram_Number Like " & Forms!frm_Add_Violation_Building!Telegram_Number) Then MsgBox ("number existed") Me.Telegram_Number = "" Else End If
everything is ok but if the data is existed the database show the message and clear the field but i'm getting a Run-time error
'-2147352567(80020009)': the macro or function set to the beforeupdate or validationRule property for this field is preventing [ISF] from saving the data in the field
I guess the problem because the form is bounded to a table so he will save automatically
My solution is to unbound the fields and save the data via vba but is there any solution with a bounded form???
I have an Access form that gathers students test scores on Reading & Writing exams. Depending on those scores, there are five possible classes for them to be assigned.
I have the intervals worked out, and have created a table that contains the min reading, max reading, min writing, max writing, and class they belong in.
The scores are entered into a form as scoreReading & scoreWriting (integer)
I'm trying to figure out the best way to pull and compare the values and return the proper englishPlacement text. Its set up this way because there are multiple tables to pull from, depending on the date of the exam. Each table has different ranges for each Eng1,...,Eng5.
I'm thinking perhaps to iterate through each column and evaluate T/F, then return the englishPlacement value of the correct row. I'd like to keep this stuff in the VBA so that the code to select a table based upon date doesn't break when the intervals inevitably change requiring an additional table.
I have a continuous form with a text field that says "Select". There are two other fields, one of which is Brand. When the Brand Combo box has nothing in it I want the text box to appear (instructing the user to select a Brand) But once the user selects a Brand and goes to the next records, I would like the "Select" in only that record to become not visible. I tried conditional formatting but can't apply that to an unbound control ( or at least it is grayed out when I select the text box) and any other possible solutions I have found changes all of the selects - not only the one in the changed record.
How to do an UPDATE using VBA on a form to update a specific record on the table using an unbound field on the form to filter the update.
Every time the code runs, it tells me: Run-time error '3144': Syntax error in UPDATE statement and takes me to the "CurrentDb.Execute strSQL, dbFailOnError" line at the end of my sample below.
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ******** Public Function GetTargetType() As Variant GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]") End Function ************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ******** Private Sub Form_BeforeInsert(Cancel As Integer) Me!target_group = GetTargetType() 'Forms!frmNmsConsumptionEntry!target_group = GetTargetType() '[tblNmsConsumption.target_group] = GetTargetType() End Sub ************************************************** ********
making sure I can insert this value once retrieved.
I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.This is the code I placed in the Control Source property for the field I want to be displayed on the main form:
In my Access 2010 database there is a form with a large number of checkboxes enabling me to select/deselect certain record details.
Each click on one of the checkboxes triggers a VBA routine setting true/false values in one of several tables linked to the main table.
A query over all records is filtered by the true/false values of the linked tables and the resulting set of records is displayed in the (continuous) form.
The header of the form contains a text field with the value "=GetRecCount()" displaying (correctly, but randomly fast) the number of records selected:
Code: Function GetRecCount() As String Dim NumRecs As Long Dim rs As Object On Error Resume Next Set rs = Forms![Super Search].RecordsetClone rs.MoveLast NumRecs = rs.RecordCount Set rs = Nothing GetRecCount = Format(NumRecs, "##,###") End Function
Now, the funny thing is that each click on one of the boxes results in 1 call of the VBA routine setting table values, but 21 calls (!) of the text field "=GetRecCount()" in the form. I have established this by incrementing a public variable at each pass.
Why a field in a form is updated 21 times when a filter is applied?
I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.
I want to extract the day number from the Date field, and write it to the Day field.
I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".
So far, my code looks as follows:
Code: Private Sub Command16_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim sqlString As String Dim dataDay As Byte 'Open connection to current Access database Set db = CurrentDb()
[Code]...
I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.
I am trying create some code that checks if the contents of the status field in a table is "Authorised" where the ID = something specific for multiple records. If all records witht the specific ID are "Authorised" Then generate a new record in another table. Where to start, perhaps a Dlookup?
