Problem with DMax & DLookup

[AricC]

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

[MMcCarthy]

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.

 
Dim tmpID As Long 'Assumed
 
tmpID = DMax("[intReturnGoodsAuthorizationID]", "[TReturnGoodsAuthorizations]") 
 
 

[AricC]

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

[MMcCarthy]

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.

 
Private Sub cmdSubmit_Click()
 
Me.[intReturnGoodsAuthorizationID] = DMax("[intReturnGoodsAuthorizationID]", "[TReturnGoodsAuthorizations]")+1
 
End Sub
 
 

[AricC]

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

[MMcCarthy]

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.

 
Private Sub btnSubmit_Click()
Dim intReturnGoodsAuthorizationMaxID As Integer
Dim intSerialNumberMaxID As Integer
Dim intConditionMaxID As Integer
 
'turn off access add record warning 
DoCmd.SetWarnings False
 
'Insert Return Goods Authorizations
DoCmd.RunSQL "INSERT INTO [TReturnGoodsAuthorizations](strReturnGoodsAuthorizationNumber) VALUES('" & Me.txtReturnGoodsAuthorizationNumber & "');"
 
'Insert Serial Number
DoCmd.RunSQL "INSERT INTO [TSerialNumbers] (strSerialNumber) VALUES('" & Me.txtSerialNumber.Value & "');"
 
'Insert Condition
DoCmd.RunSQL "INSERT INTO [TConditions] (strCondition) VALUES('" & txtCondition & "');"
 
'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 & "','" & cmbProduct & "'," & intSerialNumberMaxID & "," & intReturnGoodsAuthorizationMaxID & ",'" & cmbWarranty & "'," & intConditionMaxID & ",#" & txtDateReceived & "#);"
 
DoCmd.SetWarnings True
 
End Sub
 
 

[AricC]

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.

[MMcCarthy]

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

[AricC]

Is there any easy way built into access to check for a duplicate entry and only allow one without setting index to no duplicates?

[AricC]

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

[MMcCarthy]

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.

[AricC]

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

[AricC]

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!