Build Table with Criteria

[jw5107]

I am tryin' to build a table with specific criteria (based on another table) vs. utilizing a Make Table Query...

There is a form inwhich an Item ID (IID) is entered. From there, on the AfterUpdate event - a M/Tbl query runs..
Is there a way to do this via a module. The M/Tbl query runs very slow..

The M/Tbl query looks for the IID on the form, and anything that is >0 in a field named MAX.

Any suggestions or examples available? The IID field is text, and the MAX field is a number field. I have such a hard time setting criteria in code. I almost always get a syntax error!!!

Thanks in advance!!
jw

 

[drkhelmt]

First, why not use declare a "sqlst" variable, and then use the DoCmd.RunSQL(sqlst) code?

Second, MAX is a reserved word in SQL. This could be causing an syntax error. Try changing to MX and see what you get.

Third, the syntax error... what is your code? I'm not sure how you are using the variables.

Hope this gets you closer to the solution.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[jw5107]

drkhelmt,

Below is an example of what I got thus far...

I want to create this table then update it with values from another table.
Can this be done all in shot?
Thanks for the help & quick response!
jw

Sub CreateAllocStatusTbl()
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb

db.Execute "DROP TABLE AllocationStatusMain;"

strSQL = "CREATE TABLE AllocationStatusMain"
strSQL = strSQL & "(IID TEXT(18), "
strSQL = strSQL & "Allocated TEXT(3), "
strSQL = strSQL & "OverAllocated TEXT(3), "
strSQL = strSQL & "OnBackOrder TEXT(3), "
strSQL = strSQL & "Allocation TEXT(20));"

db.Execute strSQL

Set db = Nothing
End Sub

 

[drkhelmt]

Greetings...

I believe a single freaking space (damn those things) after your table name may causing the use of Tylonol...

strSQL = "CREATE TABLE AllocationStatusMain " _
    & "(IID TEXT(18), " _
    & "Allocated TEXT(3), " _
    & "OverAllocated TEXT(3), " _
    & "OnBackOrder TEXT(3), " _
    & "Allocation TEXT(20));"

Hope this gets you closer to the solution.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[jw5107]

drkhelmt,

Right!!! I withya!!

The table is created just fine..

Now - how would I get records added/appended - based on a textbox from a form, from another table with criteria for 3 fields (field1 >0 or field2 >0 or field3 >0)?

I plan to have command button on the form that runs all this based on the IID text box.

Thanks again,
jw

 

[drkhelmt]

Alright.... now we're getting to the meat and 'taters of it...

After running the above stuff to create the table, take a look at the help file for the INSERT INTO sql statement.

To get this to run by the click of a button, add all the code into a sub similar to:

Private Sub command01_btn_Click()

code goes here

End Sub

hope this gets you closer to the solution.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[jw5107]

drkhelmt,

Below is what I am working with now..

I keep getting a syntax error and then it highlights:
Set rsGTWYs = db.OpenRecordset(strSQL,DB_OPEN_DYNASET)

Whats the fix??
I'm running out of my skillset!!!

Thanks,
jw

Private Sub PartStatus_Click()

Dim db As Database
Dim rsGTWYs As Recordset
Dim strSQL As String

Set db = CurrentDb

DoCmd.RunSQL ("Delete * From GtwyInfo;")
DoCmd.RunSQL ("Delete * From WWRIIDMainInfo;")
DoCmd.RunSQL ("Delete * From AllocationStatusMain;")

strSQL = "Select * from gtiaiq Where "
strSQL = strSQL & "[IID] = '" & Me.IID & "'"
strSQL = strSQL & "[MAX] >0"

Set rsGTWYs = db.OpenRecordset(strSQL,DB_OPEN_DYNASET)
If rsGTWYs.EOF Then
strSQL = "INSERT INTO AllocationStatusMain "
strSQL = strSQL & "(GTWY)"
strSQL = strSQL & " values ('"
strSQL = strSQL & Allocated & "');"
db.Execute strSQL
End If
rsGTWYs.Close
db.Close

Set db = Nothing
End Sub

 

[PHV]

strSQL = strSQL & " AND [MAX] >0"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jw5107]

PHV,

Tried it and it gave me a type mismatch error...

Highlighted same thing....

MAX is a number field... Any other suggestions??

Thanks!!
jw

 

[PHV]

Dim rsGTWYs As DAO.Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lesw1433]

Possibly?

Set rsGTWYs = db.OpenRecordset(strSQL, dbOpenDynaset)

removing the underscore???