i want to use the AfterUpdate to calculate the age using two text fields
Private Sub Birthdate_AfterUpdate()
Me.AGE = DateDiff("yyyy", [Birthdate], Now()) + Int(Format(Now(), "mmdd") < Format([Birthdate], "mmdd"))
End Sub
but it wont work
Hi
I'm having problems trying to get an If statement working on my form.
What I have is a form with two combo boxes linked to a Calendar when you click on the arrow. I can get the dates I select from the calendar into the combo boxes which works perfectly.
What I wanted to do then was create a textbox to list the number of days between each date.
I have created an If statement for me to do this, which works fine.
The problem I am having is that it doesn't seem to update these changes on the form after the date has been changed.
I have placed the code in the AfterUpdate event on both of the combo boxes and also in the Form Current Event.
Anyone any ideas?
If you want to see the If statement let me know, but I don't think it's that.
Thanks
Hello,
First post, new here. :)
I'm doing a course on Access and I'm trying to populate a text box based on the value I select in a combo box.
Basically, in the combo box I want to select a drug (for pets, it's for a veterinary clinic...) and in the text box next to it I want the cost to pop up. The list of drugs with their costs are listed in a separate table, with the drug name in column 1 (counting from 0) and the cost in column 3. I then have a continous form so I'd like to be able to keep adding drugs and their costs and then sum up the drug costs in a text box with the total.
Any help is much appreciated.
I am using an unbound textbox at the top of my form for filtering purposes. The user types in a string and hits "Tab" which kicks of the filter which is defined in the AfterUpdate property. I want the focus to return to the same textbox and position the curosor after the last character. I am trying to use SelStart but not having success.
Code:
Me.Filter = "COURSE_NAME Like '*" & Me.txtFilter & "*'"
Me.FilterOn = True
txtFilter.SetFocus
txtFilter.SelStart = txtFilter.SelLength
Hi,
I'm new here and I thought I had already posted this but i couldn't find it so I am posting again... sorry if I've posted twice
When I pass a value from MyTextBox to MyComboBox using:
Me.MyTextBox = Me.MyComboBox
It does Not trigger the AfterUpdate Event of the ComboBox
I need it to! Any Suggestions??
I have tried: copy and pasting by but that creates a problem
Me.MyTextBox = BarCodeData$ 'passes the variable value to MyTextBox
Me.MyTextBox .SetFocus
Me.MyTextBox .SelStart = 0
Me.MyTextBox .SelLength = Len(Me.MyTextBox ) + 1
SendKeys "^c" 'copies the value of of MyTextBox
as soon as I add the below line, it no longer copies the value in
MyTextBox
Me.MyComboBox.SetFocus
The onEnter Event of MyComboBox has the following code that works fine.
Private Sub cboLookupPart_Enter()
Me.MyComboBox.SetFocus
Me.MyComboBox.SelStart = 0
Me.MyComboBox.SelLength = Len(Me.MyComboBox.Text)
SendKeys "^v"
End Sub
If I ran all the code above, all works but the "Copy" and thus anything
that may be in the clipboard is pasted into MyComboBox, and the
AfterUpDate of MyComboBox triggers.
I know that there are compelling reason NOT to use the SendKeys but I
was just trying something
What I'd really like to do is eliminate MyTextBox and pass the Variable
directly to MyComboBox:
Me.MyComboBox= BarCodeData$
And have it force the AfterUpdate Event of MyComboBox
Eagerly awaiting suggestions!
Thank you,
Robert Bloomfield
Can the result of a combo-box be used in text in an AfterUpdate event?
Example, the combo-box (Result) can be negative or positive. If it is negative a textbox is populated with the test is (combo-box here)?
I have an afterupdate event that is supposed to send an email when a box is updated. Most of the times the code works fine except for one case.
The code checks the value of a field in subform in another tab of the same form
Code:
'find the MSM date
[Forms]![Project Main]![Marketing Specialist Main]![MSMDate].SetFocus
MSMDate = Nz([Forms]![Project Main]![Marketing Specialist Main]![MSMDate].value, "__-___-__")
This field (MSMDate) keeps its value in a separate table.
The problem comes when there is no value in this other table for the given project. If this is the case, the subform does not even appear on the main form (it is all a grey box) and the Setfocus returns an error.
I tried to comment the setfocus line to see what the NZ function would do in the next line and it also returns an error.
It is almost as if the subform was not "initialized" for that particular record...
How can I account for those cases?
I have a form with (among others) a Y/N field and a multivalued field. The Y/N field indicates whether or not the resident is year-round or not (seasonal/snowbird). The multivalued field contains the months the resident it here. The user wants me to auto-select all months in the multivalued vield when the year-round field is set to yes in order to minimize data entry.
I have been trying to use an AfterUpdate Event using the following syntaxes as a test but Access doesn't like the format at all:
[Master Table].[Months Available].[Value] = "June" (this gives 2465 error)
Me ! [Months Available].[Value] = "June" (this gives a 424 error)
I'm not at all fluent with VBA but have been successful in populating form fields using data fields in another table so thought this would be similar.
Any way other than not to use multivalued fields? Because that isn't an option.
I'm trying to place an if statement in an afterupdate event on a form. The code I have is
Code:
If [Customer] Like "BRO001" And [Inv No] is null Then [Job_Price] = [Shots] * 27.5
this gives me a run time error, object required and highlights
Code:
If [Customer] Like "BRO001" And [Inv No] Is Null Then
I'd like the code to do nothing where the requirements to alter the job price are not met.
Someone fills in a new patient into the database, and the 'chipsoftnummer' which is the number in another database. That number is unique, so i want to have it where if someone fills in a number that already exists in the database the afterupdate event will open that record in the form.Here's the code i put in the input textbox update field:
private Sub chipsoftnummer_AfterUpdate()
Dim NewCHIP As Integer
Dim stLinkCriteria As Integer
Dim custNo As Integer
[code]...
It doesn't work, simple things like hello world do work so VBA is enabled.
I've attached the corresponding part of the database (took out all non-relevant fields and tabs) .
I've come across a problem that doesn't make sense to me. I have a form with a combo box on it. Each time the combo box is used or the form is moved to another record it triggers an event to update the text boxes.
I get the information for the text boxes from a query. Everything was working smoothly until I decided to add a text box to the form to be filled in. My thought was I add the column from the query to the code and it will update. No dice. It doesn't recognize any information in the query. But when I run the query as a standalone it sees the text. When i open the table that holds the text, it is still there.
Code:
Private Sub cboDoctor_AfterUpdate()
'update doctor fields on the update of the combo box
If Len(Me.cboDoctor) > 0 Then
Call DoctorName_Change
[code]...
When i step through the code it shows values for each of the columns except for column 11. It reports as 'null.' Things I've tried:
-Updating the combo box by reselecting the value for the record.
-Restarting DB.
-Changing from one record in the combo box back to the original record.
Is there a limit of columns that can be used? Column 10 works just fine...
On a form, I want to disable the save button, 'cmdSave' whilst the form's mandatory fields have been left blank.
I've put in a smart tag, called, 'Req' against each required field and have used the following code on the forms current event.
Code:
Private Sub Form_Current()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
[Code] ....
The save button is disabled, but it won't enable again after each field has data entered against it.
I also have this code in the AfterUpdate event in each required field:
Code:
Private Sub cboErrorID_AfterUpdate()
Call Form_Current
End Sub
How to prevent duplicates on the combination of two fields - text & numeric?
I'm currently using the code below that warns users when the combination of two fields have already been used. (Combination of the TWO fields has to always be unique so if used again will warn the user)
Works well when both fields are numeric but fails when the JobDetails field is changed to text in the main table (tblPPMPLanner)
Code:
Option Compare Database
Option Explicit
Private Function IsDuplicateRecord() As Boolean
On Error Resume Next
Dim PreviousRecordID As Long
IsDuplicateRecord = False
[Code] ....
The field that should be a text field is called "JobDetails"
I have two tables in a query joined by a ID field. The problem is in the one table the ID fiels is a text format and in the other table its a number format so the query builder doesnt like that and gives me the Type mismatch in expression error.
There's no way around it though; i need it to be those formats in its respective tables for reasons i wont go into here for simplicity.
I have an on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is :
Private Sub cmd_mailreport_Click()
Dim office As Object
Set office = Checks.office
DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _
"info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _
True, ""
End Sub
Hi, can someone help me with this formula.
i have one field called notice_1 this field is a combo boxs multiple choice ( YES - NO )
Then i have 1 field called score_1 ( if notice_1 = "YES" then let score_1 = 5 else then let score_1 = 10 )
Does this make sense , I am an, amateur at access 2003,
Thank You For any help i can get...
John Calcitrai
I need to pull 2 fields from 2 different queries, then calculate them. Those 2 queries has the same structure, just one is last year's data, the other is this year's. :rolleyes:
I tried using the 3rd queries to combine them, then calculate from there, but then I had no clue where to go from there. I don't sql...I was wondering is there anything to do with sql? :confused:
I'm stucked so far, how can I solved it? :mad:
In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return? If so, can you share how this could be accomplished. Thanks
View 5 Replies View RelatedI am trying to get one of my fields to calculate this expression which includes other fields:
If "Financing Type" = 1 or 2 THEN 20% * "Loan Amount" OR
If "Financing Type" = 3 or 4 THEN 100% * "Loan Amount" = 2,000,000.
I thought that this would work, but it only works in queries or reports.
=IIF([FinanceType]<3,[LoanAmount]*.2,[LoanAmount])
I would like this to work in the table.
I have 2 tables - Customer and Carrier.
Each table has 3 headers (fields). Address, Latitude and Longitude.
I would like to know, how I take the value of the field (i.e Latitude) in each table for my calculation?
In Excel, I can specify the column (i.e Range("B" & x) - where 'x' is the row number)...but how do I do it in Access.
Code:
Dim db As DAO.Database
Dim rsCustomer As DAO.Recordset
Dim rsCarrier As DAO.Recordset
Set db = CurrentDb
Set rsCustomer = db.OpenRecordset("Customer")
Set rsCarrier = db.OpenRecordset("Carrier")
[Code] .....
I have a database that I need to calculate a query that is based on 3 dates. For example...I have an admission date and a discharge date in my database.
If there is no date in the discharge date then I need it to calculate the number of days between the admission date and "today's date." If there is a discharge date then I need to calculate the difference between admission date and discharge date.
Basically i need a length of stay figure. I can figure the formula in excel but cannot get it to work in access. Here is my formula in excel
=If(ISBLANK(DischargeDate),Sum(today()),sum(Discha rgeDate-AdmissionDate)
Ok, this one has me stumped.
I have a several fields on my form that are list boxes that must have text as the properties so I can populate the lookup data with things like" Severe rating = 7 points" and "Moderate rating = 3 points". I have another similar field where the choices are "High probability = 9 points" and "Low probability = 2 points" etc. A third field needs to show the total points (product) of the choices from the first two fields, i.e. 9 points x 2 points = 18 points. Then,
a fourth field needs to display where the answer falls in a grid, i.e. 1-14 points = Low, 15-29 points = medium, 30-49 points = high, etc.
How do I apply the calculations on the field choices if the field properties have to be text instead of number?
Thanks,
bugleboy:confused:
i have 1 table name "table1"and i have in this table 4 fields
1) id
2)pay
3) tax
4) total
The id is primary. I have form in this form i have 3 text box "pay" "tax" "total". i want to insert some number to pay and number to tax and make some button to make calculation of the 2 text box and it will show me the result in the total and insert them to the fields.
Here is an explanation of my database and what I am trying to accomplish. The tables and their fields I have in the database are as follows .....
EmployeeT
EmployeeID - Primary Key - Number (not an autonumber because they are county assigned)
LastName - Text
FirstName - Text
EmailAddress - Text
JobTitle - Text
MobilePhoneNumber - Text
Notes - Memo
[Code] .....
All users will interface with a form that will fill in data on the TransactionT table. I would like the data that is being input into the TransactionT.Quantity field to change the data ItemT.ESBuildingQty or ItemT.D3Qty based on which is chosen from StockLocation (they will only be able to choose either "ES Building" or "D3" when inputting the data).
For example, if the stock amount at the ES building of a particular item is 3 and the stock amount of that same item at D3 is 1 and a user inputs that a quantity of 3 of a specific item are taken from ES Building and Added to D3, I want the new values of ESBuildingQty to be 0 and D3Qty to be 4.
I am trying to calculate 5 fields within my table in a query, but for some reason some rows do not calculate as not all the fields contain a value (blank field), is there anyway that I could bypass this issue?
View 1 Replies View RelatedHowever Instead of entering the postal code continually or selecting from the combo box. How do I set zipcode 1 and zipcode 2 based on fields in a form. I would like to use the current FROM (Default zip based on the current job in form) and TO zipcode [Project Postal Code] that is on the form at that time. Not necessarily combo box.
Currently you have
zip1 = Me.Combo2.Value
zip2 = Me.Combo4.Value
I want Zip 1 to be Default zip (however I can still change it if needed) ZipCode field is [Project Postal Code]
Here is the full code:
Private Sub Command6_Click()
Dim zip1 As String, zip2 As String
Dim sResponse As String
Dim sLink As String
On Error GoTo Command6_Click_Error
zip1 = Me.Combo2.Value
[code]....