Visual Basic,NET - Not inserting correct date.

[Jim Dudley]

The code we use was created by a Contract Programmer. This section of the code is to post the File Name, date & time, number of records imported to the table.

The Name and number of records post correctly but the time post only as time no date and the time is wrong.

The format for the [Date/Time] field is 'Short Date', mm/dd/yy.

Windows 7 date setting is the same. Our Db is Access 2010.

My skill level is 0. This will be my first attempt at modify/editing VBCode.
Please be patient.

2 things:

Is there something wrong with the Code?
What would have to be added to the code to have the 'tbl_ImportHistory' display at the end of the Import Process?

Code:

'Append to the import history. Added 1/2/2012
strFileAdd = Mid(strcurfrm, 12, Len(strcurfrm) - 11)
intTotalCount = DCount("*", strImportTable)
CurrentDb.Execute "INSERT INTO tbl_Import_History ( ih_File_Name, ih_Import_Date, ih_Total_Records ) " & _
"VALUES (""" & strFileAdd & """, " & Date & ", " & intTotalCount & ")"

114 intImportCount = intImportCount + 1

115 Next x

intRetVal = CheckForNew(strImportTable, strDBTable, strImportHoldingTable)

Thank you.

Jim Dudley

 

[pbaldy]

Does this work?

"VALUES (""" & strFileAdd & """, #" & Date & "#, " & intTotalCount & ")"

 

[Kiwiman]

Howzit

Or this? The date will only give the date portion and no time content. FOrmatted to american date so SQL can handle it - not sure if strictly necessary but this is what I tend to do.

"VALUES (""" & strFileAdd & """, #" & format(Now(),"mm/dd/yyyy") & "#, " & intTotalCount & ")"

To open the table when the code stops something like

docmd.opentable "tbl_ImportHistory"

 

[Jim Dudley]

Thank you both for your input. I have tested the suggestions and everything is working as I wish. Opening the Import_History table allows the user visual confirmation that the correct number of records have been imported.

One further question for either of you.

I have several Macros that I created to automate functions within this application. If I asked Access to convert these Macros to VB, how much editing to the finished product might I be expected to do. Is this a good way to slide into VBCoding?

Thank you..

Jim

 

[pbaldy]

I've only done a couple of fairly simple macros that way and the code worked fine. I'd say the conversion can use older deprecated methods, but it can certainly be a good way to learn.

 

[Kiwiman]

Howzit

Likewise for me. I have only converted a couple of macros and found no problem.

I found using the wizard when adding controls quite useful when first starting out as you could then identify how the syntax is created and what it actually does.

Good luck

 

[tehNellie]

I'd suggest it's a reasonable way to start getting your feet wet into the world of VBA. You know what the macro does and are presumably comfortable with creating them, so you can compare the macro to the VBA.

I don't tend to go near macros if I can help it so this might no longer be the case, but my recollection is that the macro conversion will make functional VBA but not necessarily Elegant or Efficient code. So while it's not a bad place to start, don't get to attached to how it implements some things in code, it'll work but there are often better ways of doing it. That said you can apply that to lots of code.

 

[gemma-the-husky]

i think when you convert a macro you tend to get error handling added to the code, which is both harmless, and useful at the same time.

i cannot see why a macro could not convert to code.

The only "special macros" that I think will not convert (well they may convert, but will not work in the same way) are

a) autoexec macro - "startup code"
b) autokeys macros - "respond to certain keypresses"