Sql question 05-17-07

Hi all.

I have a table (tblStoreInv)  with these fields: Index, Store, Product, 
Quantity.
A form (SetInventory)  that is connected to that table has these fields: 
StoreID, Product, Squantity.

Only one record is allowed per store, product, Squantity.

So here is what I wrote:

Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE 
FORMS![SetInventory]![StoreID]=Store AND 
FORMS![SetInventory]![Product]=Product AND 
FORMS![SetInventory]![Squantity]=Quantity;"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
    Cancel = True
    MsgBox "Dupicate!"
    Me.Undo
    End If
Exit_Here:
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub

Private Sub Exit_Click()
DoCmd.Close
End Sub

It does not work.

Please help

TIA,

Tom 


-1
Tom
5/17/2007 7:03:33 PM
access 16762 articles. 2 followers. Follow

15 Replies
676 Views

Similar Articles

[PageSpeed] 56

You need to put the references to the form controls outside of the quotes.

If all three fields are numeric, try:

  strSQL = "SELECT [Quantity],[Store],[Product] " & _
    "FROM tblStoreInv " & _
    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
    " AND Product = " & FORMS![SetInventory]![Product] & _
    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

That assumes that all 3 fields are numeric. If they're text, you need to put 
quotes around the value that's being passed. For example, if Product is Text 
(and the other two numeric), you'd use:L

  strSQL = "SELECT [Quantity],[Store],[Product] " & _
    "FROM tblStoreInv " & _
    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
    " AND Product = " & Chr$(34) & _
    FORMS![SetInventory]![Product] & Chr$(34) & _
    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tom" <partner1973@yahoo.com> wrote in message 
news:eGalRYLmHHA.4688@TK2MSFTNGP03.phx.gbl...
> Hi all.
>
> I have a table (tblStoreInv)  with these fields: Index, Store, Product, 
> Quantity.
> A form (SetInventory)  that is connected to that table has these fields: 
> StoreID, Product, Squantity.
>
> Only one record is allowed per store, product, Squantity.
>
> So here is what I wrote:
>
> Private Sub Squantity_BeforeUpdate(Cancel As Integer)
> Dim db As DAO.Database
> Dim rst As DAO.Recordset
> Dim strSQL As String
>
> Set db = CurrentDb
>
> strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE 
> FORMS![SetInventory]![StoreID]=Store AND 
> FORMS![SetInventory]![Product]=Product AND 
> FORMS![SetInventory]![Squantity]=Quantity;"
>
> Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
>
> If rst.RecordCount > 0 Then ' duplicate entry
>    Cancel = True
>    MsgBox "Dupicate!"
>    Me.Undo
>    End If
> Exit_Here:
>    rst.Close
>    Set rst = Nothing
>    Set db = Nothing
>    Exit Sub
>
> Private Sub Exit_Click()
> DoCmd.Close
> End Sub
>
> It does not work.
>
> Please help
>
> TIA,
>
> Tom
> 


-1
Douglas
5/17/2007 7:11:43 PM
Hi Doug,
Thanks for the prompt response.
Store and Quantity are numeric and Product is Text.
I tried to implement the second option, but it did not work.

TIA,
Tom
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
news:OVfn2cLmHHA.5024@TK2MSFTNGP06.phx.gbl...
> You need to put the references to the form controls outside of the quotes.
>
> If all three fields are numeric, try:
>
>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>    "FROM tblStoreInv " & _
>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>    " AND Product = " & FORMS![SetInventory]![Product] & _
>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>
> That assumes that all 3 fields are numeric. If they're text, you need to 
> put quotes around the value that's being passed. For example, if Product 
> is Text (and the other two numeric), you'd use:L
>
>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>    "FROM tblStoreInv " & _
>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>    " AND Product = " & Chr$(34) & _
>    FORMS![SetInventory]![Product] & Chr$(34) & _
>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Tom" <partner1973@yahoo.com> wrote in message 
> news:eGalRYLmHHA.4688@TK2MSFTNGP03.phx.gbl...
>> Hi all.
>>
>> I have a table (tblStoreInv)  with these fields: Index, Store, Product, 
>> Quantity.
>> A form (SetInventory)  that is connected to that table has these fields: 
>> StoreID, Product, Squantity.
>>
>> Only one record is allowed per store, product, Squantity.
>>
>> So here is what I wrote:
>>
>> Private Sub Squantity_BeforeUpdate(Cancel As Integer)
>> Dim db As DAO.Database
>> Dim rst As DAO.Recordset
>> Dim strSQL As String
>>
>> Set db = CurrentDb
>>
>> strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE 
>> FORMS![SetInventory]![StoreID]=Store AND 
>> FORMS![SetInventory]![Product]=Product AND 
>> FORMS![SetInventory]![Squantity]=Quantity;"
>>
>> Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
>>
>> If rst.RecordCount > 0 Then ' duplicate entry
>>    Cancel = True
>>    MsgBox "Dupicate!"
>>    Me.Undo
>>    End If
>> Exit_Here:
>>    rst.Close
>>    Set rst = Nothing
>>    Set db = Nothing
>>    Exit Sub
>>
>> Private Sub Exit_Click()
>> DoCmd.Close
>> End Sub
>>
>> It does not work.
>>
>> Please help
>>
>> TIA,
>>
>> Tom
>>
>
> 


1
Tom
5/17/2007 8:52:16 PM
I made a typo. There's an unnecessary "=Quantity" in what I posted. It 
should be:

  strSQL = "SELECT [Quantity],[Store],[Product] " & _
    "FROM tblStoreInv " & _
    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
    " AND Product = " & Chr$(34) & _
    FORMS![SetInventory]![Product] & Chr$(34) & _
    " AND Quantity = " & FORMS![SetInventory]![Squantity]

Sorry about that.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Tom" <partner1973@yahoo.com> wrote in message 
news:OsCrCVMmHHA.3736@TK2MSFTNGP03.phx.gbl...
> Hi Doug,
> Thanks for the prompt response.
> Store and Quantity are numeric and Product is Text.
> I tried to implement the second option, but it did not work.
>
> TIA,
> Tom
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
> news:OVfn2cLmHHA.5024@TK2MSFTNGP06.phx.gbl...
>> You need to put the references to the form controls outside of the 
>> quotes.
>>
>> If all three fields are numeric, try:
>>
>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>    "FROM tblStoreInv " & _
>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>    " AND Product = " & FORMS![SetInventory]![Product] & _
>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>>
>> That assumes that all 3 fields are numeric. If they're text, you need to 
>> put quotes around the value that's being passed. For example, if Product 
>> is Text (and the other two numeric), you'd use:L
>>
>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>    "FROM tblStoreInv " & _
>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>    " AND Product = " & Chr$(34) & _
>>    FORMS![SetInventory]![Product] & Chr$(34) & _
>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Tom" <partner1973@yahoo.com> wrote in message 
>> news:eGalRYLmHHA.4688@TK2MSFTNGP03.phx.gbl...
>>> Hi all.
>>>
>>> I have a table (tblStoreInv)  with these fields: Index, Store, Product, 
>>> Quantity.
>>> A form (SetInventory)  that is connected to that table has these fields: 
>>> StoreID, Product, Squantity.
>>>
>>> Only one record is allowed per store, product, Squantity.
>>>
>>> So here is what I wrote:
>>>
>>> Private Sub Squantity_BeforeUpdate(Cancel As Integer)
>>> Dim db As DAO.Database
>>> Dim rst As DAO.Recordset
>>> Dim strSQL As String
>>>
>>> Set db = CurrentDb
>>>
>>> strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE 
>>> FORMS![SetInventory]![StoreID]=Store AND 
>>> FORMS![SetInventory]![Product]=Product AND 
>>> FORMS![SetInventory]![Squantity]=Quantity;"
>>>
>>> Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
>>>
>>> If rst.RecordCount > 0 Then ' duplicate entry
>>>    Cancel = True
>>>    MsgBox "Dupicate!"
>>>    Me.Undo
>>>    End If
>>> Exit_Here:
>>>    rst.Close
>>>    Set rst = Nothing
>>>    Set db = Nothing
>>>    Exit Sub
>>>
>>> Private Sub Exit_Click()
>>> DoCmd.Close
>>> End Sub
>>>
>>> It does not work.
>>>
>>> Please help
>>>
>>> TIA,
>>>
>>> Tom
>>>
>>
>>
>
> 


0
Douglas
5/18/2007 12:18:11 AM
Thanks Doug,
I thought it was and I deleted it.
It still didnt work.


TIA,

Tom
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
news:OPhUEIOmHHA.3888@TK2MSFTNGP05.phx.gbl...
>I made a typo. There's an unnecessary "=Quantity" in what I posted. It 
>should be:
>
>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>    "FROM tblStoreInv " & _
>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>    " AND Product = " & Chr$(34) & _
>    FORMS![SetInventory]![Product] & Chr$(34) & _
>    " AND Quantity = " & FORMS![SetInventory]![Squantity]
>
> Sorry about that.
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Tom" <partner1973@yahoo.com> wrote in message 
> news:OsCrCVMmHHA.3736@TK2MSFTNGP03.phx.gbl...
>> Hi Doug,
>> Thanks for the prompt response.
>> Store and Quantity are numeric and Product is Text.
>> I tried to implement the second option, but it did not work.
>>
>> TIA,
>> Tom
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
>> news:OVfn2cLmHHA.5024@TK2MSFTNGP06.phx.gbl...
>>> You need to put the references to the form controls outside of the 
>>> quotes.
>>>
>>> If all three fields are numeric, try:
>>>
>>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>>    "FROM tblStoreInv " & _
>>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>>    " AND Product = " & FORMS![SetInventory]![Product] & _
>>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>>>
>>> That assumes that all 3 fields are numeric. If they're text, you need to 
>>> put quotes around the value that's being passed. For example, if Product 
>>> is Text (and the other two numeric), you'd use:L
>>>
>>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>>    "FROM tblStoreInv " & _
>>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>>    " AND Product = " & Chr$(34) & _
>>>    FORMS![SetInventory]![Product] & Chr$(34) & _
>>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>>>
>>> -- 
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "Tom" <partner1973@yahoo.com> wrote in message 
>>> news:eGalRYLmHHA.4688@TK2MSFTNGP03.phx.gbl...
>>>> Hi all.
>>>>
>>>> I have a table (tblStoreInv)  with these fields: Index, Store, Product, 
>>>> Quantity.
>>>> A form (SetInventory)  that is connected to that table has these 
>>>> fields: StoreID, Product, Squantity.
>>>>
>>>> Only one record is allowed per store, product, Squantity.
>>>>
>>>> So here is what I wrote:
>>>>
>>>> Private Sub Squantity_BeforeUpdate(Cancel As Integer)
>>>> Dim db As DAO.Database
>>>> Dim rst As DAO.Recordset
>>>> Dim strSQL As String
>>>>
>>>> Set db = CurrentDb
>>>>
>>>> strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE 
>>>> FORMS![SetInventory]![StoreID]=Store AND 
>>>> FORMS![SetInventory]![Product]=Product AND 
>>>> FORMS![SetInventory]![Squantity]=Quantity;"
>>>>
>>>> Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
>>>>
>>>> If rst.RecordCount > 0 Then ' duplicate entry
>>>>    Cancel = True
>>>>    MsgBox "Dupicate!"
>>>>    Me.Undo
>>>>    End If
>>>> Exit_Here:
>>>>    rst.Close
>>>>    Set rst = Nothing
>>>>    Set db = Nothing
>>>>    Exit Sub
>>>>
>>>> Private Sub Exit_Click()
>>>> DoCmd.Close
>>>> End Sub
>>>>
>>>> It does not work.
>>>>
>>>> Please help
>>>>
>>>> TIA,
>>>>
>>>> Tom
>>>>
>>>
>>>
>>
>>
>
> 


0
Tom
5/18/2007 1:51:14 AM
"Didn't work" doesn't tell me much.

Do you get an error message? If so, what's the error? If there's no error, 
what are the symptoms you're experiencing?

Let's see the exact VBA code you're using.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Tom" <partner1973@yahoo.com> wrote in message 
news:ujZxG8OmHHA.5024@TK2MSFTNGP06.phx.gbl...
> Thanks Doug,
> I thought it was and I deleted it.
> It still didnt work.
>
>
> TIA,
>
> Tom
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
> news:OPhUEIOmHHA.3888@TK2MSFTNGP05.phx.gbl...
>>I made a typo. There's an unnecessary "=Quantity" in what I posted. It 
>>should be:
>>
>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>    "FROM tblStoreInv " & _
>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>    " AND Product = " & Chr$(34) & _
>>    FORMS![SetInventory]![Product] & Chr$(34) & _
>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]
>>
>> Sorry about that.
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Tom" <partner1973@yahoo.com> wrote in message 
>> news:OsCrCVMmHHA.3736@TK2MSFTNGP03.phx.gbl...
>>> Hi Doug,
>>> Thanks for the prompt response.
>>> Store and Quantity are numeric and Product is Text.
>>> I tried to implement the second option, but it did not work.
>>>
>>> TIA,
>>> Tom
>>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
>>> news:OVfn2cLmHHA.5024@TK2MSFTNGP06.phx.gbl...
>>>> You need to put the references to the form controls outside of the 
>>>> quotes.
>>>>
>>>> If all three fields are numeric, try:
>>>>
>>>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>>>    "FROM tblStoreInv " & _
>>>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>>>    " AND Product = " & FORMS![SetInventory]![Product] & _
>>>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>>>>
>>>> That assumes that all 3 fields are numeric. If they're text, you need 
>>>> to put quotes around the value that's being passed. For example, if 
>>>> Product is Text (and the other two numeric), you'd use:L
>>>>
>>>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>>>    "FROM tblStoreInv " & _
>>>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>>>    " AND Product = " & Chr$(34) & _
>>>>    FORMS![SetInventory]![Product] & Chr$(34) & _
>>>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>>>>
>>>> -- 
>>>> Doug Steele, Microsoft Access MVP
>>>> http://I.Am/DougSteele
>>>> (no e-mails, please!)
>>>>
>>>>
>>>> "Tom" <partner1973@yahoo.com> wrote in message 
>>>> news:eGalRYLmHHA.4688@TK2MSFTNGP03.phx.gbl...
>>>>> Hi all.
>>>>>
>>>>> I have a table (tblStoreInv)  with these fields: Index, Store, 
>>>>> Product, Quantity.
>>>>> A form (SetInventory)  that is connected to that table has these 
>>>>> fields: StoreID, Product, Squantity.
>>>>>
>>>>> Only one record is allowed per store, product, Squantity.
>>>>>
>>>>> So here is what I wrote:
>>>>>
>>>>> Private Sub Squantity_BeforeUpdate(Cancel As Integer)
>>>>> Dim db As DAO.Database
>>>>> Dim rst As DAO.Recordset
>>>>> Dim strSQL As String
>>>>>
>>>>> Set db = CurrentDb
>>>>>
>>>>> strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE 
>>>>> FORMS![SetInventory]![StoreID]=Store AND 
>>>>> FORMS![SetInventory]![Product]=Product AND 
>>>>> FORMS![SetInventory]![Squantity]=Quantity;"
>>>>>
>>>>> Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
>>>>>
>>>>> If rst.RecordCount > 0 Then ' duplicate entry
>>>>>    Cancel = True
>>>>>    MsgBox "Dupicate!"
>>>>>    Me.Undo
>>>>>    End If
>>>>> Exit_Here:
>>>>>    rst.Close
>>>>>    Set rst = Nothing
>>>>>    Set db = Nothing
>>>>>    Exit Sub
>>>>>
>>>>> Private Sub Exit_Click()
>>>>> DoCmd.Close
>>>>> End Sub
>>>>>
>>>>> It does not work.
>>>>>
>>>>> Please help
>>>>>
>>>>> TIA,
>>>>>
>>>>> Tom
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
Douglas
5/18/2007 12:51:18 PM
Thanks Doug for your help. Sorry for not being clear.
The code suppose to find a record in the table and prevent from the user to 
create a duplicate.
I dont receive my MsgBox and I can create a duplicate
There is no error massage



Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
  "WHERE Store = " & Forms![SetInventory]![StoreID] & _
    " AND Product = " & Chr$(34) & _
    Forms![SetInventory]![Product] & Chr$(34) & _
    " AND Quantity = " & Forms![SetInventory]![Squantity]

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then
    Cancel = True
    MsgBox "Duplicate"
        Me.Undo
    End If
Exit_Here:
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
End Sub
Private Sub Exit_Click()
DoCmd.Close
End Sub

Thanks again,

Tom

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
news:uA8g%23sUmHHA.3656@TK2MSFTNGP06.phx.gbl...
> "Didn't work" doesn't tell me much.
>
> Do you get an error message? If so, what's the error? If there's no error, 
> what are the symptoms you're experiencing?
>
> Let's see the exact VBA code you're using.
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Tom" <partner1973@yahoo.com> wrote in message 
> news:ujZxG8OmHHA.5024@TK2MSFTNGP06.phx.gbl...
>> Thanks Doug,
>> I thought it was and I deleted it.
>> It still didnt work.
>>
>>
>> TIA,
>>
>> Tom
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
>> news:OPhUEIOmHHA.3888@TK2MSFTNGP05.phx.gbl...
>>>I made a typo. There's an unnecessary "=Quantity" in what I posted. It 
>>>should be:
>>>
>>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>>    "FROM tblStoreInv " & _
>>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>>    " AND Product = " & Chr$(34) & _
>>>    FORMS![SetInventory]![Product] & Chr$(34) & _
>>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]
>>>
>>> Sorry about that.
>>>
>>> -- 
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no private e-mails, please)
>>>
>>>
>>> "Tom" <partner1973@yahoo.com> wrote in message 
>>> news:OsCrCVMmHHA.3736@TK2MSFTNGP03.phx.gbl...
>>>> Hi Doug,
>>>> Thanks for the prompt response.
>>>> Store and Quantity are numeric and Product is Text.
>>>> I tried to implement the second option, but it did not work.
>>>>
>>>> TIA,
>>>> Tom
>>>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in 
>>>> message news:OVfn2cLmHHA.5024@TK2MSFTNGP06.phx.gbl...
>>>>> You need to put the references to the form controls outside of the 
>>>>> quotes.
>>>>>
>>>>> If all three fields are numeric, try:
>>>>>
>>>>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>>>>    "FROM tblStoreInv " & _
>>>>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>>>>    " AND Product = " & FORMS![SetInventory]![Product] & _
>>>>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>>>>>
>>>>> That assumes that all 3 fields are numeric. If they're text, you need 
>>>>> to put quotes around the value that's being passed. For example, if 
>>>>> Product is Text (and the other two numeric), you'd use:L
>>>>>
>>>>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>>>>    "FROM tblStoreInv " & _
>>>>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>>>>    " AND Product = " & Chr$(34) & _
>>>>>    FORMS![SetInventory]![Product] & Chr$(34) & _
>>>>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>>>>>
>>>>> -- 
>>>>> Doug Steele, Microsoft Access MVP
>>>>> http://I.Am/DougSteele
>>>>> (no e-mails, please!)
>>>>>
>>>>>
>>>>> "Tom" <partner1973@yahoo.com> wrote in message 
>>>>> news:eGalRYLmHHA.4688@TK2MSFTNGP03.phx.gbl...
>>>>>> Hi all.
>>>>>>
>>>>>> I have a table (tblStoreInv)  with these fields: Index, Store, 
>>>>>> Product, Quantity.
>>>>>> A form (SetInventory)  that is connected to that table has these 
>>>>>> fields: StoreID, Product, Squantity.
>>>>>>
>>>>>> Only one record is allowed per store, product, Squantity.
>>>>>>
>>>>>> So here is what I wrote:
>>>>>>
>>>>>> Private Sub Squantity_BeforeUpdate(Cancel As Integer)
>>>>>> Dim db As DAO.Database
>>>>>> Dim rst As DAO.Recordset
>>>>>> Dim strSQL As String
>>>>>>
>>>>>> Set db = CurrentDb
>>>>>>
>>>>>> strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE 
>>>>>> FORMS![SetInventory]![StoreID]=Store AND 
>>>>>> FORMS![SetInventory]![Product]=Product AND 
>>>>>> FORMS![SetInventory]![Squantity]=Quantity;"
>>>>>>
>>>>>> Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
>>>>>>
>>>>>> If rst.RecordCount > 0 Then ' duplicate entry
>>>>>>    Cancel = True
>>>>>>    MsgBox "Dupicate!"
>>>>>>    Me.Undo
>>>>>>    End If
>>>>>> Exit_Here:
>>>>>>    rst.Close
>>>>>>    Set rst = Nothing
>>>>>>    Set db = Nothing
>>>>>>    Exit Sub
>>>>>>
>>>>>> Private Sub Exit_Click()
>>>>>> DoCmd.Close
>>>>>> End Sub
>>>>>>
>>>>>> It does not work.
>>>>>>
>>>>>> Please help
>>>>>>
>>>>>> TIA,
>>>>>>
>>>>>> Tom
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
Tom
5/18/2007 4:14:56 PM
Since the RecordCount property is known to be unreliable unless you move to 
the end of the recordset, try replacing

If rst.RecordCount > 0 Then

with

If rst.EOF = True Then

(either that, or put rst.MoveLast before you check the Count property)

If that still doesn't work, put a Debug.Print strSQL in your routine after 
you've set its value. Go to the Debug window (Ctrl-G) and see whether the 
SQL looks okay. Copy it into a new query and run it. What result do you get?

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Tom" <partner1973@yahoo.com> wrote in message 
news:OUuHveWmHHA.1220@TK2MSFTNGP03.phx.gbl...
> Thanks Doug for your help. Sorry for not being clear.
> The code suppose to find a record in the table and prevent from the user 
> to create a duplicate.
> I dont receive my MsgBox and I can create a duplicate
> There is no error massage
>
>
>
> Private Sub Squantity_BeforeUpdate(Cancel As Integer)
> Dim db As DAO.Database
> Dim rst As DAO.Recordset
> Dim strSQL As String
>
> Set db = CurrentDb
> strSQL = "SELECT [Quantity],[Store],[Product] " & _
> "FROM tblStoreInv " & _
>  "WHERE Store = " & Forms![SetInventory]![StoreID] & _
>    " AND Product = " & Chr$(34) & _
>    Forms![SetInventory]![Product] & Chr$(34) & _
>    " AND Quantity = " & Forms![SetInventory]![Squantity]
>
> Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
>
> If rst.RecordCount > 0 Then
>    Cancel = True
>    MsgBox "Duplicate"
>        Me.Undo
>    End If
> Exit_Here:
>    rst.Close
>    Set rst = Nothing
>    Set db = Nothing
>    Exit Sub
> End Sub
> Private Sub Exit_Click()
> DoCmd.Close
> End Sub
>
> Thanks again,
>
> Tom
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
> news:uA8g%23sUmHHA.3656@TK2MSFTNGP06.phx.gbl...
>> "Didn't work" doesn't tell me much.
>>
>> Do you get an error message? If so, what's the error? If there's no 
>> error, what are the symptoms you're experiencing?
>>
>> Let's see the exact VBA code you're using.
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Tom" <partner1973@yahoo.com> wrote in message 
>> news:ujZxG8OmHHA.5024@TK2MSFTNGP06.phx.gbl...
>>> Thanks Doug,
>>> I thought it was and I deleted it.
>>> It still didnt work.
>>>
>>>
>>> TIA,
>>>
>>> Tom
>>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
>>> news:OPhUEIOmHHA.3888@TK2MSFTNGP05.phx.gbl...
>>>>I made a typo. There's an unnecessary "=Quantity" in what I posted. It 
>>>>should be:
>>>>
>>>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>>>    "FROM tblStoreInv " & _
>>>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>>>    " AND Product = " & Chr$(34) & _
>>>>    FORMS![SetInventory]![Product] & Chr$(34) & _
>>>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]
>>>>
>>>> Sorry about that.
>>>>
>>>> -- 
>>>> Doug Steele, Microsoft Access MVP
>>>> http://I.Am/DougSteele
>>>> (no private e-mails, please)
>>>>
>>>>
>>>> "Tom" <partner1973@yahoo.com> wrote in message 
>>>> news:OsCrCVMmHHA.3736@TK2MSFTNGP03.phx.gbl...
>>>>> Hi Doug,
>>>>> Thanks for the prompt response.
>>>>> Store and Quantity are numeric and Product is Text.
>>>>> I tried to implement the second option, but it did not work.
>>>>>
>>>>> TIA,
>>>>> Tom
>>>>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in 
>>>>> message news:OVfn2cLmHHA.5024@TK2MSFTNGP06.phx.gbl...
>>>>>> You need to put the references to the form controls outside of the 
>>>>>> quotes.
>>>>>>
>>>>>> If all three fields are numeric, try:
>>>>>>
>>>>>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>>>>>    "FROM tblStoreInv " & _
>>>>>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>>>>>    " AND Product = " & FORMS![SetInventory]![Product] & _
>>>>>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>>>>>>
>>>>>> That assumes that all 3 fields are numeric. If they're text, you need 
>>>>>> to put quotes around the value that's being passed. For example, if 
>>>>>> Product is Text (and the other two numeric), you'd use:L
>>>>>>
>>>>>>  strSQL = "SELECT [Quantity],[Store],[Product] " & _
>>>>>>    "FROM tblStoreInv " & _
>>>>>>    "WHERE Store = " & FORMS![SetInventory]![StoreID] & _
>>>>>>    " AND Product = " & Chr$(34) & _
>>>>>>    FORMS![SetInventory]![Product] & Chr$(34) & _
>>>>>>    " AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity
>>>>>>
>>>>>> -- 
>>>>>> Doug Steele, Microsoft Access MVP
>>>>>> http://I.Am/DougSteele
>>>>>> (no e-mails, please!)
>>>>>>
>>>>>>
>>>>>> "Tom" <partner1973@yahoo.com> wrote in message 
>>>>>> news:eGalRYLmHHA.4688@TK2MSFTNGP03.phx.gbl...
>>>>>>> Hi all.
>>>>>>>
>>>>>>> I have a table (tblStoreInv)  with these fields: Index, Store, 
>>>>>>> Product, Quantity.
>>>>>>> A form (SetInventory)  that is connected to that table has these 
>>>>>>> fields: StoreID, Product, Squantity.
>>>>>>>
>>>>>>> Only one record is allowed per store, product, Squantity.
>>>>>>>
>>>>>>> So here is what I wrote:
>>>>>>>
>>>>>>> Private Sub Squantity_BeforeUpdate(Cancel As Integer)
>>>>>>> Dim db As DAO.Database
>>>>>>> Dim rst As DAO.Recordset
>>>>>>> Dim strSQL As String
>>>>>>>
>>>>>>> Set db = CurrentDb
>>>>>>>
>>>>>>> strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE 
>>>>>>> FORMS![SetInventory]![StoreID]=Store AND 
>>>>>>> FORMS![SetInventory]![Product]=Product AND 
>>>>>>> FORMS![SetInventory]![Squantity]=Quantity;"
>>>>>>>
>>>>>>> Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
>>>>>>>
>>>>>>> If rst.RecordCount > 0 Then ' duplicate entry
>>>>>>>    Cancel = True
>>>>>>>    MsgBox "Dupicate!"
>>>>>>>    Me.Undo
>>>>>>>    End If
>>>>>>> Exit_Here:
>>>>>>>    rst.Close
>>>>>>>    Set rst = Nothing
>>>>>>>    Set db = Nothing
>>>>>>>    Exit Sub
>>>>>>>
>>>>>>> Private Sub Exit_Click()
>>>>>>> DoCmd.Close
>>>>>>> End Sub
>>>>>>>
>>>>>>> It does not work.
>>>>>>>
>>>>>>> Please help
>>>>>>>
>>>>>>> TIA,
>>>>>>>
>>>>>>> Tom
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
Douglas
5/18/2007 5:23:10 PM
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
news:erl5zEXmHHA.4840@TK2MSFTNGP04.phx.gbl: 

> Since the RecordCount property is known to be unreliable unless
> you move to the end of the recordset, try replacing
> 
> If rst.RecordCount > 0 Then

Not true. In DAO if the recordcount is not 0 then you know there are
1 or more records in your resultset. You need only check for it not
being zero, and there's no need to test for .EOF and .BOF (you have
to check both for an empty recordset) or to do a .MoveLast. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
5/18/2007 6:55:18 PM
Hi Tom,

Perhaps you can try a different approach. Create a combined field index 
(unique / no duplicates) using the three fields: StoreID, Product and 
Squantity. Then add the following code to the form:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
    Case 3022
        MsgBox "This item has already been added." & vbCrLf & _
               "You cannot create duplicates.", _
                vbOKOnly + vbInformation, "Item Already Added..."
        Me.Undo
        Response = acDataErrContinue
    Case Else
        Response = acDataErrDisplay
    End Select

End Sub


Also, your previous message showed this:

Private Sub Exit_Click()
DoCmd.Close
End Sub

I recommend that you change it to read as follows, assuming this is a bound 
form:

Private Sub Exit_Click()

    If Me.Dirty = True Then
         Me.Dirty = False
    End If

    DoCmd.Close acForm, Me.Name

End Sub


Here is the reason that I make this recommendation to set Dirty = False:

     Losing data when you close a form
     http://allenbrowne.com/bug-01.html

Specifying "acForm, Me.Name" will help prevent any possibility of the wrong 
object getting closed. While this is usually a rare occurance, it is easy 
enough to add this little bit of insurance.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"Tom" wrote:

> Thanks Doug for your help. Sorry for not being clear.
> The code suppose to find a record in the table and prevent from the user to 
> create a duplicate.
> I don't receive my MsgBox and I can create a duplicate
> There is no error message
0
Utf
5/20/2007 10:17:20 AM
Thanks Tom.

I have index field in the table which is unique autonumber. I put that field 
on the form and I put that code you posted.
It did not prevent from creating dups. I may did it wrong or missed 
something.
Can you explain more about this?

TIA,

Tom


"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message 
news:AA23944C-EF1C-4299-8017-73975FC543C3@microsoft.com...
> Hi Tom,
>
> Perhaps you can try a different approach. Create a combined field index
> (unique / no duplicates) using the three fields: StoreID, Product and
> Squantity. Then add the following code to the form:
>
> Private Sub Form_Error(DataErr As Integer, Response As Integer)
>    Select Case DataErr
>    Case 3022
>        MsgBox "This item has already been added." & vbCrLf & _
>               "You cannot create duplicates.", _
>                vbOKOnly + vbInformation, "Item Already Added..."
>        Me.Undo
>        Response = acDataErrContinue
>    Case Else
>        Response = acDataErrDisplay
>    End Select
>
> End Sub
>
>
> Also, your previous message showed this:
>
> Private Sub Exit_Click()
> DoCmd.Close
> End Sub
>
> I recommend that you change it to read as follows, assuming this is a 
> bound
> form:
>
> Private Sub Exit_Click()
>
>    If Me.Dirty = True Then
>         Me.Dirty = False
>    End If
>
>    DoCmd.Close acForm, Me.Name
>
> End Sub
>
>
> Here is the reason that I make this recommendation to set Dirty = False:
>
>     Losing data when you close a form
>     http://allenbrowne.com/bug-01.html
>
> Specifying "acForm, Me.Name" will help prevent any possibility of the 
> wrong
> object getting closed. While this is usually a rare occurance, it is easy
> enough to add this little bit of insurance.
>
>
> Tom Wickerath
> Microsoft Access MVP
> https://mvp.support.microsoft.com/profile/Tom
> http://www.access.qbuilt.com/html/expert_contributors.html
> __________________________________________
>
> "Tom" wrote:
>
>> Thanks Doug for your help. Sorry for not being clear.
>> The code suppose to find a record in the table and prevent from the user 
>> to
>> create a duplicate.
>> I don't receive my MsgBox and I can create a duplicate
>> There is no error message 


0
Tom
5/21/2007 3:17:51 PM
I tried your suggestions, here is what happen:
when I enter duplicate and try to exit fro the form:
1. there is an error and that part "If Me.Dirty = True Then" is yellow.
2. The sub Form_error do not kick in. I do get error 3022 that basiclly 
prevent me to create a duplicate, but not in elegant way and without the 
custom massage.

Tom

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message 
news:0D5612BA-ECE4-4BB2-A5B2-A52965D65A22@microsoft.com...
> You need to have a multifield unique index, created using the three fields 
> in
> question: StoreID, Product and Squantity.
>
> To create a multifield unique index, open the table in design view. Click 
> on
> the lightening bolt toolbar icon, or View > Indexes. Add an Index Name and
> pick the first field, say StoreID. Leave Primary set to No, but choose Yes
> for Unique. Then add the next two field names directly underneath StoreID,
> without assigning a new index name. Save the table. Test it out in normal
> preview mode for the table. You should not be able to enter a duplicate 
> set
> of data for these three fields, if you created a correct multi-field 
> index.
> After you are satisfied that it is working at the table level, give the 
> code
> in the form another try.
>
> Don't forget to make the change to Private Sub Exit_Click() as well.
>
>
> Tom Wickerath
> Microsoft Access MVP
> https://mvp.support.microsoft.com/profile/Tom
> http://www.access.qbuilt.com/html/expert_contributors.html
> __________________________________________
>
> "Tom" wrote:
>
>> Thanks Tom.
>>
>> I have index field in the table which is unique autonumber. I put that 
>> field
>> on the form and I put that code you posted.
>> It did not prevent from creating dups. I may did it wrong or missed
>> something.
>> Can you explain more about this?
>>
>> TIA,
>>
>> Tom 


0
Tom
5/21/2007 5:19:37 PM
I tried something, Please express your opinions:

I created a query (Look4Q)  which include all the elements: StoreID, 
DeliveryDay, Squantity and Product.
In order to check dups I have only to count how many records apply to these 
criteria.

Private Sub Exit_Click()
Dim Q As Variant
Q = DCount("*", "Look4Q")
If Q = 1 Then
MsgBox "This item has already been added." & vbCrLf & _
"You cannot create duplicates." & vbCrLf & _
"Your entry will not be save.", vbInformation, "Item Already Added..."
 Me.Undo
  End If
If Me.Dirty Then
        Me.Dirty = False
    End If
    DoCmd.Close acForm, Me.Name
End Sub

Any suggestions? opinions?

Thanks,

Tom



"Tom" <partner1973@yahoo.com> wrote in message 
news:eqrz3w8mHHA.1240@TK2MSFTNGP04.phx.gbl...
>I tried your suggestions, here is what happen:
> when I enter duplicate and try to exit fro the form:
> 1. there is an error and that part "If Me.Dirty = True Then" is yellow.
> 2. The sub Form_error do not kick in. I do get error 3022 that basiclly 
> prevent me to create a duplicate, but not in elegant way and without the 
> custom massage.
>
> Tom
>
> "Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message 
> news:0D5612BA-ECE4-4BB2-A5B2-A52965D65A22@microsoft.com...
>> You need to have a multifield unique index, created using the three 
>> fields in
>> question: StoreID, Product and Squantity.
>>
>> To create a multifield unique index, open the table in design view. Click 
>> on
>> the lightening bolt toolbar icon, or View > Indexes. Add an Index Name 
>> and
>> pick the first field, say StoreID. Leave Primary set to No, but choose 
>> Yes
>> for Unique. Then add the next two field names directly underneath 
>> StoreID,
>> without assigning a new index name. Save the table. Test it out in normal
>> preview mode for the table. You should not be able to enter a duplicate 
>> set
>> of data for these three fields, if you created a correct multi-field 
>> index.
>> After you are satisfied that it is working at the table level, give the 
>> code
>> in the form another try.
>>
>> Don't forget to make the change to Private Sub Exit_Click() as well.
>>
>>
>> Tom Wickerath
>> Microsoft Access MVP
>> https://mvp.support.microsoft.com/profile/Tom
>> http://www.access.qbuilt.com/html/expert_contributors.html
>> __________________________________________
>>
>> "Tom" wrote:
>>
>>> Thanks Tom.
>>>
>>> I have index field in the table which is unique autonumber. I put that 
>>> field
>>> on the form and I put that code you posted.
>>> It did not prevent from creating dups. I may did it wrong or missed
>>> something.
>>> Can you explain more about this?
>>>
>>> TIA,
>>>
>>> Tom
>
> 


0
Tom
5/21/2007 6:41:23 PM
Hi Tom,

Okay, the Error 3022 is an encouraging sign.

Did you put this procedure:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)

in the code module associated with your main form, or in a module associated 
with your subform?  It needs to be in the subform's module.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"Tom" wrote:

> I tried your suggestions, here is what happen:
> when I enter duplicate and try to exit fro the form:
> 1. there is an error and that part "If Me.Dirty = True Then" is yellow.
> 2. The sub Form_error do not kick in. I do get error 3022 that basiclly 
> prevent me to create a duplicate, but not in elegant way and without the 
> custom massage.
> 
> Tom
0
Utf
5/21/2007 8:23:02 PM
I do not use this method myself, so I have no comments regarding it. See the 
posting I just made, here:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access&mid=e9516fa4-1925-4829-9003-e0a7020c465f


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"Tom" wrote:

> I tried something, Please express your opinions:
> 
> I created a query (Look4Q)  which include all the elements: StoreID, 
> DeliveryDay, Squantity and Product.
> In order to check dups I have only to count how many records apply to these 
> criteria.
> 
> Private Sub Exit_Click()
> Dim Q As Variant
> Q = DCount("*", "Look4Q")
> If Q = 1 Then
> MsgBox "This item has already been added." & vbCrLf & _
> "You cannot create duplicates." & vbCrLf & _
> "Your entry will not be save.", vbInformation, "Item Already Added..."
>  Me.Undo
>   End If
> If Me.Dirty Then
>         Me.Dirty = False
>     End If
>     DoCmd.Close acForm, Me.Name
> End Sub
> 
> Any suggestions? opinions?
> 
> Thanks,
> 
> Tom
0
Utf
5/21/2007 8:25:01 PM
Hi Tom,

There is no subform, only one form.
and I put it on the form's on error event.

Thanks,

Tom
"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message 
news:E9516FA4-1925-4829-9003-E0A7020C465F@microsoft.com...
> Hi Tom,
>
> Okay, the Error 3022 is an encouraging sign.
>
> Did you put this procedure:
>    Private Sub Form_Error(DataErr As Integer, Response As Integer)
>
> in the code module associated with your main form, or in a module 
> associated
> with your subform?  It needs to be in the subform's module.
>
>
> Tom Wickerath
> Microsoft Access MVP
> https://mvp.support.microsoft.com/profile/Tom
> http://www.access.qbuilt.com/html/expert_contributors.html
> __________________________________________
>
> "Tom" wrote:
>
>> I tried your suggestions, here is what happen:
>> when I enter duplicate and try to exit fro the form:
>> 1. there is an error and that part "If Me.Dirty = True Then" is yellow.
>> 2. The sub Form_error do not kick in. I do get error 3022 that basiclly
>> prevent me to create a duplicate, but not in elegant way and without the
>> custom massage.
>>
>> Tom 


0
Tom
5/21/2007 8:34:45 PM
Reply:

Similar Artilces:

Calling Dex Procedures from SQL
Is there any way to call dex procs from SQL or VB code? Jed There is an undocumented and unsupported method of calling Dex code from VBA. However, I know of no method of calling Dexterity from SQL. Please email me if you want an example, just remove the online. from the address below. David Musgrave [MSFT] Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. This p...

DSum Question
I have a continuous form with an unbound text box that has a DSum function as it's control source. It is used to display the sum of a field in a query. It works fine except that the last line of my form (the one for a new record) displays #Error in this text box. My DSum function is; = DSum("SumOfFootprint", "qryBoxTotals", "BinID = " & [BinID]) I've tried assigning it to the text box control source through code; Me.txtFootPrintSum.ControlSource = DSum("SumOfFootprint", "Query1", "BinID = " & Me![BinID]) but th...

FYI
Since the January 07 IMF update to our Exchange server, we have tried adding 4 new Blackberry's to our BES. None of them would activate wirelessly? I was forced to connect them to the server via usb. Today i figured it out... The IMF was catching the BES activation email being sent. Once i cleaned out the IMF folder of all unwanted email, i tried once again to activate wirelessly, and saw the email being blocked, so i clikced the "resubmit" button, and sure enough... the Blackberry activated! Just thought i would let you all know this, in case you are running into this p...

I cannot see me questions!!!
I posted two questions onto the system: Query of excluded items CrossTab Query but I cannot see them or find them with a search!!! The system sent me an e-mail saying that they have been responded to, but if I cannot see them it is not of much use. Do I have a setting wrong? Please feel free to contact me directly pmw0813@yahoo.com Thanks Send to you to your supplied email. Vanderghast, Access MVP "Paul" <Paul@discussions.microsoft.com> wrote in message news:DF3A5ED4-122E-4AFF-BE51-242AAF4DB5CA@microsoft.com... >I posted two questions onto the system: > >...

is SQL 2000 architecture different SQL Server 2005 / 2008 architecture?
Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I have been told is a good book. I am going backwards... reading about the sql server architecture in chapter 3 of the book. As I'm reading... I was wondering is it different SQL Server 2000 vs SQL Server 2005/2008? Also where can I find a book/or link that can give me more details about the internals on architecture? Thank you norm (normanchan@gmail.com) writes: > Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I > have been told is a good book. I am g...

Moving customizations from SQL 2k to SQL 2k5 CRM
This may be a non-issue but I wanted to check to see if anyone has done this and ran into issues. I have the XML customization files from our previous install of CRM 3.0 with SQL 2k database. I now want to import and publish these same customizations to CRM 3.0 on SQL 2k5. Has anyone done this or can point me to documentation that tells about the success or issues for doing this? Thanks. On 7 Feb., 06:35, Chris Treanor <ChrisTrea...@discussions.microsoft.com> wrote: > This may be a non-issue but I wanted to check to see if anyone has done this > and ran into issues. > &...

VBA Question #4
Hi: I have BASIC programming skills but have never had the opportunity to use VBA inside Excel. Using a UDF, I need to know how to take the contents of a cell and pass it to a variable inside my user defined function. I am trying to take the results in one cell and if it matches, then take the information from two other cells and multiply them together. I've poured over the VBA documentation and see how to push a value into a cell, but can't find any examples of retrieving a cell's content. Thanks in advance! -- Steve Spence Independent AMSOIL Dealer AMSOIL - The "...

CRM 3.0 Install Error with SQL Reporting Services and SQL 2005
Hi, Doing a CRM 3.0 Server installation. During the last page of the install wizard (or what I assume is the last page) when I goes through and does verification I get a red X next to SQL Reporting Services. The message I receive when I look at the details is: ***** The specified path is not a metabase path. Parameter name: path ***** Now SRS is installed and working. I put in the URL: http://<servername>/reportserver and every other variation on that I can find. Thoughts? Alan try http://servername/reports also assuming you did not use ssl... =======================...

OWA 2003 questions
Hi Before i was using Exchange 2000, since I've made the upgrade to Exchange 2003 when I go to the OWA 2003 web site I have this message after my credentials has been entered. This page contains both secure and non-secure items. Do you want to display the non secure items. After that message the padlock at the bottom of the screen disapears. This is not seems good to me When I was on Exchange 2000 I didn't have this message Thanks Are you using OWA with our without SSL? If you are trying to use it with SSL you will need to have a certificate authority setup in your doma...

SQL 'for xml' and C# example??
I'm missing some piece of the puzzle. I'm using 'for xml' type queries from SQL2K - and now trying to change the front-end from ADO to C#, I can't quite figure it out.. It seems that I could use an XmlReader.. and somehow use that instead of a DataReader.. but that didn't work.. I tried using a DataReader and get the byte array - but that wasn't right. SQL returns formatted XML, I need to load that XML into an XmlDocument somehow without ANY molestation of that dom. Any ideas? Examples? Missing links???? thanks THANKS!! I've been looking for the answer to ...

Tab Control 04-06-07
I'm not sure if anyone has tried or needed to do this... I have a Form already built and I want to add another Form to it (not a subForm). I want to create two Forms separated by tabs. I think this is fairly straight forward from scratch, but I have no idea how to copy the preexisting Form onto one tab and then create a new Form on the second tab...any ideas? alex "alex" <sql_aid@yahoo.com> wrote in message news:1175875391.709252.237520@d57g2000hsg.googlegroups.com... > I'm not sure if anyone has tried or needed to do this... > > I have a Form already bu...

thread question #4
How can I determine in a block of code if the thread it's running in is the same as the main GUI thread? I was trying to use GetCurrentThread to get the thread the function is being called from. Not sure how compare that to GUI thread. On Wed, 21 Dec 2005 13:58:35 -0600, "Eric Margheim" <NOSPAM***eric@prism-grp.com***NOSPAM> wrote: >How can I determine in a block of code if the thread it's running in is the >same as the main GUI thread? > >I was trying to use GetCurrentThread to get the thread the function is being >called from. Not sure how...

sql summarizing help
Hi All, Given: table1 --------- id, cdate, weeksago, amount, typeid, timeclose 496, 2010-02-11, 0, 450, 3, 1 490, 2010-02-01, 1, 200, 4, 1 491, 2010-02-01, 1, 350, 2, 2 493, 2010-02-01, 1, 500, 5, 1 489, 2010-01-21, 3, 150, 2, 1 136, 2010-01-12, 4, 500, 3, 2 137, 2010-01-12, 4, 100, 3, 1 138, 2010-01-12, 4, 500, 2, 1 1. i'm trying to create a sql that will give me a summary resultset grouped by weeksago. 2. i want to sum the columns and get counts based on typeid. 3. avg of timeclose here's the sql i have: select weeksago, , (sum(timeClose)/(count(timeclo...

Lotus Notes Integration 05-10-04
I am running Lotus notes for my email. Is it possible to use MS CRM with Lotus Notes ? Definately not Microsoft CRM 1.0 and 1.2 will only work against an Microsoft Exchange 2000 or later server Note Microsoft CRM will work without an email server. Of course you lost the ability to send/receive and track emails in it. "Michael" <anonymous@discussions.microsoft.com> wrote in message news:a99c01c43635$e1c7a2a0$a101280a@phx.gbl... > I am running Lotus notes for my email. Is it possible to > use MS CRM with Lotus Notes ? The organization implements an Exchange server...

Stupid question, I know
I feel like an idiot for even having to ask but I haven't been able to find a definitive answer anywhere. When building a new Exchange Server, do you need both an O/S (Windows Server 2003) and Exchange Server (Standard or Enterprise) Edition on the server or does the Exchange Server 2003 serve as the O/S? Let the comments begin.... You need Windows Server 2003 or 2000. Now if you purchase SBS 2003 then Exchange comes as part of the SBS Suite of applications but all applications must be run from the SBS Server. -- John Oliver, Jr MCSE, MCT, CCNA Exchange MVP 2006 Microsoft Certified ...

Update multivalue field in table from form with SQL
Access 2007 on Vista I am using a form to establish the variables for a report before it is created. One field on the underlying table is a multivalue field, and the form includes a multi-select combo box control. After selections are made and the OK button on the multi-select combo drop down is clicked, focus moves to the next control. I have discovered though, that the underlying table does not update immediately, but does so correctly when I close the form. This sequence does not deliver the proper report data, so I need a way to immediately update the underlying table (m...

Case reports 08-02-05
Hi Guys, we are using CRM for logging our service calls but have a few issues which I hope someone might be able to help with. Firstly, when logging onsite visits we are recording them as appointments but we need to report on these to automatically produce onsite job sheets, incorporating information from the appointment, case and account records. I've created a report in Crystal with varying degrees of success as it seems to miss of information from Account records and also doesn't seem to restrict the report to Appontment activities, despite my filter. Has anyone else done...

Refer to SQL statements globally
I have two large SQL statements that I need to use across several forms, reports, etc. Rather than repeat them in each form, I'm trying to store and reference them globally. For example, I'd like to store strSQL1 and strSQL2 in a global module, so I can use either like this in a form: Random form: Me!lstResults.RowSource = strSQL1 Random report: Me.Report.RecordSource = strSQL2 Would I just store the SQL statements in a global module like: Public Function SQLSource() As String Dim strSQL1 As String Dim strSQL2 As String strSQL1 = "SELECT blah blah...

Questions #7
Please can anyone help me with a couple of questions I have about am Excel XP spreadsheet I have devised to pass out to other people. 1. I have a graphic on the spreadsheet which I want to make 'delete proof' I have used cell protection and a pass work to protect the sheet, but the graphic is still removeable, is there a cure for this? 2. On the sheet is a graph, the graph itself needs to be protected, but the titles could do with being editable, or linkable to another cell (in a seperate data sheet) that the user could edit to change the titles. I'm not a stranger to VBA, ...

To SQL or not to SQL?
I have a very vertical product developed 100% in M/S Access. I developed it myself in 1993 in the initial release of Access and been with it ever since. It is currently very stable and running in Access 2003. My big question is how or even *if* we should move it to SQL. My customers are getting bigger all the time with more users. Typically our customers run one or two workstations generally never more than 5. Performance, however, is starting to slow with database sizes passing 500 meg. We also have a LOT of code behind forms so are bound tightly to the Access development environ...

MDI, beginner questions
Im trying to find the best way for me to implement multiple mdi child windows with dialog layout. My application will need several dialog windows with different layout (sort of like photoshops toolbar windows). All windows will only occur once so there is no need for any new/open/save functionality. I only need MDI to keep all windows organized. Could someone please help me with a simple code example? I've read a bunch of usenet posts, but I still can't figure out a suitable approach for my type of app. Thanks! Fredrik wrote: > Im trying to find the best way for me to implement ...

Requey Question?
On my Switchboard [frmMain] I have a Combobox [cmbTaxWarning] it shows clients through a query which Clients have not been taxed My problem is when I go to my Clients form and change him to No Tax then go back to frmMain my combo box dose not show the change unless I close the DB and open it............Any Help Please.....Bob "Bob V" <rjvance@ihug.co.nz> wrote in message news:e50a1Wc7HHA.4304@TK2MSFTNGP03.phx.gbl... > On my Switchboard [frmMain] I have a Combobox [cmbTaxWarning] it shows > clients through a query which Clients have not been taxed > My probl...

PostgreSQL or other SQL servers in RMS
Has anyone successfully implemented RMS with one of the free SQL servers? Theoretically, is there any reason why we have to use MS SQL Server 2000 for large databases? David "dh" <dh@discussions.microsoft.com> ha scritto nel messaggio news:1783A90D-AF3E-4D91-A445-0039D4E7E379@microsoft.com... > Has anyone successfully implemented RMS with one of the free SQL servers? > Theoretically, is there any reason why we have to use MS SQL Server 2000 > for > large databases? RMS has implemented only for MS SQL Server (2000/MSDE) and isn't possible to work with ano...

Question about antivirus for Exchange 2003
I currently use Mcafee groupshield for exchange 5.5 and while it does block infected attachments, it still sends the email message to the user which generates alot of complaints. I am ugrading to Exchange 2003 and this time I want to get something that is a little more flexible and can block not only the attachment, but the message too. I will look into the latest Mcafee solution, but wanted to hear about the experiences of others on this NG. Thanks NH Hi Ned, I'm using McAfee GroupShield for Exchange 2003. For your information, the software still behave exactly as you mentioned in...

very general mail question
Hi, I just tried to send an e-mail after copying and pasting the e-mail address from a Contact (from Outlook 2007, Vista Home Premium, SP1). The message could not be sent. There were no error messages and I had no idea why this message could not be sent. For some reason the address was enclosed with single apostrophes ..... 'name@something.com' I tried copying the same e-mail address again and this time those apostrophes did not appear and the message was in fact sent. As a test I tried sending my self a message by enclosing my own valid e-mail address with enclosed with si...