Ms Access Form/Subform/Subform Validation 1

[oxicottin]

Hello, I have been messing with this DB for over 2 weeks now and I can not figure out how to validate this thing. First, I want to say I open it throughout the day and add data so when I create a record in the main form [frmShiftDay] I have to have the controls txtShiftDate,cboShift and cboSupervisorName filled in and the subform [frmShiftMachinesSubform] I have to have txtMachineID and cboEmployeeName along with the SubSubForm [frmMachineOutputSubform] control cboProductID before moving on to the next machine using the next/prev buttons I made on the subform. I don't need the other data validated because like I said im in and out of it adding info for the day.

I uploaded the database to my MS SkyDrive please have a look. Thanks!

Thanks,
SoggyCashew.....

 

[dhookom]

You should be able to accomplish this by creating relationships and making specific fields required.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

There is relationships and do you mean required as in setting the table field as required?

Thanks,
SoggyCashew.....

 

[dhookom]

do you mean required as in setting the table field as required?" yes

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Duane, I needed something to tell the person what is required if they try to move on without entering required data first. And if I enter data in my main form it would allow me to move on since I havent created a record in my sub or subs sub which I dont want because some of the data is required in those forms before moving on.

Thanks,
SoggyCashew.....

 

[dhookom]

I believe you would need some code in the "next/prev buttons I made on the subform" that would check the data using DLookup() or whatever. If the data isn't value, don't move to another record.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Ok Duane ill keep poking at it until I get it... Thanks!

Thanks,
SoggyCashew.....

 

[MajP]

1) You have child tables, but you do not have a relationship to enforce referential integrity. Ensure you create the relationships and enforce referential integrity.
2) You have a lot of fields that I would assume are required, but you do not make them required fields in your tables. As a starting point make them required, in that way if you forget to validate on a form you cannot create a record without the required fields.
3) To validate fields I usually create a function that returns true if all the fields are valid.

Public Function isValidData() As Boolean
  Dim strMsg As String
  strMsg = vbCrLf & "Fix missing data on form or select CANCEL to exit without saving"
  isValidData = True
  If Trim(Me.Subject & " ") = "" Then
    MsgBox "You must supply a Task Subject" & strMsg, vbInformation, "Subject Missing"
    isValidData = False
  ElseIf Me.DateCompleted < Me.StartDate Then
    MsgBox "Your completion date was before your start date." & vbCrLf & "The date will be set to the start date. Please change if needed.", vbInformation, "Invalid End Date"
    Me.DateCompleted = Me.StartDate
    isValidData = False
  'keep adding else ifs for each field.
  End If
End Function

I would usually call that from some event that can be canceled like before update

If Not isValidData Then
    DoCmd.CancelEvent
    Exit Sub
End If

 

[oxicottin]

Majp, you say I don't have relationships I looked and I hadn't noticed I changed a table name and it deleted the relationships.. I fixed that, sorry!

Thanks,
SoggyCashew.....

 

[MajP]

As a design concept, I always do this at both the table level and the interface level.

So you should ensure at the table level that you establish relationships to enforce referential integrity and account for cascade deletes and updates as appropriate. Then at the table level require the appropriate fields.

Then at the form level verify this so that you do not get the ugly Access default messages. So if a field is required check it first, instead of allowing the user to try to save the record and get the default message. Also at the form level do not allow the user to try to create a child record without a parent.

 

[oxicottin]

Majp, I tried to add the VBA in the before update and fixed the relationships and set the required to yes but yet im still in a bind.

The thing is yes it does work if I create a record in the subform and IF I create a record in the sub sub form. I have to have at least one control filled out in each form for the required to work. so if in the main form I create a record and fill in the 3 required fields then I can move to another record in the main form which is fine BUT if I create a record "machine" in the subform [frmShiftMachinesSubform] and fill in the required data for the two controls I then can move on to another machine WHICH ISNT GOOD. once I have the required fields in the first subform I have to create a record in the subsubform [frmMachineOutputSubform] and have the one control [cboProductID]
filled in before I move on to another machine. How can I do this? I followed your instructions but it isnt working.

I uploaded a new database with all the BeforeUpdate events filled in and the relationships fixed. Thanks!

Link to my NEW SkyDive database



Thanks!

Thanks,
SoggyCashew.....

 

[MajP]

To me this is a design issue. I separate navigation, addition, and update. I do not do all of this on a single form. So my main form is navigation. To me having a form, subform, subsubform all editable is just ridiculous. If I want to add a new main item, I force the user to a pop up form to add a new main item. To add a sub item I force the user to a new pop up form for the sub item. Then maybe they can add subsub items. To me your form is way to complicated. You can navigate, add sub items, and add sub sub items. I personally would never design that way.

 

[oxicottin]

I got it all to work but one last thing is need to find out if there is a record in my Main forms[frmShiftDay] subforms[frmShiftMachinesSubform] subform[frmMachineOutputSubform] and I can’t get it to reference at all. Thanks!


ElseIf Me!frmMachineOutputSubform.Form!frmMachineOutputSubform.Recordset.RecordCount = 0 Then
MsgBox "Product Name is missing", vbCritical + vbOKOnly, "Required Data!"
Me.frmShiftMachinesSubform.Form.frmMachineOutputSubform.SetFocus
Exit Sub


Thanks,
SoggyCashew.....

 

[MajP]

To reference a subform you use the "Form" property of a subform control not the name of the form.

To get a subform from a main form class
me.YourSubformcontrolName.Form (where "YourSubFormControlName" is the name of your subform control)
to get a control on a subform
me.YourSubformControlName.Form.YourControlName
To get a sub sub form
me.YourSubFormControlName.Form.YourSubSubFormControlName.Form
And a control on the sub sub form.
me.YourSubFormControlName.Form.YourSubSubFormControlName.Form.Control

 

[oxicottin]

Thank you MajP... Why is there a period sometimes and sometimes there are ! instead of the period and sometimes there are []. I think the brackets are if your control or form has a space but not sure what the diference of period and question mark are. Thanks!

Thanks,
SoggyCashew.....

 

[PHV]

Do a search for access dot vs bang

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Brackets are required when a word has spaces in it or the name is a reserved word that might confuse vba/sql.
Me.SomeControlName
or
Me.[Some Control Name]
or since Date is a vba function it might be reserved so
Me.[Date]

Bottom line do not use spaces or special characters in your names. Use and underline or camelback
Some_Control_Name
Do not use names that could be confusing that could be a name of a vba function, property, object or words used a part of SQL. Example do not call a field or table something like "select" or "where" or "from".

In vba a there are two types of notation for referencing items in a collection: Bang or Dot. Most objects in vba that end in "s" are collections. Forms, Controls, Fields, ...
So to reference an form object which is a member of the forms collection
Forms("YourFormName")
Forms!YourFormName
or
Forms![Your Form Name]

If it is a property or method you cannot use Dot

Me.SomeControl.text
not
Me.SomeControl!text
although you can mix it
Me!SomeControl.text

Now when you add a control to a form or report it adds it to the form/report controls collection but also each one is added as a property so you can do this
Me.SomeControl which is a direct reference to the property
or
Me.Controls("SomeControl") which is a reference to the same control but getting the reference from the controls collection.
or you can use bang

Dot notation can be longer, but I always use dot because of intellisense
If you type
me.
Intellisense will pop up with all available properties. If you type something wrong then it will show you. Bang does not.

 

[oxicottin]

Thank you very myuch MaJp I got everything working correctly now.... Your detailed explamation was awsome thank you!

Thanks,
SoggyCashew.....

 

[PHV]

If it is a property or method you cannot use Dot
Really ?
I thought you MUST use dot and NOT bang ...

 

[MajP]

If it is a property or method you cannot use Dot
Really ?

Sorry for the typo. Should read "If it is a property or method you cannot use Bang, you must use dot." The examples following the original statement are correct.

Me.SomeControl.text
not
Me.SomeControl!tex