Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    15

    Unanswered: Quick date introduction

    Hi, I'm trying to speed up the introduction of dates since right now on access 2007 you need to either write the full extent of the date (05-05-2010 or something similar), so what I'm trying to do is for example I introduce the number 20 and it automatically would give me 20-04-2010, I already have the code to do this, but the problem is that I can't do this on a Date/Time field, I can only do it on a text field since as soon as I leave the record on a Date/Time field access verifies if the data on the field is a valid date, which OFC it isn't since the VBA code hasn't run yet.

    I have found several solutions to this problem like making a unbound box introducing the day, and then it would copy the day-month-year to the field that is connected to the table, I could also do a keydown with the tab key so every time that I pressed the tab it would run the code, but even though that does work, it has a problem since when you don't use tab to change the field the error would pop up, there are a few more solutions that I thought about but I'm not really interested in using any of those.

    Anyway getting back on topic what I want, is like I have said introduce a 1 or 2 digit numbers and get a full date, and be able to do this on a Date/Time field

    The basic code to do this is:

    Me.TxtData = Me.TxtData & "-" & 0 & Month(Date) & "-" & Year(Date)

    I introduced this on afterupdate event, and a few others but can't seem to get the code to run before access verifies for data integrity, the only place that I tried that run the code before access verified was on the keydown event.
    I have a few more line of codes that handle the variations like when it's a 2 digit month, a 1 digit day etc... but those are just details that have no real importance to the problem at hand.

    Also on a side note does anyone know of a way to scan a file directly to a database?

    Thank you.

  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Try throwing it into the field's Exit event, that way it'll change it before it updates. Or a dirty way could be to count your field length in the change event and when the length gets to 2 have it put the month and year in for you.

    i.e.

    Private Sub txt_SomeField_Change()

    If len(trim(txt_SomeField.Text)) = 2 Then
    txt_SomeField = txt_SomeField.Text & "-" & 0 & Month(Date) & "-" & Year(Date)
    txt_NextField.SetFocus
    End If

    End Sub

    I would also throw something in there to make sure it's valid entry.

    Sam, hth
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    a way to scan a file directly to a database
    What do you mean by this?
    Have a nice day!

  4. #4
    Join Date
    May 2009
    Posts
    15
    Sinndho imagine something like a print button where you would press it and it would print the data that was on the form, but instead of a print button would be a scan Button, that when pressed would scan the file that was on the scanned an attach the file that was created to an attachment field on the form, something like

    docmd.scanin samsung_scan_a_lot_1000, me.attachment_field 'me.attachment_field would be where the file would be saved

    I'm just asking if anyone has been able to do something similar, since it would be something that would certainly be handy for some people (me included)

    Quote Originally Posted by SCrandall View Post
    Try throwing it into the field's Exit event, that way it'll change it before it updates. Or a dirty way could be to count your field length in the change event and when the length gets to 2 have it put the month and year in for you.

    i.e.

    Private Sub txt_SomeField_Change()

    If len(trim(txt_SomeField.Text)) = 2 Then
    txt_SomeField = txt_SomeField.Text & "-" & 0 & Month(Date) & "-" & Year(Date)
    txt_NextField.SetFocus
    End If

    End Sub

    I would also throw something in there to make sure it's valid entry.

    Sam, hth
    Thanks for the help, but unfortunately putting it on the exit field doesn't work on a date/time field, since it verifies for data integrity before it runs the vba code, as such it only works on a text field and afterupdate also works in a text field.
    The second suggestion is something that I hadn't thought about it, although I already had if len's in there to introduce 0's and such I never thought about doing it that way, unfortunately it isn't working, maybe my code is wrong, please take a look:

    Private Sub TxtData_Change()
    If Len([TxtData]) = 2 Then
    Me.TxtData= Me.TxtData & "-" & 0 & Month(Date) & "-" & Year(Date)
    Me.IDFuncionário.SetFocus
    End If
    End Sub

    It gives no error message or anything, it just doesn't run the code when the number of characters = 2.
    Last edited by HunterPT; 04-16-10 at 07:48.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    scan a file directly into a db... bad idea in my books
    scan the file, store the file in your naming convention then store the URL to that file in the db
    incidentally think about how you name files. often it helps tputting the date as part fo the filename, if you do then use the ISO date YYYY/MM/DD form rather than the skank mm/dd/yyyy.

    you could fill the text box easily enough, assign the value if a date varaible
    mydatevar=cdate (year() & "/" & month() & "/" & "20"_
    textbox1.value = mydatevar
    or you may need to set the value of that text box as a date
    textbox1.value = cstr(year() & "/" & month() & "/" & "20")
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Quote Originally Posted by HunterPT View Post
    Private Sub TxtData_Change()
    If Len([TxtData]) = 2 Then
    Me.TxtData= Me.TxtData & "-" & 0 & Month(Date) & "-" & Year(Date)
    Me.IDFuncionário.SetFocus
    End If
    End Sub

    It gives no error message or anything, it just doesn't run the code when the number of characters = 2.
    Since the text box hasn't updated it yet, the form can't see the text unless you explicitly call for it.
    You need to use the "TxtData.Text". I would personally use a non formatted/unbound text box for this, as input masks are a pain at times to get them to do anything custom.

    Sam, hth
    Good, fast, cheap...Pick 2.

  7. #7
    Join Date
    May 2009
    Posts
    15
    Just tested adding the .text in front of the txtData, but it still isn't working.

    Edited:
    Lol forget it I was putting the .text in the wrong place lol.
    Now I just need to modify the code that I already have, this is so far the best approach to my problem, I retain the date/time fields withouth having to had unboud text boxes, and it's easy to implement (just copy paste the code), the only thing that could be better was if I was able to add just a 1 digit number like 4 or 5 instead of having to add 04 or 05, maybe it's possible to do it by using the keydown approach, something like if keydown vbkeytab and len(me.txtdata.text) = 1 then run_the_quick_date_code.

    Quote Originally Posted by healdem View Post
    scan a file directly into a db... bad idea in my books
    scan the file, store the file in your naming convention then store the URL to that file in the db
    incidentally think about how you name files. often it helps tputting the date as part fo the filename, if you do then use the ISO date YYYY/MM/DD form rather than the skank mm/dd/yyyy.

    you could fill the text box easily enough, assign the value if a date varaible
    mydatevar=cdate (year() & "/" & month() & "/" & "20"_
    textbox1.value = mydatevar
    or you may need to set the value of that text box as a date
    textbox1.value = cstr(year() & "/" & month() & "/" & "20")
    Yeah, scanning directly to the DB is indeed a bad idea, after I took a break from trying to solve this date problem I started thinking about the pro's and con's of saving it to the DB, and pretty much got to the conclusion that there is no upside lol, so yeah I will end up saving to a certain folder.
    As for the name I'm probably going to use the same thing I have been using for a few different things, that it's the ID number of the record that is open in the main form (in this case is the provider), with the ID number of the bill document, and the time of the scan, and one or more things, I'm already doing something similar but it's a print to pdf order.

    As for the second part I'm not sure I got what you mean, I'm assuming your suggestion is for me to use a unbound text box and put the code in there, and then add a line on after update where the value on the unbound text box would go to the bound field, I have already thought about that, it is one of the ways to do it, but I'm really trying to get this to work in a date/time field (even if it is just for the sake of doing it).

    Also I have a question you talked about using a YYYY/MM/DD, I'm assuming that this is because it's a more "international used date" than the others right?

    Thanks for the help.
    Last edited by HunterPT; 04-16-10 at 10:26.

  8. #8
    Join Date
    May 2009
    Posts
    15
    Hi, sorry for the double post but since this is going to be the final code I think it's best to just add it on a different post so if someone wants to use it he won't have to read trough everything, so here it is:

    Private Sub Data_Change()
    If Len(Me.Data.Text) = 2 Then
    On Error Resume Next
    Me.Data = Me.Data.Text & "-" & Month(Date) & "-" & Year(Date)
    Me.IDFuncionário.SetFocus
    End If
    End Sub

    Private Sub Data_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyTab And Len(Me.Data.Text) = 1 Then
    On Error Resume Next
    Me.Data = Me.Data.Text & "-" & Month(Date) & "-" & Year(Date)
    End If
    End Sub

    Now for the code with comments:

    'This first part of the change is for when it's a 2 digit day (10 to 31, or if the user wishes 01 to 31)
    Private Sub datetimefield_Change()
    If Len(Me.datetimefield.Text) = 2 Then 'This part checks to see if the field has 2 characters, if the field has 2 characters it will execute the command
    On Error Resume Next 'This is here because when someone enters two letters the code will still execute (since it's searching for characters not digits), as such adding this will prevent from the debugger appearing
    Me.datetimefield = Me.datetimefield.Text & "-" & Month(Date) & "-" & Year(Date) 'This is where the "magic" happens, the - can be replaced with / and the month(date) and year(date) for a specific month/year or in my case for the month/date that there is in a unbound text box, since using a unbound text box allows the user to not change date if for example he is still introducing stuff from last month/year
    Me.Nextfield.SetFocus 'this is not necessary, but it saves time since the user will not need to press tab, however if the user makes a mistake and introduces the wrong date he will loose more time because he will have to get back to the field to fix it.
    End If
    End Sub

    'This part is optional and it's just if the user wants to allow someone to just enter 1 instead of 01, however this only works when the user presses tab, if the user clicks to change field it will give an error.
    Private Sub datetimefield_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyTab And Len(Me.datetimefield.Text) = 1 Then 'It checks to see if tab key was press And if the number of characters is only 1
    On Error Resume Next 'just like before this is for when users introduce letters instead of numbers)
    Me.datetimefield= Me.datetimefield.Text & "-" & Month(Date) & "-" & Year(Date) 'exactly the same code as before.
    End If
    End Sub

    -----------------------------------------------------------------------------

    Now for a different question does anyone knows if there is a possibility to use a date/time server like windows uses on it's clock to get times and dates?

    EDITED:

    This is just a curiosity that I have since it might not end up being vary practically since it would require internet to be up.
    Last edited by HunterPT; 04-16-10 at 11:05.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •