Why can't I get this simple piece of code to work?
Code:
Private Sub txtDateBack_AfterUpdate()
' updates loan status
txtLoaned = 0
If txtDateBack = Null Then txtLoaned = 1
End Sub
Code:
Private Sub txtDateBack_AfterUpdate()
' updates loan status
txtLoaned = 0
If txtDateBack = "" Then txtLoaned = 1
End Sub
Access 2007
Unbound Form
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'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.
So if I have a layout like this:
| label | textbox | button | label | textbox | button |
| label | textbox | button | label | textbox | button |
| label | textbox | button | label | textbox | button |
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?
Our quotation tool, which is built in access 2003, has a memo field, which is extracted in a .txt file, to be uploaded by another system.
When the user starts the memo field with a blank line, by hitting the enter key, the upload file writes the memo field as a new line, separating it from the line indicator and thus giving errors.
My question would be : How would I determine in VBA, whether the first line of the memo field is blank.
I need to import 300+ individual .txt files into a MS Access DB table (named "tbl_input"). Each of the the .txt files have a naming convention similar to this:
Pink_Floyd_YYYYMMDD.txt
Because this initial import is so large and I would like to avoid doing it manually, I need to be able to:
import each of these files into a single table
delimit the text in each based on the "|" symbol
add the filename into an empty field
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
[code]...
I have a small database for producing various financial reports, by date period (from/to). It works perfectly except when there are either no payment records or no receipt records for the chosen period. Naturally enough, MS Access comes up with the message "Database engine does not recognize 'payment' as a valid field name or expression" --- !!!
Is there some way I can tell MS Access that I don't mind if eg the payment column result is zero?
The structure of the table on which the report is based (via a crosstab query) is :
transaction date
auto number ID
transaction type (either payment or receipt, chosen by form's drop down box) - TEXT
amount - CURRENCY
receipt type - TEXT
payment type - TEXT
fundno - TEXT
The crosstab query design is as per the attached jpeg file
How to Make an empty field schedule database ACCESS default text in case the field is empty ....
Default text like 12345
Hello World:I have two tables, one called Assets and the other table called Job Sites. the Assets table has several fieldsAssetID - autonumberplus othersthe Job Sites table has two fieldsJobSiteID - autonumberJobSite - text (with about 22 names in it)the I add the JobSite to the Assets table (hiding the JobSiteID) it shows me the expected drop down box but it is BLANK. If I click on any of the BLANK spaces in the drop down, it populates the field. It is behaving as if the font color was white.Any ideas??? I am baffled. :confused: :confused:
View 8 Replies View RelatedI am trying to make a query with two tables. Each row consist of a student ID, their name and all their personal information. Both tables are exactly the same. But one table is a link table. That link table gets update every so often from an outside program and the other table is not linked. I want to be able to run a query that updates certain fields called "address changes". I have figured out how to find the updates, but I am having a problem with a field that is blank. If I have a field in the non-linked table that is empty but there is information in the linked table, it does not see it. Is there a way to get around this empty field problem?
View 3 Replies View RelatedI have a module for importing several excel files from a folder in Access. The user is prompted to select the folder which contains the Excel files.The issue:The code works fine if I select a folder from my company network drive (strFile variable returns with correct file name).The code does not work if I copy and paste the same folder to my desktop and select this location (strFile variable returns empty)
Code:
Option Compare Database
Private Sub btn_ImportData_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
[code]....
I use code to check folder as below.
If the folder not exists, then create it.
If dir(myfolder) = "" then
mkdir(myfolder)
end if
How to add code to check empty folder, like below?
If dir(myfolder) = "" then
mkdir(myfolder)
else if empty(myfolder) then //there is no empty function in access
do something
end if
I'm trying to store all the OrderNumber + Item combinations in 2 arrays and then because the OrderNumber column really contains 2 values I'm interested in, I split it up and store that column's values in 2 arrays. So in total, I have 3 arrays. An array for Item, an array for Order, and an array for RepId (which is the one that I split up from the OrderNumber column).
Anyways, when I print the RepId array with the ' MsgBox PostValCol1(x) ' It prints 4 values like it's supposed to. But when I tested it again by looping through the values and just doing a MsgBox, It goes for a long time and that's because it has a lot of empty values in that array. How to get rid of those empty values/not store them in the first place?
Code:
Set rop = CurrentDb.OpenRecordset("Select OrderNumber, ItemNumber From dbo_EntryStructure Where (ProductNumber = '" & txtPartNumber & "') AND (ActionCode = 'I')")
While rop.EOF = False
ReDim Preserve ArrRepOrder(j)
ReDim Preserve ArrItem(j)
[Code] .....
How do i use DMax() function instead of Autonumber, since the table is empty and it wont know what to do with Dmax() + 1 for next record?
E.g I have a new table Customer_details which contains
customerID
customerName
Age
now if i enter data through forms, i want the CustomerID to be incremental for each new record, so i can use Dmax() function. But since the table is empty how can i tell the form, through VBA that if it is null store 1 else store Dmax("CustomerID", "Customer_Details") + 1
Can anyone help me with this small problem?
Using expression builder what expression do I use to not display the filed in my query if the field is formatted as currency and is blank (showing £00.00)
I have a query which has a field where it shows reservations. if there is no reservation the field is empty.
how can I achieve getting a 0 in this field when it is empty?
Hi, I have a very junior question here but I cannot able to figure it out.
I'm writing a query on Access database, that will basically merge a table and a result from another query. Here's the result of the query
field1--field2--field3--field4
sdf -- 34654-- 234 --sdf
sdf -- empty-- 234 -- sdf
sdf -- empty-- 354 -- sdf
sdf-- 76456 -- 902 -- piof
My question is, is there anyway on the query, I can write something so that I can insert a "0" into those empty field in that partically one field? That field2 is a "Number".
anyway help is highly appreciated.
Im trying to check if a field is empty (i.e hasnt got anything in it)- but how do i do this. Ive called the field in my sql statement and then Ive put it in a variable- i,
I am then running a if statement
Code:If i = "" theni = "1" ElseEnd If
But this is not working?????
The overall goal is to hide a whole line of a report using "canshrink" on a series of boxes, some of which are numbers and some that aren't. Some will be blank when the data is loaded into the form and I want these to make the rest of the form adjust.
Code:
Private Sub Form_Load()
If Me.Label33.Value = "<1" Then
Me.Label34.Caption = "Nothing."
End If
End Sub
I have a module that creates records into a contacts table in my access database. When there is no data in the field from outlook, the data is populated as an empty string "" instead of a null value, creating problems with subsequent queries and processes. I'd like to create the data as a null value but the module keeps failing with "object is required" on the statement
rstImport("Customer ID").Value = System.DBNull.Value
It doesn't seem to like System.DBNull.Value or DBNull.Value, and when I assign the vbNullString constant instead, it gets set to "" again and not null.
Are new, empty record in form have recordset presentation, how to get recordset in form for empty row?
View 1 Replies View RelatedThe last field in my table is 'Comments'. I want to find if any record or records with 'Comments' field empty. I created a query and added all fields from my table. In the criteria section I typed Like "null". But the query is not returning records where 'Comments' field is empty. I have deliberately added (for testing) a few records leaving comments field empty. This can be viewed clearly in Table view. But the query I made is not displaying the records. Please help.
View 2 Replies View RelatedI have the query below and it returns the number of cases for eache of the case status (open, closed or private) Some of the cases have no status, the field is empty. Is there a way to count the number of cases which have no status in the status field?
Can someone tell me how to do this?
SELECT
Count([Report table part one].CaseStatus) AS CASES,
[Report table part one].CaseStatus AS STATUS
FROM [Report table part one]
GROUP BY [Report table part one].CaseStatus;
Can you set up a list box so when the form opens the list box is on a blank value at the start of the list. At the moment it opens with the first option selected. I have tried playing with the default value but this doesn't work.
View 4 Replies View RelatedI have a report that gives an update on construction projects. I've recently added an OLE field so I could attach photos and pdfs. I would like to see the photos and pdfs but not the empty space for the OLE field. I also want to see the construction project data even if there is no OLE. If I use the 'Is Not' Null in the query, the whole project record is not generated in the report. Is that enought info to solve?
View 1 Replies View Related