why does it do that !?

[netscamp]

the following code works, no errors, but the query it creates doesn't open, instead, when opened it puts up a dialog box asking for a value. I am trying to tell it, in line 8, that the criteria value is the contents of a text box in the subform which contains the button that fires the code.
I numbered the lines for this message.



1.Private Sub Command13_Click()
2.Dim dbs As DAO.Database
3.Dim qdf As DAO.Querydef
4.Dim strSQL As String
5.Dim lkval As String
6.Dim jones As String
7.Set dbs = CurrentDb
8.lkval = PO_number.Value
9.jones = "poqry" & Str(Int(Now))
10.strSQL = "SELECT transaction_num, po_num, po_date FROM po_table WHERE po_num = lkval;"
11.Set qdf = dbs.CreateQueryDef(jones, strSQL)

 

[RoyVidar]

Hi!

I see the 'lkval' thingie at the end of the statement, is a declared variable, which also gets a value assigned. Then you need to make sure what goes in to the sql string is the value of the variable and not the variable name -> text concatination.

Because lkval is a string, you also need text qualifiers

strsql= "SELECT transaction_num, po_num, po_date FROM po_table WHERE po_num = '" & lkval & "'"

HTH Roy-Vidar

 

[RoyVidar]

BTW - if you're doing some Access developing, you might also consider visiting;

Access VBA: forum705 queries: forum701 reports: forum703 tbl/rel: forum700 Misc: forum181

 

[netscamp]

thank you for the advice.
I tried, as shown below... see line [*] still, when I try to open the query, ACCESS asks me for a value with a dialog box.
The variable "LKVAL" is being fed properly, as it shows up in the name of the query as expected.

Private Sub Command13_Click()
Dim dbs As DAO.Database
Dim qdf As DAO.Querydef
Dim strSQL As String
Dim lkval As String
Dim jones As String
Dim james As String
james = Format(Now, "mdyyhss&quot
Set dbs = CurrentDb
lkval = PO_number.Value
jones = "poqry" & lkval & james
[*] strSQL = "SELECT * FROM po_table WHERE po_num = " & lkval & ""
Set qdf = dbs.CreateQueryDef(jones, strSQL)
End Sub

 

[netscamp]

P.S. !

I got it! I used chr(34) in the string instead of typing in quotation marks.

Thank you very much for your help.

Robert Winters

 

[DrBowes]

Alternatively note the use of single quotes in Roy's posting.