Message by changing one datafield if other fields are filled

[johan]

Hello,

In a database I have, for example, 15 datafields in a record.
The first one is the AutoNr, the second one is what I called "the
lead" and all the others are information.
When I change "the lead" I want a popup 'only' if at least one of the
information fields is filled with data.

So (I think) I need a changemacro on the second datafield.
The macro should do (I think).....

.) count the filled in datafields of the total datafields in the
record.
.) if the filled in datafields is more then 2 give a popup
"blabla"

Can somebody (please) help me out.

regards,
Johan

 

[Guest]

In a database I have, for example, 15 datafields in a record.
The first one is the AutoNr, the second one is what I called "the
lead" and all the others are information.
When I change "the lead" I want a popup 'only' if at least one of the
information fields is filled with data.

Right, no problem...

numData = 0
for i = 1 to myRecordSet.fields.count
if not isnull(myRecordSet.field(i)) then numData = numData + 1
next i

if numData > 2 then...

If you want to make it even easier, you can skip the autonum field, and even
lead, by increasing the for loop to 2 or 3.

Maury

 

[johan]

Thanks, but......
It didn't works well. I think I make a misstake in the completed
macro.
Below the complete macro as I'd written. When changing the lead
datafield I got an error.
So please....... help me out.
regards,
Johan

Private Sub Floc_Change()
Set db = CurrentDb()
Dim myRecordSet As Recordset
Dim msg As Integer

numData = 0
For i = 1 To myRecordSet.Fields.Count
If Not IsNull(myRecordSet.Fields(i)) Then numData = numData + 1
Next i

If numData > 2 Then
msg = MsgBox("Test")
Else
End If

End Sub

 

[Pieter Wijnen]

Your Db and Recordset is Not Linked to a Source

Private Sub Floc_Change()

Dim Db As DAO.Database

Dim myRecordSet As Recordset
Dim msg As Integer
Set db = CurrentDb()

numData = 0

Me.Dirty = False ' Must be a saved record to do it this way
Set myRecordSet = Me.RecordSetClone
myRecordSet.BookMark = Me.BookMark 'Alternate Code below
For i = 1 To myRecordSet.Fields.Count -1 ' Starts with Field(0) !!!

If Not IsNull(myRecordSet.Fields(i)) Then numData = numData + 1
Next i

If numData > 2 Then
msg = MsgBox("Test")
Else
End If

End Sub

Alternate:
'Requires On Error Resume Next Or That all RecordsetSource Fields are
Present on the Form
' Also Requires that Control Names equals RecordsetSource Field Names
Dim Db As DAO.Database
Dim myRecordSet As DAO.Recordset
Dim Fld As DAO.Field
Dim msg As Integer
Set db = CurrentDb()

numData = 0
Set myRecordSet = Me.RecordSetClone
For i = 1 To myRecordSet.Fields.Count -1 ' Starts with Field(0) !!!
Set Fld = myRecordSet.Fields(i)
numData = numData - Not IsNull(Me.Controls(Fld.Name).Value)
Set Fld = Nothing
Next
' etc

HTH

Pieter

 

[johan]

THANKS A LOT, It works well but I also got a new problem with it.
(remarks for info: I forgot to set on the the thickbox "Microsoft DAO
3.6 Object Library" in the references of Modules -> Tools otherwise
you got an module error).

I had used below code but in the fields that I have are also a few
True/False fields registrated.
So.... the message should only popup if all the registrated True/False
fields are set on FALSE.
I'd used now a new part of a IF/Then but now I have to descripe all
the fields separately.
I think (and hope) that for this part there is a better solution.

this part should be another description:

If numData > 10 or ( [blabla1] = -1 Or [blabla2] = -1 Or
[blabla3] = -1) Then

regards, Johan


Private Sub Floc_Change()
Dim Db As DAO.Database
Dim myRecordSet As DAO.Recordset
Dim Fld As DAO.Field
Dim msg As Integer
Set Db = CurrentDb()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

numData = 0
Set myRecordSet = Me.RecordsetClone
For i = 1 To myRecordSet.Fields.Count - 1 ' Starts with Field(0) !!!
Set Fld = myRecordSet.Fields(i)
numData = numData - Not IsNull(Me.Controls(Fld.Name).Value)
Set Fld = Nothing
Next i

If numData > 10 or ( [blabla1] = -1 Or [blabla2] = -1 Or [blabla3] =
-1) Then
msg = MsgBox("test")
Else
End If
End Sub