Replies

Standby….I’m looking at your database now.

Hi Bill,

Feel free to post a compacted and zipped sample, that shows your efforts, if you run into problems. Of course, remove any sensitive data first, or just create a new database importing just the bare minimum to show the functionality. Note: I have a personal preference for the .mdb file format.

Hi Bill,

Implementiong a synchronized combo box is really not that difficult, if you spend some time working with the Northwind sample database. I agree that implementing proper database design can be difficult, especially for beginners, but doing so will pay dividends in the future. There is lots of good information on the topic of database normalization:
.

Database Design Tips by Michael Hernandez:
http://www.accessmvp.com/JConrad/downloads/DatabaseDesignTips.zip

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Good Luck[/FONT]

Hi Bill,

> Is the above the best way to achieve what I am trying to do? Are there any alternatives?

No and yes to your two questions. Having similar information (catagory subtype) in three different tables is not in accordance with database normalization rules. The much better alternative is to have a categoryType lookup table, as you do, and a categorySubType table that includes all of the data in the three tables. You will relate the data by creating a relationship between the primary key of the categoryType table and the foreign key of the categorySubType table. You can use either a text-based primary key in the categoryType table (ie. the actual data: Expenses, Income and Refunds), or a meaningless autonumber (surrogate) primary key. I prefer using autonumbers myself. I’ll use an autonumber primary key (pk) and Long Integer foreign key (fk) in my example.

tblCategoryTypes
pkCatType (autonumber / set as primary key)
CategoryType

tblCategorySubTypes
pkCatSubType (autonumber / set as primary key Note: Pretty much every table should have a primary key.)
CatSubTypeName (this would likely be the same data that you have in the three tables).
fkCatType (long integer. If you are using Access 2003 or earlier, remove the default value of 0).
plus any other fields that describe attributes specific to category subtypes.

The fkCatType field will contain values from the pkCatType field in the tblCategoryTypes table.

Here is an example, from the Northwind sample database for Access 2002 and 2003 (the same logic works in any version of Access). This example looks to use numeric keys:

How to synchronize two combo boxes on a form in Access 2002 or in Access 2003
http://support.microsoft.com/?id=289670

In your case, the field “CatSubTypeName” would be used in place of ProductName in the Northwind sample, I think something like this [untested “air code”]:
[Code]
Me.cboCatSubType.RowSource = “SELECT CatSubTypeName FROM” & _
” tblCategorySubTypes WHERE fkCatType = ” & Me.cboCategories & _
” ORDER BY PCatSubTypeName
Me.cboCatSubType = Me.cboCatSubType.ItemData(0)
[/Code]

In the above example, I have used a commonly used naming convention, “cbo”, as part of the names of two combo boxes on your form. Try working the example out first, in the Northwind sample database. Then try mimicking this example in your database, that has table and field names specific to your application.

Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp → http://www.xoc.net/standards/rvbanc.asp#Access

Using a Naming Convention
http://msdn2.microsoft.com/en-us/library/aa164529(office.10).aspx

Reserved Words
Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Hi RG,

I tried all kinds of things….simply hitting , adding some blanks spaces and then hitting , and, as you’ve said to copy the code from a module and paste it in-between the [ Code ] and [ /Code ] tags. Nothing has worked for me.

Thanks. That looks better.
How do I add a blank line in the procedure?

You can use a Continuous form, with some fairly simple VBA code, which allows you to sort the form by clicking on the appropriate column label. Here is an example, for a label named lblSubject, with caption = Subject. The field is named SubjectTitle. You use the click event of the label.
[Code]
Option Compare Database
Option Explicit

Private Sub lblSubject_Click()
On Error GoTo ProcError

Static blnOrderDesc As Boolean

Call UnboldLabels

If blnOrderDesc = 0 Then
Me.OrderBy = “SubjectTitle”
blnOrderDesc = -1
Else
Me.OrderBy = “SubjectTitle Desc”
blnOrderDesc = 0
End If

Me.lblSubject.ForeColor = 128
Me.lblSubject.FontBold = True

ExitProc:
Exit Sub
ProcError:
MsgBox “Error ” & Err.Number & “: ” & Err.Description, _
vbCritical, “Error in procedure lblSubject_Click…”
Resume ExitProc
End Sub
[/Code]

Add similar click event procedures for all fields that you wish to allow the user to sort. Here is the code for a procedure that is called from each lblName_Click procedure:
[Code]

Public Sub UnboldLabels()
On Error GoTo ProcError

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.ControlType = acLabel Then
ctl.ForeColor = 0
ctl.FontBold = False
End If
Next ctl

ExitProc:
Exit Sub
ProcError:
MsgBox “Error ” & Err.Number & “: ” & Err.Description, _
vbCritical, “Error in procedure UnboldLabels…”
Resume ExitProc
End Sub
[/Code]

None of these things are show stoppers. They are just examples of the fact that sometimes things that work in one version cause problems (big or small) in other versions.
I prefer 2003 myself, but I have clients who ask “Does this work with 2010?” So I force myself to use it.

I agree that none of these issues are show stoppers. I’m really curious about the advice that the OP received from his consultant. From what I’ve heard, 2010 is a vast improvement over 2007, but, I have to qualify this statement because I haven’t used it myself.

While I certainly agree with the general proposition that most aspects of a database developed in earlier versions of Access should work OK with 2010, it is worth being alert to the prospect that some changes might be needed.

A few examples I have encountered.

[*]If you have a Report with “Page Break After Section” Access 2010 will always print a blank page at the end. Earlier versions of Access suppressed the blank page. I have reports that are usually printed one at a time on a single page. 2010 always churns out a second page.
[*]If you change the Record Source of a Report when you open it, and also apply a Filter, Access 2010 ignores the filter.
[*]If an unbound text box has a default value, Access 2010 will not let you set the value to Null.

Some of these might have been fixed with Service Packs or Hot Fixes, but I don’t like to rely on users having applied these.

I’m still using Access 2003 myself. What can I say….I’m just not in love with the Ribbon (or the Navigation “Pain”). The first issue you mentioned, with the report page break, should not be a show stopper that requires an extensive re-write, as the original poster quoted. Disappointing? Sure.

I guess I’d never encounter the 2nd issue, since I do not filter open reports. I do my filtering at the recordset level, to minimize network traffic. This approach follows the golden rule: request only the data you need. I have heard of the third issue, but I think it may be corrected with a service pack.

I think these days we pretty much *have* to rely on users having the latest service packs installed, or make darn sure you test throughly with an earlier version if there are one or more service packs available. At my place of work, I ran into a TON of errors with one particular Access application, used by hundreds of users, when a few people did a voluntary upgrade to Office 2007 (including Access 2007). This was with the original RTM version of Access 2007, as well as with SP-1. It was not until SP-2 was made available that many of these errors were fixed. As an MVP, I personally reported many of these errors directly to the Access PMs at Microsoft, and they were always able to replicate them with the samples I provided. It seems easy enough to use code to check for the existance of a service pack at startup. I have since implemented this type of code in the application at work.

Snapshot to .pdf issue:
I have used Stephen Leban’s code for personal projects, but I haven’t had any luck getting his download made available on an internal download site, at the Fortune 100 company that I work at. As a result, I have not been able to implement his methods for work-related applications.

I just recently used the Win2PDF utility, for an Access 2003 database at a company that my sister runs. I had heard good comments about this product, for several years, from another Access MVP. The opportunity presented itself in this application, as I needed something that I could program with VBA code to automate some tasks. Yes, I did have some learning issues, but once I worked through these, the result was beautiful. She is now able to produce 30 customer invoices, in .pdf format, at the click of a button. It takes approx. 90 seconds to write these 30 files, which she then emails to her customers. This compares with the process she showed me, which was taking her over 4 hours start to finish each month to produce 30 invoices.

Or, even as a quicker fix, simply reset any references marked as MISSING to the appropriate version specific reference. That should take maybe one minute of your database “professional’s” time. I agree with RetiredGeek that using late binding, when possible, is the preferred long-term solution.

She said Access databases written for 2003 will not run under 2010 and must be extensively rewritten to do so.

Your 2003 databases should run just fine in Access 2010, without further modification (assuming they do not contain old methods that *should* have been updated long before Access 2003 was released). Are you able to post a copy, perhaps with some dummy data in it?

Getting back to the issue of the Access 14 reference, with two versions of Access installed, you can prevent this problem if you use virtualization. I use VMWare Workstation, which, while not free, is a very good product. Microsoft offers a free alternative (Virtual PC). There are some other free virtualization software products available as well. One requirement: you need to have a separate license for any software that you install in a guest machine, including the operating system. But, setting up virtualization allows you to essentially have one or more computers within your main (host) computer. I have guest machines available with Windows 98, 2 with Windows XP (one for A2003 and one for A2007), Windows 7 RTM, etc. It really is slick.

You will want plenty of hard drive space and RAM memory to effectively run a host machine with a guest machine.

Hi Nancy,

Access MVP Allen Browne has two articles that show how to accomplish this goal:

Assign default values from the last record
http://allenbrowne.com/ser-24.html

Duplicate the record in form and subform
http://allenbrowne.com/ser-57.html

Hi Karl,

Try using a debug.print statement to print the SQL statement to the Immediate Window:

“‘” & Me.cboSeventh & “‘, ” & _
“‘” & Me.cboEighth & “‘)”

Debug.Print strSQL ‘<—Add this line of code

CurrentDb.Execute strSQL, dbFailOnError
End Sub
——————————–

Run your procedure. Then open the Immediate Window using or View | Immediate Window. You should see the SQL statement. Click on the Stop button, if necessary, to stop the code (Run | Reset). Copy the SQL statement to the clipboard. Open a new query, switch to SQL view, and paste your SQL statement in as a new append query. Try running it. Does it work there?

If you cannot get the procedure to run at all, try using the undocumented /decompile switch while opening your application. Then, follow this up with a Compact & Repair. Finally, see if you can get the code to compile.

Hi Karl1971,

I just noticed this posting. It was mentioned in the lastest issue of Windows Secrets.

Brackets are required when you include spaces or other special characters in the names of fields, tables and controls. They are also often-times needed when you use reserved words as well. My advice is to avoid the need altogether, by using proper naming conventions. Here are some links that provide helpful information:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

My copy of “AccessLinks.doc”, a Word document with a collection of links and helpful tips:
http://www.accessmvp.com/TWickerath/downloads/accesslinks.zip