Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    christoh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    12

    Need to write back to a column with date and warn if there is a date in that column already.

    I apologize about my tittle I am new to access and would like some help if what I want is possible. I attached my file in a zip access test1.zip

    I have a table with info that the column "J" (GE) might have duplicates which is fine, but I would like it to once I scanned the barcode "F" using the combobox and it fills out my form, Access populates the date in column "O" (Validity) in all the rows that match the GE, even though the barcode is not the same so if I scan another different barcode "F" but the same GE example Row 2 and 4, it will pop up and error box warning me that it has been scanned before and give me the options to continue or cancel.

    IS something like this possible with Access or should I try Excel instead?



    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    How should Access 'know' that 'it' has been scanned before? What data indicates that?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    christoh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    12
    June,

    that is a great question... would it be possible to:

    Currently I scan the barcode then I use the command button to print current record ("on click" embedded procedure).
    Could that click trigger another procedure that will write to the "validity" column? And then reset the combobox to clear?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Certainly. Does a value in Validity field provide the indication that 'it' has been scanned before? Do you not want this value later overwritten?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    christoh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    12
    June7,

    Yes the Validity field will provide the indication that the "GE" has been scanned before using a different barcode. If that happens it would be nice to have the error box give me a choice of "Ok" (rewrite the new date/time) or "cancel" just clear the combo box like nothing happened...

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Okay, something like:

    Code in combobox BeforeUpdate event:

    If Not IsNull(Me.Validity) Then
    If MsgBox("Do you want to change Validity date?") = vbNo Then Cancel = True
    End If

    Code in combobox AfterUpdate event:
    CurrentDb.Execute "UPDATE tablename SET Validity=Date() WHERE GE=" & Me.comboboxname
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    christoh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    12
    Click image for larger version. 

Name:	vba.JPG 
Views:	20 
Size:	64.9 KB 
ID:	20222Click image for larger version. 

Name:	access.JPG 
Views:	20 
Size:	39.3 KB 
ID:	20223

    Is this correct bc I get the error below?

    Click image for larger version. 

Name:	error.JPG 
Views:	20 
Size:	34.2 KB 
ID:	20224

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The CurrentDb.Execute is VBA - won't work directly in macro. Macro can call a VBA function with that code. I don't use macros. Why have macro middleman?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    christoh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    12
    OK I might have not understood correctly, the steps I followed:

    Combobox-event-before update-code builder-copied your code in.

    If Not IsNull(Me.Validity) Then
    If MsgBox("Do you want to change Validity date?") = vbNo Then Cancel = True
    End If


    Combobox -event-after update-runcode-function name-copied your code in.

    CurrentDb.Execute "UPDATE tablename SET Validity=Date() WHERE GE=" & Me.comboboxname

    Again thanks for the help, and I apologize if I look like a complete noob...

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I don't know your table and combobox names. Substitute as appropriate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    christoh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    12
    Ok that makes sense, am I entering the code in the correct way (steps I listed above), bc I did change it to reflect my table name (table) and combobox (Combo33) out and still get the same error.

    CurrentDb.Execute "UPDATE Table SET Validity=Date() WHERE GE=" & Me.combo33

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Can't have that code in a macro.

    Is GE a text type field? If so, need apostrophe delimiters: GE='" & Me.combo33 & "'"

    Table should not be used as table name - it is a reserved word. And not at all informative.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    christoh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    12
    June7,

    So I updated the table name (EUDE) and included the delimiters for GE since it is a text type field. I am still lost when you say I cant have the code in a macro. Where and how do I do it? You might have to give me step by step if you don't mind.

    Thanks

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    How did you create the BeforeUpdate VBA procedure? Do similar process to create AfterUpdate VBA procedure.


    Is that + character in form name? Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    christoh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    12
    OK I changed the form name (EUDEnew). I run into an error stating Run-time error 3073 "Operation must use an updateable query" and takes me to the debug screen:
    Click image for larger version. 

Name:	macro.JPG 
Views:	11 
Size:	79.8 KB 
ID:	20234

    I promise I will figure this out! Thanks

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date Format Different in same column
    By drunkenneo in forum Access
    Replies: 2
    Last Post: 01-20-2014, 01:55 PM
  2. date amd text as same column
    By drunkenneo in forum Access
    Replies: 1
    Last Post: 12-08-2013, 10:16 AM
  3. Date Difference from same column
    By akmehsanulhaque in forum Queries
    Replies: 1
    Last Post: 02-19-2013, 01:31 PM
  4. Replies: 3
    Last Post: 09-19-2012, 08:59 AM
  5. Replies: 1
    Last Post: 04-15-2010, 02:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums