Function Code To Log Field Changes Into A Table?

Jan 21, 2015

As you can see the purpose of this function code is to register into the TblLogChanges every single record exclusion or record change made in any form field of my project when the form is active. In other words if the user changes for example the value of the field Color in the form FrmX from Yellow to Black, the TblLogChanges will have added a line indicating: FrmX, type of change (record change or record exclusion), RecordId (in my case CADID), Current User, Date, and a complimentary string showing Field Color old value=Yellow --> Field Color new value=Black.It works fine when the field "old" value is populated (not null). However, if the feld old value is null, meaning, if the field value is originally null, or yet if the field is originally empty, and I fill it up with some data, then the code does not add an indicative line into the table TblLogChanges.

See function code below.

FYI the function is called in both Before Update and Delete events of the form with the following syntaxes: =logChangeFrmCadastro("E") for record exclusions, and =logChangeFrmCadastro("A") for record changes.

I have failed so far to make it happen. Reason is I'm able to read and understand most written syntax but still not good enough to create my own codes since I do not know to use the range of VBA commands and syntaxes available yet. This is a knowledge gap I'm doing my best to fill in.

what function code changes are required so the function be able to add an indicative line into TblLogChanges when the user changes a form field from "empty/nothing" to something?

Code:
Option Compare Database
Option Explicit
Function LogChangeFrmCadastro(strTipo As String)
On Error Resume Next
Dim db As Database, rslog As Recordset
Dim frm As Form, I As Integer

[code]....

View Replies


ADVERTISEMENT

Modules & VBA :: Code To Check Contents Of A Field In A Table Where Another Field

Sep 11, 2013

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?

View 1 Replies View Related

VBA Code To Update Field In Table

Jun 23, 2012

I am currently trying to add a last updated field for each field on my table. I figure I would have to add a before update code for each field on my form. However, the field that I want to have updated is not located on my form, but just on the table. I would like to have the field on the table updated with the last user and time the field was updated.

View 1 Replies View Related

Updating A Table Field In A Form Using Public Function?

Nov 13, 2014

I have created a Public Function that would get a new Production Instructions number based off the [PI Number] of Tbl_Production_Instruction table.

I have a form that people will put in all information but the PI Number, then when ready they will click a button to update the PI Number. I place an unbound txtbox that will be hidden, with the control source to =NewPINum(), but when I tested the see if the unbound txtbox was populated with the new PI Number it was blank.can't figure out what I am doing wrong.

Code:
Public Function NewPINum() As String
Dim vNum As String
Dim strYYMM As String
Dim getnextPI As String
strYYMM = Format(Date, "yy") & "-" & Format(Date, "mm") & "-"
If strYYMM = Left(DMax("[PI Number]", "Tbl_Production_Instruction"), 6) Then
vNum = Right(DMax("[PI Number]", "Tbl_Production_Instruction"), 3) vNum = vNum + 1 getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")Else
vNum = "001" getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")
End If
End Function

View 4 Replies View Related

Table Field Not Accepting Data From Form Function COUNT.

Oct 7, 2005

Table Field not accepting data from the Form function, count.

On the Form with the properties displaying the field, in the Control Source I have the following formula; "= count([OccupantName])

Now this does calculate properly except that the data in not saved to the table field.

Is the above count statement wrong?

Or does this need to be done another way? If so how?

Does anyone know of another way to have the computer count information on a Form that can be saved to a table field?

View 2 Replies View Related

Queries :: Importing Excel Text Field Into A Table - CDate Function?

Apr 28, 2014

I am importing an excel text field into a table. I am then appending the data to another table. I am having a hard time with the Cdate function

Excel Date Field:
YYYY.MM.DD HH:MM:SS

Example Value:
2014.04.21 18:24:30

My Query Code:

NewDate: CDate(Left([DateQ],4)+"/"+Mid([DateQ],6,2)+"/"+Right([DateQ],2))

My Query Result:
4/30/2014

What it should be:
04/21/2014

View 12 Replies View Related

Automatic Date Function With Code

Aug 13, 2004

I have a date field (WhitesDue) that I want automatically filled in when information in another field (Conditions) in another table is entered. The field Conditions is a number field and depending on the number of conditions determines when the whites are due.

I saw the other thread, Automatic Date Function, and was wondering if it was possible to put an if then code in to have the date updated with different dates depending on the number of conditions and the date that the information is put in. For example if someone is putting information in today and they have 4 conditions then I want the date to be automatically filled in with 8/17/04 (is it possible to factor weekends in too?).

Does that make sense? Can anyone help me out here?


Okay I stepped through some code that I have in the Conditions after update event. It works, it puts the date in the code like it is supposed to. Now, how do I get it to put the date into a field on another form?

If [Conditions] > 3 And [Conditions] < 5 Then
[Whites] = Now + 2
ElseIf [Conditions] > 5 Then
[Whites] = Now + 4
Else
[Whites] = Now + 1
End If

learnasugo

View 9 Replies View Related

Any Good Book That Have Examples Of Code, Function, Etc

Feb 15, 2008

Just wondering if anyone new of any books that contain examples of code, functions, syntax, etc. I would like to have a good reference that I could go to instead of searching the internet everytime I need to try something new.

Thanks in advance.

View 6 Replies View Related

Queries :: VBA Code On Subform To Lookup Email Field On Table And Launch Email?

Jun 26, 2015

VBA Code to go in the on double_click event of a name field in a Subform bound to a table. The subform is just a copy of a data table and within the subform view, When the field "employee name" which contains e.g. John is double clicked, I would like access to Lookup and get John's email in the employee table under field "Email" and launch outlook application and insert it into the To field.

I assume hyperlinking the field can also achieve this similar to what excel does but I am fast learning that what is standard functionality in excel is a whole another story in Access..

View 3 Replies View Related

Code To Disable A Field Based On The Values Of Another Field

Nov 10, 2005

If I have the following Code to disable a field based on the value of another field:

Private Sub lstAgreementType_AfterUpdate()

If Me.lstAgreementType.Value = "BN" Then
Me.txtSenateAandCDate.Enabled = False
Else
Me.txtSenateAandCDate.Enabled = True
End If

End Sub

But I also need it to disable the field is equal to "BA" or "BT" as well, how would I add that to my code?

View 3 Replies View Related

DLookup Function To Load Information From Table To Another Table Using Subform

Mar 22, 2013

I have 3 table table; Invoice table, Product table and Sale_product table. Sale product table records all sale from the product table

Invoice table has these fields
ID
TOTAL
CASH_TENDERED
CHANGE

Product table has

ID
CODE
QUANTITY
NAME
PRICE

and Sale_Product table has these
ID
PRODUCT_CODE
QUANTITY
PRODUCT_NAME
PRICE
SUBTOTAL
INVOICE

I did main form from Invoice table and sub form from Sale_product table. I want to use DLOOKUP function to load the name and price, quantity and calculate subtotal automatically from the product table based on the product code entered. i have being trying hard and i keep on getting "Name? error"

View 1 Replies View Related

Help With ZIP Code Field

Jun 20, 2005

Hi. I'm a newbie to access so I was wondering if somebody could help me with this issue. We're trying to construct a database with information from visitors of a park. The information the visitors provide include the ZIP code where they're from. This information is stored on a field called ZIPCode. We're trying to create a report of the percentage of visitors from each region based on the ZIP Code. The first digit of each zip code represents the region they're from. For example the zip code 12345 belongs to region 1. We're having a problem creating a query and getting access to read only the first digit of the zip code to determine the region. How can we do that?

Thanks

View 7 Replies View Related

Set The Value Of A Report Field From Code

Nov 14, 2004

I am using this code on a button, to print a report:

Private Sub buttonPrint_Click()
Dim P1 As Integer
Dim R1 As Integer
Dim stDocName as String

R1 = MsgBox("Print;", vbYesNo)
If R1 = vbYes Then
If stDocName = "FIRST REPORT" Then

P1 = MsgBox("Printing options;" & vbCrLf & vbCrLf & _
" YES: Option 1" & vbCrLf & vbCrLf & _
" ΝΟ: Option 2", vbYesNo "Printing Options")

If P1 = vbYes Then
DoCmd.OpenReport stDocName, acNormal
End If
Exit Sub

Here is the difficulty I need help to overcome:
In report "FIRST REPORT" there is one field called txtSet1, Boolean.
Can I set the value of this field to either True or False from this point in my code ?

If P1 = vbYes Then
set Reports![FIRST REPORT]!txtSet1.value = True
DoCmd.OpenReport stDocName, acNormal
End If
Exit Sub
I know that the answer is NO because the above does not work. I think the problem is that because the "FIRST REPORT" is not opened until line
DoCmd.OpenReport stDocName, acNormal
is executed, the line
set Reports![FIRST REPORT]!txtSet1.value = True
can not find "FIRST REPORT".
Can I have some guidance please ?
Rgds

View 2 Replies View Related

Sum Function Counting Field Name

Mar 5, 2012

I have a query where one field is a calculated field dividing one field summed by another field summed. But when looking at the data in excel, I figured out that one of the fields was adding a fraction onto the number when I summed up the whole column including the field name leading me to believe that Access was doing this as well.

View 14 Replies View Related

Setting A Field Default Value Through Code

Dec 17, 2007

High all.

I would like to know how to set a fields default value on a table.

Eg - i have a Yes/No field that and i want the default value to be set to 'Yes'.

I cant use the front end application because the form its on is a generic form used by about 30 other tables.

At present it is not set to anything and so always defaults to 'No' on the form.

Thanks in advance.

View 6 Replies View Related

Multiply A Currency Field With Code

Apr 27, 2005

Currently i have:

If CIS = False Then
CIS_Payment = "£0.00"
Else
CIS_Payment = "£20.00"
End If

which works ok but what i want to do but do not know the correct way/syntax is:

If CIS = False Then
CIS_Payment = "£0.00"
Else
CIS_Payment = "Labour_Total*18/100"
End If

Where CIS_Payment and Labour_Total are currency fields, and CIS is a checkbox updated using an update query with the same info as detailed in another table (Couldnt get the conditions to work referencing the checkbox in the other table, so duplicated the box in both tables and used a query to sync)

Any ideas???

View 2 Replies View Related

Changing Field Attributes In Code

Jan 24, 2007

I know that I can use an Alter Table query to add table fields from code.

But what if I just want to change a field from being Required or Not Required, or perhaps change the field width of a text field?

How do I do that from VBA?

View 1 Replies View Related

Modules & VBA :: Code To Return Field Name Rather Than Value

Apr 17, 2014

I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.

Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant
Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer
Dim I As Integer, J As Integer
ReDim varTempArray(UBound(FieldArray))
intArrayValues = 0

[Code] ....

As you can see, this works to find these values while ignoring NULLS. How to return the field name?

View 1 Replies View Related

Modules & VBA :: Lock Down A Field Through Code?

Jun 7, 2013

I have a field within a form that needs to be modified based off of someone's access level and I have written that part but not sure how to keep others from modifying the field.

Code:
Private Sub Qty_Rcvd_Click()
If ap_GetUserName() = "Danny_Davis" Or ap_GetUserName() = "christopher_ayers" Or ap_GetUserName() = "Tena_McCrackin" Then
GoTo 10
Else
MsgBox "You are not authorized to view this form"
End If
10
End Sub

Right now this code will bring up the msgbox for someone not authorized but once they hit "ok" the msgbox goes away and they can still change the field. What can I type in here to lock down the field for someone not authorized?

View 3 Replies View Related

Modules & VBA :: Code That Populate Field

Feb 25, 2014

I am using MS Access 2010 and I am struggling to write some vba in my database. On my form, I have a Payment Type dropdown field.When someone selects a payment type I want the code to calculate the end date. Then populate the End Date field with the answer.I will be having a few payment types too, but for now on the dropdown I have two.2/Month and 24/Annum.

View 3 Replies View Related

Possible To Use Function: Left(Field,4) In A Query?

Aug 19, 2006

Hi!In my query i'd like to format a selected field.At the moment i write it like this:MyOwnFieldName:[Table].Field I want to achieve:MyOwnFieldName: Left([Table].Field, 4) & "-" & Right([Table].Field, 4).Is this possible?Thanks for helpful feedback ;)

View 5 Replies View Related

Nz Function Parameter On A Text Field

Apr 12, 2007

I've been searching for several hours now and can't find the answer to this problem...
I'm using a form to capture criteria for a query. One of the fields is a text field and here is the parameter I'm using:
Nz([forms]![frmMain]![Company],[CNm])

I want to return all records if the text box named Company is null. I'm getting only the non-null records. How can I get it to display all the records in the field, CNm?
Thanks!

View 2 Replies View Related

Field Lookup Function (simple Probably)

Mar 22, 2005

I have a table with the following 4 fields (these are the ones i'm having the issue with but there are others).

ID, Country, City, Date

I enter data into this table via a form

The ID is an auto number. The date is simply enetered.

The Country Field is a lookup (in the table itself) with the following lookup properties

Display Control: Combo Box

Row Source Type: Value List

Row Source: "England";"Spain";"France" etc


Now the problem...

I also want a lookup in the City combobox (on the form) which changes to reflect what was selected in the Country field.

E.G

If in the Country Field Spain is selected then in the City Field I should have the option of selecting Barcelona, Madrid, Valencia etc.

I was told to use the following code in the afterupadate of "parent combobox":

Private Sub ComboCountry_AfterUpdate()
Select Case Me.ComboCity
Case "Test"
Me.ComboCity.RowSource = "A;B;C"
End Select
End Sub

Ive tried it but as always i get an error when i use the country drop down...

A pop up box with:

Compile error:
Method or data member not found

and visual basic opens with the top line of the code "Private Sub Country_AfterUpdate()" highlighted in yellow, and the ".RowSource =" is highlighted in blue.

These are the properties of my form

My Country Combobox is called - "Country"
My City Combobox is called - "City"

I have put the above code in the after update of the "Country" combobox as below:

Private Sub Country_AfterUpdate()
Select Case Me.City
Case "England"
Me.City.RowSource = "London;Manchester;Leeds"
End Select
End Sub


Anyone know where the error in the code is? Obviously i have reduced the code to include only one case.

Attached is the DB

View 3 Replies View Related

Call A Function From A Field On A Form

Aug 8, 2006

Is it possible to call a function from a field on a form, ie, in the
control source put = FunctionName(abc)? Then I want to concantenate
it to another variable so it would look like:

=FunctionName(abc) & TextVariable

I tried this and got the ?Name on the form, like it couldn't find the field
name, which is really a function I'm calling.

Is this possible and if so, what am I doing wrong?

Thanks in advance for any help.

View 1 Replies View Related

Queries :: Create A Field Using IIF Function

Jan 28, 2014

I am trying to , create a field using the IIf function that will display a $250 bonus for agents every time they sell a car for at least $20,000. Otherwise, the function will return a 0. Name the field Bonus. I put in Bonus: IIf(SalePrice >= "20,000" Then + "250",True, 0) and it is saying that I have an improper syntax.

View 2 Replies View Related

Sort Alphanumeric Field - Code Problem

Dec 1, 2006

hi
i found this code here and it works IF the number comes first and is preceded by a letter
123AA

but it does not work if the letters come first
AA123

here's the code

Public Function GetString(WholeString As String) As String
Dim i As Integer
Dim Temp As String
Temp = CStr(WholeString)
For i = 1 To Len(WholeString)
If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then
GetString = Mid(Temp, i)
Exit Function
End If
Next i
GetString = Temp
End Function

Public Function GetNumber(WholeString As String) As Double
Dim Temp As String
Dim i As Integer
Temp = CStr(WholeString)
For i = 1 To Len(Temp)
If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then
GetNumber = Mid(Temp, 1, i - 1)
Exit Function
End If
Next i
GetNumber = Temp
End Function


the probelm is with this line but i'm not sure what it is

GetNumber = Mid(Temp, 1, i - 1)

(i also get runtime error 13) but my data is in the same format as the example i downloaded.

any ideas anyone?

thanks in advance and thanks to the person who created the code

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved