Replies

> Where do you find documentation for things like that?

In the Access VBA help.

> I am trying to actually get the tab itself to have the focus and not the first field on the tab. Is that possible?

Don’t think so.

Hans,

Using the Me.TabCtl0.Value = 3 in the on open event, I can hit Control Tab once the form opens and the tab is highlighted/has the focus. Is there a way to send shift tab after the Me.TabCtl0.Value = 3?

Sorry to be so dense.

Thanks,

Ken

You have to set the Value of the tab control. The value is the zero-based index of the active tab page. Setting it to 0 activates the first page, setting it to 1 activates the second page, etc.

So if you want to set focus to the fourth tab page, you’d use

Me.TabCtl0.Value = 3

Thanks Hans.

Where do you find documentation for things like that?

Having asked that, using the above sets the focus on the first control on the tab. I am trying to actually get the tab itself to have the focus and not the first field on the tab. Is that possible?

Thanks for your help.

Ken

You May be able to use the AutoKeys Macro to redefine the F1 Key.

If you do a help search on AutoKeys it should tell you how to do this.

You need to Create a Macro and show the macroNames column

In the name column Define the Name as {F1} (same syntax as with sendkeys.

In the action Box you can then use RUNCODE to call your own function to run a suitable help pop up.

Hope that helps.

Andrew,

Thank you for the suggestion. I will give it a try.

Ken

You can’t use an Access table for this.
You’d have to create a Windows Help file (.hlp) or a HTML Help file (.chm) and specify that in the Help File property. There are specialized tools to create such help files.
Each subject in a help file has a Help Context ID that uniquely identifies it.

Thanks Hans.

A Google search for help authoring tools is overwhelming and the tools seem to have more bells and whistles than I really wanted to include. Maybe I can just create a little something that reroutes the F1 key, and present the info to the user in a pop up window.

Thanks for your help. As usual, you are dead on.

Ken

The essential line is

ShowFiles = .SelectedItems(1)

Because ShowFiles is the name of the function, this tells VBA that the function should return the value of .SelectedItems(1). It wouldn’t have worked if the line had been

SomethingElse = .SelectedItems(1)

that makes sense.

Thanks, Hans!

You could create a function:

Code:

Function ShowFiles() As String
	With Application.FileDialog(msoFileDialogFilePicker)
		.InitialFileName = "C:AIR*.*"
		If .Show Then
			ShowFiles = .SelectedItems(1)
		Else
			MsgBox "No file selected", vbInformation
		End If
	End With
End Function

Ah! so setting the function ShowFiles as a string, that allows passing values back to calling code. Awesome.

Hans, thank you not only for your help, but for your patience.

Ken

You can use Application.FileDialog to let the user select the file. See [post=”735585″]Post 735585[/post] for example code.

Hans,

That worked GREAT! However, I had to use the follwing within the module and not as a sub.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim r As Long
Dim strFile As String
Dim f As Integer
Dim strLine As String
Dim lngPos1 As Long
Dim lngPos2 As Long

On Error GoTo ErrHandler
Set db = CurrentDb

With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = “C:AIR*.*”
If .Show Then
strFile = .SelectedItems(1)
Else
MsgBox “No file selected”, vbInformation
End If
End With

f = FreeFile
Open strFile For Input As #f
. . .

If I want to call the sub and pass the .SelectedItems(1) back to the calling module, what is the syntax? In other words instead of using the above as a part of the onclick event, I want to do something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim r As Long
Dim strFile As String
Dim f As Integer
Dim strLine As String
Dim lngPos1 As Long
Dim lngPos2 As Long

On Error GoTo ErrHandler
Set db = CurrentDb

strFile=ShowFiles()

f = FreeFile
Open strFile For Input As #f
. . .

Does that make any sense? I am probably not using the correct terms.

See [post=”795434″]Post 795434[/post] for an example.

Hans,

THANKS! I like this much better. A cleaner approach, and more control.

As a followup, is it possible to have the user navigate to the file, instead of hardcoding it? For example the incoming file will not always have the same file name, unless I force the user rename the file once they get it.

As always, thank you sharing your brilliance.

Ken

See [post=”795434″]Post 795434[/post] for an example.

That looks interesting. I will play with it.

THANKS!

An alternative would be to use VBA to open the text file, read it line by line and add the data to the appropriate tables. But this will probably be slower than the method you use now.

Thanks Hans. Just for kicks and giggles, how do you open a text file via VBA?

What command do you use to export, OutputTo or TransferText, or maybe some other way?

New discovery. If I move the field from the first position in the query to the second, it DOES leave the zeros in.

Weird.

What command do you use to export, OutputTo or TransferText, or maybe some other way?

Right now I am in test mode, so I am not exporting via VBA, but instead export the query manually (Export/Save As Type/Text Files (*.txtl;*.csv;*.tab;*.asc), give it a name. When I exported delimited I set the field widths.

Exporting ignores the format set for a numeric column; you must use the Format function or a text field.

When I export from Access 2002 to a text file, leading zeros created by the Format function or in a text field are preserved.

Which application are you using to view the exported file? If I open such a file in Excel, it tries to interpret a column as numeric if possible, so I lose the leading zeros. But if I use Notepad, I do see them.

Obviously I am doing something wrong then, as I use Notepad to open. I am using Access 2003, and I tried both the Format function as well as exporting as a text field. I even tried hard-coding the data, instead of using the field in the table. The following SQL Statement is an example of the query.
SELECT Format(23396,”000000000″) AS ID, tmpTEST.ICN, tmpTEST.HICN, tmpTEST.I_txtPersonID, tmpTEST.I_txtLastName, tmpTEST.I_txtFirstName, tmpTEST.Gender, tmpTEST.DOB
FROM tmpTEST;

Yet the results drop the leading zeros in the first field.

Thanks Mark,

I, too, use CutePDF, but I’ll look into the code that you mentioned.

Here’s a sample using Lebans’ class.

[post=”776166″]Post 776166[/post]

Are you sure that it’s a good idea to delete a printer using VBA?

See How to Add/Delete Printer Programatically using API? for the code needed.

You may recall a few months back I asked about TIFF printers.

Long story short, we acquired a PeerNet product “TIFF Image Printer”. It allowed programming in the path and file name, similar to Stephen Lebans pdf (similar being a relative term).

The code written to access the TIFF printer in code creates an instance of the TIFF printer and creates the TIFF files as expected. However, when done, it leaves the TIFF printer installed. So in one case, the user had over 300 printers, 294 of which were TIFF printers that had not been deleted.

So I figured if there was a way to delete printers with names starting with values 0-9 I could delete them after printing. I hope this makes sense.

Thanks for your help.
Ken