Get form VBA variables into query where clause

[dcavaiani]

I understand I cannot easily run a SELECT * WHERE ... query in VBA ?

I want to run a simple Select query but use variables from the Form vba for the WHERE clause selections.

 

[pr2-eugin]

You can, you need a Recordset object that will hold the data from your SELECT statement. Something like,

Dim tmpRS As DAO.Recordset
Dim tmpNo As Long

tmpNo = 10

Set tmpRS = CurrentDB.OpenRecordset("SELECT * FROM yourTable WHERE yourField = " & tmpNo)

 

[dcavaiani]

I like it and am trying it. How do I set the query where clause to compare if it is an alphanumeric comparison?

 

[dcavaiani]

I have the code set correctly now but when it executes the sql - nothing pops up and no other indications that is ran the code - which it did?

 

[pr2-eugin]

What do you want it to do?

 

[dcavaiani]

What do you want it to do?

Pop up the query results in read-only mode

 

[namliam]

Paul's sample will read the query into memory.

You want to create an object to show on screen, which to be honest is a bad idea.
Data should be shown to a user on forms only, here you can control read-only etc.... make it look pretty, etc....

However..... Simply make any query, save it as YourQueryName ... then this should do the trick for you.

Currentdb.Querydefs("YourQueryName").sql = "select..." 
docmd.openquery "YourQueryName"

 

[gemma-the-husky]

then try

docmd.openquery ("SELECT * FROM yourTable WHERE yourField = " & tmpNo)

 

[dcavaiani]

Paul's sample will read the query into memory.

You want to create an object to show on screen, which to be honest is a bad idea.
Data should be shown to a user on forms only, here you can control read-only etc.... make it look pretty, etc....

However..... Simply make any query, save it as YourQueryName ... then this should do the trick for you.

Currentdb.Querydefs("YourQueryName").sql = "select..." 
docmd.openquery "YourQueryName"

I tried that, but do not know how to get the VBA variables into the stored SQL WHere comparison ??

 

[pr2-eugin]

then try

docmd.openquery ("SELECT * FROM yourTable WHERE yourField = " & tmpNo)

Does this actually work?

 

[pr2-eugin]

I tried that, but do not know how to get the VBA variables into the stored SQL WHere comparison ??

Show the code you actually have used so far.

 

[dcavaiani]

Does this actually work?

no - does not

 

[dcavaiani]

Show the code you actually have used so far.

Private Sub Review_Invoice_Click()
Dim tmpRS As DAO.Recordset
Dim customer2 As Variant
Dim PurchaseDate2 As Variant
Dim VendorName2 As Variant
Dim VendorInvoice2 As Variant
customer2 = customer
PurchaseDate2 = PurchaseDate
VendorName2 = VendorName
VendorInvoice2 = VendorInvoice
' DoCmd.OpenQuery ("SELECT * FROM materials WHERE customer = """ & customer2 & """")
Set tmpRS = CurrentDb.OpenRecordset("SELECT * FROM materials WHERE customer = """ & customer2 & """")
End Sub

 

[pr2-eugin]

Try this,

Private Sub Review_Invoice_Click()
    Dim tmpRS As DAO.Recordset
    Dim customer2 As Variant
    Dim PurchaseDate2 As Variant
    Dim VendorName2 As Variant
    Dim VendorInvoice2 As Variant
    
    customer2 = customer
    PurchaseDate2 = PurchaseDate
    VendorName2 = VendorName
    VendorInvoice2 = VendorInvoice
    
    On Error Resume Next
    Currentdb.QueryDefs.Delete "YourQueryName"
    Currentdb.CreateQueryDef("YourQueryName", "SELECT * FROM materials WHERE customer = " & Chr(34) & customer2 & Chr(34))
    On Error GoTo 0
    DoCmd.OpenQuery "YourQueryName"
End Sub

 

[dcavaiani]

Try this,

Private Sub Review_Invoice_Click()
    Dim tmpRS As DAO.Recordset
    Dim customer2 As Variant
    Dim PurchaseDate2 As Variant
    Dim VendorName2 As Variant
    Dim VendorInvoice2 As Variant
    
    customer2 = customer
    PurchaseDate2 = PurchaseDate
    VendorName2 = VendorName
    VendorInvoice2 = VendorInvoice
    
    On Error Resume Next
    Currentdb.QueryDefs.Delete "YourQueryName"
    Currentdb.CreateQueryDef("YourQueryName", "SELECT * FROM materials WHERE customer = " & Chr(34) & customer2 & Chr(34))
    On Error GoTo 0
    DoCmd.OpenQuery "YourQueryName"
End Sub

Syntax error

 

[gemma-the-husky]

Does this actually work?

you are right.

it ought to work, hadn't it?

I got it to work by creating a querydef, (as you did) but then I end up adding a querydef to the database, which you then have to delete which is a pain.

 

[pr2-eugin]

Syntax error

Explain. Is the Data Type of the column number?

which you then have to delete which is a pain.

Not really

 

[namliam]

Syntax error

Funny :banghead:

 

[JHB]

In the code pr2-eugin posted add the below marked in red:

Private Sub Review_Invoice_Click()
    Dim tmpRS As DAO.Recordset
    Dim customer2 As Variant
    Dim PurchaseDate2 As Variant
    Dim VendorName2 As Variant
    Dim VendorInvoice2 As Variant
    [B][COLOR=Red]Dim AQueryDef As QueryDef[/COLOR][/B]
  
    customer2 = customer
    PurchaseDate2 = PurchaseDate
    VendorName2 = VendorName
    VendorInvoice2 = VendorInvoice
    
    On Error Resume Next
    CurrentDb.QueryDefs.Delete "YourQueryName"
    [B][COLOR=Red]Set AQueryDef =[/COLOR][/B] Currentdb.CreateQueryDef("YourQueryName", "SELECT * FROM materials WHERE customer = " & Chr(34) & customer2 & Chr(34))
    On Error GoTo 0
    DoCmd.OpenQuery "YourQueryName"
End Sub

 

[dcavaiani]

JHB - Just tried that and looks to work PERFECTLY!!! Thanks!