Required fields in Form and its Subforms 1

[mrfilez]

Access 97
I have a form with 3 subforms. I want all of the fields in the form(frmName), one field in the 1st subfrm(subfrmSeat), both fields in 2nsubform(subfrmComp), and 5 fields in 3rd subform(subfrmProb) to be required. This form is a data entry form. I have a button that will add the record and move to a new record.

My problem is two fold I think. First off two of the required fields are combo boxes(frmName.cboTitle and subfrmProb.cboStat). Second is the subforms. I am using "Is Not Null" in the Validation Rule to make these fields required. This is the only way I could come up with, using my very limited knowledge of Acces and VB. I know there is a way using VB, that would get me much more closer to what I actually want. But I lack the knowledge to produce this code myself. Surprisingly, I am not asking someone to give me the code. I would really like to know if what I am doing so far is a valid way of accomplishing my goal.
Of course I am always open to suggestions.

Thanks,
Don

 

[LonnieJohnson]

The simplest way is to set the Required property to Yes for the tables behind these forms.

If you want to go the fancier VB route, you can place code in the form's BeforeUpdate event that checks the fields:

If IsNull(Field1) Then
Msgbox Field1 & " is required!"
Field1.Setfocus
Exit Sub
End If

If IsNull(Field2) Then
Msgbox Field2 & " is required!"
Field2.Setfocus
Exit Sub
End If

...and so on. This will send an error message to the user for each field that is empty. Let me know if this works for you. If you need something more sophisticated, let me know. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[mrfilez]

Thanks!! That works. I know that this is probably going a little over board, but I like to swim.
the line:
txtFirstName.SetFocus
Is it supposed to put the cursor into the txtFirstName field if it contains a Null Value?

Also is there a way to change the message that pops up to let the user know that they must enter a value in the field?

I like to seem kinda nice to the users, makes my bosses happy. If I could replace the message with a nice, friendly, warm, and fuzzy message it would be wonderful.
Thank you for your time and tremendous help!!

Thanks,
Don

 

[LonnieJohnson]

I assume you are using the code.

Yes, the .setfocus command puts the curser at the field that is named before the dot.

Yes, you can change the message. Every thing that is between the "quotes" can be changed.

Actually I didn't give you the complete code for the message. The part that that says Field1 should be Field1.Name and so on.

ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[mrfilez]

Yes, I am sorry, I am using the code. I now have txtFirstName and txtLastName to where you get a message if you do not enter something into the fields.
I then have cboTitle and then txtReportsTo. These have the same code, but do not give an error if they are blank. I am not sure where the problem lies here. Is it the cboTitle? Can you not use the same method for a combo box as for a text field?

Also, the cursor never moves from the last field. It tells me that txtFirstName can not be null, but does not move the cursor there. I am tinkering around with it and trying different things. I do not have a descent VB book, all I have is a Programmer's Reference VB6 book. If I was kind of familiar with VB it would be a great book, but it is hard to find something when you do not know what you are looking for.
I do not have any training in Access or VB, nor do I have a way to get trained. I can not afford it and the department I am in will not pay for it. So I do the best I can. I am sorry to be such a pain, and would understand completely if you are frustrated with me or just do not want to bother any with this any more.

Thanks,
Don

 

[LonnieJohnson]

For the first line of your combo boxes use this.

If Nz(Combo0) = "" Then

It appears that the combo boxes treat nulls a little differently.

I understand your situation. Four years ago I knew nothing of VB or Access programming. I was a clerk at our Power & Light company that did data entry and assisted the programmer with small data matters. The programmer disappeared one day and we never heard from them again. I was slammed into that position. I finally got some formal training about a year later. And it wasn't much.

Hang in there. You will learn something new everyday that you program.

Access has a great tutorial. It is very descriptive and has some pretty good examples. Your doing good to go to the forums. If you don't get an answer, rephrase your question and ask it again.

Let me know if you need more.
ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[mrfilez]

Well, its definitely nice to know that Im not the only person put in this situation. I came into this job a little over a month ago and it is a nightmare. There is no documentation on how anything is done. No inventory list, no way to keep up with help desk requests. There was a stock room full of equipment that did not work. No one had a clue as to whether or not it was under warranty, most of it has been sitting there for over a year. I had to inventory it all and then jump through hoops to find out if it was under warranty. Most of the equipment warranties ran out last week. I had to have it all replaced the week before. I have not even thought about doing the 150+ position on the floor yet. I am doing this DB to keep up with HelpDesk calls and make my life much, much easier. So here I am.

I will try the suggestion with the combo box Thursday. I have meetings all day Wednesday. I really appreciate your help!!

Thanks,
Don

 

[mrfilez]

Well, the "If Nz(Combo0) = "" Then" worked. The combo box now works. I still have a problem with the ReportTo field not working. Does this have something to do with it coming after the combo box?
This is what I have for the required field coding so far.
Private Sub txtFirstName_BeforeUpdate(Cancel As Integer)

'Checks to see if Information has been input into field.
'This field is a required field.

If IsNull(txtFirstName) Then
MsgBox txtFirstName.Name & " is required!"
txtFirstName.SetFocus
Exit Sub
End If

End Sub

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)

'Checks to see if Information has been input into field.
'This field is a required field.

If IsNull(txtLastName) Then
MsgBox txtLastName.Name & " is required!"
txtLastName.SetFocus
Exit Sub
End If

End Sub

Private Sub txtReportsTo_BeforeUpdate(Cancel As Integer)

'Checks to see if Information has been input into field.
'This field is a required field.

If IsNull(txtReportsTo) Then
MsgBox txtReportsTo.Name & " is required!"
txtReportsTo.SetFocus
Exit Sub
End If


End Sub

Private Sub cboTitle_BeforeUpdate(Cancel As Integer)

'Checks to see if Information has been input into field.
'This field is a required field.

If Nz(cboTitle) = "" Then

MsgBox cboTitle.Name & " is required!"
cboTitle.SetFocus
Exit Sub
End If


End Sub

Thanks,
Don Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome.fedex.com
mrfilez@midsouth.rr.com