SetFocus to a Record on a Continuous Form

[Liz_I3]

Hi
Iam working with Access 2010 I have code that loops through a continuous form and will give the user a message "You have Data Missing" if a Null value is found.

After the message I would like to set focus on that blank record. How do I do that. Here is my code

Dim blnSuccess As Boolean
blnSuccess = True
Me.Recordset.MoveFirst
Do While Not Me.Recordset.EOF
If Me.Type & "" = "" Or Me.RFrom & "" = "" Or Me.Quantity & "" = "" Then
blnSuccess = False
Exit Do
End If
Me.Recordset.MoveNext
Loop

If blnSuccess = True Then
DoCmd.Close acForm, "frmSearch"
DoCmd.OpenForm "frmGeneral"
"
Else

MsgBox "You are Missing Data"
Exit Sub
End If

Thanks you
L

 

[ranman256]

Can't you just filter the entire recordset and only see the nulls?

 

[HiTechCoach]

Hi
Iam working with Access 2010 I have code that loops through a continuous form and will give the user a message "You have Data Missing" if a Null value is found.

After the message I would like to set focus on that blank record. How do I do that. Here is my code

Dim blnSuccess As Boolean
blnSuccess = True
Me.Recordset.MoveFirst
Do While Not Me.Recordset.EOF
If Me.Type & "" = "" Or Me.RFrom & "" = "" Or Me.Quantity & "" = "" Then
blnSuccess = False
Exit Do
End If
Me.Recordset.MoveNext
Loop

If blnSuccess = True Then
DoCmd.Close acForm, "frmSearch"
DoCmd.OpenForm "frmGeneral"
"
Else

MsgBox "You are Missing Data"
Exit Sub
End If

Thanks you
L

Hi Liz,

I will be glad to answer the question you ask. Yes it is possible to do exactly what you want to do. I do it something similar in my applications. There is built in functionality to make it work using the Bookmark Property.


See :Recordset.Bookmark Property (DAO)

I would use the Bookmark property of the recordset.


Here is some air-code (not tested) that will give you the idea:

Dim HasFailed As Boolean
Dim MyBookmark As Variant 
HasFailed = False


With Me.Recordset

  .MoveFirst


Do 
     If Nz(.Type,"")  = "" Or Nz(.RFrom, "") = "" Or Nz(.Quantity,"") = "" Then
           HasFailed = True
          MyBookmark  = .BookMark
     End If

     .MoveNext

Loop Until (.EOF or  HasFailed)

End With


If HasFailed  Then

     MsgBox "You are Missing Data"

     If IsEmptyMyBookmark  Then
        MsgBox "No Bookmark set!" 
    Else
     '     return  to the saved bookmark
          Me.Recordset.Bookmark =  MyBookmark  
   End If
Else

      DoCmd.Close acForm, "frmSearch"
     DoCmd.OpenForm "frmGeneral"

End If

 

[Micron]

My suggestion would be to create a separate function in a standard module so you can reuse it for any form in any project easily since it would be a stand alone procedure. Nothing wrong with the bookmark idea, but using the function, you could build a list of controls that are missing data. Thing is, if the control label is attached/linked to the control, you can get its text value as a meaningful indicator of which ones are missing data; otherwise you can only get the name of the control, which isn't always meaningful to the user. I would, however, test for Null as well, since an empty string is not always the value of a control that has no value. To use the following, the controls you want to check need the Tag property set to something like "Reqd". The function would be something like

Public Function IsNullEmpty(ctl As Control) As Boolean()
If IsNull(ctl) Or ctl = "" Then IsNullEmpty = True
End Function

The call might be

Dim strList As String
Dim ctl As Control

For Each ctl in Me.Controls
  If (ctl.ControlType = acTextbox Or ctl.Type = acCombobox) And ctl.Tag = "Reqd" Then
    If IsNullEmpty(ctl) Then strList = strList & "- " &  ctl.Controls(0).Name & vbCrLf
  End If
Next
Msgbox "Data is missing in " & vbCrLf

More untested air code...

 

[Liz_I3]

Thanks so much for your help....I got it working

 

[Micron]

Out of curiosity, which way did you go?
You could be referring to any responder.