Warning Msg box for missing data on a form.

[vad60]

I would like to know how to display a msg box for missing or duplicate information when entering data into a form. My database keeps track of call accounting codes and the code is the primary key index field. I use macro's for all of my automated tasks such as finding codes, filtering for available codes and such. What I want to do is to display a simple message when adding a new one to the database if they forgot to enter the code and if they did enter a code if they entered a duplicate. The default error meesage tells them there is a problem but they won't know how to fix it. I know I should use the Before Update and After Update but I am not good at writing the vb code. I found some code in one of the the threads and copied it and changed the field names but it failed. I need a simple message like "You did not enter the Fac code" and "The Fac code you entered is already in the database please check your information."
The field name of my primary key is "Fac"
Thanks in advance for anyone that could help me with this.

 

[gromit]

Hi -

Take a look at this post and see if it helps. If you still have questions, please post back...

http://www.access-programmers.co.uk/forums/showthread.php?t=99765

hth,

- g

 

[vad60]

Thanks that worked! This is the code I used for leaving the Fac field blank.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.FAC) Then
MsgBox "You must enter a Fac code"
Cancel = True
End If
End Sub

Just 2 more questions
Can I make the box bigger and make it beep?

The other thing I need is to put up another message box if they enter a duplicate code. I thinking it also needs to be Before Update and added to the code above on an "If" statement to check the fac field is not null, correct.

Again thanks,

 

[gromit]

Good work!

You can adjust the size of the field with the Height and Width properties. The units are a little complicated, but you can just experiment to see what works.

E.g.
Me.FAC.Height = 25
Me.FAC.Width = 175

Don't forget to set the height and width back to the correct values (probably in AfterUpdate for the field, and maybe the OnLoad for the form).

You can also use heighlights, such as setting the background color
Me.FAC.BackColor = 16777215 ' Normal background
Me.FAC.BackColor = 8421631 ' Light red background

Just add a Beep statement for noise...
MsgBox "You must enter a Fac code"
Beep

As for the duplicate code, what are you checking against for duplicates? If you need to check duplicates against all the records, you are much better off by going into the table properties and setting the Indexed value for the field to "Yes (No duplicates)" This will let Access do the work for you.

- g

 

[vad60]

The Fac field is my primary key and is set for No Duplicates. If you enter a code that is already in the table the message that appears is not clear to the users. "The changes you entered were not saved because they would create duplicate values in the primary key field ...
I want a simple message to pop up like the one in my original post. I need this in case the user doesn't check to see if the code is already in the table. I have a button at the top of my form that runs a macro to select the Fac field and bring up the find dialog box. My hope is that the user will use this prior to adding a new Fac code to see if it is in the database. If they don't they will get the error message.

(I put Beep below the MsgBox line but it didn't work. Plus I think I will just leave the box size alone.)

 

[gromit]

This can be done, but you are starting to get into another layer of complexity.

You can check for duplicates with the BeforeUpdate event for the field.

Something like...

    Dim varData As Variant
    
    varData = DLookup(ItemNum, "tblItems", "[ItemNum] = " & ItemNum)
    If Not (IsNull(varData)) Then
        MsgBox "Duplicate value - please choose another"
        Cancel = True
    End If

However, you can get in a little trouble with this. E.g. suppose the user is editing the an existing record and starts to change the field, but then changes their mind and tries to set it back to what it was. The code will look at the value (set back to the original), see that it already exists in the database (albeit for that same record) and generate an error (which will probably confuse the user even more).

One issue here is using FAC as your primary key. It is strongly recommended that you use a numeric, autonumber field for a Primary Key. The general rule is that the primary key should have no other information associated with it, other than just being a unique record identifier (and this is a good example of why).

[Beep should work, make sure that you have the speaker turned on. I agree that leaving the box size alone is probably a good idea. Highlighting the field with color or some other approach is usually more than enough.]

- g

 

[vad60]

So it is best that I create a new field lets say called Index and make this my primary key and set it for auto number. Remove Fac as primary key. I can then set the Fac field as No Duplictes then I would be able to do what I want to do easier than if were the primary key. Am correct in saying that?
If so how would I code it to display the msg box.

Again, thanks for your quick responses and much needed help. I learn something everyday using Access. I will look into a book for VB. I want to be able to code more in VBA. Any suggestions?

I put the highlight statement in, it worked. Then I added one in the After Update to change it back to white.

 

[vad60]

My volume is up and sound is working on my laptop. Only Access won't beep. I created a test macro to bring up a msgbox with beep. When I run it, there is no beep from access.

 

[gromit]

First of all, creating a separate, distinct primary key is good general practice. If I have a table of books, I typically call the primary key (PK) BookID. If it is a table of employees, EmployeeID. This also helps when you have to join tables with related information.

Secondly, if you have a primary key that is separate from Fac, you can then use the primary key in the BeforeUpdate property to decide if you have a duplicate with a different record, or are just comparing the same record.

E.g. Assuming a table called "tblItems" with PK of "ItemID" and a second field "ItemNum" where we want to check for duplicates:

Private Sub ItemNum_BeforeUpdate(Cancel As Integer)
    Dim varData As Variant
      
    varData = DLookup(ItemID, "tblItems", "[ItemNum] = " & ItemNum & " AND [ItemID] <> " & Me.ItemID)
    If Not (IsNull(varData)) Then
            MsgBox "Duplicate value - please choose another"
            Cancel = True
    End If
    
    
End Sub

Notice how the DLOOKUP function can now distinguish whether it is looking at the same record or not.

- g

 

[vad60]

Thanks again, almost there. I entered the code word for word except for names. My table is named: tbl_Fac_Codes, My PK is named: FacID, and my data field is named: FAC.


Private Sub Fac_BeforeUpdate(Cancel As Integer)
Dim varData As Variant

varData = DLookup(FacID, "tbl_fac_codes", "[Fac] = " & FAC & " AND [FacID] <> " & Me.FacID)
If Not (IsNull(varData)) Then
MsgBox "Duplicate Fac Code - Please enter a unique Fac code"
Cancel = True

End If
End Sub

When I try this code I get a compile error saying the Method or data member not found. The first line is then highlighted in yellow. I guess that just shows which code in the module has the error. Then the word FAC is highlighted that is between the & &.

 

[gromit]

Check to see that you have a control for the FacID on the form.

Me.FacID is referring to the form and needs to have a corresponding item.

 

[vad60]

Got it, it works now. Thanks!!
What happened was when I created the field FacID in my table I made a typo and named it FaxID. I didn't notice this until I added the control to my form. I went back to my table and corrected the field, I checked my query that is the control for the form and it was OK and on the form. There was one place that I didn't check and that was in the properties of FacID under the other tab. The name was still set as FaxID. I corrected it there and then tried it and it failed but with a different error this time. One that I know which was a run time error data type mismatch. The light went off and I figured the code is refering to numbers, I have my FAC field set to text. I did it that way becuase I have codes that start with 0, not many though. So I changed the field to Number and it worked. I know if I am going to use VBA that has to do with numbers I have to get out of the habit of setting number field as TEXT. I understand the importance of it now.
Thanks for your time and your expert help. Now if I could just get Access to Beep when I tell it to I will be complete.

Thanks again!!!
VAD

 

[gromit]

Congratulations, sounds like you are making real progress.

Yes, the example did assume that field (FAC) was a number, not text. Sorry for the confusion. You can in fact use a text field, but have to add additional quote marks so that Access recognizes the comparison as text.

E.g. instead of
If (intNumberValue = 10)

You would have
If (strTextValue = "10")

When you build up a string value like we used in DLOOKUP you have to add additonal quotes so that Access embeds them in the string.

varData = DLookup([FieldName], "tblData", "[strField] = """ & strValue & """ )

Can't figure out why the BEEP doesn't work for you. That should be sufficient to make the computer beep.

- g

 

[vad60]

Thanks for that last bit of info. Sorry I didn't check back earlier but have been really busy at work and haven't had a chance. I will try your last suggestion with a backup copy of my db. I need to have the FAC field as text because I could and do have values that start with 0. But if there is a way to have values in a number field that start with 0 I could just leave it as is. Do you know of a way to have this requiremnet of a number field? The value could have one 0 or two, example: 003031, 022001.