Modules & VBA :: If Then Statement Doesn't Recognize The Value In Combobox
Sep 18, 2013
I have used the code below to send an email automatically when a new record has been created. However I recently added the If Then statement to the code as you can see. The problem is that the email wont send because it says "There must be at least one name or distribution list in the To Cc or Bcc box". It is most likely saying this because it doesn't recognize "IT" in the txtDeparment combobox. How can I get it to recognize IT in the department combobox? The reason I am using an If Then is because I will make it send to different email addresses depending on what is in txtDepartment by using ElseIf
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
If Me.txtDepartment = "IT" Then
.To = "my email"
End If
.Subject = "hi"
.HTMLBody = "hi"
.send
End With
I am trying to get a Case Statement to evaluate multiple conditions. Example: below when I get diagnosis code 20400 and the age_at_diagnosis is 40 the code is basically ignoring the second condition of the Case "And rs![Age_At_Diag] < 18". How do I get the code to recognize both conditions?
Code:
Private Sub cmd_Update_Conditional_Codes_Click() Dim rs As DAO.Recordset Dim rs2 As DAO.Recordset
I split my database. Then got the bright idea to rename them. Now when I launch the front end (which contains the forms, etc.), I get a message saying that it can't find my backed. I've tried renaming the back end back to its original name, but its still not working.
What is the best way to fix this situation? I was thinking about opening the front end, and re-importing the tables from the back end, or, breaking the links between the front end and back end. If I re-import, should I move stuff from the front end to the back end or vice versa?
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
I'm designing a form which will display a lot of employee information in a tabular layout. Users need the option to update a lot of these controls, but I want them to do that on a separate form. I'm wondering if VBA can determine the position of a clicked button within the tabular layout.
Can I have some code which determines that, for example, the second button across on the 3rd row is clicked and read the corresponding label?
I was planning to have each button just call a function passing an argument, but since all these buttons will be labelled "update" it could get a bit confusing if I need to re-arrange the controls. So I wondered if what I describe above is possible, and which VBA functions I would need to look into if so?
I have a table of records, with one field of the records a combobox populated by a select query based onanother table.
My problem ism that it doesn't seem to recognise any of the values as on the list, though deleting one character and then replacing it results in the value being accepted with no problems.
This is a database I have inherited (Cheers predecessor!), the table is populated with a few thousand records and scrolling through this table results in an error message on this field for every record and is making displaying data on this form a nightmare! Anyone know of the cause or of a quick fix?
I don't know the history of this table unfortunately.
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code: SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact FROM tbl_Contacts WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company])) ORDER BY tbl_Contacts.ContactForename;
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.
I have a combobox [CBreason] that reads from a table to show me why someone is gaining access to the building.
One of the options is "Incident".
When incident is selected I want a textbox [TBIncidentNo] and a button[BIncidentDup] control I made to appear.
I thought that the code:
If Me.CBReason.Value = "Incident" Then Me.CBIncidentNo.Visible = True Me.BIncidentDup.Visible = True Else Me.CBIncidentNo.Visible = False Me.BIncidentDup.Visible = False End If
Placed in the after update action of the CBReason box would sufice but it doesnt work
I'm guessing the value is not recognised fro some reason but i cant work out why. There is no error message or issus, it makes the box dissapear when there is no entry but no change for selecting incident.
I'm trying to figure out why I can email a report as a PDF, but not save it as a PDF using the code below? When I save, I get the dreaded error 2501.
The searching I did for this said to uninstall and reinstall printers (I've done that - seems to have no effect), or took issue with filtering the report with a where clause (but if that was an issue, why does it email a PDF just fine?).I'd just like to be able to save a report as a PDF to a specified location.
CB Developing in: Access 2010; Win7 For use in a mixed 2007/2010 XP/7 environment
Code:
Private Sub EmailDietCardBtn_Click() On Error GoTo MyErrorHandler Me.Refresh Dim stReport As String Dim stWhere As String Dim stSubject As String Dim stEmailMessage As String Dim stCaption As String Dim myPath As String
I have developed a project in Access 2003 and it works absolutely fine. There is a switchboard form too and that works fine as well. But now in our company we are moving all the projects developed in 2003 to 2010. And so I have noticed the switchboard form doesn't work in Access 2010 the way it works in 2003. The Switchboard form doesn't display all the options in 2010.
I have a process which runs lots of slow append queries, so I want to reassure the user of progress, by changing the font colour of labels to green when the relevant queries have finished. This is the code I'm using
Code: 'Update the older data if that option is selected If Me.Menu_YearOption.Value = 1 Then DoCmd.OpenQuery "2-10 Append FY1112" Me.lbl1112.ForeColor = 32768 Me.Repaint DoCmd.OpenQuery "2-12 Append FY1213"
[Code]...
Each query takes about a minute to run, as it gets data from a sharepoint server, but the labels don't turn green one at a time as expected. Instead, I get the spinning wheel until all the queries have run, then all the labels turn green together.
Is there something else I should be doing rather than Me.Repaint ?
Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.
However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.
I'm using a label as a button so it looks nicer, but if I press it without officially exiting the last text field I was in, then that text field doesn't update, so the vba believes it's blank or whatever it was.
I could manually setfocus to a couple different fields or have a teeny field thats hard to see to set focus to, but these both seem roundabout.
The report is bound to a query that has 2 group by fields, 1 count field a a further field, a date field ([Part Date], that I put a default criteria on. This field is not displayed. If I don't put a criteria on this field disappears when I close and open again.
I pass a date to the program via a form and this ultimately ends up in SQLDate. When I run this I get promted to enter [Part Date] even though I'm setting it equal to SQLDate above. I can out garbage to a proper date in here either way the report picks up the default date entered in by the query.
1. Get rid of all of the parameters off the query.
2. Then you can use the Where Clause of the DoCmd.OpenReport code to specify the parameters based on your variables.
1.Not sure what this means but when I get rid of the criteria for the parameter the field disappears (I'm setting the show field to no as I don't want totals group by date). Getting rid of the field gives me all parts used.
2.I think I'm doing this in the above but will bow to superior knowledge!!
or is it I can't pass a parameter to a report run by a query that is grouping fields together to produce a count.
Incidentally once the report has been run (albeit with the wrong parameters) and I go into design mode and look at the property sheet for the report the correct filter is there (i.e., the date that has been input) but it quite clearly ignores this.
I already success to run this dynamic query where the parameters taken from the main form.Now the problem is the query result doesn't show in the subform.But the status bar below tell me that it have 2 records in the subform, but there is no data in the subform, it just Blank.I already apllied the Requery or Refresh to the subform (in the Command Button), but it have no result too.This is the code:
My database is used by a few users on a shared drive in "read-only" mode so they can't change any data.I set a scheduled task to copy an updated version of the database (which I edit) every morning.
The problem is that when the users forget to close the database file I can't overwrite it.For this purpose I wrote a small code that quits the application at 00:00 using Application.Quit.The code WORKS when I test it on my computer, but every morning when I try to open the file I see that it is opened by another user since I also open it as "read-only" - meaning the code didn't work...getting the code to work properly OR get a better solution to be able to overwrite the file even though it's opened by other users.
In the Main Form , I have few buttons and I am writing the following code to open the right form when a button is clicked but unfortunately the code isn't doing anything. No form is opening. I am using MS office 2010.
Code: Private Sub BtnOption_Click() DoCmd.OpenForm "Form1", acNormal, , , acFormAdd, acDialog End Sub Private Sub Form_Load() 'Me!Label1.Caption = "Welcome " & Environ("username") & "!"
When enforcing referential integrity, does access get angry about capital letters?Or just in general, if an address gets put in with a capital letter in one instance and then without in another does it make a difference?
I am trying to create a user-friendly database to enter invoice records (deductions and additions) for securities. I would like to be able to run queries with running totals from month to month inclusive (end total of one month = starting total of next month). I have already figured out how to create a running total; however, I have numerous "companies." Each record could be from any of 30 companies. When I add up the running total from say January 1 to February 28th, I get the additions and deductions of all the companies, not just the one I want. Inputting the companyID (example A110) in the query box only adjusts what is displayed, not the running totals that go by the total deductions from the records before that record (I assigned each record a "database ID" because dsum does not recognize dates in the criteria box, if I want to do a running sum.
I also was wondering if it would be possible to build forms with macros to create these queries in a user-friendly manner (so the company that I am interning for can create queries when they need them without me there).
