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

Problem with DMax & DLookup

Expert 100+
P: 1,892
Howdy Errrrbody,

I'm new here! I have a few questions that I can't seem to get answered, maybe you all can help.

I am writing an access program ( first time I'm accustomed to .Net + Sql Server ) I am trying to select the Max ID from a table and store so I can use that ID in a referenced table here is what I have tried:

intReturnGoodsAuthorizationID = DMax("[intReturnGoodsAuthorizationID]", "[TReturnGoodsAuthorizations]")
This should return 10 (the highest ID) but it returns 0



intReturnGoodsAuthorizationMaxID = DLookup("[intReturnGoodsAuthorizationID]", "[MaxRGA]")
This produces a 2001 runtime error.

Here is the query, not sure what's going on in theory if the DLookup doesn't work the DMax should return something other than 0 which is not an ID in my db. This is in Access 2003.

Thanks looking forward to contributing!
AricC
Oct 20 '06 #1
Share this Question
Share on Google+
12 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You don't say where you are trying to do this. You can't do it in the default value in the table if thats what you are trying to do.

You can't return its value to itself.

If this was a query:

SELECT Max([intReturnGoodsAuthorizationID]) AS MaxID FROM[TReturnGoodsAuthorizations];

In code:

declare a local variable.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim tmpID As Long 'Assumed
  3.  
  4. tmpID = DMax("[intReturnGoodsAuthorizationID]", "[TReturnGoodsAuthorizations]") 
  5.  
  6.  
Oct 20 '06 #2

Expert 100+
P: 1,892
You don't say where you are trying to do this. You can't do it in the default value in the table if thats what you are trying to do.
I'm doing this in a Submit buttons click event.
You can't return its value to itself.
Jeez long Friday it's time for me to go home!

Thanks!
AricC
Oct 20 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I take it back, you can return it to a field control on your form referencing it.
Just a thought, if this is a primary key it won't let you return 10 you have to return 11.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdSubmit_Click()
  3.  
  4. Me.[intReturnGoodsAuthorizationID] = DMax("[intReturnGoodsAuthorizationID]", "[TReturnGoodsAuthorizations]")+1
  5.  
  6. End Sub
  7.  
  8.  
Oct 20 '06 #4

Expert 100+
P: 1,892
It is the primary key that I want the max of, however it is returning the correct value... Another problem I am trying to work out is inserting all the form fields into there respective tables. I keep being asked for parameters on the variable inserted fields, also is there any way for it not to ask if you are sure you want to append 1 row? Ie.. I have to make 3 inserts then I am getting the ID's of those fields, then inserting into a child table. Here is my code: ( there may be a better way to do this like I said first MS Access app )

Thanks,
'-------------------------------------------
'btnSubmit_Click()
'-------------------------------------------
Private Sub btnSubmit_Click()

Dim dtmDateReceived As Date
Dim strCustomer As String
Dim strReturnGoodsAuthorizationNumber As String
Dim strProduct As String
Dim strSerialNumber As String
Dim strWarranty As String
Dim strCondition As String
Dim intReturnGoodsAuthorizationMaxID As Integer
Dim intSerialNumberMaxID As Integer
Dim intConditionMaxID As Integer

'Insert Return Goods Authorizations
DoCmd.RunSQL "INSERT INTO [TReturnGoodsAuthorizations](strReturnGoodsAuthorizationNumber) VALUES(txtReturnGoodsAuthorizationNumber.Value)"

'Insert Serial Number
DoCmd.RunSQL "INSERT INTO [TSerialNumbers](strSerialNumber) VALUES(txtSerialNumber.Value)"

'Insert Condition
DoCmd.RunSQL "INSERT INTO [TConditions](strCondition) VALUES(txtCondition.Value)"

'Get the Max ID's from RGA
intReturnGoodsAuthorizationMaxID = DMax("[intReturnGoodsAuthorizationID]", "[TReturnGoodsAuthorizations]")

'Get the Max ID from Serial Number
intSerialNumberMaxID = DMax("[intSerialNumberID]", "[TSerialNumbers]")

'Get the Max ID from Conditions
intConditionMaxID = DMax("[intConditionID]", "[TConditions]")

'Insert into child
DoCmd.RunSQL "INSERT INTO [TRepairs](intCustomerID, intProductID, intSerialNumberID, intReturnGoodsAuthorizationID, intWarrantyID, intConditionID, dtmDateReceived)" & _
"VALUES (cmbCustomer.Value, cmbProduct.Value, intSerialNumberMaxID, intReturnGoodsAuthorizationMaxID,cmbWarranty.Value ,intConditionMaxID,txtDateReceived.Value)"

End Sub
Oct 20 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
However, if you try to enter the maxID fields into the table it won't accept them because they are already in there. Put + 1 at the end of each of those lines if I'm right. Otherwise, try the following it should solve most of your problems.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub btnSubmit_Click()
  3. Dim intReturnGoodsAuthorizationMaxID As Integer
  4. Dim intSerialNumberMaxID As Integer
  5. Dim intConditionMaxID As Integer
  6.  
  7. 'turn off access add record warning 
  8. DoCmd.SetWarnings False
  9.  
  10. 'Insert Return Goods Authorizations
  11. DoCmd.RunSQL "INSERT INTO [TReturnGoodsAuthorizations](strReturnGoodsAuthorizationNumber) VALUES('" & Me.txtReturnGoodsAuthorizationNumber & "');"
  12.  
  13. 'Insert Serial Number
  14. DoCmd.RunSQL "INSERT INTO [TSerialNumbers] (strSerialNumber) VALUES('" & Me.txtSerialNumber.Value & "');"
  15.  
  16. 'Insert Condition
  17. DoCmd.RunSQL "INSERT INTO [TConditions] (strCondition) VALUES('" & txtCondition & "');"
  18.  
  19. 'Get the Max ID's from RGA
  20. intReturnGoodsAuthorizationMaxID = DMax("[intReturnGoodsAuthorizationID]", "[TReturnGoodsAuthorizations]")
  21.  
  22. 'Get the Max ID from Serial Number
  23. intSerialNumberMaxID = DMax("[intSerialNumberID]", "[TSerialNumbers]")
  24.  
  25. 'Get the Max ID from Conditions
  26. intConditionMaxID = DMax("[intConditionID]", "[TConditions]")
  27.  
  28. 'Insert into child
  29. DoCmd.RunSQL "INSERT INTO [TRepairs] (intCustomerID, intProductID, intSerialNumberID, intReturnGoodsAuthorizationID, intWarrantyID, intConditionID, dtmDateReceived) " & _
  30. "VALUES ('" & cmbCustomer & "','" & cmbProduct & "'," & intSerialNumberMaxID & "," & intReturnGoodsAuthorizationMaxID & ",'" & cmbWarranty & "'," & intConditionMaxID & ",#" & txtDateReceived & "#);"
  31.  
  32. DoCmd.SetWarnings True
  33.  
  34. End Sub
  35.  
  36.  
Oct 20 '06 #6

Expert 100+
P: 1,892
MMcarthy,
Let me ask you what did you include the # sign in the last 2 Values for?

I get a syntax error(missing operator) on this line:

DoCmd.RunSQL "INSERT INTO [TConditions] (strCondition) VALUES('" & Me.txtCondition.Value & "');"

The syntax looks the exact same as the other fields.
Oct 20 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
MMcarthy,
Let me ask you what did you include the # sign in the last 2 Values for?
# on either side of a date field. Not always required but good practice.



DoCmd.RunSQL "INSERT INTO [TConditions] (strCondition) VALUES ('" & Me.txtCondition & "');"

I just put in a space after VALUES and removed .Value. See if it makes any difference
Oct 20 '06 #8

Expert 100+
P: 1,892
Is there any easy way built into access to check for a duplicate entry and only allow one without setting index to no duplicates?
Oct 21 '06 #9

Expert 100+
P: 1,892
Also,
Does anyone know of a refresher once I enter a record it stays kind of cached how to I refresh and get a fresh blank copy of the form?

Thanks!
Aric
Oct 21 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
AricC

I don't understand what you're asking in your last two posts.

If these are separate issues can you post them separately and with a more detailed explanation.
Oct 21 '06 #11

Expert 100+
P: 1,892
AricC
I don't understand what you're asking in your last two posts.
If these are separate issues can you post them separately and with a more detailed explanation.
Ok once you enter a record and hit submit. The info is inserted into Tables, is there any way to make the record 'Refresh' or should I programmatically set the fields to not have anything in the text/combo boxes?
Aric
Oct 23 '06 #12

Expert 100+
P: 1,892
Ok once you enter a record and hit submit. The info is inserted into Tables, is there any way to make the record 'Refresh' or should I programmatically set the fields to not have anything in the text/combo boxes?
Aric
Disregard I took care of it!
Oct 23 '06 #13

Post your reply

Sign in to post your reply or Sign up for a free account.