Modules & VBA :: Dir Function Empty For Certain Drives

Sep 17, 2014

I 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]....

View Replies


ADVERTISEMENT

Modules & VBA :: Users With Different Mapped Network Drives

May 13, 2015

I need to make sure that all users on the network can access a specific network drive folder.

Most users don't have a problem but some users have the network drive mapped differently so when running the feature required their system can't find the file at that specified location.

I have the location of the files hard wired into the VBA code.

E.g. Z:INVDATACOMPANIESCompanyAASSETS Pty LtdPrecedents 2015STAGE 3A - COLLS-DEMS

The location is outside of the project address.

I need to be able to make sure the address of the folder is the same for all users.

View 3 Replies View Related

Adding Fields Into A New Field Drives Me Mad.

Dec 4, 2007

So I have a table containing 2 fields containing the first name and last name of a customer. I do need to add these names in the same table into a new field called Name. Can someone help me out?

View 5 Replies View Related

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

Dec 11, 2013

So basically I need making a function that will count the number of records from another table/query based on a field from the current query.

View 2 Replies View Related

Modules & VBA :: How To Recognize Empty Field

Sep 24, 2013

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

View 4 Replies View Related

Modules & VBA :: How To Check Empty Folder

Jun 12, 2013

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

View 2 Replies View Related

Modules & VBA :: Empty Values In Array

Aug 16, 2013

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] .....

View 3 Replies View Related

Modules & VBA :: Dmax For Empty Table?

Apr 15, 2015

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

View 2 Replies View Related

Modules & VBA :: Minimizing Empty Lines On A Report?

Jul 30, 2013

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

View 3 Replies View Related

Modules & VBA :: Replacing Empty String With Null Value

Sep 12, 2014

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.

View 5 Replies View Related

Modules & VBA :: How To Get Recordset For Empty Rows In Form

Oct 14, 2014

Are new, empty record in form have recordset presentation, how to get recordset in form for empty row?

View 1 Replies View Related

Modules & VBA :: Empty Combobox Rowsource Returning ListCount Of 1

Jan 17, 2014

I have a combo box which I define a SQL statement as its rowsource.

When I open the form, I have yet to define the SQL statement for the combo box.

My code is:

if me.cmbobox.listcount = 0 then
me.cmbobox.enabled = false
else
me.cmbobox.enabled = true
end if

But it shows the box as still enabled.

When I do debug.print me.cmbobox.listcount, it shows a value of 1. But that is impossible since I have yet to define a rowsource for it or its empty.

I also have column heading turned off too.

View 3 Replies View Related

Modules & VBA :: Show Msgbox If Search Is Empty - Error 94

Sep 19, 2014

I have a button that opens a form which is based on a query.

The query has a prompt so when you click the button the prompt pops up.

However if nothing is entered you get a VB error 94 error.

How do i get it to show a msgbox if the search is empty?

View 4 Replies View Related

Modules & VBA :: Close Form With Required Fields Empty?

Mar 4, 2015

One form. Several fields which are required using event 'On exit' - "If isnull" statements for each one.

Button on form to close said form...

Where on the form would I put the event for the button to override all other events?

View 4 Replies View Related

Modules & VBA :: Check Whether First Line Of A Memo Field Is Empty

Feb 10, 2015

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.

View 4 Replies View Related

Modules & VBA :: Financial Database - Loop Query Until Empty

Oct 4, 2013

I'm an accountant and I'd like to build my own personal financial database in Access. One thing I like about MS Money is the 'Forecast Cashflow' feature, which is based on a schedule of recurring transactions, amounts, next due date, and frequency. I want to replicate this.

So, I created a dummy database for the sake of learning the code behind this process. I've got 2 tables (but no microphone.

1) tbl_InitialPoint (which is my schedule of recurring transactions, amounts, frequency (in days))
2) tbl_Register (where I want forecasted transactions to wind up)

I've got 2 saved queries:

1) qry_MaxDate (looks for any transaction @ 'tbl_InitialPoint' and finds the last date of that transaction in 'tbl_Register'
SELECT MAX(tbl_Register.PostDate) AS LastDate, tbl_InitialPoint.Description
FROM tbl_InitialPoint INNER JOIN tbl_Register ON tbl_InitialPoint.Description = tbl_Register.Description
GROUP BY tbl_InitialPoint.Description;

2) qry_InsertTransactions (populates 'tbl_Register')
INSERT INTO tbl_Register ( PostDate, Description, Amount )
SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount
FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description
WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon];

And I've got a form called 'HomePage' that has

1) A textbox where I input a date horizon (this is where I want the eventual 'Forecast Cashflow' to stop
2) A button to start the process

It seems to me that qry_InsertTransactions needs to be run over and over (looped) until the query is empty. And it seems to me that this can only be done via VBA (not sure if I could structure my SQL to avoid VBA in this case, but I need/want to learn the vba to accomplish this).

I'm having trouble getting VBA to run qry_InsertTransactions a single time. I'm pretty sure if I could get the thing to run once I could get it to run through a loop. So, here's what I came up with.

Public Sub InsertTransactionsBttn_Click()
Dim db As Database
Dim qdfNew As QueryDef
Dim qdfInsertTransactions As QueryDef
Set db = CurrentDb()

[Code] ....

View 11 Replies View Related

Modules & VBA :: Import More Than 300 TXT Files And Add Filename Into Empty Field

Aug 14, 2015

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

View 1 Replies View Related

Modules & VBA :: How To Make Label Visible On A Report If A Textbox Is Empty

May 10, 2014

I'm working on a report. I have a text box that contains directory path for a picture which is showed in a image box on my report which works great. I have now created a label called "nopiclb" that has "no Pic "entered in it but I want to set the label to not visible if path1 has a file path in it but visible if path1 is empty

I put the code below in onformat event in the detail section, but is not working

Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.path1) = True Or Me.path1 = "" Then
Me.Nopiclb.Visible = True
Else
Me.Nopiclb.Visible = False
End If
End Sub

View 3 Replies View Related

Modules & VBA :: Display Last Record Next To Empty Space On Current Record

May 6, 2015

I'm trying to make a form that shows what the last record was next to the empty space where you enter a new record.

This is so the user knows that what they are entering is roughly in line with what has come before.

So for example if I was recording temperature every May, I would like a form that has a field called temperature and next to that field I would like to see last year's temperature.

Records:

Date | Temp
2014 | 20.5
2013 | 18.5
2012 | 19.0
2011 | 22.7
2010 | 15.2

So when I enter the record for 2015 I have a box that says: Temp and next to that box is "Last year was 20.5" or something like that.

View 2 Replies View Related

Modules & VBA :: Want To Get Status Of Fields Where Fields Are Empty

May 29, 2015

I am working on MS Access program where i import data from Excel and i would like to generate a report in MS Access where the fields are empty. Like the MS Access generate a table of errors when we import data and shows information like field name and row number along with Type Conversion label. Can i generate similar table from an MS Access table where fields are null.

View 3 Replies View Related

Modules & VBA :: Inserting Function Within A Sub

Feb 25, 2015

I am new at VBA in access and I am working on an application I didn't develop but I am maintaining.

Code:
Private Sub Command122_Click()
Dim formName As String
formName = getFormName(tblName)
DoCmd.OpenForm (formName)
DoEvents
Forms(formName).FilterOn = False

[Code] ....

I am trying to to write afunction that translates the deptid into a table 'id (myreference)

View 1 Replies View Related

Modules & VBA :: Multiplication With Str Function

May 6, 2015

I got the following problem.

This code doesn't calculate the exact value:

Code:

Sub TEST()
Dim strSQL As String
Dim b As Double
DoCmd.SetWarnings False
b = DLookup("MC", "COMMON_DISTRIBUTION", "YEAR = 2015")

[Code] ....

My variable b is 0.230.
The TNS is multiplied with 0.228.
It seems a problem with the Str(b).

View 5 Replies View Related

Modules & VBA :: How To Run A Function In Subroutine

May 17, 2014

I have a database which needs to be updated everyday. So I decided to make a subroutine to perform the update steps just by clicking on a button.

The problem is that I can run all the steps except one! At the end of the steps, there is a function/module that must be run to calculate and complete the job.

How I can run a function in a vba macro. This is my code:

Private Sub cmdUpdateDatabase_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry1AddNewStockSymbols"
DoCmd.OpenQuery "qry2AddDailyPrice"
DoCmd.OpenQuery "qry3UpdateStockDailyPrice"
DoCmd.OpenQuery "qry4AddOverallStockValue"
DoCmd.OpenQuery "qry5AllSymbolsActiveDaysTractions"
DoCmd.OpenQuery "qry6SortToCalculateDailyRemainedStock"
DoCmd.SetWarnings True
End Sub

Module name is: CalculateDailyRemainedStock

View 3 Replies View Related

Modules & VBA :: Run Function From Another Database

Aug 7, 2014

I have 2 database files.

database A has a button (buttonRun) that when clicked, it opens database B and runs the autoexec macro.

In database A, there's a label control (lblRun) next to the buttonRun that has its back style property set to transparent.

What I'm trying to accomplish is this:

After the procedure in database B completes, it exits the system. I want

lblRun's back style property to be set to normal.

I already have a function that does this and the function works fine from within the database A.

Code:
Dim frm As Form
Dim color As Variant
Set frm = Form_frmReportingDashboard
frm!lbl_cmslite.BackStyle = 1

I need to trigger that function from database B before it exits.

View 8 Replies View Related

Modules & VBA :: How To Write A Function That Does Query

Jul 22, 2013

I have a query string in the following code. I want to put the entire code into a function so I can use it somewhere else as well.

Code:

Private Sub Form_Load()
'To enable the AllowAddition property once the form is opened
Dim dbs As DAO.Database

[Code]......

View 5 Replies View Related

Modules & VBA :: Possible To Initialize A Function Like Do For Variables?

Jul 15, 2015

Sometimes we need a one-line function to just get the database path or things like that we cannot do on a query or on a Macro Object.

Like:

Function GetDatabasePath() = currentproject.path

possible?

View 5 Replies View Related







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