Message box criteria

[Fisher]

I have a combo box (ICUAdmittingDx) that has a table (AdmittingDxtbl) for row source. There is a text box (AdmDxOther) on the form that needs to be filled in when specific items are selected from the combo box. There are 13 of these out of a total of 86. I would like to have a message box appear reminding the user to put an entry in the text box if they have selected one of these 13. I thought I was on the right track but I have the message box appearing when any selection is made.
Any help would be greatly appreciated.

 

[delboy2]

Hi Fisher
Here is some code i have used before it might give you some ideas


Private Sub Next_Click()

Private Sub cmdnew_Click()
On Error GoTo Err_cmdNew_Click

If IsNull(txtAC) Then
MsgBox "Please enter the customer's account number", vbInformation, "Datanet"
txtAC.SetFocus
Exit Sub
ElseIf IsNumeric(txtAC) = False Then
MsgBox "Please re-enter the customer's account number", vbInformation, "Datanet"
txtAC.SetFocus
Exit Sub
ElseIf txtAC = 0 Then
MsgBox "Customer account number is '0', you may have pressed ESC key, you must restart details from beginning or Click Delete before exiting form", vbInformation, "Datanet"
txtAC.SetFocus
Exit Sub



End If

If cbofranchise.Value = 0 Then
MsgBox "Please enter a franchise number", vbInformation, "Datanet"
cbofranchise.SetFocus

End If

If IsNull(DISCOCode) Then
MsgBox "Please enter a disconnection reason", vbInformation, "Datanet"
DISCOCode.SetFocus
Exit Sub

End If

If IsNull(SaveCode) And txtsumC > 0 Then
MsgBox "Please enter a save reason", vbInformation, "Datanet"
SaveCode.SetFocus
Exit Sub

End If

If IsNull(cboattempt) And txtsumC = 0 And IsNull(MoveDate) Then
MsgBox "Please enter what you offered the customer", vbInformation, "Datanet"
cboattempt.SetFocus
Exit Sub
End If
If IsNull(Combo214) Then
MsgBox "You Must Enter The Call Type"
Combo214.SetFocus
Exit Sub
End If
If DISCOCode = "HMNS" Then


DoCmd.GoToRecord , , acNewRec
end if
end sub

 

[Mile-O]

Let's suppose your table is called tblProducts.

In tblProducts we have the following fields:

• ProductID (an Autonumber) - This is the table's primary key
• ProductName (a Text field) - This is the product's name
• ProductPrice (a Currency field) - This is the product's price
• ProductDescription (a Text field) - A short description of the product
• SupplierID (a Number field) - This is a foreign key to the suppliers table.

Okay, we have our products table now.

We now want to allow the selection of a product in a form. So we set the RowSource of the combobox (we'll call it cboProducts) to the table. Access allows this but it is not necessary to bring the Price, Description, and Supplier into the combobo. So what do we do? We make a query. Having a query as the combobox's RowSource is more efficient as it allows us to only select the fields we need and to sort the fields.

Thus we can make a query with SQL like this:

SELECT ProductID, ProductName
FROM tblProducts
ORDER BY ProductName;

We save the query as: qryProductNames


The ORDER BY clause lets us sort by the ProductName - a feature you won't get from setting a table as the RowSource. We've also saved Access the huffing and puffing necessary to process the other fields in the table which, in this situation, are deemed unnecessary.

So, we tidy up our combobox. We set it's ColumnCount to 2 to reflect the two columns selected in the query and we set it's ColumnWidths property to 0. Although there are two columns the 0 only applies to the first which is the ProductID - a meaningless number (to the user) which the user doesn't need to see.

But now we have the problem that we have a textbox that we want the user to enter something into should they select one of the thirteen items in our list of eighty-six. The solution is not to hardcode and checks for one of the thirteen values but to add a new field to the table. Maybe the field could be called: TextRequired. This field would be set to a Yes/No field to reflect the boolean nature of our problem.

So, now our table structure (of tblProducts ) is this:

• ProductID (an Autonumber)
• ProductName (a Text field)
• ProductPrice (a Currency field)
• ProductDescription (a Text field)
• SupplierID (a Number field)
• TextRequired (a Yes/No field)

Now, for the thirteen items that require extra information, check their TextRequired field.

We need also to alter our query:

SELECT ProductID, ProductName, TextRequired
FROM tblProducts
ORDER BY ProductName;

This means we must update the combo's ColumnCount to 3 and it's ColumnWidths to 0;2;0 - we only want the second column to be visible to the user.

Now we have everything in place to force the user to enter text when they select one of our thirteen items. We'll call the textbox txtInformation.

In the form's BeforeUpdate event we can put:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.cboProducts.Column(2) =True And IsNull(Me.txtInformation) Then
        MsgBox "You have not entered the extra information required for this product.", vbExclamation, "Missing Information"
        Cancel = True
    End If 
End Sub

This is how you do it. Remember to change the control and object names to suit your database.

 

[Mile-O]

A few comments on delboy's code:

Private Sub Next_Click()

Private Sub cmdnew_Click() ' Two sub declarations. First one needs an End Sub first
On Error GoTo Err_cmdNew_Click

If IsNull(txtAC) Then
MsgBox "Please enter the customer's account number", vbInformation, "Datanet"
txtAC.SetFocus
Exit Sub
ElseIf IsNumeric(txtAC) = False Then
MsgBox "Please re-enter the customer's account number", vbInformation, "Datanet"
txtAC.SetFocus
Exit Sub
ElseIf txtAC = 0 Then ' This would have been picked up in the IsNumeric() If prior. Only textual code will ever get to this point.
MsgBox "Customer account number is '0', you may have pressed ESC key, you must restart details from beginning or Click Delete before exiting form", vbInformation, "Datanet"
txtAC.SetFocus
Exit Sub

End If

Also, delboy, using the Me. prior to you form control's will make your compiler smile.

And you have this code in the wrong event. You should have it in the form's BeforeUpdate event.

 

[Fisher]

Mile and Delboy thank you for your responses. I think I will go with Mile's solution. Mile, thank you for taking the time to explain this so completely. It is so clear now that I was looking at it the wrong way. Your efforts (and all others who help here) are very much appreciated.

 

[Fisher]

I'm sorry to keep at this but, believe it or not, I'm still struggling. The message box does not come up.
Because the list for the combo box is so long, I have the items divided into categories. I have an ubound combo box that the users first select a category from and then the combo box only displays the items for the category selected. I seems that since the first combo is unbound, the query comes up empty at the time of the Form_BeforeUpdate event.
Am I way off?

 

[Mile-O]

The TextRequried field should be a field in the table at the lowest point of the cascade which will also be the bound field.

I trust you have this structure for the cascade?

tblCategories
CategoryID
Category

tblItems
ItemID
ItemName
CategoryID (foreign key to tblCategories)