Vba to close forms Results

It seems so simple to do, but I've spent ages looking through the forums and haven't found a simple implementation of it.

How would you close a form based on a query called 'Retired' if no records are found?

I assume it's going to be a bit of VBA code, but I'm still learning VBA. :-(

I have a db app that has all forms maximized continually (I have set the vba code to do so on Open, Close and GotFocus events). From my app Home Page form the user can browse to other forms in the db. When another form is opened the window automaically maximizes. However, when the user navigates back to the Home Page it goes in restore mode. I haven't told it to do this anywhere. I have also tried saving the form stretched out to fit the full screen, so that if it goes into restore mode it at least still fills the screen, but it still reverts to a restored window (about half the screen). It doesn't matter what configuration I use in the AutoResize, AutoCenter properties either.

I have been developing custom db apps in Access for over 8 years. However, this seems to be a stupid (albeit simple) problem that is annoying me. I currently use Access 2003 and this issue is new (or so I believe since I never ran into the problem in previous versions).

Any ideas?


Can someone help? I would like to insert a button/command onto one of my forms to close the entire database that is open, but without exiting Access. I have another database that has an Exit button on the switchboard that does this operation, but I cannot seem to find the 'procedure'/VBA anywhere on how to make this operation work. I know how to insert a command button/text box etc, but don't know which one I should use (if any) or if I need to write up a VBA command to do this. Can someone give me some step-by-step ideas? Thanks


This is my first forum post. I am developing a schedule/rota system in Access 2000. For security reasons Access is locked down tight and I am not able to use controls like the MS FlexGrid etc.

I have normalised my data and created several tables, one of which holds shifts:

tbl_shifts: ShiftID, FK_EmployeeID, Start_Date, Start_Time, End_Time, FK_Duty_Type

where FK is a foreign key, start date is the start date of a shift and start/end times define when the shift starts and ends.

I need to display shifts for employees in following format:

Week Com Monday Tuesday Wednesday
J Smith 02/05/05 OD 17:00-22:00 OD 18:00-02:00
J Smith 02/05/05
R Carter 02/05/05 OD 02:00-09:00 OD 17:00-03:30
R Carter 02/05/05 OD 22:00-07:00
P Jones 02/05/05 TR 09:00-17:00 OD 17:00-03:30
P Jones 02/05/05

Where OD and TR are specific duty types.

I have created a form which will eventually hold a sub form to display the schedule/rota. The user can enter a start date into a text box and click a button to show the rota for that week. At least it will once I’ve finishedJ

Using the start date I have created a query which returns all records between start date and six days after, and concatenates the duty type, start time and end time:

PARAMETERS Forms!frm_Rota_Display!txtWeekCom DateTime;
SELECT fullName([First_Name],[Last_Name]) AS txtName, tbl_Rota_Shifts.Start_Date, fnctShift([Duty_Short],[Start_Time],[End_Time]) AS txtShift
FROM tbl_Employees INNER JOIN (tbl_Duty_Types INNER JOIN tbl_Rota_Shifts ON tbl_Duty_Types.Duty_TypesID = tbl_Rota_Shifts.FK_Duty_Type) ON tbl_Employees.EmployeeID = tbl_Rota_Shifts.FK_EmployeeID
WHERE (((tbl_Rota_Shifts.Start_Date) Between [Forms]![frm_Rota_Display]![txtWeekCom] And ([Forms]![frm_Rota_Display]![txtweekcom]+6)) AND ((tbl_Rota_Shifts.Shift_Changed) Like False));

The query works as a stand alone query but I am trying to call it using VBA and I get an error message:

Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, ‘UPDATE’.

If I remove the parameters from the query then I can capture the data but obviously being able to only capture data for an appropriate week is key.

The VBA code used is below:

Private Sub cmdUpdateDisplay_Click()
On Error GoTo Err_cmdUpdateDisplay_Click
Rem macro code to capture rota data for a given week and place in an array
Rem Sort it
Rem then display it in form

Dim rst As ADODB.Recordset ' create recordset object
Dim y As Integer

Set rst = New ADODB.Recordset ' instantiate recordset object
rst.ActiveConnection = CurrentProject.Connection ' set the recordset active connection to the current project
rst.CursorType = adOpenStatic ' set the cursor type to allow forward and backward movement through recordset

rst.Open "qry_concatenated_shift_Times" ' open the recordset with the results of query

ReDim arrWeeklyRota(rst.RecordCount, 2) ' redimensions the array to be the same size as the count of records
'Debug.Print rst.RecordCount ' prints the count of records in immediate window

rst.MoveFirst ' move to first record

For y = 1 To rst.RecordCount ' from beginning to end of recordset
Rem place values from records into array
arrWeeklyRota(y, 0) = rst![txtName]
arrWeeklyRota(y, 1) = rst![Start_Date]
arrWeeklyRota(y, 2) = rst![txtShift]

Rem can delete/comment out - used to check what data is returned
Debug.Print arrWeeklyRota(y, 0)
Debug.Print arrWeeklyRota(y, 1)
Debug.Print arrWeeklyRota(y, 2)

If rst.EOF = False Then ' if end of file has not been reached...
rst.MoveNext ' move to next record
End If

rst.Close ' close the recordset
Set rst = Nothing ' and kill

Exit Sub

MsgBox Err.Description
Resume Exit_cmdUpdateDisplay_Click
End Sub

I read one thread that suggested using DAO would be better but my attempts to do this have caused different problems.

Can anyone suggest how I can use parameters in this situation or point me to threads that have dealt with this. I had a good look this afternoon but could not see anything.

Thank you


Close Access97 Open Sessions and Then Close Database- new twist

I need to automatically close a Access97 database so that Compacting can be done from a maintenance program.

I cannot use the On Timer() Event to automatically close the database because the Users often close all database objects (like forms) and work directly with the tables (which have no On Timer() events); The On Timer() event only works when a form is open.

Is there perhaps a way to remotely kick Users out of a database and then close it? (i can manually do it, but i need a VB or VBA solution!)

I am open to any suggestions; my current solution is to issue a memo that tells all users to close the database at the end of the day - but this is not a real solution.

Thank you!


At some point, you may find it necessary to use VBA to get your job within a database done. Also, there are cases where some databases model a complex set of business rules and requirements and you must use VBA generously.

The most important thing to understand is that there are no truly hard and fast rules regarding how to be a good programmer. However, since the time of vacuum tubes, several programmers have observed patterns and thus contributed to a set of guidelines that help other programmers, regardless of whether they are using C, COBOL, Java, Visual Basic, or any one of many programming languages available.

This article deals in generalities and a bit of programming theory. The overall objective is to provide a maturing programmer with a framework to work within when considering how they will implement solutions to the problem at hand. Thus, the article is intended to call your attention to *potential* best practices, but it is still *your* responsibility to decide whether you will apply these recommendations or not.

Have hammer, will see nails

The first thing to consider is a caveat: VBA is truly a wonderful way to extend your application's functionality, and it can be tempting to turn to it for problems for which there exists a better solution without need for VBA. There's a saying- if your only tool is a hammer, everything starts to look like a nail. Thus, just because it's possible in VBA, it doesn't mean you should do it in VBA. SQL has its niches, and so does Access's built-in functionality and bound forms/reports/controls. Therefore, you may want to make a point of asking yourself what is the right tool for the job before swinging away.

Topics CoveredNaming Conventions & Reserved Words Option Explicit and Variable Declaration Data Types do matter Scope of Action Another Meaning of Scope Compile early and compile often and test your code just as often How do I code this? Using Constants, Functions, and Properties Eight atomic procedures are better than one big sub Using class modules to maximize reusability and reduce work
Naming Conventions & Reserved Words

Our first step is to decide on a naming convention. It actually doesn't matter what you ultimately decide on. The real criteria here is that this is a convention you are able to stick with throughout the life of project. It has failed as soon as you stray away from the convention because you found something more convenient.

The most commonly used convention among Access developers is Leszynski-Reddick style. That should give you an idea of what is usually included in such conventions.

Furthermore, you should be aware of 'Reserved Words'; it may seem intuitive to name a field storing dates "Date", but how would Access know if you're talking about a field named "Date" or a function named "Date"? This kind of naming can create confusion and unpredictable behavior. Generally, if there's a function, object, or property that has a name, it's a reserved word and thus should be avoided (or at least prefixed/suffixed).

Here's a list of reserved words that will serve as a good starting point and give you a sense of what constitutes a reserved word.

Option Explicit and Variable Declaration

The single thing that will do the most to help you to code efficiently is to require variable declaration for everything.

This can be set by Tools -> Options. On the dialog, select Editor tab and check "Require Variable Declaration"

This will automatically insert a line at top of every module:

	Option Explicit

This protects you from accidentally creating extra variables that you did not intend. For example,

	Public Sub TryMe()

Dim st As String

sr = "foo"

End Sub

Without the option explicit, we would end up with two variables, st which is never set, while sr now has value of "foo" which should have been set for the variable st. With Option Explicit, this would raise an error and thus call your attention to the troublesome variable which you can then take steps to correct.

Another benefit of using Option Explicit is that it helps to reinforce the good habit of specifying a data type explicitly. The default data type for any variable is Variant when we do not specify a type. While variant has its uses, it is relatively expensive because VBA has to resolve the type every time you use a variant.

	Dim x 'This will be initialized implicitly as a variant. Not good.
Dim y As Integer 'Better now we have an explicit data type

Data Types do matter

As alluded to earlier, it is important to be very precise in what data type you use. Each data type has its costs in terms of memory consumption and time to process that data type.

Generally, it is best to use the smallest data type that fits your needs. For instance, an Integer data type will serve you well if you do not need a number less than -32768 or greater than 32767.


	Dim obj As Object 'May be iffy
Dim wrksht As Excel.Worksheet 'This is more explicit

Declaring a generic Object data type is also expensive because, like the Variant data type, VBA has to resolve the capability of data type Object before it can use it. For programmers who actually need late binding, this is a legitimate use.

The bottom line is that you need to know various data types that are available to you and be explicit when defining variables. This also works for disambiguating different libraries. Suppose we used both ADO and DAO (a problem area for those using Access 2000, 2002, and 2003 with MS flip-flopping between the libraries). There is nothing wrong with using them both as long we disambiguate:

	Dim drst As DAO.Recordset
Dim arst As ADODB.Recordset

There is one more point to remember when working with data types- it's easy to end up with several different types when you think you're using just one. This operation actually involves two data types:

	Dim dtMyDate As Date
dtMyDate = "2008-31-10"

Which has two data types, but VBA will automatically coerce the string to a date for you, but this may not be always what you want. Therefore, there are instances where you will have problem with data types. It will be useful for you to remember that not everything is what it seems to be. This is especially true when you are dealing with objects such as controls or recordsets where you may set various data type to its various properties; being careless can have unintended side effects.

Furthermore, if it happens that you have two different data types but need to perform operations with both, it's usually a good idea to be explicit in casting them.

	MyString = MyInt + MyDbl* 
'How does VBA know if you wanted a Double stored as a String or an Integer stored as a String?
MyString = Cstr(CDbl(MyInt) + MyDbl) 
'This explicitly tells VBA that you want your Integer cast to Double, added to another Double, then cast as a String.

Scope of Action

One simple concept becomes incredibly important all too soon when dealing with VBA. That concept is "scope of action" - which in programming terms, means - how far-reaching is this program's operation? How much do you want it to do? How much data will it have to "touch" to accomplish its goal?

At one end of the spectrum, we can talk about a bit of VBA code that is associated with a single control on a single form, with the intent of making the control's background pale green when a number is positive or pink when the number is negative. This is a very simple code requirement that probably has the smallest possible scope of action. In VBA terms, the hardest part here is knowing where to put the code, which will probably not exceed five or six lines including the IF statement and its two main branches - one for the positive case and one for the negative case.

At the other end of the spectrum, VBA can read a directory, open a file, control a loop that reads records, import data to a table via Recordset operations, and update a progress bar on a display while ALL of the above is going on. Or it can open an Excel Workbook to read various cells. It can open a Word document to perform searches and statistical operations. It can do all of these at once.

With this incredible range of action, VBA can be your friend for all sizes of problems. However, there are times when VBA is more of a hindrance than a help. For beginners, the temptation is prevalent to write some VBA code to convert some complex data from one format to another. However, an update query can often do this without any VBA code at all. OR you can write the VBA code as a function that could then be used by a query. That means that the data conversion would be in VBA code but record navigation would be done via SQL.

Therefore, one useful skill is to know whether some action is or is not within the range of actions available within VBA. It is recommended that any person wanting to solve a problem with VBA should first know other aspects of Access and SQL syntax. VBA can and will help you "re-invent wheels." This sometimes leads to a glut of wheels, which isn't necessarily a good thing.

Another Meaning of Scope

VBA actions can have implied scope. However, the VBA routines themselves also potentially have a specific scope in terms of "visibility." The visibility of a VBA function or subroutine depends on where it is defined and who can call it (activate it). Scope of visibility, in simplest terms, is "who/what can see it?"

Access supports two types of VBA (code) modules. These are the standard modules and the class modules. A standard module is accessible from an object shown in the Module tab of the database window. Code in a general module could be activated from any form or report you have defined.

Forms and reports have class modules. All private procedures in a class module is visible only from the object associated with that module, and public procedure are accessible only when the object is open. A closed form doesn't exist in the list of forms. (It is, however, in the "Documents" or "AllForms" collection. But that's a concept for another lesson.)

A common mistake is to define a function in a class module for form A and expect form B to be able to use that function. If you want to reuse a function in multiple forms, it must be stored in a general module. This, too, is a practical application of the concept of "scope."

One fine point: The scope of action for a piece of VBA code does not have to match the scope of visibility for that code. But some authorities suggest that the two should be related by your choice of module design.

Therefore, an issue to consider when you are thinking about using VBA is to decide HOW you want to use it - from many places or one – and how much you want to do with it - narrow scope of action or wide-open scope of action. "Ambitious" is a word that accurately describes a novice VBA programmer wishing to write his/her first VBA subroutine to have a wide scope of action from a class module.

So when you are contemplating VBA as a solution, ask yourself two questions. (1) Who/what will need to do this? (2) To what must this be done when run from each of the possible answers to #1? THEN and ONLY THEN are you ready to think about the steps required to implement a VBA solution to your problem.

Compile early and compile often and test your code just as often

The biggest favor you can do to yourself is to make a habit of compiling your code frequently. This aids the process of debugging because you confine yourself to only last few lines you added since the last successful compilation.

Likewise, you definitely want to get in habit of testing your code often. By focusing on writing one procedure at a time and testing it, you can make the process simpler. Of course, this is not always possible, so discretion is required in determining what constitutes smallest block of code that can be meaningfully tested.

This is especially true with "Object or With variable not set" or "Too few parameters; expected 1" and similar run time errors. Run time errors are those not caught by the compiler, which only looks at syntax errors. Thus, it may be not always clear what triggered the error in first place. You do not want to see that error after having written a hundred lines of code without having had compiled & tested the code.

How do I code this?

A common situation that crops up even for most experienced programmers is they have a process that they haven't quite coded for and don't know the appropriate methods, properties, or statements to execute the statement. When you consider that Windows APIs collectively can have 60,000 different procedures, it should become apparent that even the C programmer has to know how to get the data he needs to program, just as much as we need to programming in VBA.

Therefore, a good habit is to get quite familiar with help files. I even reference help files for several methods that I've already used just to make sure I am aware of all possible side effects of using those methods. At this point, I should note that for Access 97, help files is quite extensive, but not as in 2000 & 2003. In case of 2003, I actually find that using search from the Visual Basic Editor to get the appropriate information is more effective than using the search in Access UI.

Another useful tool is to use Object Browser (can be called by pressing F2 or Views -> Object Browser), which is very useful for a quick perusal of different libraries, objects contained inside each of libraries and what arguments they take and/or return.

Also, it bears repeating that for any problem you are having right now, someone had it before you and in all probability already solved it, so it is usually a matter of searching effectively on various websites. Sometimes they are contained in whitepapers, technical documentation, and/or kb/msdn articles provided by Microsoft. Sometimes they are just a post on a bulletin board. The greater length you are willing to go through to read the fine print and test it out, the better programmer you will be.

Finally, there are times where it's just beneficial to test for yourself instead of running on a wild goose chase or waiting for an MVP to swoop in and rescue you or even if you just plain don't understand. This also implies that your debugging skills should be honed so you can be fully informed when examining the result of the test case. Tips on debugging is beyond the scope of this paper but there are several resources detailing different tools used in debugging.

Using Constants, Functions, and Properties

A rule of thumb is that whenever you have a magic number, a formula or something that exemplifies your business rules, it belongs as a constant, function, and/or property of a module.

For magic numbers such as Pi of a circle, Euler's constant 'e', or anything that will never change ever, constants makes the most sense. (If you need a magic number to be stored persistently but not immutable, the lookup table may be more appropriate.)

	Const Pi As Double = 3.14159

With numbers that you use as a part of business rules... say, calculating tax rates, functions are more appropriate.

	Public Function TaxRate(SubTotal As Decimal) As Decimal

TaxRate = SubTotal + (SubTotal * CurrentTaxRate)

End Function

Note that the 'CurrentTaxRate' isn't defined in that function- that is because it is up to you to decide the best place to store such information. Perhaps querying a table holding history of all tax rates is what your business requires. Or the business may be content having one tax rate that is updated periodically. Regardless of how you decide to implement this, functions are big time-savers.

A prime example is when you need to use calculated controls. It's easy to say "Why write a function when I can just do it all in a calculated control with the control source set to "=SubTotal + (SubTotal * CurrentTaxRate)"?

Well, that is true. Now say that you have 100 of them. Now you've been told that you must charge a shipping & handling fee after the tax. Now you could go and change every one of 100 calculated controls, or you could just go to the function and change it once and all the 100 controls will work as expected. Your decision.

Ideally, your programs should be a bunch of black boxes that can be strung up with a given string so you do not have to go through the arduous process of figuring out which one line causes an error in a giant main function (and there are plenty of errors that will raise on one line but is actually caused by a bad line that could be several lines earlier).

Furthermore, by far the biggest advantage of using functions (and subroutines as well) in a standard module is that it's incredibly easy to debug. You do not even need to open a form to debug it. You do not need to run the application and test it as if you were an end user to debug it. You only need to run it either by clicking the play button or calling it from immediate window (the small window at the bottom of the Visual Basic Editors):

Result will be printed in the immediate window

Property is like Public variables (aka Globals) but is much more versatile and provides you with more control over how it can be manipulated.

A good example would be when you want to use a listbox and its rowsource needs to be changed for every record. Instead of referring to the querydef and recordset every time you fire the OnCurrent event of the form, you can just make it a property and actually concentrate on modifying the parameters. This is what it would look like in the form's module:

	Private rst As DAO.Recordset

Property Get qdf() As QueryDef

'MyQuery is a Parameter Query with the SQL:
'PARAMETERS lngPrimaryKey
'SELECT a FROM table WHERE b = [lngPrimaryKey];
Set qdf = CurrentDb.QueryDefs("MyQuery")

End Property

Private Sub Form_OnCurrent()

qdf.Parameters = Me.MyPrimaryKey 'Set the parameter
Me.MyListBox.Recordset = qdf.OpenRecordset

End Sub

Note that qdf is a read-only property. You can refer to it from everywhere within the form's module (actually, from anywhere as properties are public by default but can be set private if so desired), and if you need to change the name, there's only one place to do it. Everything else will just work™.

Eight atomic procedures are better than one big sub

A common mistake for beginning programmers is to beam with pride over their one-hundred line subroutine. Rarely, if ever, should any procedure should be that many lines. This usually results because you try to wrap a process into a single procedure, when each procedure should in theory represent single task.

It's much easier to write out a procedure that does only one thing but does it well, and then pipe several simple-minded procedures together to generate a complex output. In fact, that is how programmers have been doing it for decades. Suppose we had a process that required us to extract a set of data, format it into something, then export it out. It would be easier to write one sub:

	Public Sub Foo()

Extract it!

Format It!

Export It!

End Sub

But this is better for a production environment:

	Public Sub foo()




End Sub

Public Function Extract(MyData As Arguments)

Extract It!

End Function

Public Function Format(MyData As Arguments)

Format It!

End Function

Public Function Export(MyData As Arguments)

Export It!

End Function

Now, a case can be made that that the second approach is more work than first one because you now have to write four procedures instead of one. However, by writing four smaller procedures, you significantly simplify your debugging process because you only need to know that your function works as expected then forget it. Furthermore, down the road, you may discover that you needed a similar task for a different process... Well, golly, it's a function so it's ready to go! Increasing reusability of your code will make you more productive.

Also, it's easier to go back five years later and re-read the atomic functions and understand what they do, rather than reading the big sub and wondering what the heck you did there. So in this sense, the second one leads itself to better documentation without requiring you to write comments as would certainly be required in first example.

Using class modules to maximize reusability and reduce work

One common task that crops frequently is automating Excel and manipulating its spreadsheet. Thus, it's not uncommon to see code for initializing such automation such as this:

	Private Sub foo()

Dim objExcel As New Excel.Application
Dim objWorkBook As Excel.WorkBook
Dim objWorkSheet As Excel.WorkSheet

Set objWorkBook = Excel.OpenWorkbook("MyWorkBook")
Set objWorksheet = objWorkBook.Worksheets(0)

'Do something....

Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing

Whew, that was a lot of work managing several objects, especially if all we wanted to do was to export a bunch of data in a specific worksheet for same workbook. And then multiply that by every time we need to create a new procedure to deal with different aspects (e.g. maybe to read off a worksheet for example, or to add new worksheet or whatever).

Some may use public variables to help resolve this issue but that has its own set of problems. One good example is if you set a public variable to this worksheet but later refer to it thinking it was set for that worksheet, you have a problem.

A Class module is a perfect solution in this circumstance. You only need to declare one line:

	Dim MySpreadSheet As New MyExcelClass

And the class module will run through the steps to initialize the application for you, retrieve the workbook and worksheet for you from this single line. Depending on what methods and properties you give to a class module you can even make it as simple as:


In which the method AppendNewData (written by you) already knows the correct position to place the new data, so you only need to write the class module once, then just refer to the methods of that class module. Intellisense (that tiny scrolldown menu that pops up to help you complete code) also will work for your custom class modules.

If you find yourself needing classes; Here's a primer. Generally, your code should aim to be highly cohesive yet loosely coupled.


Whenever you turn to VBA to fill in the niches to meet your business requirements, it is possible for you to start out with a generic procedure that encapsulates the task at hand. When you have a rough draft, it become more apparent how you can refactor your code into black boxes that are 1) easy to use, 2) can be used for several different tasks, 3) are self-documenting or self-evident. Developing those habits will serve you well in long time.

Last thing. Whenever a solution seems out of reach, or the light bulb above your head turns on, no matter how brilliant and crystal clear it is, you want to first ask yourself: Who else has had this problem or task in front of them, and how did they solved it? Their solution is just a search away. By doing this, a solution can probably be found to your insurmountable problem, or if others solved similar tasks differently than you had planned to, then maybe your planned solution is not the best way to approach your task. Paraphrasing AWF's geoergedwilkinson: "People are very clever in their ability to twist VBA or Access to do easy things in the hardest way possible". A critical eye is what saved me from running down a long alley only to hit a wall or re-inventing unnecessarily complicated wheel when there already exist a nice & simple wheel.


There are certainly a lot of questions out there about the internet and VBA. I use internet explorer quite often with VBA to navigate and perform other various automation tasks. This thread will hopefully give you the basics of navigating the internet with IE through VBA code.

Which browser do I use to read webpage source code?
They're making it easier to read through stuff, but I would recommend using Firefox to read source code. It is formatted in a very friendly way and you can find data very easily. Internet Explorer is very poor and I would not recommend using it unless you have IE8. IE8 has been modified greatly for web developers, and it is probably better for readability and navigation now than Firefox is. It is color coded and line numbered, which is a huge improvement by Microsoft (one of the few for sure!)

First thing to know is how to open IE in Access. Just like any other application, the best way is probably to create the object and make it visible. Like this:

	dim ie as object
set ie = createobject("internetexplorer.application")

ie.visible = true

Navigating from webpage to webpage can be done like this:

	ie.navigate "URL"

After navigating to a page, it is always a good idea to pause the code until the page completely loads. This code will do that after the navigation:

	         While ie.busy

You may also want to manipulate some data while you're browsing the internet. This all has to be done by using HTML code. Some of the tasks I do with it are things like pulling values from pages, populating textboxes, and submitting forms. You can do a number of other things, but these are the most common, at least for me. Here is an outline of how to do each of these:

How do I get a value from a page?
Values are stored in a variety of elements in HTML. They can be in tables, textboxes, links, and others. Regardless of where the target value is, you need to know how to reference an HTML control. The most common way is probably by it's ID. You can get an element's value from it's ID with the following syntax:

	ie.document.getElementById("ELEMENT ID").value

I don't believe there is any easy way to identify elements on a web page other than viewing it's source code. To do this, right click on the page and select "View Source" from the menu:

Then you need to find the ID of the element you want to manipulate. In this above example, the ID of the textbox happens to be "q". So, if there was indeed a value in the search box, you can pull it into Access like this:


the HTML source code that tells us the ID is found by viewing the page source. There you will find this:


Elements are generally listed in HTML in the order that you see them appear on the page. Therefore, if you're searching for the ID of a textbox, and the page code is 1000 lines long, use the find feature to find some text that you see on the page that is relatively close to the textbox object. In this example, I searched for the phrase "Google Search". I did this because it is the caption (value) of the submit button that is right below the search box that I wanted to find. It just so happens that in the code, the element that comes right before the button's code is the search box! Easily found....

How do I put a value into a texbox?
Say you want to dynamically put a value in the google search box. You would need to use this code:

	ie.document.getElementById("q").value = "My Search Text"

How do I check a checkbox?
Example - you want to opt-in for a mailing list when submitting a form. Chances are, the HTML will look something like: PHP Code:

here is how to usually mark the box (code not tested in VBA, only in an HTML editor Dreamweaver): PHP Code:
ie.document.getElementById("optin").checked = true 

How do I click a radio button?
Here is the way I did it one time: PHP Code:
ie.document.getElementById("buttonName").value = "on" 

How do I click on TABS?
This one is a little bit tricky. From what I can tell, there are plenty of ways to write this sort of stuff. Some websites use javascriipt functions with tab number argument variables, and then use them to run the script that takes you to the tab you want. Other websites will simply create tables that look like tab strips, like this source code shows (this is one table row from the entire table): PHP Code:

as you can see, what you see on the screen looks like a tap strip, but it's really a bunch of images behind anchors that take you to the different pages. Notice too, that all the images have a 100px width and a 30px height, to make it look uniform. to click on tabs that are actually tables, you have to get the URL behind the tab image and navigate to it.

How do I choose a value in a combo box list?
Combo boxes are usually called SELECT elements in HTML code. Typical syntax will look like this: PHP Code:

(any prefix)
(no prefix)
To change the value, you can usually set the value of the combo box by giving it one of its option values, like this: PHP Code:
ie.document.getElementById("prefixchoice[]").value = "Solved" 

I pulled this code from the Search Page's HTML on this forum as an example. So, if you were to run that code, you would be searching for all the threads that have been officially "Solved" (provided you actually pushed the Search button too). List Boxes (multi-select or not) are manipulated the same way.

How do I click a button?
There are a couple of ways to do this. In the google example, the ID of the search button is "btnG". So, in order to search google, you need to click it, like this:


Another way to do the same thing is to submit the parent form of the button. In this case, there is only one form on the page, and the index for HTML forms starts at 0, just like the default option base of an Access array. So, in order to submit the google form by using this method, you would need to use this:


In some cases though, these methods are not readily available for any number of reasons. Here is another way to submit a form using the "click" method of the form's button element:


This is a basic overview of how to get starting navigating the web with VBA. You can also do this sort of stuff with other browsers like Firefox, Netscape, etc... However, it is much too complicated for the scope of this FAQ. And in all reality, it is much easier and more efficient to perform tasks like these with internet explorer, because that program is built into windows, and you can simply retrieve the program object by using the CreateObject method, as with all other Microsoft Office applications in VBA.

I just had a requirement whereby I had to search 10,000 webpages for some information that was always located in an HTML table. Before this burden was put upon me, I had no idea how to query tables within a webpage, but in order to get paid, I had to figure it out. So....those who are looking at this FAQ can also benefit from what I did. In VBA, you get an HTML's table cell value like this (tag name for a table cell = ):

	Dim elTableCells

elTableCells = ie.Document.getElementsByTagName("td")

debug.print elTableCells.innerHTML

For the requirement I had to fulfill, there was one table on every page that I accessed. But...the information in the table cell was quite lengthly. Luckily though, the info that I wanted was always preceeded by a uniform text string: Country: . Thus, I could use VBA's text extraction functions to get the information I was looking for within the table's innerHTML. Below is some of the code I had to use to get the info across pages I had to access:

	do until rs.eof

elTableCells = ie.Document.getElementsByTagName("td")
  rs!LOCATION= Trim(Mid(elTableCells.innerHTML, InStr(elTableCells.innerHTML, _
Country: : ") + 27, _ (InStr(elTableCells.innerHTML, "
Director:") - 1) - _ (InStr(elTableCells.innerHTML, "
Country: : ") + 27))) rs.Update rs.movenext loop

i recently had the need to find this code, for a reason i never thought i would use it for, so i'm posting it here. it uses VBA to PUT (FTP) a file to a server...

	function FtpSend()
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long

vFTPServ = "www.MYDOMAIN.com" 'your server

'Mounting file command for ftp.exe
fNum = FreeFile()
Open vPath & "FtpComm.txt" For Output As #fNum
Print #1, "USER UsernameHere" 'use this if a UN is required
Print #1, "PasswordHere" 'use this if a PASS is required
Print #1, "put " & vFile ' upload local filename to server file
Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program

Shell "ftp -n -i -g -s:" & vPath & "FtpComm.txt " & vFTPServ, vbNormalNoFocus

End function

here is an example i gave to someone who didn't know how to operate DOS, and needed to get files to me from a remote location...

	function FtpSend()
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long

vPath = "c:"
vFile = "c:'documents and settings'%username%desktop" & _
          inputbox("Give the file name to upload from your desktop...")
vFTPServ = "www.MYDOMAIN.com" 'your server

'Mounting file command for ftp.exe
fNum = FreeFile()
Open vPath & "FtpComm.txt" For Output As #fNum
Print #1, "USER UsernameHere"
Print #1, "PasswordHere"
Print #1, "put " & vFile ' upload local filename to server file
Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program

Shell "ftp -n -i -g -s:" & vPath & "FtpComm.txt " & vFTPServ, vbNormalNoFocus

End function

MY NEEDS FOR THIS THING now have expanded beyond my wildest expectations, but because the people I work for are not interested in buying the "GoToMyPC" thing, I guess we do this instead, through Access no less! Wow...talk about using programs for what they were NOT intended for. At any rate, here is something that will give the user a chance to select as many folders as they want to upload to a server root directory. As the upload takes place and you see the DOS window on screen doing it's magic, there is a box that pops up on top of that even that says something "Wait until it's done, then press OK". (it wont work this way for others, because my popup for is customize with plenty of code. but in general, here's the IDEA: PHP Code:
Option Compare Database

Function FTPtransfer()

Dim varitem As Variant
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long

vPath = "C:"
vFTPServ = "www.mydomain.com"
jumpdone = False

fNum = FreeFile()

Open vPath & "FtpComm.txt" For Output As #fNum
Print #1, "USER MYUSERNAMEHERE" ' your login
Print #1, "PASSWORDHERE" ' your password
Print #1, "echo Press any key to pick your files to be transferred to the server"
Print #1, pause

With Application.FileDialog(msoFileDialogFilePicker)

         With .Filters
           .Add "All Files", "*.*"
         End With

             .AllowMultiSelect = True
             .InitialFileName = "c:"
             .InitialView = msoFileDialogViewDetails

                    If .Show Then

                      For Each varitem In .SelectedItems
                        If InStr(CStr(varitem), ".") > 0 Then
                            vFile = """" & varitem & """"
                            Print #1, "put " & vFile
                        End If
                      Next varitem

                    End If
End With

Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program

Shell "ftp -n -i -g -s:" & vPath & "FtpComm.txt " & vFTPServ, vbNormalNoFocus

DoCmd.OpenForm "msgboxOK"
Forms!msgboxOK!Message1.Caption = CStr(varitem) & _
                            " is uploading...  Press OK when it has finished"
Forms!msgboxOK!Message1.Visible = True
   While jumpdone = False

End Function 

Here's a substitute for buying an FTP program that I wrote for , to upload, download and delete files from there personal directories on the server. Below is the upload code and download code I used. Quite simple really...(uses DOS FTP): PHP Code:
Function UploadServerFiles()

DoCmd.Close acForm, "MsgBoxSelect"

Dim varitem As Variant
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Uploads", dbOpenDynaset)

vPath = "C:"
vFTPServ = "www.mydomain.com"
jumpdone = False

fNum = FreeFile()

Open vPath & "FtpComm.txt" For Output As #fNum
Print #1, "USER myUserName" ' your login
Print #1, "MyPassword" ' your password
Print #1, "cd MyPersonalServerDirectory" 'DAD'S DIRECTORY

With Application.FileDialog(msoFileDialogFilePicker)

         With .Filters
           .Add "All Files", "*.*"
         End With

             .AllowMultiSelect = True
             .InitialFileName = "c:"
             .InitialView = msoFileDialogViewDetails

                    If .Show Then

                      For Each varitem In .SelectedItems
                        If InStr(CStr(varitem), ".") > 0 Then
                            vFile = """" & varitem & """"
                            Print #1, "put " & vFile
                                    rs!TransferPerson = application.currentuser
                                    rs!fileuploaded = varitem
                                    rs!UploadDate = Date
                        End If
                      Next varitem

                    End If

If .SelectedItems.Count > 0 Then
   Cancelled = False
End If

End With

Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program

If Cancelled = False Then

Shell "ftp -n -i -g -s:" & vPath & "FtpComm.txt " & vFTPServ, vbNormalFocus

                            jumpdone = False
                            DoCmd.OpenForm "msgboxOK"
                            Forms!MsgBoxOK!Message1.Visible = True
                                While jumpdone = False

DoCmd.OpenTable "Uploads", acViewNormal, acReadOnly

                            jumpdone = False
                            DoCmd.OpenForm "msgboxOK"
                            Forms!MsgBoxOK!mESSAGE2.Visible = True
                                While jumpdone = False

DoCmd.Close acTable, "Uploads"
End If

Cancelled = True
Set rs = Nothing

DoCmd.OpenForm "MsgBoxSelect"

End Function 
PHP Code:
Function DownloadServerFiles()

Dim DownloadLoc As String

If DCount("fileuploaded", "uploads") I have attached an FTP application that can be used on any local machine. It connect to remote servers if you put the right data into it! I suppose this is a bit too much to post on here, but it is actually a pretty good program I wrote. Whoever uses it, Enjoy!

This little demo uses a soundex index to search for similar sounding names in a database.

How to incorperate into your application:
Create a new field in your selected table:

Name: FldSoundexCode
Type: Long Integer

Create a new query and bring down the field you want to perform the soundex search on and the above field.

In a new column

Run the query. This should bring up 3 columns

Col1 : Your text field
Col2 : Your new empty field
Col3 : The soundex value of the text field

Highlight column 3 and select copy
Highlight column 2 and select paste.

Design the module ModSoundex as bring up the Function CheckForSimilarSurnames()

edit this line

	    sStr = "Select * From TblPeople Where FldSoundexCode=" & Val(xSoundex)

Change it to match your table name and field name

Edit this line

strItems = strItems & rs("fldNameforLetter") & ";"

Change this to match any field(s) in yout table

Save and Close module

View the vba in the form FrmSoundexDemo to see how to use in your application.


The code below is for a form, and is triggered by the OnExit event of the subform. A MsgBox asks the user if they want to print a report. If they clicked yes, then the report opens in print preview. Next, the print dialog box appears. Here's where a problem occurs:

If the print job is executed, then everything is okay. However, if the print job is canceled from the print dialog, then the application "freezes" because the screen repainting isn't turned back on. I could easily remove the screen repainting, but then the user has to see all of the background steps to print the report. What can be done to still have the screen repainting disabled at the beginning, and then enabled at the end, even if the user cancels the print job from the print dialog box?

Thank you in advance

Code: Private Sub subSubform_Exit(Cancel As Integer) 'To show a Yes/No message box when the user exits the subform to ask if they want to print a report. If MsgBox("Do you want to print a report?, 4 + 32, "Print Report?") = 6 Then 'Enter below what to do if they click 'Yes' (start printing) On Error GoTo ErrorHandler 'To turn off screen repainting Application.Echo False DoCmd.OpenReport "rptReport", acViewPreview 'Opens print dialog for current screen (report in this case): DoCmd.RunCommand acCmdPrint '>>>>> 'To close the report after being sent to the printer DoCmd.Close acReport, "rptReport" 'To turn on screen repainting Application.Echo True ErrorHandler: If Err.Number 0 And Err.Number 2501 Then MsgBox "Error: " & Err.Number & vbNewLine & Err.Description Exit Sub End If 'Enter below what to do if they click 'No' (exit Sub) Else: 'MsgBox "They Clicked No" Exit Sub End If End Sub

As the title suggests Im trying to close a form using VBA if a listbox is empty as per
Code: Private Sub CloseApprovalForm_Click() If TimeApproval_lstbox.ListCount = 0 Then DoCmd.Close acForm, "frm_TimesheetApproval", acSaveNo End If End Sub It works if I comment out the If listcount bit, but I only want users to close it if its empty. Is it me being simple?


Good day,
Please give me your suggestion.
I have a form ‘Orders’ (Orders.jpeg) and if I double click of the ‘Order Code’ = (ID Vessel) open a second form ‘Form Record’ (Form Record.jpeg) that I can change the status etc.
Now when I close the form ‘Form Record’ I need to return back to the ‘Orders’ form and the cursor to remain to the selected previous ‘Order Code’ not to the last one as actually.
In the close form of the ‘Form Record’ the VBA is:

Code: Private Sub Form_Close() Forms!Orders.Requery DoCmd.GoToRecord , , acLast End Sub Thank you. Attached Thumbnails     Reply With Quote 02-16-2012, 11:13 AM #2 pbaldy Who is John Galt? Windows XP Access 2007 Join Date Feb 2010 Location Nevada, USA Posts 9,234 One way that could be adapted to point to a different form:



I have created a search form containing combo boxes and command buttons and would like add an option group to the form.

On my search form, the user must select a department (deptcombo), and then must select a supervisor (supcombo), with the option of selecting an employee (empcombo), before they can click on the button that opens the required form. I would like to add in an option group where, after selecting the department and supervisor, the user then selects the data year (2011, 2012, etc.) and from there clicks on a command button to pull up the appropriate form containing data for the year selected. I have included the field DataYear to all my tables and queries and need to find a way to separate the years when pulling the reports/forms. (I am very new to vba, and have been trying for almost a week to find the right code, with no luck.)

The code I have for one of the command buttons is as follows: (this command button pulls the previous 6 months of data; I need to separate the years somehow)

Private Sub CmdPrev6Mths_Click()
On Error GoTo CmdPrev6Mths_Click_Err

If Forms!Switchboard!DeptCombo & ""= "" And [Forms]![Switchboard]![SupCombo] & ""="" Then
Msgbox "Missing Dept and Supervisor"
Exit Sub
End if

If Forms!Switchboard!DeptCombo & ""= "" Then
Msgbox "Missing Dept"
Exit Sub
End if

If [Forms]![Switchboard]![SupCombo] & ""="" Then
Msgbox "Missing Supervisor"
Exit Sub
End if


If (Forms!Switchboard!DeptCombo = "Tech Ops") Then
' Open Tech Ops Scorecard Metrics Form
DoCmd.OpenForm "_frm_Scorecard_Mth", acNormal, "", "[_qry_Scorecard_Mth]![Supervisor] Like ""*"" & [Forms]![Switchboard]![SupCombo] & ""*"" And [_qry_Scorecard_Mth]![EmpName] Like ""*"" & [Forms]![Switchboard]![EmpCombo] & ""*"" And [_qry_Scorecard_Mth]![MthEndDate]>=Date()-183", , acNormal
End If
If (Forms!Switchboard!DeptCombo = "Plant Ops") Then
' Open Plant Ops Scorecard Metrics Form
DoCmd.OpenForm "_frm_Plant_Mth", acNormal, "", "[_qry_Plant_Mth]![Supervisor] Like ""*"" & [Forms]![Switchboard]![SupCombo] & ""*"" And [_qry_Plant_Mth]![EmpName] Like ""*"" & [Forms]![Switchboard]![EmpCombo] & ""*"" And [_qry_Plant_Mth]![MthEndDate]>=Date()-183", , acNormal
End If
DoCmd.Close acForm, "Switchboard"

Exit Sub

MsgBox Error$
Resume CmdPrev6Mths_Click_Exit

End Sub

Someone told me to use either the Year or DateDiff functions, but I don’t know how to code those to bring it all together. I am extremely new to vba and am really hoping you can help me.


In using this new version of Access, I have found that when creating buttons, Access no longer uses vba codebehind to handle all the dirty work - rather, it seems Microsoft is forcing me to use the upgraded (and very nice I must say) Macro builder. This is fine for all the simple tasks, but what I have myself into is a little more complex than the Macro builder is ready to handle I think...

Before, when I created a button, it would put the code in VBA, not a Macro. I am trying to build the following button types:

Button 1 - Update a Field in a table, Save form, Close form
Button 2 - Update a field in a table, save form, new record
Button 3 - Update a field in a table, save form, print report, close form
... and so on and so forth.

I used to put 3 or 4 buttons on the form, then copy and paste the generated vba in the correct order in a single button, and delete the dummy buttons. But now it seems as the basic functions are built using an embedded macro, and when that is combined with VBA, Access must choose between one or the other, rendering my button useless.

Is there a setting in 2010 that will change the way buttons are created in that VBA is used by default instead of macros?

I have a difficult one that is hard to explain but here goes.

When a new record is being created in "frmProjects" I want that upon closing the form it will require the person via pop up warning? to add a comment which is a button on the same form that opens "frmRemarks" other wise the record will not save.

Or it could upon attempting to close the frmProjects it could pop up the frmRemarks with a message box saying "Comment is required when creating new record" ???

Any ideas? Please note that I am a novice at VBA and pretty much need things spelled out in detail, but I don't give up!

Thanks, Joe

Hi all

I am using the following code on closing a form in access to kill excell

Set oWS = Nothing
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
Set oExcel = Nothing

all is fine and dandy and it does as i want it to do with the exception that on exiting I get a message box asking if I would like to save the changes to the excel file that is about to close.

I haven't made any changes and wouldn't be doing so, so the question is how do I inhibit the save changes message box so that I never see it, using access vba, a modification to the above code would be nice

Many thanks for your help


I'm using VBA to create a number of queries and forms and would like to refresh the DB so that the queries and forms show up without having to close and re-open the DB manually. I appreciate the assistance.


I have this login form and when I add a new user to the DB, I set the password to temporary. When the password is "temporary" then a new form opens up asking the user if he wants to change password. When user clicks on "yes", then he is taken to another form where he has to enter "new password" and also "confirm new password". So far so good!

I have a button called "Change password" and when user clicks on it, the password is supposed to be updated in the DB. This is where I am facing an error. The code for onClick of the change password button is below:

Private Sub cmdChangePassword_Click()
If Me.txtnewPwd.Value = Me.txtConfirmPwd.Value Then
DoCmd.RunSQL "Update Login set strEmpPassword = Me.txtnewPwd.Value where strEmpPassword = " & strMyPassword & " And lngEmpID = " & lngMyEmpID & ""
MsgBox "Password successfully changed!", vbOKOnly, "Password change"
DoCmd.Close acForm, "F_ChangePasswordYes", acSaveNo
DoCmd.SelectObject acTable, , True
DoCmd.OpenForm "F_Products"
MsgBox "Passwords do not match", vbOKOnly, "Password error"
End If
End Sub

I have declared strMyPassword and lngMyEmpID as global variables.

The problem is when I click on change, I get 2 MsgBoxes asking me to "enter parameter value" (Please check the attached file)

and then I get the confirmation msg saying "you are about to update 0 row(s)". (Please check the attached file - confirm.jpeg)

I know there is some error in my update query cos my password value is not changed.

And even if everything was peachy with my update query, will I still get the confirmation saying ""you are about to update 1 row(s)" ?? Is there anyway to not get that??? Attached Thumbnails       Last edited by accessnewb; 07-27-2011 at 10:33 AM. Reply With Quote 07-27-2011, 10:43 AM #2 TheShabz Court Jester Windows XP Access 2003 Join Date Feb 2010 Posts 1,371 "Update Login set strEmpPassword = " & Me.txtnewPwd.Value & " where strEmpPassword = """ & strMyPassword & """ And lngEmpID = " & lngMyEmpID & ""

See my changes in red and give that a go. Let me know if that works. The two issues were that Me.txtNewPwd.Value was treated as part of a string and not a reference to your textbox. also strMyPassword was not being treated a string. Do a Google search on how to denote a string within a string in VBA.

On a sidenote, unlike VB, in VBA the .Value is implied, so you don't have to specify it. Me.txtnewPwd.Value is the same as Me.txtnewPwd

I am using Access 2003 vba to create a spreadsheet with variable tab names. Below is the code I am using. I am using the same code on another button with different data and it works just fine; the only variance between the 2 code sets is that the one that works has an integer value instead of text (where highlighted red below). When I run the code below - I get a pop up asking me for a value for "General" (which is one of the categories). If I look at the temporary query that was created in Access, it has the criteria as [General] instead of "General" - which explains the parameter request; but I don't understand why it is doing that.

Any help would be much appreciated!

Private Sub Command0_Click()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstDisc As DAO.Recordset
Dim strSQL As String, strTemp As String, strDisc As String
Const strFileName As String = "Name of File"
Const strQName As String = "zExportQueryDis"
Set dbs = CurrentDb
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
strTemp = strQName
strSQL = "SELECT DISTINCT CategoryFROM DescType;"
Set rstDisc = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
If rstDisc.EOF = False And rstDisc.BOF = False Then
Do While rstDisc.EOF = False

strDisc = rstDisc!Category.Value

strSQL = "SELECT * FROM qryMemberData WHERE [Date_Submitted] between #" & Me.txtBeginDate & "# AND #" & Me.txtEndDate & "# AND Category = " & rstDisc!Category.Value & ";"

Set qdf = dbs.QueryDefs(strTemp)

qdf.Name = strDisc

strTemp = qdf.Name

qdf.SQL = strSQL
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp, strFileName & ".xls"
End If
Set rstDisc = Nothing
dbs.QueryDefs.Delete strTemp
Set dbs = Nothing

MsgBox "Description Form Created", vbInformation
Cancel = True

End Sub

How can I use vba code to check for many conditions on my form before allowing user to close out Order?


and so on ? Attached Thumbnails   Reply With Quote 12-04-2012, 05:08 PM #2 pbaldy Who is John Galt? Windows XP Access 2007 Join Date Feb 2010 Location Nevada, USA Posts 9,234 One way:


repeating the test for each. You can add

Exit Sub

to a failed test to prevent it from testing further if you want.

Not finding an answer? Try a Google search.