Search results

Well, I think I've almost got it. I modified the Insert Into statement, and it doesn't give me the Type Mismatch Eror, but it tells me I'm missing a semicolon. Screenshot of Error attached. The error occurs in the red highlighted line. Here's my code: Public Sub ProdGrpExport() Dim dbPath...

Can someone tell me if what I'm trying is even possible? I read somewhere that it may not be. It seems to me that it should be easy. Set recordset to a variable, and use variable for reference to my recordset. I don't understand why it won't let me do it.

I think I got the nested SQL figured out. New SQL line in red. The second query was simply summaries, so I think I just needed to add sum() around the fields, and remove the fields I didn't want to show. Although; now the Insert Into statements that refer to the recordset aren't running...

SOS, If that is true, how do you do it for this? Set Rs2 = CurrentDb.OpenRecordset("SELECT tblSmGrd.Year AS Yr, tblSmGrd.MarketSegSCust, tblSmGrd.RegionSCust, tblSmGrd.SalesTerrSCust, tblSmGrd.ProdGrp, tblSmGrd.ProdCat, tblSmGrd.NWgtImperial AS Vol, tblSmGrd.GrossSalesUSD AS GsUSD...

Rs1 is a loop recordset to see how many SalesReps there are, and how many to loop. Rs2 is a filtered recordset of the original table, with first SalesRepID from Rs1 as the Where clause. Rs3 is a summary query, based on Rs2 results. Here's what I have after changing to Paul's syntax, and get a...

I did try it his way first, and realized he possibly made a typo. My variable name for the recordset is Rs2, not Rs2.Yr. Correct me if I'm wrong, but only the variable name goes between the syntax (" & variable & "). I just tried it his way again, and get "Method or data member not found"...

That's what I thought at first, but it doesn't seem to work. I tried this, and get a type mismatch error, and it highlights the very first Rs2. Set Rs3 = CurrentDb.OpenRecordset("SELECT " & Rs2 & ".Yr, " & Rs2 & ".ProdGrp, " & Rs2 & ".ProdCat, Sum(" & Rs2 & ".Vol) AS Vol, Sum(" & Rs2 & ".GsUSD)...

Hello, I'm trying to process some reports, and need to be able to create a recordset from a variable recordset created in my code, and keep getting the following error: The Microsoft Office database engine cannot find the input table or query 'Rs2'. Blah, Blah, Blah. Rs2 is a variable in my...

Works great. Thanks a million.

Thanks again PBaldy, I'll try that out.

I have a form with various TextBoxes. The code should convert the form entries into variables, and use the variable values for the DoCmd.RunSQL statement. I've tried typing in the variables with single quotes, and no quotes, but TextBox values don't seem to be passing to the SQL statement. Am...

I like your int counter idea, and will try I tomorrow. As for the bound subform... I didn't learn Access the typical way, so I don't know some of the basic concepts. I started out writing short macros/scripts for Excel to get reports together for them, until the data got too big. About a year...

Ok, here's a scaled down version. The form will open automatically, and you can use the following inputs to see what's going on. Ship To: 501508 Item SKU: 53850 Branch/Plant: Eaton Warehouse (10903) FHC: DLT MOT: DMP Price: $3.00 Annual Tons: 100 Frt Rate per CWT: $1.00 The module isn't set...

pbaldy, Would it be easier, if I removed customer info, and sent you a copy of the DB?

I may have set this up wrong to begin with, and it's an unbound form that queries the table for source info to calculate on. Here's what I have... I have a tblItemDetails & tblCustDetails tblItemDetails has all Item Costs, based on which Plant they are made. Therefore; each Item/Plant...

YAHOOOOOOOOOOOOOOOOOOOOO! It worked! Thanks for all your help. One more question... There will be 25 lines of products on the completed form, to be able to process several products all at once. My goal was to get one piece of working code, and call the procedure when any of the user fields...

I want the fields locked by default, so they must be unlocked before update. I will remove the Set Focus lines, if not needed. As for the If/Then statements, I assume you mean they should look like this: If strCustCntry = "US" And strBPCntry = "CA" Then dblTCost = dblToTCost * dblXrate...

Thanks guys. The code runs without errors, and updates the fields, but they are all zeros. Here's what I'm working with now, if you have any ideas. Option Compare Database Public Sub RfshItemOne() If "[Forms]![frmDataColl]![cboItemSKU1]" = Null Then Exit Sub Else Dim dbPath...

Ok, here's what I put together. I'm getting an error at the line in red, stating Object Varable or With Block Variable not set. I'm sure there are better ways to write this, but I'm still learning. Option Compare Database Public Sub RfshItemOne() If "[Forms]![frmDataColl]![cboItemSKU1]"...

F'n DUH. :o I haven't figured out my problem yet, but I just realized what you were trying to tell me by "doing the calculations in the code". You're telling me to query only the "real" fields (not calc) in my SQL statement, and then do all my calculations with variables. Therefore; the steps...