Problem of executing the code in Access

[Guest]

Private Sub Lot_No_AfterUpdate()

'declare variables
Dim dbObject As Connection
Dim rst As Recordset

'set database
Set dbObject = CurrentProject.Connection

'set recordset
Set rst = dbObject.Execute _
("SELECT Lot_No FROM tlbReceiving " & _
"= " & Me![Lot_No])

With rst
Me![Rcv Date] = ![Rcv Date]
Me![Item No] = ![Item No]
End With
End Sub

But it prompt ".Execute" has problem. Would you help me thanks.

 

[Van T. Dinh]

The problem is in the SQL String. The construction will result in

SELECT Lot_No FROM tlbReceiving = {value of Me![Lot_No]}

which is an invalid SQL since there is no left operand for the criterion.

 

[Guest]

is it changed to this?

Set rst = dbObject.Execute(SELECT Lot_No FROM tlbReceiving = Me![Lot_No])

But it still prompt me error. pls help me again. do i need change Private to
Public too?

The problem is in the SQL String. The construction will result in

SELECT Lot_No FROM tlbReceiving = {value of Me![Lot_No]}

which is an invalid SQL since there is no left operand for the criterion.

--
HTH
Van T. Dinh
MVP (Access)

Private Sub Lot_No_AfterUpdate()

'declare variables
Dim dbObject As Connection
Dim rst As Recordset

'set database
Set dbObject = CurrentProject.Connection

'set recordset
Set rst = dbObject.Execute _
("SELECT Lot_No FROM tlbReceiving " & _
"= " & Me![Lot_No])

With rst
Me![Rcv Date] = ![Rcv Date]
Me![Item No] = ![Item No]
End With
End Sub

But it prompt ".Execute" has problem. Would you help me thanks.

 

[Guest]

I have changed to
dbObject.Execute("SELECT Lot_No FROM tlbReceiving = Me![Lot_No]")
and the SQL String is ok? Right?

But it still prompt ".Execute" sau complied error: must variable. And here
is changed to yellow color "Private Sub Lot_No_AfterUpdate()", and ".Execute"
change to red color. Thank you very much. Pls help me

The problem is in the SQL String. The construction will result in

SELECT Lot_No FROM tlbReceiving = {value of Me![Lot_No]}

which is an invalid SQL since there is no left operand for the criterion.

--
HTH
Van T. Dinh
MVP (Access)

Private Sub Lot_No_AfterUpdate()

'declare variables
Dim dbObject As Connection
Dim rst As Recordset

'set database
Set dbObject = CurrentProject.Connection

'set recordset
Set rst = dbObject.Execute _
("SELECT Lot_No FROM tlbReceiving " & _
"= " & Me![Lot_No])

With rst
Me![Rcv Date] = ![Rcv Date]
Me![Item No] = ![Item No]
End With
End Sub

But it prompt ".Execute" has problem. Would you help me thanks.

 

[David C. Holley]

The SQL statement is invalid. If you're selecting records based on a
criteria you'll a need a statement similar to

"SELECT Lot_No From tlbReceiving WHERE Lot_No = 34;"
or using a form field
"SELECT Lot_No From tlbReceiving WHERE Lot_No = " & Me![Lot_no] & ";"

Also, what are you trying to accomplish? If you're trying to load form
fields with values from the recordset, I would suggest using DLookup()'s.

 

[Van T. Dinh]

Your criteria is constructed incorrectly.

Normally, an SQL String has 3 distinct clauses like:

SELECT {Selection list}
FROM {Table list}
WHERE {criteria}

Criteria clause can be an single Boolean expression like:

Field1 = 1

or a combined Boolean Expression like:

(Field1 = 1) And (Field2 = 2)

In your case, you joined the FROM Clause and the WHERE clause togenther
omitting the keyword WHERE. Note that the Table is an object and it doesn't
have a value so you *can't* compare it with a value.

 

[RD]

I think the problem is a little more fundamental than that. Isn't the Execute
method only for action queries? He's trying to execute a Select query.

 

[Van T. Dinh]

The Execute Method of an ADO Connection can use a SELECT Query to return a
ADO Recordset.

It is described in Access VB (ADO) Help.

 

[RD]

The Execute Method of an ADO Connection can use a SELECT Query to return a
ADO Recordset.

It is described in Access VB (ADO) Help.

Ah yes, I see:
"If the CommandText argument specifies a row-returning query, any results that
the execution generates are stored in a new Recordset object."

I haven't used ADO in a long time so I'm kind of DAO-centric these days. (daze?)

Thanks for straightening me out,
RD