A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need a little help with syntax



 
 
Thread Tools Display Modes
  #1  
Old November 7th, 2009, 07:00 PM posted to microsoft.public.access.tablesdbdesign
plisvb via AccessMonster.com
external usenet poster
 
Posts: 13
Default Need a little help with syntax

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/For...esign/200911/1

  #2  
Old November 7th, 2009, 10:21 PM posted to microsoft.public.access.tablesdbdesign
Ken Snell
external usenet poster
 
Posts: 177
Default Need a little help with syntax

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@uwe wrote in message
news:9ec4e5eceaf26@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/For...esign/200911/1



  #3  
Old November 8th, 2009, 04:10 AM posted to microsoft.public.access.tablesdbdesign
plisvb via AccessMonster.com
external usenet poster
 
Posts: 13
Default Need a little help with syntax

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/For...esign/200911/1

  #4  
Old November 8th, 2009, 04:53 AM posted to microsoft.public.access.tablesdbdesign
Ken Snell
external usenet poster
 
Posts: 177
Default Need a little help with syntax

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@uwe wrote in message
news:9ec9b42827e6b@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/For...esign/200911/1



  #5  
Old November 8th, 2009, 05:55 AM posted to microsoft.public.access.tablesdbdesign
plisvb via AccessMonster.com
external usenet poster
 
Posts: 13
Default Need a little help with syntax

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

  #6  
Old November 8th, 2009, 02:07 PM posted to microsoft.public.access.tablesdbdesign
Ken Snell
external usenet poster
 
Posts: 177
Default Need a little help with syntax

We cannot see the data in your tables. We've told you that the logic for
your expression is correct. Therefore, I must assume that your data are not
what you think they are. We are asking for your help in telling us specific
details about your data so that we can help you debug your problem.

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;

And what result do you see as the top record for this query?
SELECT * FROM Orders
WHERE [Transaction Type] = 2
ORDER BY OrderID DESC;

--

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



"plisvb via AccessMonster.com" u55912@uwe wrote in message
news:9eca9e83e49f5@uwe...
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



  #7  
Old November 13th, 2009, 06:17 PM posted to microsoft.public.access.tablesdbdesign
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Need a little help with syntax

Plisvb -

Is OrderID a numeric field, and is this the max field you want, or is InvID
the field you really want?
--
Daryl S


"plisvb via AccessMonster.com" wrote:

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/For...esign/200911/1

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:33 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.