Code:
---------
If (Me.Variance_From_Budget < 99999 And Me.Variance_From_Budget >= -99999)
---------
This code is working when variance is less then -100,000, but does not work when variance is greater then -100,000.
Im trying to create a record set that compares a quantity value in the recordset to a Value in a temporary table that holds the most recent remaining quantity. I have the following code in the after update of a text box, but it does not trigger. I want it to run after a value is entered into the text box. Is the code wrong or the location im putting it incorrect?
Code:
Private Sub txtQty_AfterUpdate()
Dim rs As DAO.Recordset
DoCmd.OpenQuery "qryQuantitySoFar"
Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
[Code] ....
why this IF and Nz statement in my code below is not working. I know the me.txtrefNo value is "" (null) on a form field with property set to General Number. Therefore the Nz statement should return a "0" as I specified making the statement true as in 0 = 0 and then execute the actions below to generate a reference number however this it now happening as it's rendering the if statement as false and showing me the message " Whats going On?" which is after the else statement.
Code:
If Nz(Me.txtrefNo, 0) = 0 Then
Me.txtrefNo = DMax("[refNo]", "[R_P_Data_P]") + 1
tmpRefNo = Me.txtrefNo
'testing variable value
MsgBox (tmpRefNo)
Else
MsgBox ("What's going on?")
End If
PS. Does this have anything to do with the table field format that the me.txtrefNo value will be saved to?
Every time i try to get info from a sub form or its table ,Things never sem to work.Is there something special you have to do for eg maybe going via the main form?I am trying to use dsum as an alternative to multiplying info in the sub form.This is the code
=DSum("SoldAtPrice*Quantity","[Order Details]","IDNewOrders=" & [OrderID FK]).
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 select clause, it executes fine, BUT the totamt is not calculated. The field is Amount. The code is not working.
strSQL = "SELECT Account, Dept, " & "Sum([Amount])" & " As [totamt] FROM TB201410 GROUP BY Account, Dept;"
I have a combobox, and the 'OnNotInList' event has code to show a message box, then set focus to another control. That all works fine, however I am still getting the system generated 'Not in List Warning', despite the code for the not in list event starting with DoCmd.Setwarnings (False)
How to disable the warning?
I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.So, i select the module for Access 2010 but also not finish with function.Table name is "LeaveSettlement" and column name is "Total_Wdays", what field type i have to select for result column.
Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.
[code]...
I have a form I use to search for clients when I hit a command button, using surname etc. I'd like a yes no button to pop up rather than the clunky ok button route I currently have. I'm a beginner with VBA and most this I've gleaned from internet.
If there are matching results, everything is fine. If there are no matching results, I've managed to get my desired messagebox come up by using the code below in the onload event of the resulting search results form, however the yes no buttons do nothing and the results form loads blank as though no records were found.
I'd like Yes to send users to the client input form, and No to return them to the search form.
Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
MsgBox "Do you want to add a new Client?", vbQuestion + vbYesNo, "No clients found"
Select Case intanswer
Case vbYes
[Code] .....
Hi everyone!! I need some advice regarding the following query:
This is a big database with many tables and queries etc. but for simplicity lets say i have database with 2 tables (table1 and table2) both contain invoice numbers. table2 contains one record while table1 contains many. Ok simple so far but here comes the question, how can I write a query that selects all records from table1 that are greater than the value in table2, hope that makes sense? I've been trying to slog this out on my own but dont really know where start so any help will be appriciated
Thanks Steve.
Hey, cool forum. going to be really useful!
Have an assignment due in a few weeks and basically its a gym senerio.
Got a lot going so far, macros tables and forms. reports shouldnt be a problem.
Now i'm not sure this is actually expected but i'm sure i'll get high marks if i include it.
anyway:
there are 3 memberships, gold silver and bronze, gold can take 7 classes max and bronze 2 so how would that query work?
I can make it so no more than 7 member ID's are entered into a field but how will i make the queary recongnize the difference between gold and bronze?
would it be something like group by Membership and type criatera Gold and then in member ID have <=7?
if i did this how can i work that into a form?
anyway, know that might not make sense, or may make perfect sense but i just dont get it! either way thanx for reading, any suggestions would be much appriecated
I have an SQL statement that I finally have working after much help from this web site. But I want to enhance it to be able to search between two dates, I have on my search form txtStartDate and txtFinish Date. I want it to read > or = to txtStartdate or txt< or = to txtFinishDate. This is my code at present. Can anyone help please?
Private Sub cmdSearch_Click()
Dim strSearch As String
If IsNull(Me.txtExerciseName) Then 'this is to ensure user keys/select at least 1 criteria; add on rest of the controlnames as desired
MsgBox "Please enter at least one criteria"
Else
quot = """"
If Not IsNull(Me.txtExerciseName) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.Exercise_Name,BoxNo,Fau lt, Catagory,Time_Reported,Date,Brief_Description,TopT en,TFRNos FROM tblTechnicalIncidentReport " & _
"WHERE Exercise_Name LIKE " & quot & Me.txtExerciseName.Value & "*" & quot & _
"AND BoxNo LIKE " & quot & Me.txtBoxNo.Value & "*" & quot & _
"AND Fault LIKE " & quot & Me.TxtFault.Value & "*" & quot & _
"AND Catagory LIKE " & quot & Me.txtCatagory.Value & "*" & quot & _
"AND Time_Reported LIKE " & quot & Me.txtTime.Value & "*" & quot & ";"
End If
Me.Results.Form.RecordSource = strSearch ' Update the record source, this line is to be placed at the end of all your criteria's if end if codes
' Requery the subform
Me.Results.Requery
End If
End Sub
I used below vba code to to send email with PDF attachment in Access 2007 which works perfectly.
DoCmd.SendObject acSendReport, "R_TradeOrdAck", acFormatPDF, strMailList, , , .............
But since the db is installed at single pc user to work with Access 2003, the command acFormatPDF wont work. Instead acFormatSNP is working.
But I need acFormatPDF to work in Access 2003.
My application relies on whether Outlook is open and more importantly, with the correct Exchange profile selected. To ensure this I have the following code which, on the work PCs (Windows XP and Office 2003) works correctly.
Code:
If Outlook_is_Running = True Then
Set myOlApp = CreateObject("Outlook.Application", "localhost")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set colFolders = myNameSpace.Folders
On Error Resume Next
[code]....
The work PCs are being upgraded soon to Windows 7 and Office 2007 so I have taken the application home and started to develop it on my home PC (Windows 8.1 64 and Office 2013 64). I have used PtrSafe where necessary and have compiled with no errors, but this code always returns false even though Outlook 2013 is open.
On further investigation, if I comment out the 'On Error Resume Next line I get the error Active X component cannot create the object (or similar) Error 429. This is the same regardless of whether Outlook is open or not
I inherited this quote and trying to enhance it but having problem.
The filter in navigation form is not working but it is on its own form, the filter is working without problem.
As part of a larger system I currently have a standalone A2010 program which processes emails that it sees within Outlook. As my client is moving to Exchange Server I was wondering whether there was a better way of doing this by getting rid of Outlook and "talking" directly to Exchange.
View 1 Replies View RelatedI have created a module, where based on various selections (form), the output is thrown in the table for editing various fields. This works fine with single user. But once I have placed the same database on the share drive for multiple users, the users are unable see the data in the text filters. I don't know what is the issue all about. Also if i use me.requery, the text filters becomes blank. Below is the code :
Code:
Option Compare Database
Option Explicit
Public Function SelRec(shDate As Date, ATMID As String, City As String, Depots As String, Vendor As String) As Boolean
SelRec = False
[Code] .....
I have a SQL view linked into a ms access application. That view is used in a subform and in datasheet view. I want to select multiple records and click on a button that will delete those selected rows. I need the button because the view has 1 table and a subquery joined in the view and will not allow me to delete from the main table in that view. So, I figured I can select the rows I want to delete and then create a button that will send a passthru command to delete those records from the main table.
View 4 Replies View RelatedI have a Public Function that is being auto executed when my database opens. It is designed to change the value of a record from "Trailing" to "Critical" when the record is older than 90 days. Everything works as expected initially by changing the status and adding some text to a comment field for tracking purposes. However, I manually flipped the status of those records back to "Trailing" for continued testing purposes and they are no longer being included in the code execution. Do recordsets have a cache that is identifying these records as already having been updated and is, therefore, excluding them for all future executions of this module? Code is below. I added a counter just for the purposes of testing and it's counting zero records, even though there are several records that meet the necessary criteria.
Public Function Trailing()
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Dim ND As Date
Dim CT As Integer
[code]...
I am trying to make pass through query that triggers a database restore on a SQL server based on a few parameters entered in a form.
As soon as i run the command button the output just put all lines as one line without line breaks.
Here is my code:
Private Sub cmdRestore_Click()
On Error GoTo ErrHandler
Dim sSQL As String
Dim sSQL_Local As String
Dim rs As ADODB.Recordset
[Code] ......
Im v new to VBA and been having trouble getting the following code to loop through the records in my subform:
Private Sub cmdComplete_Call_Click()
Dim rs As dao.Recordset
Set rs = Me.fsub_Call_Off_Quantities.Form.RecordsetClone
rs.MoveFirst
[code]....
It only seems to update the first records txtQuantity_Called_Off value. I have tried all the 'looping' code variations i can find and they all seem to only update the first .
I managed to cludge around my earlier deciles query so now have the break levels though have run into a brick wall.
I have 2 tables (simplified below)
tbl1:
decilerank (basically a record number)
value
eg
1 150
2 160
3 170
tbl 2:
value
name
What I want to be able to do is be able to select all the names where the values lie between 150 and 160
algorithm is select tbl2 where tabl2.value > tbl1.decilerank[1].value and < tbl1.decilerank[2].value
and so on
Anyone able to help?
Edited to add:
Nothing links the tables.
Basically I am trying to use the data from one table as input into a query against the other.
eg select person where age is > 20 and < 30
where 20 and 30 are stored as seperate records in another table.
Hi,
I have a query setup, using a caculated field, to determine the greater of 2 dates, from 2 separate fields. Here is my calculated field:
LastContactDecide: IIf([Date1_Last_Contacted]>[Date2_Last_Contacted],[Date1_Last_Contacted],[Date2_Last_Contacted])
This works fine, except when there is a null value in either Date1_Last_Contacted or Date2_Last_Contacted. How do I get around this ?
Thanks !
hi,
im having trouble setting up a query in a sports day database i have to complete for school. in a form i have a combo box which allows the user to select an age group. a query is then performed using the selected age group and a report generated. the agre groups are under 13s, under 14s, under 15s, under 16s and open (open is anyone over 15 years old). the query for under 13 to under 15s works fine, however im not sure how to get the 'open' query to work. in the combo box i have the value for 'open' set as '>15', but access doesnt seem to like this and no records are selected from my tables, even though i have entries who have an age greater than 15.
does anyone have an idea how i can solve this? i would prefer to use just one query which gets an age group from a form rather than have an individual query for each age group.
thanks!
Hello All
I'd appreciate some help with a (for me) pretty complicated query Im attempting. Im trying to achieve 1 answer if the source is less then 7, and another answer if it is greater.
I've been searching through archived posts and picking up tips to the extent that I think im in the right ball park, but Im making a mistake with invalid syntex.
in plain english Im trying to calculate
If:
date range from bookings1 is 7 or less
I want to multiply the result of the date range
By the daily hire rate (from a different table)
If:
date range from booking! is over 7
I want to multiply the result of the date range
by the daily hire rate
then multiply that answer by 0.8
(to achieve a 20% discount on the total)
Cost:IF([Forms]![Bookings1]![End Date]-[Forms]![Bookings1]![Start Date]<7)*[Car]![Daily Hire Rate] IF ([Forms]![Bookings1]![End Date]-[Forms]![Bookings1]![Start Date]>7)*[Car]![Daily Hire Rate]*0.8)
I've tried quite a few variations on that and it reads right to me, but im obviously missing some vital formatting.
I know that criteria I had for calculating the date range, and multiplying that by the daily hire rate works because I've tested that and saved that version
Any help on this would be greatly appreciated
Thanks
George
I have a real estate database.
I have a table of;
Purchaser's Requirements tblApplicantRequirements
Property Price tblPropertyPrice
Property Bedrooms tblPropertySummary
I am trying to match the bedroom and price requirements within the Purchaser's Requirements table to their respective fields within the Property Price and Property Bedrooms.
My query is thus;
SELECT tblApplicantRequirements.ClientID, tblPropertyPrice.PropertyID, tblPropertySummary.Address2
FROM tblApplicantRequirements, tblPropertySummary AS tblPropertySummary_1, tblPropertySummary INNER JOIN tblPropertyPrice ON tblPropertySummary.PropertyID = tblPropertyPrice.PropertyID
WHERE ((([tblApplicantRequirements]![PurchasePrice])>=[tblPropertyPrice]![PropertyID]) AND (([tblApplicantRequirements]![Bedrooms#])<=[tblPropertySummary]![Bedrooms#]))
GROUP BY tblApplicantRequirements.ClientID, tblPropertyPrice.PropertyID, tblPropertySummary.Address2;
The problem is that the query is returning houses that fit one requirement (ie. they might be lower in price than the applicant is willing to spend) but not the other (ie. they may have 3 bedrooms when the applicant requires 4).
Any help to fix this much appreciated.
Thanks, J.