automatically filling in field if left null

[laurat]

Is there any way to automatically fill in a field on a form if it is left empty when the form is closed. I tried putting some code in the Before_Update() event of the form but that did not work. Here is what I put:
If IsNull(Part#) Then
Part#=Void
End If

Any suggestions?

 

[laurat]

A little more info:

Part# is a required field. The problem I am having is that users will leave a required field like Part# Null and then close the form. It does give a warning that the data will not be saved. Well then other users will go looking for this record but it does not exist in the table because one of the required fields was left null.

 

[wjoc1]

How exactly are they able to close the form if it is a required field. If I try this and set a required property to yes for a field in a table then I am unable to save it, I simply get an error when I close the form?

I don't know much about VB programming but could you not place a default value in the field such as "Void" and then if the user didn't write over it with other information you'd still have some value in there.

Apologies if this isn't much help! Just curious

 

[laurat]

When a required field is left empty and the user closes it there is an error message that says that field is required. However, after the user hit OK another message comes up and tells the user that the info will not be saved and asks if they want to close anyways. If the user closes the record does not appear in the table. This user knows that the record was not saved but another user may see the record as missing and not know why it is in the table. There is physical paperwork that goes along with the database. We are running into problems where there is a paper for a certain record but no entry in the database, all because a required field was left blank.

I have thought of just putting a default value in the form, however, my supervisor does not want that because then users will not fill in all the necessary data because there is already something in there. That is why I only want something to fill in if the field happened to be left blank.

 

[Drevlin]

Try to change the textbox control's name to something other then Part# (i.e. txtPart). The problem seems to be with the # sign. Using it at the end of a word in VBA is the same as declaring a variable type double.
In this case VBA is reading Part# as if you had type:

Dim Part as Double

Try to avoid using such characters in your naming conventions (though feel free to use "Dim Part#" if you want a variable called Part to be double. Then you don't have to type "as Double") The following is a list of the other declaration variables:

integer - %
long - &
single - !
currency - @
string - $

Anyway the code you have should work in Form_BeforeUpdate() if you make this change.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(txtPart) Then
txtPart = "Void"
End If
End Sub

oh yeah, and because your passing it as a string Void needs to be in quotes.

Peace

 

[laurat]

That field name Part # (This db was created over a year ago, I now know better when creating field names) is in about 15 different queries, 15 reports and 6 forms, and also in some code. Is there an easy way to change the name or does it have to be done on each thing separatly?

 

[Drevlin]

I wouldn't worry about changing the field name or any of the other controls until it becomes necessary, as in the case for this particular control. I go with the whole: "If it works. Don't fix it" mentality.

As for how you would go about doing that if it did become necessary...? I'd like to hear what people have to say to that one m'self. To my knowledge there's really no quick fix. But my knowledge, I'm quite sure, is limited.

 

[laurat]

???

Did you say though that the only way I would be able to use Part# in my VBA code was if I changed the name and took out the #? So then I would have to change the name everywhere that it is used in order to put a check on that field

 

[Drevlin]

Well, actually you can use Part# when referring to a table field, because of the fact that you can put it in braces to let VBA know it's a field name.

[MyTable]![Part#] should be fine to use.
I'm saying that it's not necessarily the best choice.

As in this situation, you really aren't dealing with the Field name in your code. You're dealing with a control that is referencing what is in the field. Therefore you don't have to change the field name itself, only make sure that the names of your objects don't end in #.

*is waiting for Pat to interfere and tell him he has no idea what the hell he's talking about*

I apologize for not making this as clear to begin with.

 

[Drevlin]

*is psychic*

Thanks Pat.
Of course I've just killed my credibility.

Though, I've never actually tried to use brackets around control names, so I guess I didn't think that was an option. Though it makes sense.

 

[laurat]

As you can tell, I am a beginner at VBA code so I appreciate all the help you have given me. I do now know however, to choose the names more wisely.

I have a question. I tried to use code to check if the part # was null and I did use brackets:

If IsNull (Me.[Part_#])Then
MsgBox "Part # cannot contain a null value."
DoCmd.GoToControl "Part #"
Cancel=True
End if

However it does not like that first line. Do you see what is wrong with my code? Thanks again.

Laura

 

[Rich]

Rename the control to something else, say PartNum then it's
If IsNull(Me.PartNum) Then
Cancel = True
etc

 

[Drevlin]

Also be careful when writing your code [Part_#] is not the same as [Part #] and in your original post you had it as Part#. Use what VBA pops up for you when you type "Me.P" (vba should give you the option to pick the control name). Then after its been filled in add the brackets.

But I concur with Rich. Just change the name of the control and you won't have to worry about the brackets. =)