joined yesterday. Great forums here.
Here is my problem. I am designing a small app to track Meter boxes issued to a group of contractors.
I have a from "frmUpdateMeterBoxDetails" with 30 textboxes "txtBoxNo1 ... txtBoxNo30". I have so far succeeded in coming up with the code to validate whether a Meter Box has already been issued.
My problem is that I have failed to come up with a way of saving only those boxes that have not been issued and at the same time highlighting those that have already been issued.
Here is my code:
Private Sub cmdSave_Click()
Dim rs As DAO.Recordset
Dim i As Integer
Dim NoOfBoxes As Integer
Dim MeterBoxNo As String
On Error GoTo HandleError
NoOfBoxes = Me.txtNoOfBoxes
MeterBoxNo = "txtBoxNo"
Set rs = CurrentDb.OpenRecordset("tblMeterBoxNos")
For i = 1 To NoOfBoxes
If DCount("BoxNo", "tblMeterBoxNos", "BoxNo ='" & _
Me.Controls(MeterBoxNo & i).Value & "'") > 0 Then
Me.Controls(MeterBoxNo & i).ForeColor = vbRed
MsgBox "The meter box(es) in RED have already been isued." & _
" Please enter correct meter box No(s) or delete to proceed.", _
vbOKOnly, "Duplicate Meter Box No(s)!"
Set rs = Nothing
Any assistance will be greatly appreciated. I am confident that this is the coolest forum.