Object Required Error

[spdolan]

CurrentDb.Execute "UPDATE tblAT SET Total = Total + " & Me.Quantity & " WHERE tblAT.ISBN = " & Me.ISBN And tblAT.Condition = " & Me.Condition"

I am trying to update two tables at once in the OnClick Event Proc. One with this line of code, one with GotoNewRecord.

This code returns "Object Required".

Can anyone tell me what I am doing wrong?

Sean

 

[ajetrumpet]

CurrentDb.Execute "UPDATE tblAT SET Total = Total + " & Me.Quantity & " WHERE tblAT.ISBN = " & Me.ISBN And tblAT.Condition = " & Me.Condition"

Sean,

the syntax is off:

CurrentDb.Execute "UPDATE tblAT SET 
   Total = Total + " & Me.Quantity & 
      " WHERE tblAT.ISBN = " & Me.ISBN [COLOR="Red"][B]" [/B][/COLOR]And tblAT.Condition = " 
         & Me.Condition

the code is diffferent for different data ttypes. so the possibilities are endless here. that is the bad thing about using functions like this in VBA.

 

[aman]

Modify it with the following statement and see if it works

CurrentDb.Execute "UPDATE tblAT SET Total = Total + " & Me.Quantity & " WHERE tblAT.ISBN = " & Me.ISBN & " And tblAT.Condition = " & Me.Condition &"

And make sure that ISBN and Condition has number data type. If not then surround them with single quotes.

Aman

 

[spdolan]

Oh - Both fields are text, should have mentioned that. I have checked, and both fields are text, 2.

Thanks

 

[aman]

try the following statement then

CurrentDb.Execute "UPDATE tblAT SET Total = Total + " & Me.Quantity & " WHERE tblAT.ISBN = ' " & Me.ISBN & " ' And tblAT.Condition = ' " & Me.Condition &" '

Cheers

 

[spdolan]

Modify it with the following statement and see if it works

CurrentDb.Execute "UPDATE tblAT SET Total = Total + " & Me.Quantity & " WHERE tblAT.ISBN = " & Me.ISBN & " And tblAT.Condition = " & Me.Condition &"

And make sure that ISBN and Condition has number data type. If not then surround them with single quotes.

Aman

Hi Aman -

They are text fields - where do i put the single quotes? around the TblAT.* or the Me.*?

 

[spdolan]

well that removed the error, but it is not dumping the calculated total into the tblAT.

Any thought on that one?

Sean

 

[Lil' Rascal]

Do Not Store Calculated Values!

http://allenbrowne.com/casu-14.html

As for not updating? You're probably not pulling any records with your criteria.

Put a break point after your execute fires and debug.print (Or ? me.quantity) your variables in the Immediate window (CTRL+G in the VBA window) when the code breaks. Then see if you actually have any records in tblAT that match the criteria specified.

Or build a select query with the same criteria and see if you return any records.

 

[spdolan]

I understand that I am not supposed to store calc'd values, but I am at a loss to know how to do this any other way. Books from all over the country come to my warehouse, and arranged alphabetically on my shelves. I dont know what books I am getting until they arrive, so I have a pool of 2 million ISBN and Condition combinations, which designates the SKU for each book.

For Example:

ISBN Author SKU_Title Rating Condition
0375709320 ELSTER VERBALADVANTAGE K 4

calculates a SKU of KELSTEVERBA93204.

In a second process, as books are sold, they are sold by this SKU. I need to create pick lists for my warehouse guys based upon SKU and Quantity sold.

My initial reaction, and source of much frustration, is to add a total field to the tblAT which will store the calculated amount, and both add and subtract to it from the two different forms.

This is a simple operation, and while multiple people may be adding books in, only one will be taking them out. What I don't understand is how to subtract one book from many different records. I can make a query that shows the total quantity on hand, but am unsure how to manipulate that info.

Any help is greatly appreciated.

Sean

 

[Lil' Rascal]

Without deviating from the OP:

Did you set the break and test your results?

 

[spdolan]

I tried, but there is no error happening now, just no updating of the destination field.

I know my data exists, because a subform pulls Author Title Rating and SKU from the tblAT.

Since the SKU is the primary on tblAT, I am going to try to update based upon that field instead of multiple criteria. I think the code will be easier.

 

[spdolan]

Private Sub Add_New_Record_Click()
On Error GoTo Err_Add_New_Record_Click

CurrentDb.Execute "UPDATE tblAT SET Total = tblAT.Total + " & Me.Quantity & " WHERE tblAT.SKU = ' " & Me.sfTblAT.SKU '
DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Record_Click:
Exit Sub

Err_Add_New_Record_Click:
MsgBox Err.Description
Resume Exit_Add_New_Record_Click

End Sub

This is now giving me a method or object not found error on Me.Quantity

Edit ** When I do CTRL-G and put in ?Me.Quantity, it returns the correct value.

 

[Lil' Rascal]

Can you .zip your db and post it so we can see what's going on?

 

[spdolan]

I'd love to but its over 300MB due to 2 million records. I'll cut down the amount of records so that there will be a small sampling, should knock the size down.

EDIT: I knocked out all but about 13k records, so please check tblAT for testing ISBN's etc.

Thanks.

Sean

 

[Lil' Rascal]

O.k. I got that error taken care of. "Me.sfTblAT.SKU" wasn't a valid object so I got it pointed right for you.

Just copy and paste the code from
Changes in Red
See Below for Blue

Private Sub Add_New_Record_Click()
On Error GoTo Err_Add_New_Record_Click
    
[COLOR=Red]    Dim MyQuantity          As Byte
    Dim MySku               As String[/COLOR]
       
[COLOR=Red]    MyQuantity = Me.Quantity.Value
    MySku = Forms!frmInput![tblAT subform].Form![Text10].Value[/COLOR]
    
    CurrentDb.Execute "UPDATE tblAT [COLOR=Blue]" _[/COLOR]
                    [COLOR=Blue]& "[/COLOR]SET tblAT.Total = tblAT.Total + [COLOR=Red]'[/COLOR]" & [COLOR=Red]MyQuantity[/COLOR] & "[COLOR=Red]'[/COLOR] [COLOR=Blue]" _[/COLOR]
                    [COLOR=Blue]& "[/COLOR]WHERE tblAT.SKU = '" & [COLOR=Red]MySku[/COLOR] [COLOR=Red]& "[/COLOR]'[COLOR=Red]"[/COLOR]
    DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Record_Click:
    Exit Sub

Err_Add_New_Record_Click:
    MsgBox Err.Description
    Resume Exit_Add_New_Record_Click
    
End Sub

The " _ then the & " on the next line following concatenates the two lines so Access reads it as one line but we can read it by what your statement is doing:

UPDATE
SET
WHERE

IMO makes it easier to read through if you (and you will) need to go back and read through your code. But it will vary from person to person. There's a few different ways to code that.

I would recommend a little reading on normalization, quotes within quotes and referring to controls on subforms.
I found that the more I read the more I realize that there are better and faster ways to get the results you want from my data.

One more thing.

I might drop this little sub at the top of the frmInput module:

Private Sub RefreshMe()
On Error GoTo Err_RefreshMe
    Me.sfInvDetail.Requery
Exit_RefreshMe:
    Exit Sub
Err_RefreshMe:
    MsgBox Err.Description
    Resume Exit_RefreshMe
End Sub

Then when you want your subform refreshed (and you're in frmInput) all you have to do is:

Call RefreshMe

 

[spdolan]

Thanks so much. This worked for the major problem. However, when I dropped this code in, my refresh stopped working. I tried to put in that RefreshMe code, but I think I messed it up somehow.

I will keep playing with it.

Sean

 

[Lil' Rascal]

Thanks so much. This worked for the major problem. However, when I dropped this code in, my refresh stopped working. I tried to put in that RefreshMe code, but I think I messed it up somehow.

Your refresh command button stopped working?

Ahhhhh yes. there it is:

 Me.sfInvDetail.Refresh
Exit_RefreshMe
    Exit Sub

should read:

    Me.sfInvDetail.Re[COLOR=Red]query[/COLOR]
Exit_RefreshMe[COLOR=Red]:[/COLOR]
    Exit Sub

Ooops. There's some real help for you. Can't even debug my own code. I've changed it in the prior post as well.
Look at your relationships in your query as well. You've got two relationships Condition and USBN. BOTH of those criteria MUST be met to return records.

My advice:

As aggravating as this will be. Go back to the fundamentals. The normalizing and naming conventions of your db app will save you hours if not days of trial and error. Not to mention the pain of ripping your hair out.

Good luck & Let us know how it goes.
LR

 

[spdolan]

well it seems we arent out of the woods yet. it is now writing to the tblAT, and adding the quantity together, but on the wrong condition. I entered condition 2, 22 copies, and condition 2, 25 copies, but in the tblAT it shows 47 copies in the condition 11 record.

I appreciate your advice, and will go back to the basics on a redesign, but I am a little under the gun at the moment.

 

[Lil' Rascal]

well it seems we arent out of the woods yet. it is now writing to the tblAT, and adding the quantity together, but on the wrong condition. I entered condition 2, 22 copies, and condition 2, 25 copies, but in the tblAT it shows 47 copies in the condition 11 record.

I appreciate your advice, and will go back to the basics on a redesign, but I am a little under the gun at the moment.

That's going to be an additional criteria in the execute statement.

Look at the MyQuantity variable at the beginning of the OnClick sub. You're going to do this for another variable called MyCondition and then add a tblAT.Condition = '" & MyCondition & "' criteria to the WHERE clause of the execute statement

 

[aman]

Hi

Try this code:

Dim a As Integer
Dim sum As Integer
a = DLookup("[Quantity]", "tblAT", "[ISBN]= '" & Me.ISBN & "' and [Condition] = '" & Me.Condition & "'")
sum = a + me.Quantity
Debug.Print sum
strsql = "UPDATE table1 SET tblAT.Quantity =" & sum & " WHERE tblAT.ISBN = ' " & Me.ISBN & " ' And tblAT.Condition = ' " & Me.Condition &" ' "
Debug.Print strsql
Stop ' halt the code to see the value of strsql
CurrentDb.Execute strsql

Cheers