Data Please help!!! Need answer ASAP. Entry required for Subform based on button

[LaRockera02]

I have a main form (CallDatabase) and a subform(NOTES). I have a button that appends a new record everytime you click on it. I would like to make it require for data to be inserted into the Notes subform, specifically the Note field. I want to make sure the user won't be able to move on to a new record on the main form unless there's a record in the subform.

thanks,

 

[CBrighton]

If forms!CallDatabase!SubformObjectName.Form!ControlName = null then
'code if field is null
Else
'code if field not null
End If


Note that it's the name of the subform control, not the form which it contains.

 

[LaRockera02]

He Cbrighton thanks for replying. I'm sort of a novice when it comes to this.

I'm not entirely sure where I should put that code. Should I put it on the Button itself, or the form? and on which one? BeforeUpdate?OnClick?

thanks

 

[CBrighton]

I would imagine it should go in the code behind the command button, before it saves the record & moves to the next.

But you haven't given loads of info, that's based on you saying:

I have a button that appends a new record everytime you click on it.

 

[LaRockera02]

ok sorry. the command botton I have is pretty basic. it's just set up to append a record on the main form. What I'm looking for is for this button to check the fields in the subform and make sure data is entered in them. If not, I don't want the user to go on to a new record. I want it to be mandatory for them to enter a note on the subform.

Let me know if that made it somewhat clearer of if you need more info,

 

[mdlueck]

ok sorry. the command botton I have is pretty basic. it's just set up to append a record on the main form. What I'm looking for is for this button to check the fields in the subform and make sure data is entered in them. If not, I don't want the user to go on to a new record. I want it to be mandatory for them to enter a note on the subform.

In that case on the button click event, validate the values in the subform fields. If all are acceptable, proceed with inserting / committing a new record.

If one of the fields is found unacceptable, then exit the click event and leave the user to correct the problem(s).

For this sort of validation logic, I flag the background of the invalid field(s) RED color to make it obvious why their commit was not allowed. That coloring logic, be sure to reset the field color if on subsequent commit attempts the field is then found to have a valid value as leaving it red would be confusing.

 

[LaRockera02]

Hey, I don't think I'm understading what you want me to do. How do I validate the subfrom from the button click event? What you have replied sounds like what I'm looking for but I'm not sure on how to do it. Please elaborate

thanks

 

[mdlueck]

Please elaborate

First jot down a list of fields you are interested in.

Using VBA code on the button click event, perform the various datatype tests you need on each field you are interested in.

You may color code the fields at the same time, once you had figured out the pass/fail.

Keep a flag variable, initialized to 0 / False, and if you find a bad field, set it to 1 / True.

At the end of checking the fields, check the flag. If it is still 0, commit the changes. If it is 1, then exit the click event WITHOUT committing.

 

[LaRockera02]

Hey thanks for the input but the main problem I don't know what VBA code I should insert. Unfortunately I'm not so tech savvy. Sorry for making this so difficult.

 

[mdlueck]

I don't know what VBA code I should insert.

Then I would suggest referring to websites, or get a good book on the topic.

For the project I am working on, I purchased a copy of "Access 2007 Bible" by Groh which I would recommend. I also picked up "The Missing Manual" about Access 2007 pub by O'Reily Press which is good for Access as a general topic but the later is a better VBA guide. It comes with a CD of examples and has quite good VBA examples to glean from.

 

[LaRockera02]

Mdlueck unfortunately I don't have the time to look that up. I thought it was going to be a pretty quick thing. I was just looking for the right code to do that. I was hoping someone would provide me with one.

thanks anyways

 

[vbaInet]

Show us the code behind the Click event of your button, tell us the name of the notes textbox. We will show you were the code will go.

We also need to know if the subform is a continuous form or datasheet. If it's Single Form then has it got the default Navigation buttons at the bottom?

NB: If the name of the Notes textbox is called Note (i.e. the same name as the field), change it to txtNote.

 

[LaRockera02]

Thanks vBaInet,

This is what I have behind the On Click event (button)

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click
DoCmd.GoToRecord , , acNewRec
Me.CS_Rep.SetFocus
Exit_Add_Record_Click:
Exit Sub
Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click
End Sub

I renamed the subform "txtNote" and the its required data field "Note", but I also made it into a datasheet.

Let me know what else you need.

thanks,
again

 

[vbaInet]

I renamed the subform "txtNote" and the its required data field "Note", but I also made it into a datasheet.

Is it the textbox you renamed or the subform? I'm hoping it's only the textbox you renamed.

Here's the code you will put in the Before Update event of the Notes textbox:

If Len(Nz(Me.txtNote, vbNullString)) = 0 Then
    Cancel = True
    Msgbox "Note cannot be empty"
End If

 

[LaRockera02]

Sorry I thought you were reffering to the the subform. I'm not sure what you mean by the textbox.

the code you just sent you want me to put it in the Note field or the subform??>

 

[vbaInet]

Sorry I thought you were reffering to the the subform. I'm not sure what you mean by the textbox.

The Notes textbox. The field is called Note, the textbox that is bound to Note should be called txtNote.

the code you just sent you want me to put it in the Note field or the subform??>

The sentence directly above the code clearly explains this.