Updating fields in form based on date field

[matey56]

Hi all,
I have 4 fields on my form (see below) that I'm working on.




Here's what I want to do:

1. User enters date into "Date_Zipped" field


2. Box pops up warning message to user "This will permanently move # to Previous Label Version. Are you sure you want to continue?"
3. Existing label number in "Latest_Label_Released" will be moved/copied to both "Version_on_CDMS" and "Previous_Label_Version" fields. The "Latest_Label_Released" field will now be blank.
4. The action will repeat every time a new date is entered. If the "Latest_Label_Released" field is blank, then nothing happens.

I'm not good with VB script. Can someone help me out?

[ranman256]

i would add an unbound date field for user to enter this date: txtDateIn
hide the actual txtDateZipped

this way if the user answers NO to the prompt, you do nothing.
if YES, add then do your script:

sub txtDateIn_Afterupdate()
if msgbox("Are you sure you want to continue?",vbYesNo ,"Confirm") = vbNo then exit sub
 
'new record?
DoCmd.GoToRecord , , acNewRec

txtZipDate =  txtDateIn 

'more field assignements

end sub

[June7]

Do you want to modify values in record?

Code in date textbox AfterUpdate event, something like:

With Me
    If Not IsNull(.tbxLatest) Then
        .tbxCDMS = .tbxLatest
        .tbxPrevious = .tbxLatest
        .tbxLatest = Null
    End If
End With

[matey56]

Do you want to modify values in record?

Code in date textbox AfterUpdate event, something like:

With Me
    If Not IsNull(.tbxLatest) Then
        .tbxCDMS = .tbxLatest
        .tbxPrevious = .tbxLatest
        .tbxLatest = Null
    End If
End With

This works perfectly, thanks! One more question, is there a way to bold certain parts of the MsgBox?

[June7]

None that I know of.

[isladogs]

This works perfectly, thanks! One more question, is there a way to bold certain parts of the MsgBox?

Yes there is. See Formatted Message Box (isladogs.co.uk)
Or if you want additional functionality, use a task dialog message or create your own custom message form. See Attention Seeking Database (isladogs.co.uk)

[Micron]

Colin; interesting & have to wonder why Eval does that. I understand that Eval invokes the expression service message box instead of the vba message box function but that doesn't explain why it bolds text. Have you ever tried to bold individual words in the message by wrapping Eval's in Eval's? Wondering if that could reproduce something like:

My dog does not have fleas!

EDIT - I'm thinking not. Eval function cannot accept a simple string argument. In the bold message box code, Eval is being passed a function (msgbox) which is handling the string. Perhaps if there was a function that would accept a string without modifying it, a nested Eval might work. What comes to mind is Left("does not",8).

[June7]

More info at https://www.tek-tips.com/viewthread....2%20character.

Be sure to follow the links in last 2 posts.

[matey56]

Now the users are asking if the action of moving the data in the fields and the pop-up msgbox can not happen if the date is deleted. In other words, if the user deletes the date nothing happens at all.....just the removal of the date. How do I code that?

Do you want to modify values in record?

Code in date textbox AfterUpdate event, something like:

With Me
    If Not IsNull(.tbxLatest) Then
        .tbxCDMS = .tbxLatest
        .tbxPrevious = .tbxLatest
        .tbxLatest = Null
    End If
End With

[June7]

Wrap that code in another If Then

With Me
If Not IsNul(.tbxDate) Then
'code
End If
End With

Or maybe a single If Then
If Not IsNull(.tbxDate) AND Not IsNull(.tbxLatest) Then

[isladogs]

Hi Micron
The formatted message box can only bold the first section of the message.
If you need something more complex, then the best solution is a customised message form using rich text.
Or as already mentioned, task dialog messages can be used.
One more possibility is Cypris' lookout » MS Access: Enhanced Message Box Replacement (nkadesign.com) which also uses rich text.

[matey56]

Sorry, I'm slow. Below is the code I have. Where are you saying to add the new code?

Private Sub Date_Zipped_to_Mfg_AfterUpdate()
If MsgBox("Are you sure?", vbYesNo, "Confirm") = vbNo Then Exit Sub
With Me
If Not IsNull(.Date_Zipped_to_Mfg) Then
.Version_on_CDMS_and_Current_at_States = .Latest_Label_Released_for_Prod
.Previous_Label_Version = .Latest_Label_Released_for_Prod
.Latest_Label_Released_for_Prod = Null
End If
End With
End Sub