Temporary Table View

[ECEstudent]

Hi, I'm having trouble viewing values that exist in table 'tblTest2' to the user. The first set of code (below) doesn NOT work. Yet the second set of code (all the way at the bottom) DOES work. And by work I mean that it erases the old values and only puts the tblTest2's new values. The first set of code has values but they are not the right ones. The second set of code has the correct values for its statement...

strTable = "tblTest2" 'Table for storing values for viewing purposes
DoCmd.DeleteObject acTable, strTable 'Delete tblTest2 old values
CurrentDb.Execute "CREATE TABLE tblTest2 " _
& "(RepId CHAR, OrderNumber CHAR, Item CHAR, ProductNbr CHAR, Name CHAR, [Rep Region Code] CHAR);" 'Rebuild tblTest2 for use
 
 
                strSQL = ("INSERT INTO " & strTable & " Select c.OrderNumber, c.Item, c.RepId, p.ProductNbr, p.Name, [tbl_LBP_Sales Location Num].[Rep Region Code] FROM CalculateTotal c, dbo_PartNew p, [tbl_LBP_Sales Location Num] WHERE ([Structure] like '*" & u & "*') AND ([ProductNbr] = '" & txtPartNumber & "')")
                CurrentDb.Execute strSQL
 
    DoCmd.OpenTable "tblTest2", acViewPreview

strTable = "tblTest2" 'Table values are uploaded to for viewing at end
DoCmd.DeleteObject acTable, strTable 'Delete table values from past run
CurrentDb.Execute "CREATE TABLE tblTest2 " _
& "(PartNumber CHAR, OrderTotal CHAR, ItemQty CHAR);" 'Rebuild table
 
strSQL = "INSERT INTO tblTest2 ([PartNumber],[OrderTotal], [ItemQty]) Values ('" & NewValue & "', '" & VldOrdrNbrDestination & "', '" & ItemQuantity & "')" 'Insert values in table tblTest2 for the user to view
 
DoCmd.OpenTable "tblTest2", acViewPreview

 

[plog]

Hi, I'm having trouble viewing values that exist in table 'tblTest2' to the user

Why all the code and not just a query?

 

[ECEstudent]

because i'm coding the results of the table. Figured i'd code the table just as well. plus i need the code to be automatically deleted and created. I won't be using this database once i'm done coding it. someone else will and i need it to be as convenient and user-friendly as possible.

 

[ECEstudent]

I need the table to be automatically deleted and created*

not the code lol

 

[plog]

because i'm coding the results of the table.

That's a circular argument:

Me >>> Why are you using code to do this?
You >>> Because I'm using code to do this.

Again, why are you not using a query? Why does the table have to be 'automatically' (I love that word) deleted and created?

 

[ECEstudent]

Ok. I'm lost now. What are you talking about? Why in the world would I use a query to display the results?? The results are being created through code, not from other tables.

 

[plog]

The first code does an insert from a query. Why not just show that query instead of dumping the results into a table?

 

[ECEstudent]

how did you know one of the tables was a query?


and the reason is I don't want to show the entire query, I only want to show some of its values along with values from other tables. And I want to show all of these values in one table. Thus my code for all of this.

 

[ECEstudent]

Also, I've been testing out my code to find out what is causing this problem and for some reason the table prints out just fine with this code:

                    strSQL = ("INSERT INTO " & strTable & " Select c.OrderNumber, c.Item, c.RepId FROM CalculateTotal c WHERE ([Structure] like '*" & u & "*')")
                    CurrentDb.Execute strSQL

I would have stuck with just that, except I need the values from the other 2 tables as well...

 

[plog]

I only want to show some of its values along with values from other tables.

Then build a query that does that.

 

[Pat Hartman]

To test the SQL String, print it in the debug window then copy and past it into the query window. You will frequently see the errors immediately or perhaps get better error messages from the query builder.

 

[ECEstudent]

Okay. Thanks guys got it fixed