473,287 Members | 1,613 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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
15 2929
> 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
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
> 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
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
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
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
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
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
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
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
> ' 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
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
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
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
I am sending now - I was on vacation and am just now back.
sara

Nov 13 '05 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Kevin Goodsell | last post by:
The return value of sscanf should be "the number of input items assigned" (unless an input failure occurs before any conversion). Are items assigned due to a "%n" directive included in the returned...
2
by: John Smith | last post by:
Howdy, Is it okay to use a literal value with the IN clause. E.g. SELECT somefield, anotherfield ..... WHERE ...etc. AND 1234 IN (SELECT userid FROM tblUsers) I was told it wasn't valid,...
14
by: Arne | last post by:
A lot of Firefox users I know, says they have problems with validation where the ampersand sign has to be written as &amp; to be valid. I don't have Firefox my self and don't wont to install it only...
1
by: Max | last post by:
hi i have stored my connction string in web.config as we do usually. code of web.confing is given below. check the password field contain "&" character. now when i run my application it gives...
8
by: Rasmus Kromann-Larsen | last post by:
The With Conundrum I'm currently writing a master thesis on (preparations for) static analysis of JavaScript, and after investigating the with statement, it only even more evident to me that the...
1
AllusiveKitten
by: AllusiveKitten | last post by:
Hi Is there a possible way to insert a record that includes an Apostrophe? At the moment it is splitting the field into two as it see's the apostrophe as a separator. Thank you for your...
1
by: Aprile | last post by:
Hi, everyone. I'm fairly new to Access and I need help with some code for DCount. I have a form named FormAnalystsEval with text boxes named AJDNumPending, TxtBeginDate and TxtEndDate. The user...
2
by: mk | last post by:
Hello, I'm trying to learn how with statement can be used to avoid writing: prepare() try: something_that_can_raise_SomeException() except SomeException, err: deal_with_SomeException...
2
by: JFKJr | last post by:
Hello everyone! I am a new beginner to ASP.NET, I have created two pages page1.aspx, page2.aspx and I am trying to pass course name value to page2.aspx from page1.aspx using the following URL: ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.