I am thinking that shouldn't matter but I noticed the previous post that this was a subform ... was making sure all the controls were on one form (or subform) and not spread out across the two. If so, it's just a reference problem.
Just in case they are all on the same (sub)form, you might have to adjust a little ... might try ProductId.Column(1) instead of just ProductID.
I just entered all the other "controls" other then ProductID and it works so far in that it doesn't return any error codes but I've noticed that the only time it stops you from moving to the next record is if you have the Contrator (MTTOCONT) field filled in. It is ignoring the other validation fields for the (MTTONAME, MTLOCATIONTO & UnitsTransferred) ?
Is this because I have it under a public function rather then under the private sub of (cmdMTGoToNext) ?
Seems I just find more issues the more I put in code...he-he
You made it a Public function under the form. Therefore all the Sub (functions) in that form can see it and therefore they can see the status of the variable ValidateForm (True or False).
Could you take a screenshot of the form in design mode and post it?
Here it is. I noticed after looking at the form I should also force the user to take ownership of the report so I'll have to enforce the MTEMP field as the other 5 as well once I get them sorted out.
Also, if you are going to have to maintain this thing and feel that you are going to be posting here alot --- it would behoove to get your database in somewhat better order.
First, is using that # character in a data field. I know I am harping, but that really needs to get rectified.
Next, is your naming convention. I've attached an example for your controls. Back the ole meaningful name sermon =].
For text fields use something like txtControlSource
For numbers use intControlSource
For combo boxes use cboControlSource
For check boxes use chkControlSource
For list boxes use lstControlSource
Or some such convention thereof ..
As you get deeper into the programming aspect you will appreciate this because just by the control name, you know it's data type. I usually go a step further and only use intCN for integers, lngCN for long types, etc, etc.
Same way with command buttons and everything else.
Last is code indentation. This gives you a visual cue to the different 'shells' in the code and makes it much more readable.
Example.
Code:
Dim sVar as String
If sVar = something Then
Code that does this
Else
Code that does something different
End If
More code and stuff
It gives an order of precedence for the left-most statements so you can see the forks in the code and your mental logic can work the code out just by reading it.
Okay .. here is your problem with ProductID .. it is in a subform.
What you are trying to do is validate 4 controls on the main form and 1 control on a subform. I am not sure without experimentation if that is possible or not - probably someone here would know that via experience - I've never.
All good suggestions I plan on using / taking. I'll try making those changes and see what I can figure out it likely is something simple but sometimes you can't see the forest for the trees
This would be a LOT easier if you could post a zipped copy of the db.
If one of those controls referred to in the validation code is on a subform, then you have a problem. First you have to reference it correctly, as DKinley suggested.
However, that's only going to work to test the value in the control on the currenty selected record within the subform.
Remember, the point of a subform is to handle a 1 to many relationship between the data in the main form, and the subform's source table. So, potentially, there are several records in the subform that are related to the data being currently shown in the main form.
So you need to do something else to validate that each of those subform records has been filled in, not just the current record.
Hopefully that makes sense.
So, your best bet here is to first, save the data to the subform's source table, and then use a dcount to see how many records in the subform's source table contain a null in the relevant field, where the foreign key field's value matches the appropriate primary key value from the main form. If the dcount returns a value greater than zero, you know you have a problem.
