By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,009 Members | 2,821 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,009 IT Pros & Developers. It's quick & easy.

Not getting value for second combo box "with" statement & Dcount help

P: n/a
Hi
I'm pretty new to Access here (using Access 2000), and appreciate the
help and instruction.

I gave myself 2.5 hours to research online and help and try to get this
one, and I am not getting it.

Simple database: I want to have a user enter Supply Orders (just for
tracking purposes) by Item. The user may also enter a new item - "new"
is a combination of Item, PartNumber and Vendor - they could have the
same item and partnumber purchased from a different source.

Item and Vendor are "self-updating" combo boxes - adds the item if it's
not in the list.

I am trying to take the user's input: Item, Vendor and PartNumber and
use Dcount to see if that combination is really NEW or already exists.

I can't get the Vendor to show in my code. I have had all sorts of msg
boxes and vendor is missing. I am using some "With" clauses I found on
this site to get the value from combo boxes as I couldn't get those
values before.

Here's the code:
Dim intcount As Integer
Dim tblSupplies As TableDef
Dim PartNumber As String
Dim frm As Form

Dim Vendor As Control
Dim Item As Control

Set Item = Me.cboItem
With Item
' Column(0) contains "PartID" ... which we don't need here.
Me![Item] = .Column(1)
End With
MsgBox Item & " is item here (yes)"

With Vendor
Me![Vendor] = .Column(1)
End With
MsgBox "is vendor here (no)" & Vendor

' Find out if this supply is already on tblSupplies
PartNumber = Me.PartNumber

' debug here
MsgBox " item: " & Item & " PN " & PartNumber & " Ven " & Vendor
intcount = DCount("[Item]", "tblSuppplies", "[Item] = " & Item And _
[PartNumber] = " & PartNumber And [Vendor] = " & Vendor)

' For debugging:
MsgBox intcount

If intcount <> 0 Then
Err.Description = _
"This Supply-Part Number - Vendor is already on file. Please check
your entry."
GoTo Err_cmdAddItem_Click
Else

DoCmd.GoToRecord , , acNewRec

End If

Nov 13 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
> intcount = DCount("[Item]", "tblSuppplies", "[Item] = " & Item And _
[PartNumber] = " & PartNumber And [Vendor] = " & Vendor)
You have the word AND outside the quotes, it should be inside them.

intcount = DCount("[Item]", "tblSuppplies", "[Item] = " & Item & " And " & _
"[PartNumber] = " & PartNumber & " And [Vendor] = " & Vendor)

The syntax you have also indicates that these values are a numeric data
type, if they are text a little modification will need to be made.

Another option would be to use a multifield index in the table and set it to
no duplicates. This will prevent any record where all three of these field
are identical from being added to the table. If you have some in there
already, you won't be able to set the index until you correct the current
data.

--
Wayne Morgan
MS Access MVP
"sara" <sa******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com... Hi
I'm pretty new to Access here (using Access 2000), and appreciate the
help and instruction.

I gave myself 2.5 hours to research online and help and try to get this
one, and I am not getting it.

Simple database: I want to have a user enter Supply Orders (just for
tracking purposes) by Item. The user may also enter a new item - "new"
is a combination of Item, PartNumber and Vendor - they could have the
same item and partnumber purchased from a different source.

Item and Vendor are "self-updating" combo boxes - adds the item if it's
not in the list.

I am trying to take the user's input: Item, Vendor and PartNumber and
use Dcount to see if that combination is really NEW or already exists.

I can't get the Vendor to show in my code. I have had all sorts of msg
boxes and vendor is missing. I am using some "With" clauses I found on
this site to get the value from combo boxes as I couldn't get those
values before.

Here's the code:
Dim intcount As Integer
Dim tblSupplies As TableDef
Dim PartNumber As String
Dim frm As Form

Dim Vendor As Control
Dim Item As Control

Set Item = Me.cboItem
With Item
' Column(0) contains "PartID" ... which we don't need here.
Me![Item] = .Column(1)
End With
MsgBox Item & " is item here (yes)"

With Vendor
Me![Vendor] = .Column(1)
End With
MsgBox "is vendor here (no)" & Vendor

' Find out if this supply is already on tblSupplies
PartNumber = Me.PartNumber

' debug here
MsgBox " item: " & Item & " PN " & PartNumber & " Ven " & Vendor
intcount = DCount("[Item]", "tblSuppplies", "[Item] = " & Item And _
[PartNumber] = " & PartNumber And [Vendor] = " & Vendor)

' For debugging:
MsgBox intcount

If intcount <> 0 Then
Err.Description = _
"This Supply-Part Number - Vendor is already on file. Please check
your entry."
GoTo Err_cmdAddItem_Click
Else

DoCmd.GoToRecord , , acNewRec

End If

Nov 13 '05 #2

P: n/a
Thnk you for fixing the DCount code, but I don't even get that far in
the code. I don't get the Vendor -
I had to change Item to (0) from (1), and I tried that with Vendor -
still no luck. I'm getting "Obejct Variable or With Block Variable not
set"

right before my MsgBox to see the vendor. ?Vendor is blank in the
immediate window.

I like the idea of the multiple field key, but a user group I have gone
to says that the "Supply ID" should be autonumber and the only key. I
don't know how to set a multi-field key if the Autonumber is the key
(and therefore always unique). I find the ID/Autonumber quite
limiting, but these people have much more experience than I, so I tried
it.

Your help, guidance and thoughts are very much appreciated.
Thanks

Nov 13 '05 #3

P: n/a
> Set Item = Me.cboItem
With Item

With Vendor
Me![Vendor] = .Column(1)
End With
You will notice that the error message is correct, you haven't Set vendor as
you did item. Usually, a With statement will be used to save typing when you
have multiple statements that refer to the same object. In this case, while
it will work, I don't see that it is saving you anything. Also, at the end
of the routine, don't forget to set your object variables to Nothing.

Example:
Set Vendor = Nothing
Set Item = Nothing

A multifield, unique index doesn't have to be the key field. You can set a
multifield index manually also. To do so, open the table in design view and
go to View|Indexes. Under Index Name, type the name you want to use for this
index. It must be a unique name, don't use a field name. Under Field Name,
enter the first of the three fields, set Sort Order as desired. In the next
two rows, leave the Index Name blank, but fill in the Field Name and Sort
Order for the other two fields. Now, go back to the first row and in the
bottom part of the window set Unique to Yes and close the window. An index
can contain up to ten fields.

--
Wayne Morgan
MS Access MVP
"sara" <sa******@yahoo.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com... Thnk you for fixing the DCount code, but I don't even get that far in
the code. I don't get the Vendor -
I had to change Item to (0) from (1), and I tried that with Vendor -
still no luck. I'm getting "Obejct Variable or With Block Variable not
set"

right before my MsgBox to see the vendor. ?Vendor is blank in the
immediate window.

I like the idea of the multiple field key, but a user group I have gone
to says that the "Supply ID" should be autonumber and the only key. I
don't know how to set a multi-field key if the Autonumber is the key
(and therefore always unique). I find the ID/Autonumber quite
limiting, but these people have much more experience than I, so I tried
it.

Your help, guidance and thoughts are very much appreciated.
Thanks

Nov 13 '05 #4

P: n/a
WOW!! I am going to try that. What error number am I looking for to
check and send the user a message? I found code on MS to get all error
messages in a table, but I can't find "record already exists" or
similar.

Also, I have moved on in the code (I realized after reading your "with"
information, that I had left out the Set Vendor statement). I am
getting a syntax error, "Comma missing" in the Dcount statement.
Now, it reads:
intcount = DCount("[Item]", "tblSuppplies", "[Item] = " & Item & _
" And [PartNumber] = " & PartNumber & " And [Vendor] = " &
Vendor)

And it looks ok in the error message; I don't know where the comma is
missing.

Thanks - I've learned a lot and even tho this is really simple, I will
be quite proud when it's done!
sara

Nov 13 '05 #5

P: n/a
I don't see a comma missing as long as it is on only 2 lines and the 3rd
line was caused by the newsreader wrapping the text. Something that may help
would be using Me.Item, Me.PartNumber, and Me.Vendor instead of just Item,
PartNumber, and Vendor for the values. It may be getting misread for some
reason.

On the multifield index, the duplicate value error will be trapped in the
form's Error event. It is a data error, not a code error. The error number
is 3022.

Example:
If DataErr = 3022 Then
MsgBox "You've entered a duplicate!"
Me.Undo 'or however you want to handle it
Response = acDataErrContinue
Exit Sub
End If

--
Wayne Morgan
MS Access MVP
"sara" <sa******@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
WOW!! I am going to try that. What error number am I looking for to
check and send the user a message? I found code on MS to get all error
messages in a table, but I can't find "record already exists" or
similar.

Also, I have moved on in the code (I realized after reading your "with"
information, that I had left out the Set Vendor statement). I am
getting a syntax error, "Comma missing" in the Dcount statement.
Now, it reads:
intcount = DCount("[Item]", "tblSuppplies", "[Item] = " & Item & _
" And [PartNumber] = " & PartNumber & " And [Vendor] = " &
Vendor)

And it looks ok in the error message; I don't know where the comma is
missing.

Thanks - I've learned a lot and even tho this is really simple, I will
be quite proud when it's done!
sara

Nov 13 '05 #6

P: n/a
Well, I've been working on this and have made some improvement, but
still not there.
My index works - thanks. I have tested it manually, and by entering dup
info on my form and choosing "design view" - I get the dup index
message.

My problem is code/error handling. I have been researcing fo 2.25
hours on how to do the "add new record" command, and error coding. My
code is below.

I used to get error 2105, now I'm getting 2489 (The object '|' isn't
open.@* The macro you are running (directly or indirectly) contains a
GoToRecord, RepaintObject, or SelectObject action, but the Object Name
argument names an object that is closed.
* The objectname argument for the GoToRecord, RepaintObject, or
SelectObject method names an object that is closed..)

I don't get the Dup Key entry, even tho I am intentionally adding a dup
key. The good news is that my "cancel add" function works great -
hundreds of times - perfectly.

Thanks!

Code:
' debug here
MsgBox " item: " & Item & " PN " & PartNumber & " Ven " & Vendor

DoCmd.GoToRecord , "TblSupplies", acNewRec ' have tried this as
DoCmd.GoToRecord,,acNewRec-err2105

MsgBox "added" ' I am not seeing this
Set Vendor = Nothing
Set Item = Nothing

Exit_cmdAddItem_Click:
Exit Sub

Err_cmdAddItem_Click:

'Save the Error Number
ErrNum = Err.Number
MsgBox ErrNum ' shows 2105 without table name in Add stmt; 2489
with name

If ErrNum = 3022 Then
strErrDescription = "You've entered a duplicate"
MsgBox ErrNum & " " & strErrDescription
Me.Undo 'or however you want to handle it
Response = acDataErrContinue
Exit Sub
End If

Resume Exit_cmdAddItem_Click

Nov 13 '05 #7

P: n/a
1) The If ErrNum = 3022 code needs to be in the form's Error event, not the
error handler of your code to add an item.

2) > DoCmd.GoToRecord , "TblSupplies", acNewRec
Is your form called TblSupplies? You are trying to move the form to a new
record, correct? Since you aren't specifying a data object (blank before the
first comma) then the command will assume the active data object. Try
filling in all 3 arguments, for a form the first one would be acDataForm.

DoCmd.GoToRecord acDataForm, "FormName", acNewRec

--
Wayne Morgan
MS Access MVP
"sara" <sa******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Well, I've been working on this and have made some improvement, but
still not there.
My index works - thanks. I have tested it manually, and by entering dup
info on my form and choosing "design view" - I get the dup index
message.

My problem is code/error handling. I have been researcing fo 2.25
hours on how to do the "add new record" command, and error coding. My
code is below.

I used to get error 2105, now I'm getting 2489 (The object '|' isn't
open.@* The macro you are running (directly or indirectly) contains a
GoToRecord, RepaintObject, or SelectObject action, but the Object Name
argument names an object that is closed.
* The objectname argument for the GoToRecord, RepaintObject, or
SelectObject method names an object that is closed..)

I don't get the Dup Key entry, even tho I am intentionally adding a dup
key. The good news is that my "cancel add" function works great -
hundreds of times - perfectly.

Thanks!

Code:
' debug here
MsgBox " item: " & Item & " PN " & PartNumber & " Ven " & Vendor

DoCmd.GoToRecord , "TblSupplies", acNewRec ' have tried this as
DoCmd.GoToRecord,,acNewRec-err2105

MsgBox "added" ' I am not seeing this
Set Vendor = Nothing
Set Item = Nothing

Exit_cmdAddItem_Click:
Exit Sub

Err_cmdAddItem_Click:

'Save the Error Number
ErrNum = Err.Number
MsgBox ErrNum ' shows 2105 without table name in Add stmt; 2489
with name

If ErrNum = 3022 Then
strErrDescription = "You've entered a duplicate"
MsgBox ErrNum & " " & strErrDescription
Me.Undo 'or however you want to handle it
Response = acDataErrContinue
Exit Sub
End If

Resume Exit_cmdAddItem_Click

Nov 13 '05 #8

P: n/a
1) I moved the code, thanks. I clearly didn't understand that - I'll
try to find more to read about where to put this error code.

2) No, I am trying to write the new record to the table called
tblSupplies (the fields are SupplyID (autonum), Item, Vendor, PartNum
and Category (all from the form, with Item, Vendor, Partnum having to
be unique). My form is called frmaddSupplies, based on tblSupplies.

I can't imagine what the command would be - and I can't find it on
search/help.

Thanks - I feel so close!
sara

Nov 13 '05 #9

P: n/a
To add a record to the table you have three options (more actually, but 3
basic ones).

1) You can go to a new record on the form, enter the data there, and when
you move away from that record, the form will save the data to the table.
This will work if the form is a bound form.

2) You can use DAO or ADO to open a Recordset Object, set the values of the
fields, and then do a Recordset.Update.

3) You can use an Append Query. You would provide the values as parameters
to the query then execute the query.

Telling the table to move to a new record would require that you have the
table open. It would then move to a new record just as if you had clicked
the new record button in the record selector. You would then be able to type
in your new record.

--
Wayne Morgan
MS Access MVP
"sara" <sa******@yahoo.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...
1) I moved the code, thanks. I clearly didn't understand that - I'll
try to find more to read about where to put this error code.

2) No, I am trying to write the new record to the table called
tblSupplies (the fields are SupplyID (autonum), Item, Vendor, PartNum
and Category (all from the form, with Item, Vendor, Partnum having to
be unique). My form is called frmaddSupplies, based on tblSupplies.

I can't imagine what the command would be - and I can't find it on
search/help.

Thanks - I feel so close!
sara

Nov 13 '05 #10

P: n/a
I can't believe I'm still struggling with something that all the books
and articles say should be so easy! I wish there was a class, and am
so appreciative of your help on this, Wayne.

Here goes - 3 problems:
1. Self-updating combo boxes no longer work. (I learned this in my
Access User Group) The "On Current" event doesn't seem to be firing and
my messagebox will not have a Vendor (spaces) in it.

2. I wrote an append query and it works. But I'm not getting to my
error code - the error is coming in the query and I click "yes" to the
responses and then the error number is 0. But when I go to change the
form to Design view, I get into the Error code, with error number of 0.

3. I never run my "Set Nothing" code. It did at one time, but I've
tried so many things to get the error working, it no longer works.

I still think it's me not understanding how to code errors - how to get
the error code to execute.
Code is below (again, but this time the WHOLE thing).
Thanks - I hope I'm not "over-staying my welcome" here. I am the only
person who does any programming, such as it is, in my office.

Option Compare Database
Option Explicit

Private Sub cboVendor_BeforeUpdate(Cancel As Integer)

Dim strVendor As String
strVendor = Me.cboVendor
If DCount("Vendor", "tblSupplies", "Vendor = '" & strVendor & "'")
= 0 Then
If MsgBox(strVendor & " is not currently in the Vendor list,
would you like to add it?", vbYesNo, "Add Vendor?") = vbNo Then
Cancel = True
Me.cboVendor.Dropdown
End If
End If

End Sub

Private Sub cboCategory_BeforeUpdate(Cancel As Integer)

Dim strCategory As String
strCategory = Me.cboCategory
If DCount("Category", "tblSupplies", "Category = '" & strCategory &
"'") = 0 Then
If MsgBox(strCategory & " is not currently in the Category
list, would you like to add it?", vbYesNo, "Add Category?") = vbNo Then
Cancel = True
Me.cboCategory.Dropdown
End If
End If

End Sub

Private Sub Form_Current()
Me.cboCategory.Requery
Me.cboVendor.Requery
Me.cboItem.Requery
End Sub

Private Sub cboItem_BeforeUpdate(Cancel As Integer)

Dim strItem As String
strItem = Me.cboItem
If DCount("Item", "tblSupplies", "Item = '" & strItem & "'") = 0
Then
If MsgBox(strItem & " is not currently in the Supply list,
would you like to add it?", vbYesNo, "Add Item?") = vbNo Then
Cancel = True
Me.cboItem.Dropdown
End If
End If

End Sub

Private Sub cmdAddItem_Click()

Dim tblSupplies As TableDef
Dim PartNumber As String
Dim frm As Form
Dim DataErr As Long
Dim Vendor As ComboBox
Dim Item As ComboBox
Dim StrErrDescription As String
Dim Response As String

Set Item = Me.cboItem
Set Vendor = Me.cboVendor
PartNumber = Me.PartNumber

' debug here
MsgBox " item: " & Item & " PN " & PartNumber & " Ven " & Vendor
' not getting Vendor

' Turn Warnings off to add the record
' DoCmd.SetWarnings False
DoCmd.OpenQuery "ApqAddSupply", , acReadOnly
' DoCmd.SetWarnings True

DataErr = Err.Number ' is 0
MsgBox DataErr & " before Added" ' Get "0 before Added" - how to
get 3022?
MsgBox "added"

Set Vendor = Nothing
Set Item = Nothing

Exit_cmdAddItem_Click:
Exit Sub

End Sub
Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Dim StrErrDescription As String

MsgBox "Enter Error"
DataErr = Err.Number
MsgBox DataErr
If DataErr = 3022 Then
StrErrDescription = "You've entered a duplicate"
MsgBox DataErr & " " & StrErrDescription
Me.Undo 'or however you want to handle it
Response = acDataErrContinue
Exit Sub
End If
End Sub

Nov 13 '05 #11

P: n/a
> ' debug here
MsgBox " item: " & Item & " PN " & PartNumber & " Ven " & Vendor
' not getting Vendor
You really don't need to set the object variables since you're just using
them one time any way. Instead, just replace them here with the actually
objects. Also, verify the spelling of the names of the combo boxes.

MsgBox " item: " & Me.cboItem & " PN " & Me.PartNumber & " Ven " &
Me.cboVendor

Also, from the debug window (Ctrl+G) try typing the following with the form
open and see what you get. Press Enter after each line

?Forms!frmFormName!cboItem
?Forms!frmFormName!PartNumber
?Forms!frmFormName!cboVendor
' Turn Warnings off to add the record
' DoCmd.SetWarnings False
DoCmd.OpenQuery "ApqAddSupply", , acReadOnly
' DoCmd.SetWarnings True
Instead of the above lines to run the append query, try the following line.
There is no need to set warning off/on with this. It will execute without
warning and will return an error if there is one.

CurrentDb.QueryDefs("ApqAddSupply").Execute dbFailOnError

To use this, you'll need a Reference set for DAO if you don't already have
one. In the code editor go to Tools|References and see if Microsoft DAO x.x
Object Library is checked. The checked items will be at the top. If it's
not, then it will be listed alphabetically below. Scroll down and check it
then click ok. Check the newest version listed if there is more than one.
--
Wayne Morgan
MS Access MVP
"sara" <sa******@yahoo.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...I can't believe I'm still struggling with something that all the books
and articles say should be so easy! I wish there was a class, and am
so appreciative of your help on this, Wayne.

Here goes - 3 problems:
1. Self-updating combo boxes no longer work. (I learned this in my
Access User Group) The "On Current" event doesn't seem to be firing and
my messagebox will not have a Vendor (spaces) in it.

2. I wrote an append query and it works. But I'm not getting to my
error code - the error is coming in the query and I click "yes" to the
responses and then the error number is 0. But when I go to change the
form to Design view, I get into the Error code, with error number of 0.

3. I never run my "Set Nothing" code. It did at one time, but I've
tried so many things to get the error working, it no longer works.

I still think it's me not understanding how to code errors - how to get
the error code to execute.
Code is below (again, but this time the WHOLE thing).
Thanks - I hope I'm not "over-staying my welcome" here. I am the only
person who does any programming, such as it is, in my office.

Option Compare Database
Option Explicit

Private Sub cboVendor_BeforeUpdate(Cancel As Integer)

Dim strVendor As String
strVendor = Me.cboVendor
If DCount("Vendor", "tblSupplies", "Vendor = '" & strVendor & "'")
= 0 Then
If MsgBox(strVendor & " is not currently in the Vendor list,
would you like to add it?", vbYesNo, "Add Vendor?") = vbNo Then
Cancel = True
Me.cboVendor.Dropdown
End If
End If

End Sub

Private Sub cboCategory_BeforeUpdate(Cancel As Integer)

Dim strCategory As String
strCategory = Me.cboCategory
If DCount("Category", "tblSupplies", "Category = '" & strCategory &
"'") = 0 Then
If MsgBox(strCategory & " is not currently in the Category
list, would you like to add it?", vbYesNo, "Add Category?") = vbNo Then
Cancel = True
Me.cboCategory.Dropdown
End If
End If

End Sub

Private Sub Form_Current()
Me.cboCategory.Requery
Me.cboVendor.Requery
Me.cboItem.Requery
End Sub

Private Sub cboItem_BeforeUpdate(Cancel As Integer)

Dim strItem As String
strItem = Me.cboItem
If DCount("Item", "tblSupplies", "Item = '" & strItem & "'") = 0
Then
If MsgBox(strItem & " is not currently in the Supply list,
would you like to add it?", vbYesNo, "Add Item?") = vbNo Then
Cancel = True
Me.cboItem.Dropdown
End If
End If

End Sub

Private Sub cmdAddItem_Click()

Dim tblSupplies As TableDef
Dim PartNumber As String
Dim frm As Form
Dim DataErr As Long
Dim Vendor As ComboBox
Dim Item As ComboBox
Dim StrErrDescription As String
Dim Response As String

Set Item = Me.cboItem
Set Vendor = Me.cboVendor
PartNumber = Me.PartNumber

' debug here
MsgBox " item: " & Item & " PN " & PartNumber & " Ven " & Vendor
' not getting Vendor

' Turn Warnings off to add the record
' DoCmd.SetWarnings False
DoCmd.OpenQuery "ApqAddSupply", , acReadOnly
' DoCmd.SetWarnings True

DataErr = Err.Number ' is 0
MsgBox DataErr & " before Added" ' Get "0 before Added" - how to
get 3022?
MsgBox "added"

Set Vendor = Nothing
Set Item = Nothing

Exit_cmdAddItem_Click:
Exit Sub

End Sub
Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Dim StrErrDescription As String

MsgBox "Enter Error"
DataErr = Err.Number
MsgBox DataErr
If DataErr = 3022 Then
StrErrDescription = "You've entered a duplicate"
MsgBox DataErr & " " & StrErrDescription
Me.Undo 'or however you want to handle it
Response = acDataErrContinue
Exit Sub
End If
End Sub

Nov 13 '05 #12

P: n/a
It's still not getting to the error code! I looked up (and solved)
getting the parameters for the query. I'm fine to add a new record, but
the error fails, now the 3022, but does not get to my error code! What
am I missing?

(I'm anxious as I leave on vacation tomorrow and I am afraid that if
this isn't done I'll have no idea where I am with the problems when I
come back)

new code: (simply replaced as you suggested)
Set qApQuery = CurrentDb.QueryDefs("ApqAddSupply")

qApQuery.Parameters("Forms!frmAddSupply!cboItem") _
= Forms![frmAddSupply]![cboItem]
qApQuery.Parameters("Forms!frmAddSupply!cboVendor" ) _
= Forms![frmAddSupply]![cboVendor]
qApQuery.Parameters("Forms!frmAddSupply!cboCategor y") _
= Forms![frmAddSupply]![cboCategory]
qApQuery.Parameters("Forms!frmAddSupply!txtPartNum ber") _
= Forms![frmAddSupply]![txtPartNumber]
qApQuery.Execute dbFailOnError ' I don't even get the msgbox
right after this statement; get Access Error

Once again, thanks. (I would take a class if I could find one in the
Boston area!)

Nov 13 '05 #13

P: n/a
Will you send it to me with a note about where to look? Zip the file first
please so the hotmail doesn't block it.

co*****@hotmail.com

--
Wayne Morgan
MS Access MVP
"sara" <sa******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
It's still not getting to the error code! I looked up (and solved)
getting the parameters for the query. I'm fine to add a new record, but
the error fails, now the 3022, but does not get to my error code! What
am I missing?

(I'm anxious as I leave on vacation tomorrow and I am afraid that if
this isn't done I'll have no idea where I am with the problems when I
come back)

new code: (simply replaced as you suggested)
Set qApQuery = CurrentDb.QueryDefs("ApqAddSupply")

qApQuery.Parameters("Forms!frmAddSupply!cboItem") _
= Forms![frmAddSupply]![cboItem]
qApQuery.Parameters("Forms!frmAddSupply!cboVendor" ) _
= Forms![frmAddSupply]![cboVendor]
qApQuery.Parameters("Forms!frmAddSupply!cboCategor y") _
= Forms![frmAddSupply]![cboCategory]
qApQuery.Parameters("Forms!frmAddSupply!txtPartNum ber") _
= Forms![frmAddSupply]![txtPartNumber]
qApQuery.Execute dbFailOnError ' I don't even get the msgbox
right after this statement; get Access Error

Once again, thanks. (I would take a class if I could find one in the
Boston area!)

Nov 13 '05 #14

P: n/a
I don't know if you sent it or not, but I haven't received the file.

--
Wayne Morgan
MS Access MVP
Nov 13 '05 #15

P: n/a
I am sending now - I was on vacation and am just now back.
sara

Nov 13 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.