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

[christoh]

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

[June7]

How should Access 'know' that 'it' has been scanned before? What data indicates that?

[christoh]

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?

[June7]

Certainly. Does a value in Validity field provide the indication that 'it' has been scanned before? Do you not want this value later overwritten?

[christoh]

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...

[June7]

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

[christoh]

Is this correct bc I get the error below?

[June7]

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?

[christoh]

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...

[June7]

I don't know your table and combobox names. Substitute as appropriate.

[christoh]

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

[June7]

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.

[christoh]

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

[June7]

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.

[christoh]

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:


I promise I will figure this out! Thanks