Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


The code below works if the Dept Number is new, but if it exists, instead of the Duplicate message, it defaults to 'Run Time Error 3022. Please Help.

Posted on 2005-04-26
Medium Priority
Last Modified: 2010-04-17
Private Sub cmdSaveForm_Click()

If frmAddDept.txtDeptNum = "" Then
        Msg = "The Department Number Must Be Entered! "
        MsgResult = DoMsgBox(cur_frm_obj, Msg, vbOKOnly + vbSystemModal, "Required Data")
        Exit Sub
End If

If frmAddDept.txtDeptDesc = "" Then
        Msg = "The Department Description Must Be Entered! "
        MsgResult = DoMsgBox(cur_frm_obj, Msg, vbOKOnly + vbSystemModal, "Required Data")
        Exit Sub
End If

sqldeptnum = "'" & txtDeptNum.Text & "'"
deptnum1 = "'" & Data1.Recordset.Fields("DeptNum") & "'"

SQLQuery = "SELECT deptnum FROM tblDepartments WHERE (((tblDepartments.deptnum) Like txtdeptnum.text order by deptnum"
Data1.RecordSource = "tblDepartments"

        Data1.Recordset.Fields("DeptNum") = frmAddDept.txtDeptNum.Text
        Data1.Recordset.Fields("DeptDesc") = frmAddDept.txtDeptDesc.Text

If Data1.Recordset.Fields("DeptNum") = sqldeptnum Then

        Msg = "The Department Number Already Exists! "
        MsgResult = DoMsgBox(cur_frm_obj, Msg, vbOKOnly + vbSystemModal, "Duplicate Data")
        Exit Sub
        txtDeptNum.Text = ""
        txtDeptDesc.Text = ""
        Exit Sub
    End If
End Sub
Question by:Fadennis
LVL 22

Accepted Solution

cookre earned 400 total points
ID: 13866413
If Data1.Recordset.Fields("DeptNum") = sqldeptnum Then

You originaly create sqldeptnum with enclosing quotes - I don't think the value extracted from the recordset will have those quotes.

Assisted Solution

astrohelp earned 400 total points
ID: 13866475
Msg = "The Department Number Already Exists! "
should be
 Msgbox = "The Department Number Already Exists! "
LVL 23

Assisted Solution

gecko_au2003 earned 400 total points
ID: 13866516
Just a thought here, but wouldnt it be easier and clearer to use select case instead of all the If ... else statements ?

Take a look here :

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 13866805
that is a very good point gecko

Assisted Solution

doobdave earned 400 total points
ID: 13867758
Hi Fadennis.

Firstly, could you please give an indication as to what the objects are within this code?

ie. Data1 is a RecordSet?? or what?

This would give us all a clearer understanding of what the code is trying to do.

Also, it might be an idea to give a general explanation of what you're trying to achieve with the code, rather than just posting a block of code.

Secondly, try adding an error handler to your procedure, as follows:

Private Sub cmdSaveForm_Click()

On Error goto ErrHand

' Your code goes here
' .....
' .....



End Sub

When you run this and the error happens, the debugger will halt on the 'Stop' statement. You can then use the immediate window to examine the contents of your objects/variables to see if they are as you expect them to be.
Also check the err object (eg. err.message).

Also, try stepping through the code line by line to determine the exact place where the error is occurring, this will give an indication as to where the problem lies.

Thirdly, I disagree with the comment about using a Select Case construct in your scenario, as there is only ONE else statement.

Please give more info and I'll be happy to provide what help I can.

Assisted Solution

hkang042997 earned 400 total points
ID: 13868658
Several problems:

- You should not assign Msgbox = "The Department Number Already Exists! ", because MsgBox is a VB function.

- The SQLQuery variable is not used in this Sub, but if you plan to use it elsewhere, you need to change the SQL string if you want to use the value of txtDeptNum.text in your query statement:

SQLQuery = "SELECT deptnum FROM tblDepartments WHERE (((tblDepartments.deptnum) LIKE '" & txtDeptNum.text & "' ORDER BY deptnum"

- Unless you are saving the single quotes as part of the data in the DeptNum field, you should not surround the value of  txtDeptNum.text with single quotes.  The single quote is needed in a query WHERE statement but is not needed in a VB If...Then statement.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Introduction to Processes
Loops Section Overview

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question