Validating Data in Continous Form

[clive2002]

I have a continous sub form which i need to run validation against when a button is clicked on the main form.

I suppose the best way is to loop through each record in the sub form.

- How do i determine how many records there are in the sub form using vba?

- How do i reference a field in each record using vba?

I cant set the validation at a table level.

 

[namliam]

There is a new feature on this forum... SEARCH!!!

It is even a subject in the faq i think, how to work between a main form and subform....

 

[clive2002]

Anybody actually capable of deliverying help on this

nothing in the search is answering the questions and i tried for several days before i even posted.

 

[Rusty]

nothing in the search is answering the questions and i tried for several days before i even posted.

re: the above "Good for you"

namliam there's no need for this. If it is indeed a subject that has already been covered on the forum then do us a favour and reply to the post with a LINK to the answer, not with sarcasm delivered from a dizzy height.

Anyway, if you read the original post I think it goes far beyond the basic do's and don'ts of working between forms and subforms. Rant over...time for a cup of tea.

re: How do i determine how many records there are in the sub form using vba, Clive I'll try and look into this this avo' as I'm having similar difficulties. If you don't hear from me then I've given up and gone home early via the pub!!

Rusty

 

[Mile-O]

You are aware that there can only be one record in a subform?

Although a continuous form may look as if it it showing loads of records, there is only one actively present.

 

[The Stoat]

I have a continous sub form which i need to run validation against when a button is clicked on the main form.

I suppose the best way is to loop through each record in the sub form.

- How do i determine how many records there are in the sub form using vba?

- How do i reference a field in each record using vba?

I cant set the validation at a table level.

OK this should give you some ideas.

This will work using a button on the main form. If you are using access 200X then you need the reference for Microsoft DAO 3.6 Object Library. In the visual basic window go to the menu bar and select tools the references

Dim Rst As DAO.Recordset

' FRM_ACHIEVEMENTS_GAINED_SUB is the name of my subform container. 
' This may be the same name as the form that it contains but it is worth 
' checking first.

Set Rst = Me.FRM_ACHIEVEMENTS_GAINED_SUB.Form.RecordsetClone

' to set the rst to the currently active record in the subform use.
' Rst.Bookmark = Me.FRM_ACHIEVEMENTS_GAINED_SUB.Form.Bookmark



' Number of records in the subform

Msgbox Me.FRM_ACHIEVEMENTS_GAINED_SUB.Form.Recordset.RecordCount



' To loop through all the records
Do Until Rst.EOF = True

' to edit an existing record then

    if Rst!["Name of My Field"] = 10 then
    
      Rst.Edit

        Rst!["Name of My Field"] = 1

     Rst.Update

  end if

  Rst.Movenext

loop


' to set the forms bookmark to the  recordsets bookmark to the 

' Me.FRM_ACHIEVEMENTS_GAINED_SUB.Form.Bookmark = Rst.Bookmark 

' tidy up the recordset object
Set Rst = nothing

I'm not saying that you need to use all of these at the same time but it's something to play with.

Hope that helps

TS