Multiple rows within a subform - how do I do data validation?

[shabbaranks]

Hi there peeps!

Can anyone help me - I think I need to use a loop method but am not sure how to write it. I am trying to check if a sub form field is populated which works fine with an if "" statement. The only problem is that the particular sub form in question would have multiple entries and so how would I loop through say a manufacturer column making sure that’s it’s not empty?
Hopefully this question isn’t another conundrum


Thanks!

 

[vbaInet]

So why is the check not done before the record is saved?

 

[shabbaranks]

Because its only appended to a temporary table, which holds all the records until the user is happy and submits the whole lot to the main table.

Why am I doing it this way you ask?? Because if I have the main table within the form and multiple users are submitting to the database they will see other peoples entries so I use a "holding" table initially.

Or am I wrong in doing it this way? Thanks

 

[John Big Booty]

You could use the DCount() function to count records that are Null or ZLS in the field you are interested in. If it returns zero you know all your records are complete.

 

[vbaInet]

There you go, one way from JBB.

What would your action be if you found that a field has some blanks? This would determine whether the DCount() will suffice or not.

 

[shabbaranks]

If you can picture it the subform within the main form, a user makes thier selection from the mainform and it gets submitted to the subform/holding table. When in holding a user could edit the entries if necessary, and there is nothing stopping them selecting the "blank" next available record in the holding table. So the idea is that on gotfocus if the holding record is blank a message tells the user to use the form and the focus is then set on the mainform record entry point. (I really need to learn the terminology for Access)

 

[John Big Booty]

There you go, one way from JBB.

What would your action be if you found that a field has some blanks? This would determine whether the DCount() will suffice or not.

You could then use the DLookup() to locate the record(s) that contains the Null/ZLS

 

[vbaInet]

It looks like JBB and I are on the same wavelength there

I would approach this differently though. Aircode:

dim rs as dao.recordset

set rs = me.recordsetclone

do while not rs.eof
    if nz(rs![[COLOR=Red]FieldName[/COLOR]], vbnullstring) = vbnullstring then
        me.bookmark = rs.bookmark
        me.[COLOR=Red]textbox[/COLOR].setfocus
        exit do
    end if
    rs.movenext
loop

set rs = nothing

 

[shabbaranks]

Thanks, I thought it would need a loop as the dlookup would return a value for a column but would stop at just that. Im trying to determin if the other rows in the column are empty or not - as per the code above, vbaInet has done it again thanks

 

[vbaInet]

Glad we could be of assistance.

JBB's method would have worked too. All you had to do is ensure that your Record Source is based on a query, use the DLookup() to get the ID of the row where a Null was found, perform a search for that ID and move to it. More iterations but still would have worked.