Error message for combobox rule tied to another field

[rheeyum]

Problem:

Please see my sample database attached.

I have a form called MAIN FORM with a subform called QryUnprocessed subform (datasheet view). The subform has a combobox field FORWARDED TO which I want to tie up to another field called DOC REF. What I want to accomplish was that if I select AP AUDIT on my FORWARDED TO combo box, an error message will appear if the DOC REF field is still "0". The idea is that only requests with DOC REF that is not equal to "0" can be routed to AP AUDIT. This is to ensure that user cannot proceed on choosing AP AUDIT on my FORWARDED TO field if the reference is "0".

Any help?

 

[vbaInet]

There are too many fields on your subform. You will be better off using a Single Form and having a New Record button for new records.

What you want to do in the Forwarded To field will cause the Message Box to endlessly fire. So maybe you want to rethink your approach.

 

[vbaInet]

In any case, I will tell you the only way your concept will work.

You will ONLY perform the check if a value of Forwarded To was chosen. So it shouldn't fire at all if the value is "". Something like below.

After_Update event of Forwarded to:

If Len$(Forwarded_To.value & "") <> 0 then
    if Doc_ref.value & "" = 0 then
        msgbox "Doc Ref cannot be zero." & vbnewline & "Please change doc ref"
    end if
end if

 

[rheeyum]

Oops, sorry for that bro. I want my users to feel no difference between excel spreadsheet and access forms thats why I often used datasheet. In addition, it allows users to update multiple requests instantly. Regarding the Message box that will fire endlessly, I am not sure how it will happen because not all entries/requests can have 0 as DOC REF and FORWARDED TO as APAUDIT. I just want the error message to prompt whenever the user update FORWARDED TO field to AP AUDIT and the REF was still 0. ofcourse the other entries may still be AP T&E and doc reference was still zero so I dont think that those entries will also prompt an error message.

Can you help me please?

 

[vbaInet]

Did you see my last post?

 

[rheeyum]

Yep... Sory, i was soo occupied with the first message... ahahaha, anyways, I made some changes to the code because I only wnat the error message to appear if user choose AP AUDIT, its because I want the users to be able to use 0 as reference when the forwarded to field was Incomplete Request or AP VM.

I made some tests and the error message does not fire endlessly so I am just a bit afraid how it will happen in the future because I want to avoid that as much as possible.

Thank you so much for this code!!! You're always there vbaInet! You're my idol! ahahaha = )

Private Sub FORWARDED_TO_AfterUpdate()
If FORWARDED_TO.Value & "" = "AP AUDIT" Then
If DOC_REF.Value & "" = 0 Then
MsgBox "Doc Ref cannot be zero." & vbNewLine & "Please change doc ref"
End If
End If
End Sub

 

[rheeyum]

can I ask again? My challenge is, the error message will prompt but the user can still proceed when he closed the message. I want it in a way that user cannot proceed until he changed the ref doc value.

Can you help me please?

 

[vbaInet]

You might want to change the message I put in the msgbox to something that makes more meaning to you and your users.

For your second question, you simply change the value of the combo box back to it's old value. I think something like:

Combobox1.value = Nz(Combobox1.Oldvalue,"")

 

[G37Sam]

I'm looking at your DB, why are you refreshing your Main Form every 5 minutes?

 

[rheeyum]

Hi 3ammaksamer!!! I did not not include everything in that sample database... I have a main screen which I want to refresh every 5 minutes.... ehehehe

 

[G37Sam]

I saw that, but why would you want to do that?

edit: it's 3ammaksamer btw lol

 

[rheeyum]

Because I have segragated the subforms with Tabs... The main tab has the live screen so that it displays the figures for each reason/column I want to count... Since it is a live screen, i want the data to refresh every 5 minutes.

 

[rheeyum]

Tnx vbaInet for the follow up solution!

 

[vbaInet]

No problemo!