Replies

You move back to the first record each time you go through the loop.
You don’t need the .movelast and .movefirst within the Do Loop.
The Dim strOldNote as string line should occur before the loop starts so it only happens once.

Hey All,

I’ve been googling and reading and I still can’t figure out how to simply assign a keyboard shortcut to run a VBA routine in Access 2010. This was a piece of cake in 2003 with AutoKeys macro.

I am not aware of anything changing with this from 2003. I still just use an AutoKeys macro like I used to. (But I never use accdb format. Does that affect things?)

30491-autokeysmacro

Doesn’t this do the same?

Environ$(“Username”)

I think you are right Patt.

Why not use a text field for the account number?

I agree that a text field is the right solution.
But Nancy has said: “My problem is that we have the account number field everywhere in multiple databases, tables, queries and reports. It is a key field for us. Changing it everywhere would be a challenge.”

You are correct about CurrentUser.

Here is another function that does what you want.

Code:

Public Function NetUser() As String
    Dim strName As String
    Dim strUserName As String
    Dim intPos As Integer
    strName = vbNullString
    strUserName = Space(25)
    If WNetGetUser(strName, strUserName, Len(strUserName)) = 0 Then
        intPos = InStr(strUserName, vbNullChar)
        NetUser = Left(strUserName, intPos – 1)
    Else
        NetUser = “-unknown-”
    End If
End Function

But you need to put this at the top of a general Module

Code:

Declare Function GetUserName Lib “advapi32.dll” Alias “GetUserNameA” (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function WNetGetUser Lib “mpr.dll” Alias “WNetGetUserA” _
    (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long

Kill strFileFullName

I usually rename the file and/or move it to another folder rather than just delete it. But I agree that just leaving the file there is an invitation to import it again.

If you look on the Property Sheet, at the properties of the form itself, on the Data tab, there are properties that control just this.
I would think that you don’t want users to create new records either (as you are just reusing existing records)
30480-AllowDeletions

As for multiple files and rewriting code will the outcome be any different than what it is now?

If properly rewritten it could handle multiple files and import them.

Here is a version that works (I think).

Code:

Private Sub cmdImportexcel_Click()
    Dim i As Integer
    Dim zXLFPath As String
    Dim zXLFName As String
    Dim iFileType As Integer
    ‘Requires reference to Microsoft Office 14.0 Object Library Office 2010.
    ‘Note: Returns a fully qualified filename, e.g. d:pathfilename.ext

    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim zCurDir As String

    ‘Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        ‘Allow user to make multiple selections in dialog box
        .AllowMultiSelect = True
        .Title = “Please select the file to import”

        ‘Clear out the current filters, and add your own.
        .Filters.Clear
        .Filters.Add “Excel 2003”, “*.xls”
        .Filters.Add “Access 2007-10”, “*.xlsx”

        ‘Set the initial directory using passed argument string
        .InitialFileName = “D:”

        ‘Show the dialog box. If the .Show method returns True, the
        ‘user picked a file. If the .Show method returns
        ‘False, the user clicked Cancel.
        If .Show = True Then
            For i = 1 To .SelectedItems.Count
                zXLFPath = .SelectedItems(i)
                Debug.Print zXLFPath
                If UCase(Right(zXLFPath, 1)) = “X” Then
                    iFileType = acSpreadsheetTypeExcel12Xml
                Else
                    iFileType = acSpreadsheetTypeExcel12
                End If

                DoCmd.TransferSpreadsheet acImport, iFileType, “BridesDB”, zXLFPath, True
            Next i
        End If
    End With

End Sub

This code brings the FileDialog (that was previously a separate function ) back into the sub. Not that there was anything wrng with using a separate function, but I found the multi file easier to handle this way.

You are right that changing to AllowMultiselect=true allows you to select multiple files, but

PickFileDialog = .SelectedItems(1) takes the first of the selected files and returns it.

To work with multiple files would require the function code to be rewritten to return an array of files, which would then need to be imported one at a time using a loop.

The code tries to deal with this by having the FileDialog return Exit if you click Cancel.

Code:

      If .Show = True Then       
              cmdFileDialog = .SelectedItems(1)       
      Else        
               cmdFileDialog = “EXIT”      
      End If

But the subsequent code seems to add a path and an extension to that and treat it as a filename, which it can’t then find.

Try replacing:

If Trim(UCase(zXLFName)) = “EXIT” Then Exit Sub

with

If Dir(zXLFPath) “” Then

then add an End IF after the DoCmd.TransferSpreadsheet line.

Do you even see the File Dialog?
Does it help if you make the change I showed in red?

I just tried this using RG’s code with the FileDialog and I needed to make a change to the PickFileDialog function as below:

Code:

     [COLOR=#ff0000]PickFileDialog[/COLOR] = .SelectedItems(1)    


     [COLOR=#ff0000]PickFileDialog[/COLOR] = “EXIT”       

instead of     [FONT font=monospace][/FONT]cmdFileDialog  in both cases

And this line needed to be changed, to a folder I have.
zXLFPath = PickFileDialog(“G:BEKDocsExcelArchives”)

This line worked as is, because if the table does not exist it just creates it.
DoCmd.TransferSpreadsheet acImport, iFileType, “BridesDB”, zXLFPath, True

When you say it “bombs” can you tell us more about what happens? Do you get any errors or messages?

Try some code like this, behind a button

Code:

Me.RecordsetClone.FindFirst “isNull(Title)”
    Me.Bookmark = Me.RecordsetClone.Bookmark

This assumes the current form has access to al the records.
First, of course, depends on the order they are in. But I don’t suppose it matters much which one is found, as long as you find one.

That makes it even worse. You can only use DateDiff with Dates.
There is a CDate function that will convert things to Dates but you have to be sure that what you have is recognisable as a Date. With a text field that is hard to guarantee.

FieldNo5 ReportTime Text (9.50AM on daily basis as default )
FieldNo6 ReportedAt Test Time when actually the employee reports for duty

As I see it you have Reporttime as Text , and REportedAt as a Date.
You can’t calculate a DateDiff between a text field and a Date field.

Is there a reason that REportTime is Text?