Prev: Store similar types of records all in one table or separate tables?
Next: how to best structure this?
From: plisvb via AccessMonster.com on 7 Nov 2009 13:00
Hi Guys,

Having a little problem with some code. I'm wondering if anyone with a keen
eye can point out my error.

Private Sub Transaction_Type_AfterUpdate()
If Me.NewRecord Then
Me.invID = Nz(DMax("[OrderID]", "Orders", "[Transaction Type] = 2"), 0) +
1
End If
End Sub

I want the text field invID to update once a transaction type is selected.

The field updates but not the way I need it too. I want it to show the
highest number with a transaction type of 2(outgoing) and then add 1 to it to
create a sequence for outgoing orders. It's updating, but it's just showing
me the highest number of ALL invoices regardless of transaction type.

Thanks in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1

From: Ken Snell on 7 Nov 2009 16:21
Your code appears to be syntactically correct, assuming that [Transaction
Type] field is numeric data type.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"plisvb via AccessMonster.com" <u55912(a)uwe> wrote in message
news:9ec4e5eceaf26(a)uwe...
> Hi Guys,
>
> Having a little problem with some code. I'm wondering if anyone with a
> keen
> eye can point out my error.
>
> Private Sub Transaction_Type_AfterUpdate()
> If Me.NewRecord Then
> Me.invID = Nz(DMax("[OrderID]", "Orders", "[Transaction Type] = 2"), 0)
> +
> 1
> End If
> End Sub
>
> I want the text field invID to update once a transaction type is selected.
>
> The field updates but not the way I need it too. I want it to show the
> highest number with a transaction type of 2(outgoing) and then add 1 to it
> to
> create a sequence for outgoing orders. It's updating, but it's just
> showing
> me the highest number of ALL invoices regardless of transaction type.
>
> Thanks in advance
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1
>


From: plisvb via AccessMonster.com on 7 Nov 2009 22:10
Thanks,

I think it's the right data type because the following code works perfectly
on it's own textbox

=DCount("[OrderID]","Orders","[Transaction Type]=2")



Ken Snell wrote:
>Your code appears to be syntactically correct, assuming that [Transaction
>Type] field is numeric data type.
>
>> Hi Guys,
>>
>[quoted text clipped - 20 lines]
>>
>> Thanks in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1

From: Ken Snell on 7 Nov 2009 22:53
Then I would suggest that something in your logic is not what you are trying
to achieve.

What result do you get with this?
DMax("[OrderID]", "Orders", "[Transaction Type] = 2")

And what result do you see as the top record for this query?
SELECT * FROM Orders
ORDER BY OrderID DESC;

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"plisvb via AccessMonster.com" <u55912(a)uwe> wrote in message
news:9ec9b42827e6b(a)uwe...
> Thanks,
>
> I think it's the right data type because the following code works
> perfectly
> on it's own textbox
>
> =DCount("[OrderID]","Orders","[Transaction Type]=2")
>
>
>
> Ken Snell wrote:
>>Your code appears to be syntactically correct, assuming that [Transaction
>>Type] field is numeric data type.
>>
>>> Hi Guys,
>>>
>>[quoted text clipped - 20 lines]
>>>
>>> Thanks in advance
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1
>


From: plisvb via AccessMonster.com on 7 Nov 2009 23:55
I'm trying to find out the highest number (ID) that is an Outgoing Order (2)

Ken Snell wrote:
>Then I would suggest that something in your logic is not what you are trying
>to achieve.
>
>What result do you get with this?
>DMax("[OrderID]", "Orders", "[Transaction Type] = 2")
>
>And what result do you see as the top record for this query?
>SELECT * FROM Orders
>ORDER BY OrderID DESC;
>
>> Thanks,
>>
>[quoted text clipped - 12 lines]
>>>>
>>>> Thanks in advance

--
Message posted via http://www.accessmonster.com

 |  Next  |  Last
Pages: 1 2
Prev: Store similar types of records all in one table or separate tables?
Next: how to best structure this?