SQL/VBA Code

ECEstudent

Registered User.
Local time
Today, 09:20
Joined
Jun 12, 2013
Messages
153
I really need you guys' help on this...I have no idea what the problem is with my code. I have 3 tables, 2 of them are related the other one is not because there's modification involved and bla bla. just ignore table 1. But what is supposed to happen is that user inputs a part number, that part number is searched for in table 1, the associated parents are kept in an array, and then the values within that array are searched in the query (where table 2 and table 3 are involved) and then what is supposed to happen is that the results of the search are supposed to be returned to the user in a temporary table called tmpProductQuery. The table pops up like it's supposed to but there are absolutely no values included in that table even though I know there are at least 200 values that are supposed to be there. Plllllllleaaaase help. This DB is making me go insane :(



Code:
Option Compare Database
Option Explicit

Sub PartNumberSearch()

Dim txtPartNumber As Variant
Dim rst As Recordset 'declaration of variable
Dim Arr() As String 'declaration of variable
Dim i As Integer 'declaration of variable
Dim x As Variant 'declaration of variable
Dim varCode As Variant 'declaration of variable
Dim u As Variant
 

Dim dbs As Database
Dim qdf As QueryDef
Dim strSql As String
txtPartNumber = InputBox("Enter Part Number:")

If DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] Like '*" & txtPartNumber & "*'") > 0 Then
        
    MsgBox "Part Number Found"
    
    Set rst = CurrentDb.OpenRecordset( _
    "Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input
        
         
    While rst.EOF = False 'go through while loop as long as end of record has not been reached
        ReDim Preserve Arr(i)
        Arr(i) = rst.Fields("ParentProductNbr") 'Insert parent fields into array
        i = i + 1
        rst.MoveNext
    Wend 'end of while loop
    x = Arr
    
    
    For Each varCode In x
    
        'varCode = Replace(varCode, "-", "*")
        u = varCode
        MsgBox varCode
    
        Set dbs = CurrentDb()
        strSql = "Select * from CalculateTotal where ((Structure) Like '*" & u & "*');"
       ' Set rst = CurrentDb.OpenRecordset( _
       ' "Select * from CalculateTotal where ((Structure) Like '*" & u & "*')", dbOpenDynaset, dbSeeChanges) 'search associated fields with user input
        
        Set rst = dbs.OpenRecordset(strSql, dbOpenSnapshot)
        With dbs
        Set qdf = .CreateQueryDef("tmpProductQuery", strSql)
        DoCmd.OpenQuery "tmpProductQuery"
        .QueryDefs.Delete "tmpProductQuery"
        End With
        dbs.Close
        qdf.Close
        
        
    Next varCode
Else
    
    MsgBox "Part Number Does Not Exist"
        
End If
 
End Sub
 
None of the tables in your previous Demo DB seemed to match.
The dbo_ in front of a table name indicates a linked table.
I added some very basic error trapping, display code. Perhaps you can identify where the error is at and what the errors are?


Code:
Option Compare Database
Option Explicit

Sub PartNumberSearch()

Dim txtPartNumber As Variant
Dim rst As Recordset 'declaration of variable
Dim Arr() As String 'declaration of variable
Dim i As Integer 'declaration of variable
Dim x As Variant 'declaration of variable
Dim varCode As Variant 'declaration of variable
Dim u As Variant
 

Dim dbs As Database
Dim qdf As QueryDef
Dim strSql As String
Dim mySQL As String
On Error Resume Next
txtPartNumber = InputBox("Enter Part Number:")
' Substituted the table name from the Demodb to test this - change RoicStructure and table names back to your linked tables here:
If DCount("serialcardid", "RoicStructure", "[serialcardid] Like '*" & txtPartNumber & "*'") > 0 Then
' Like '*" & ssWord & "*'
    If Err.Number = 3078 Then ' dcount can't find table dbo_ProductStructure
        Err.Clear
        MsgBox "dbo_ProductStructure not found"
        Exit Sub
    End If
    If Err.Number <> 0 Then ' Some other error
        MsgBox Err.Description, vbOKOnly, "Error Number is: " & Err.Number
        Exit Sub
    End If

    MsgBox "Part Number Found"
    mySQL = "Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'"
    ' mySQL returns (with input box entry of 565) Looks OK   Select * from dbo_ProductStructure where ChildProductNbr Like '*565*'
    Set rst = CurrentDb.OpenRecordset(mySQL) 'search associated fields with user input
    If Err.Number <> 0 Then ' Some other error
        MsgBox Err.Description & " the mySQL String is: " & mySQL, vbOKOnly, "Error Number is: " & Err.Number
        Exit Sub
    End If
    
    While rst.EOF = False 'go through while loop as long as end of record has not been reached
        ReDim Preserve Arr(i)
        Arr(i) = rst.Fields("ParentProductNbr") 'Insert parent fields into array
        i = i + 1
        rst.MoveNext
    Wend 'end of while loop
    x = Arr
    For Each varCode In x
        'varCode = Replace(varCode, "-", "*")
        u = varCode
        MsgBox varCode
    
        Set dbs = CurrentDb()
        strSql = "Select * from CalculateTotal where ((Structure) Like '*" & u & "*');"
       ' Set rst = CurrentDb.OpenRecordset( _
       ' "Select * from CalculateTotal where ((Structure) Like '*" & u & "*')", dbOpenDynaset, dbSeeChanges) 'search associated fields with user input
        Set rst = dbs.OpenRecordset(strSql, dbOpenSnapshot)
        With dbs
        Set qdf = .CreateQueryDef("tmpProductQuery", strSql)
        DoCmd.OpenQuery "tmpProductQuery"
        .QueryDefs.Delete "tmpProductQuery"
        End With
        dbs.Close
        qdf.Close
    Next varCode
Else
    MsgBox "Part Number Does Not Exist"
End If
 
Exit Sub
' error code here
 
End Sub
For Access 2010 - the extra parameter seemed to help if this is where your error is occuring.
Set rsMyRecordset = CurrentDb.OpenRecordset(mySQL, dbOpenSnapshot, dbReadOnly)
 
Last edited:
yea. I know. that's because I was working on an example DB...right now i'm working on the company's real data (which wouldn't have been possible to send over). Thing is my code found the structure values in the example data but when I put the same exact code in the real DB it's not finding anything! i'm going crazy!
 
At this point i'm thinking if it's even possible to find the structure values in linked tables...i would assume it's possible but by the way i've been testing this, nothing seems to work. do you have any ideas? I will take a look at the code you sent me right now and see if it can be helpful with what i'm working on right now. thanks.
 
Also the main problem that's making me go insane is that in the example DB, when i searched for structure using this line of code:


Code:
  For Each varCode In x
    
        varCode = Replace(varCode, "-", "*")
        u = varCode
        MsgBox varCode
    
        Set dbs = CurrentDb()
        
        If DCount("Structure", "dbo_RoicStructure", "[Structure] Like '*" & u & "*'") > 0 Then

It found it! But when I use it on the real data, using the same exact line of code! It says 'structure not found' or something like that. My brain is exhausted from thinking about this and can't find any solution to this problem...
 
a strucute in my example DB (where the code worked perfectly!) looks like this:

ECX29-U2-N3-D7

A structure from the real data (where it does not work) looks like this:

GSX1-A2-B9-C56-D2-E1-F1-J1-9A1-9C1-9D1-9E18

They look exactly the same! except maybe one is longer than the other. but the code should work just as well right?
 
Sorry i'm spamming this page, but i should also add that everything works before the structure search and after the structure search (for the most part). the only thing that is messing up the result is the structure search...
 
To troubleshoot this expression, try the following:
Press CTRL+G to open the Immediate window.
First - Test the function with no criteria. To do so, type the following in the Immediate Window, and then press ENTER:
? DLookup("[YourFieldName]", "YourTableName")


Try to get the criteria fields to work independently by hard coding values into the expression. To do so, type each of the following lines in the Immediate Window, and press ENTER after each:
? DLookup("[YourFieldName]", "YourTableName", "[YourFieldName] = 'YourString'")
You can see that an orderID can be found in the Orders table where EmployeeID = 2
-and-
? DLookup("[OrderID]", "Orders", "[EmployeeID] = 2)
As you have correctly set the single quotes for a string. Numbers don't need quotes, strings do. So you got that correct.

These should work in the Immediate window so you can test it.
The ? is a shorthand for Print - Try this in the Immediate Window.
It would appear that the Input box can take 255 char, but the method above is the best way to see if the character is too long.
Don't fret, the DLookup can easily be replaced with a Where clause in a SQL statement.
 
Thanks a lot RX. I will give it another shot and i'll let you know. You were very helpful.
 
That is a good attitude.
Try to be more specific on what works and what doesn't work.
If you will learn to use the Immediate Window (a.k.a. Debug window) it will speed things up for you many times. Just knowing you can run the DLookup directly in the Immediate Window with out running code - will put you ahead much faster.
 
Hi, I'm trying to put the results of my search into a table named ResultsTable and I'm getting the search results from a table named CalculateTotal. Below is my code:


Code:
        Set rst = CurrentDb.OpenRecordset(" INSERT INTO ResultsTable " _
        & "SELECT * " _
        & "FROM [CalculateTotal];")

It keeps giving me error 3219 Invalid Operation. Do you know why? Thanks
 
Just a guess: a recordset cannot be opened on an INSERT query

A form to a Table requires the Execute Method to insert the value.
Is CalculateTotal a single field?

Have you considered an Append Query? It would require a SQL statement of the CalculateTotal - then it appends to: A table name (ResultsTable) and you get to match up the fields. Again, you could use the execute method.

CurrentDb.Execute "<Either a Query name or a SQL statement>;", dbFailOnError

Or: just in case you prefer more lines of code
DoCmd.Setwarnings False
DoCmd.OpenQuery ("AQueryName")
DoCmd.Setwarnings True

Just in case you have a question about the difference between the two:
http://allenbrowne.com/ser-60.html
This site is better than I can possible explain. :)
 
Hi RX! Thank you for the reply! CalculateTotal is a query with 6 columns. I am only interested in 3 of them: OrderNumber, Structure, and Item. I want to transfer the results of these columns into table ResultsTable...not quite sure how to do that...

I tried :

Set rst = CurrentDb.Execute("INSERT INTO ResultsTable (Structure1, OrderNumber1, ItemNumber1) SELECT (Structure, OrderNumber, Item) FROM CalculateTotal WHERE Structure= '" & u & "'", dbFailOnError)


But it's now giving me a compile error...
 
Okay. I took a look at the link you provided and I've edited what I've had to this:
Code:
Dim strSqll As String
Dim db As DAO.Database
                    
strSqll = ("INSERT INTO ResultsTable (Structure1, OrderNumber1, ItemNumber1) SELECT (Structure, OrderNumber, Item) FROM CalculateTotal WHERE Structure= '" & u & "'")
                    
db.Execute strSqll, dbFailOnError
Set db = Nothing
and now i get Run-time error 91: object variable or with block variable not set.

any ideas? Thanks a lot.
 
when i get that error, it high lights:

db.Execute strSqll, dbFailOnError
 
You need to set the database object (db) to a database like:
Code:
Set db = CurrentDb
 
Thanks for helping them out.. I was out yesterday at the airport getting my son off to his engineering internship on a cargo ship. He flew to Amsterdam to connect in Germany. He will be sailing through and porting in Denmark too along with St Petersburg and other Northern European ports.
 
Thank you :) I got it figured out. Congrats to your son on his internship!!
 

Users who are viewing this thread

Back
Top Bottom