Programming language similar to VBA


I like using VBA but demand for this in UK is low and doesn't pay much either. Interested to hear what the situation in the US is like.

Does anyone know what is the nearest language that is similar to VBA and has visual stuff as well i.e. forms, text boxes, etc. that is in demand currently?


Sponsored Links:



I have been using MS Access and in particular VBA for many years. However it's slowly becoming less used in the UK. Does anyone know of a programming language similar to VBA but IS in demand?




The bleak outlook I am overhearing on this forum about Access's future has me thinking the question "Where to next?" (After end-of-life A2007 I am thinking...)

I remembered today:
R:BASE
http://www.rbase.com/

Development appears to still be underway for that product. Has anyone on this forum system evaluated it? How does the programming language compare to VBA? How Object-Oriented capable is it?

TIA!




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:

	Code:
	Option Explicit

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


	Code:
	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.


	Code:
	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.

Likewise:

	Code:
	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:

	Code:
	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:

	Code:
	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.

	Code:
	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.)


	Code:
	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.


	Code:
	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):

	Code:
	?MyFunction(MyArguments)
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:

	Code:
	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:

	Code:
	Public Sub Foo()

Extract it!

Format It!

Export It!

End Sub

But this is better for a production environment:

	Code:
	Public Sub foo()

Extract(MyData)

Format(MyData)

Export(MyData)

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:


	Code:
	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:


	Code:
	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:


	Code:
	MySpreadSheet.AppendNewData(MyData)

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.

Conclusion

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.

Contributors
ChrisO
datAdrenaline
georgedwilkinson
Rabbie
stopher
The_Doc_Man




(Not sure where I should have asked this questions. Moderators, please feel free to move this thread if I asked this in the wrong place)

How do you go about reviewing and writing code?

I have a project to automate a tedious process at work that involves controlling another program from Excel. This other programs has it’s own programming language that is very similar to VBA.

I am close to finishing this project but am having a major headache trying to review my code for lines that are redundant, not used, where I performing the same or similiar procedures repeatedly and so on.

I am certain that part of the problem is that I just jumped into writing the code with not much of a plan of attack other than trying to break the code into chucks that perform a particular task and then one “all-in-one” that calls the separate functions.

My question is what tools do you use to review your code (VBA in general)?

Here are some tools that I have found

Printing VBA Code
http://www.starprint2000.com/
http://www.prettycode.com/

Code Flowcharting (Big differences in prices but I haven’t looked into all the features!)
http://www.kunzlertech.com/ ($17.95)
http://www.fatesoft.com/s2f/ ($99.95)
http://www.aivosto.com/visustin.html ($249)

Code Analyzer?
http://www.aivosto.com/project/project.html

Do any of you use tools or does it just boil down to programming practices?




I have a slooow Database. Everytime I go from one form to another (through buttons) it takes longer and longer as time goes (still developing database). So I tried converting my macros ( only bout 10 or so) into VBA through the setup they have on the wizard. My question is, now that I converted to VBA, can I delete the macros?? If not then what is the point of converting to VBA other than more flexibility of programming. Also any tips on how to "unclutter" my database to make it go quicker??

Thanks




Hi,

I have an Access DB which is essentially a form with a timer event which runs code every few minutes to get data in "near" real time from a web service. It then processes the data a bit and feeds it into SQL server tables before waiting for the next timer event.

It all works fine but it's not exactly an elegant solution because I have to leave that DB open on a machine that runs 24/7 (a crude server basically) and over time it eats up the virtual memory and other little problems appear. We have a file server that I can use so what I would like to do is come up with a script that can be called from windows scheduler which will open up a DB object (hidden), call some code in there, and then close afterwards. I know I can do this via a shortcut which opens the DB and it can close itself down afterwards but i'd rather it was done at object level if possible. The problem is i'm pretty good with VBA but I don't know any other langauges and i'm struggling to find a good example.

I took the code below from a post I found on another forum but I don't even know what language it's in or how the script would start. Once I get to the access object I should be ok from there but I just don't know how to get going. I guess VBscript would be my preferred choice of language if possible just because of its similarities to VBA. If anyone could help me (or suggest another alternative) i'd be really grateful.

Thanks,

Tom


	Code:
	Microsoft.Office.Interop.Access.ApplicationClass ObjAccess =  New                                                           
      Microsoft.Office.Interop.Access.ApplicationClass();

ObjAccess.Visible = true;
ObjAccess.OpenCurrentDatabase(@"serverpublicEBtest.mdb", false, "Admin"); *open the database





Hi. I have problem converting macro to VBA.
I have Combo Box that display contract id (in my language "Redni broj ugovora") and when i chose contract id in Combo Box AfterUpdate uses Macro to open form with fields so I can enter or edit data. When I convert that Macro to VBA it does not work correctly. It always open first data entry from Table.

Macro:

	Code:
	SetTempVar
                 Name ActiveControlValue
           Expression =[Screen].[ActiveControl]

If [Form].[FilterOn] Then
    RunMenuCommand
              Command RemoveFilterSort
End If

SearchForRecord
          Object Type
          Object Name
               Record First
      Where Condition = ="[Redni broj ugovora]=" & [TempVars]![ActiveControlValue]

RemoveTempVar
                Name ActiveControlSet

VBA code:

	Code:
	Private Sub Form_Load()

    TempVars.Add "ActiveControlValue", "[Screen].[ActiveControl]"
    If (Form.FilterOn) Then
        DoCmd.RunCommand acCmdRemoveFilterSort
    End If
    DoCmd.SearchForRecord , "", acFirst, "[Redni broj ugovora]=" & TempVars!ActiveControlValue
    TempVars.Remove "ActiveControlValue"

End Sub

Pictures of Macro and VBA code are also in ZIP file.

Can someone please tell me what I'am doing wrong?
Thx




Hi,

Upgrading an Access 2000 database to Access 2003, integrating with Outlook 2003.

I get the message:
A program is trying to automatically send e-mail on your behalf.
Do you want to allow this?
If this is unexpected, it may be a virus and you should choose "No".

This interrupts the automated process in my VBA script.

I know this is caused by the Outlook E-mail Security Update, and have reviewed the workaround options in this MS article: http://support.microsoft.com/default...b;en-us;263084

The options given by Microsoft are:
1. customize the behavior of the Outlook E-mail Security Update. Our Exchange administrator is looking at this option now, but obviously it is far from desirable if it compromises the intended security features of Outlook.

2. use CDO for Windows (CDOSYS). I would like to try this but I can't find the full syntax to work with the classes in CDOSYS. I need to send messages, but also look up addresses without requiring user interaction.

3. use Extended MAPI. I would like to try this but I can't find the full syntax to work with Extended MAPI. I thought I had found an example but it used CDO 1.2 and caused the same error.

4. Create a COM add-in for Outlook. I would like to try this but the example given is for VB 6.0, and I would not know how to do this in Access.

So, has anyone had to integrate Access 2003 with Outlook 2003, and could give me syntax to create an email message which the user can check before sending, and to lookup a full or partial Outlook id in the address book and get the Outlook user's name.

Many thanks,
Keith.




Hi all,

I have a username/password form and when the user clicks ok the program goes through some checks and then is meant to load another form.

But when the user clicks on the program brings up the vba code window and highlights a line in yellow. When I press F5 the program continues and carrys on as normal and loads the form.

when I next load the form it goes in stragiht away.

The line it stops on is:


	Code:
	    Case 0, 1

There isn't a break point here (i.e. not in red).

Any ideas anybody?

Thanks




About one third of my command buttons use simple macros that open and close various forms and reports. The most complex one has 4 lines in it. The remainder required some programming control so I used VBA.

Should I bother converting the remaining Macros to VBA? This involves about 40 macros.

I am using Access 2003.

One constraining element is time - this project will be deployed on the network next week. I just want to know if it is worthwhile.




Can anyone suggest a database programming language like MS Access with Gui interface that will compile to a stand alone exe and doesn't require the ownership of other software like MS Access.

Any thought s will be appreciated




Anyone know where the Convert macro to vba is on access2007?

else
I need this criteria set into the macro somehow


	Code:
	strCriteria = "[claimID]=Forms!frmClaimsParts!claimID"

& this is the macro


	Code:
	Report, rptParts, PDF Format (*.pdf), , Yes, , 0, Print





All,

I've got the following IIF monster in a field in a select query. Can someone give me some advice on how to make this more manageable? I'm hoping to convert it to VBA as it's just a monster to try and manage or make changes to this. I'd like to convert this to VBA and then reference the result in the select query and not convert the entire select query over to VBA as I'm trying to keep this project as simple to manage for someone else who doesn't know VBA as possible.

Thanks in advance.


	Code:
	GeneratedDueSemi: IIf(IsNull([ARMS_DUE_DT]),IIf(IsNull([ACC_DT]),Null,IIf([Remaining]




I am very new to VBA. I am trying to convert an sql statement on the rowsource of a combo box to vba. The following is the part of my sql statement that I cannot figure out:

WHERE (((TB_LS_Bld.Dept)=[Forms]![Form1]![Combo12] Or (TB_LS_Bld.Dept)=[Table1]![Dept]));

Any help with this would be great!




Hello Friends....
I have a question I need to rewrite my code which I made in Expression and need to convert to VBA, if it is available:

	Code:
	=[UnitPrice]*(((100-[Deprpercent])/100)^Int(Format(Date(),"yyyy.mmdd")-Format([DateOfPurchase],"yyyy.mmdd")))

the idea is: I Placed this code on unbound text box "Depreceation" in "DeprPercent" I enter the percent eg: 18% or 20% and after that it calculates yearly.

Thanks In Advanced




Hi

I'm totally new to VBA.
And I think I will be needing it a lot, the next upcoming half a year.
I know that I'm the kind of person only learning from experience.
Therefore I turn myself to you, experts.

So, first problem I walked into and I would like to solve through VBA:
A query I repeat 12 times, once for every month: I've got a table (CalcFirstYear) with 12 columns, named 1 to 12. And I've got a table with a lot of info per month per client (tbl_Retail_CM_2004)

Now I want to update info in the CalcFirstYear table with info from the specific/related tbl_Retail_CM_2004.Month column.

This is the query

	Code:
	UPDATE CalcFirstYear INNER JOIN tbl_Retail_CM_2004 
ON CalcFirstYear.[Cust No] = tbl_xx_Retail_CM_2004.[Dealer] 
SET CalcFirstYear.1 = [tbl_Retail_CM_2004]![Total Retails]
WHERE (((tbl_Retail_CM_2004.Month)=1));

Does anyone have any suggestion of how to resolve this in VBA?

With kind regards and thanks up front for any help,
Timoo




Good afternoon,

I've converted some macro's to VBA.

How do I call on these module's?

Thankxxx




Hello,
I would like to get some advise form you experts out there

I have an SQL Statement that looks like this:

SELECT [D3 PROD HIER OUTPUT].[Item Class], [D3 PROD HIER OUTPUT].[Base System Desc], [D3 PROD HIER OUTPUT].[Family ID], [D3 PROD HIER OUTPUT].[Family Desc]
FROM [D3 PROD HIER OUTPUT]
GROUP BY [D3 PROD HIER OUTPUT].[Item Class], [D3 PROD HIER OUTPUT].[Base System Desc], [D3 PROD HIER OUTPUT].[Family ID], [D3 PROD HIER OUTPUT].[Family Desc], [D3 PROD HIER OUTPUT].[Product Line Desc], [D3 PROD HIER OUTPUT].[Brand Desc]
HAVING ((([D3 PROD HIER OUTPUT].[Family Desc]) Like "*" & [What you lookin for?] & "*"))
ORDER BY [D3 PROD HIER OUTPUT].[Item Class];

Basically I am just pulling [Family Desc].
It works fine in a query. But when I convert to VBA which looks like:

Private Sub Command2_Click()

Dim strSQl As String
strSQl = "SELECT [D3 PROD HIER OUTPUT].[Item Class]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Base System Desc]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Family ID]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Family Desc]" & vbCrLf
strSQl = strSQl & " FROM [D3 PROD HIER OUTPUT]" & vbCrLf
strSQl = strSQl & " GROUP BY [D3 PROD HIER OUTPUT].[Item Class]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Base System Desc]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Family ID]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Family Desc]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Product Line Desc]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Brand Desc]" & vbCrLf
strSQl = strSQl & " HAVING ((([D3 PROD HIER OUTPUT].[Family Desc]) Like ""*"" & [What you lookin for?] & ""*""))" & vbCrLf
strSQl = strSQl & " ORDER BY [D3 PROD HIER OUTPUT].[Item Class]"

DoCmd.RunSQL strSQl

End Sub

I set this up in the OnClick event.
Then i get this error:
Run Time error 2342
A RunSQL action requires a argument consisting of an SQL Statement.

No clue what i'm missing here ....
been troubleshooting for hours....

Any help would be appreciated.

Thanks,
nigel




I want to get a better idea of how much I can expect to extrapolate what I will be learning in my VB class to VBA for Access. I already came across some new terms that I've never seen in VBA, and some terms VBA would not accept at all.

The teacher says that what I learn in VB will apply to VBA but not the other way. But I'm skeptical of this... I'm using Visual Studio 2005 for VB, and I have Access 2003.

Any insights will be appreciated.