Popup message based on a field value

[lico]

Hello,
very new to vba, i have a data base that contains CLIENT ORDERS TABLE and a CLIENT ORDERS FORM that feeds this table, in this table i have a YES/NO field for payment status, yes = paid order.

i also created a CLIENT DEBTS QUERY that shows qlients that have a debt based on the YES/NO field.

when filling the form, there is a lookup column that let you choose the client, what i want to do is create a pop up message that pops when client chosen have an unpaid order.

1) what will be the right event for this procedure?
2) do i need to use the query for this or should i write a code that compare the value to the table directly?
3) if anyone can help me with a sample code for this procedure since i have no idea when to start, i'll appreciate it

Thanks!!!

 

[evanscamman]

First, I will assume that you also have a Clients table and a unique ID for each client. Your Combo box that lists clients needs to use this ClientID as its first column (you can use the columnwidth property to hide the ID). If all this is in order than the following will work for you:

Put your code in the AfterUpdate event of the List or Combo Box.

dim intRecords as integer
'Count # of unpaid Orders for selected Client
intRecords = Dcount("[OrderID]","tblClientOrders","[ClientID] = " & ClientComboBox & " AND [PaymentReceived] = False")
 
if intRecords > 0 then msgbox "Client owes money!"

Substitute the names of your tables, fields and ComboBox in the above code and it should do the trick.
You don't need your query.

Evan

 

[lico]

Hi evanscamman,
Thank you very much,
it did it!!!

is there a way to create a button in the message box that will run the clients debts query for the specified client so i can view the missing payments?

i alredy have a query built that show all clients in debt based on the YES/NO Check box but i need to know how to run it form the message box and how to limit the result for the selected client only.

thanks again for your help!!!

 

[evanscamman]

Yes,

You will want to create another form with your query as the recordsource.
Set its default view to datasheet.

In your query, in the clientID column, put:

Forms!YourFirstFormName!YourComboBoxName

in its criteria field.
In otherwords, the only records returned by the query will be the ones that match the ClientID that your user has selected.

Then just make a button, and in its OnClick event put:

Docmd.OpenForm "YourNewFormName"

That should do it.
Evan

 

[lico]

yap, thanks again, i wish i could see it so clear as you do.

i'm trying to create the messeage box that says "this client owes money, would you like to see debts details?" with YES and NO bottuns,
when the user click NO the meesage will cancel, if the answer is YES then the form that i created (the one that shows client debt based on the query) will open.

i tried this code but when i click yes nothing happend:

Private Sub ClientID_AfterUpdate()
Dim intRecords As Integer
Dim Answer As VbMsgBoxResult
'Count # of unpaid Orders for selected Client
intRecords = DCount("[OrderID]", "Client_Orders", "[ClientID] = " & ClientID & " AND [PaymentStatus] = False")

If intRecords > 0 Then MsgBox "Client Owes Money!", vbYesNo, "Warning!"
If Answer = vbYes Then
DoCmd.OpenForm "ClientDebt", acNormal
Else
DoCmd.CancelEvent
End If
End Sub

once again thank you very much for your help!

 

[evanscamman]

You are very close, but rather than just tell you the answer, I will try to explain the logic behind it.

First of all, 'Answer' is an undeclared variable that has no value.

Second, at the very top of your VBA code window you will find the
global definitions area (It says Option Compare Database)
Add the line: Option Explicit

Now, when you try to use an undeclared variable there will be an error. This will help you pinpoint problems and typos must faster. Make sure every module you add always has 'Option Explicit'

Second, if you want the msgbox function to return a value (if you want to know what button the user clicks) you must define an integer, than assign the integer to the value that msgbox returns.

'Define your variable
Dim intAnswer as integer
intAnswer = msgbox("This client owes money!" & vbCrLf & vbCrLf & "Would you like to view unaid items for this client?", vbYesNo, "Warning!")
 
if intAnswer = vbYes then docmd.OpenForm "frmClientDebt", acNormal

Notice the two different ways to call a function:

MsgBox "This Client Owes Money"
intAnswer = Msgbox("This Client Owes Money")

A function is designed to return an answer. When you assign a variable to receive the answer, you must enclose the function's parameters in parenthesis. Or you can just call the function and not capture its answer, in which case you do not use the parenthesis.

CancelEvent is not relevant to a msgbox.

Evan

 

[lico]

Hi Evan,
i did as you said, it still a bit complicated for me since i'm new to VBA so i didn't got the idea completely but i'm starting to get the feeling, took me a little time but i got it to work.
again, thank you very much for your time and patience i really appreciate it.
Lior

 

[evanscamman]

No problem - glad to help!
Evan