Replies

Thanks. I can append the customer list to the customers table – it gives me key violations if the company already exists in the list, which I can ignore. I have then been able to append the addresses to the addresses table, and it pulls across the company id for the relevant company. However, it is at the third stage, when I want to append contact names to the contacts table that I get problems. I get a cross-product of the records – that is, if there are four addresses and six contacts it suggests that it will append 24 records!

How can I get rid of the cross-product and ensure that it will populate the contacts table with the correct AddressID numbers? Help!

The keyboard shortcuts I refer to occurred within the Open and Save As dialogs.

The following code seems to be doing what I want. It still seems slow though??

Sub Combs()
Dim strNumber As String
Dim varCombs As Variant
Dim intCounter As Integer

For varCombs = 123456789 To 9876543210#
strNumber = CStr(Format(varCombs, “0000000000”))
For intCounter = 1 To 9
If InStr(Right(strNumber, 10 – intCounter), Mid(strNumber, intCounter, 1)) > 0 Then
GoTo SkipNumber
End If
Next intCounter
Debug.Print strNumber
SkipNumber:
Next varCombs
End Sub

This code generates the entire sequence of numbers, then performs string comparisons to if the generated number contains duplicates. Any other ideas?

Geoff’s code seems to be heading along the right lines – I do want to avoid duplicate digits. However, I was hoping there was a simpler way of coding it??

I intend to use Custom toolbars for the application, but I still need to know how to hide the Menu Bar automatically, on the OnOpen event of the main Switchboard. Andy.

In Access highlight the table and click on Design on the right. Move the cursor to the field you want to change. In the Format property in the list of Field Properties underneath type your custom format.

Almost There! The example cope from Help is:
SaveSetting appname := “MyApp”, section := “Startup”, _
key := “Top”, setting := 75
I’m OK with the ‘setting’ and ‘key’. Should ‘appname’ be the name of an Object Variable that points to Excel? Also, does ‘section’ need to pre-exist, or will it create a new section? Cheers, Andy.

Whenever you open Excel it re-loads the Add-Ins, so that this option reverts to its default. I think you will need to create a VBA procedure that runs on starting Excel that changes this option. If you are new to VBA programming perhaps someone can help you with the code to do this?

Thank you for your prompt reply.
However, I wish to create a range name that doesn’t refer to a specific sheet. In Excel you can use RefersTo: =!$A$1:$D$4 (without a sheet reference). Typing the range name in the name box and pressing Enter selects A1:D4 for whichever sheet you happen to be in. However, trying to do the same in VBA generates an error.

When you choose Insert/Hyperlink from Excel, having tracked down the document, you can type the Bookmark name directly in the Named Location area. The Tooltip doesn’t show the Bookmark name, but it does work.

Sorted! I hadn’t included a Reference to Aptbean.xla. By including this the Toolkit functions appear in the Object Browser. I don’t then need to qualify the reference to the use the function(s). For example, ‘MsgBox Networkdays(Now(),Now()+10)’ works beautifully!! Cheers.

If you use the custom format ’00”:”00′ you can enter the time of 11 minutes, 20 seconds as 1120 and it will display correctly. This avoids you having to always key a leading ‘0:’ for the hours, but involves a bit more work if you need to sum the minutes and seconds.

I tracked down an article that shows me how to use the Registry to store the invoice number. However, I would prefer not to use the registry if possible. I was thinking of storing the first number in the original template. When a new document is created I want to open the original template and read and adjust the next number from there. Is this possible?

Cheers, this works Ok. However, I feel sure I should be able to reference the function directly, without creating a string. The function must reside in the Funcres.xla Add-in, although I don’t know the name of the module. I’m look for something like ‘Workbooks(“funcres.xla”)…NetWorkDays(..)’?!

I’m not up with OutLook VBS either, but I notice you quote the word false in the first line within double-quotes. I would have thought that you should omit the quotes as the word False is a number.