How to use extract a data table from from

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In table, i have a table name call "talbe1" and one of the field is called
"lot"
Now in form, i got a form name call "form1", i am writing in program as below:

Option Compare Database
Option Explicit

Private Sub lot_AfterUpdate()
If table1.lot = Me.lot Then
MsgBox "Hi"
End If

The result prompt me "table1" has to declare!
how can i solve this problem? Do i need to do something in form before
search a data from table in form? thank you very much
End Sub
 
Thanks for your help

Actually, i want to compare two fields. one is from table. the other is from
form. My idea is a person who type in a data in text field in that form. and
then press enter, it will run the program to compare to that table. If a
table has a data is equal to the text field data. It will prompt "hi".
 
I'm first going to assume that your form is unbound (you've not connected it
to the table). I'm also going to assume that your table will always contain
one record. (if we needed to find a specific record, we'd need to do
something like a find on it as well).

I'm using DAO code as my example here - apologies if you're an ADOer. Also,
this probably isn't the most elegant way, so apologies there also.

on the after update, use
Private Sub lot_AfterUpdate()
'declare variables
dim db as dao.database, rst as dao.recordset
'set database
set db=currentdb
'set recordset
set rst = dbopenrecordset("table1")

with rst
'go to first record
.movefirst
'compare it against the form value and act accordingly
if me!lot = rst!lot then
msgbox "hi"
end if
'close the recordset to tidy up
.close
end with
end sub
 
Thanks for your greatful help. I feel so sorry. i still have a problem on
this side. Now i have changed the coding to

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 still prompt ".Execute" has problem. Would you help me again thanks.
 
I'm not familiar with dbobject.execute, I'm afraid, but I can give you a
couple of pointers.

1) Your select string should be "SELECT * FROM tblReceiving WHERE Lot_No = "
& Me![Lot_No]
(it's actually better if you specify the fields you want, rather than using *)

2) If Lot_No is a text field, you'll need to wrap it in quotes ... WHERE
Lot_No = " & chr(34) & me![Lot_No] & chr(34)

3) I don't know if it's your abbreviation or a mis-type, but you've got T L
B Receiving. I would normally have used tbl for a prefix - if it's meant to
be tlb, then fine, but I just thought it was a potential typo.
 
Right - have just had a play with an access .adp and your code works.
If your database is an MDB file, I would recomment doing it the way I
suggested earlier.
If your database is an .adp file, the following is fine (apart from the
'where' bit I mentioned before) - and don't forget to close your recordset at
the end.

Dim dbObject As Connection
Dim rst As Recordset
Set dbObject = CurrentProject.Connection

Set rst = dbObject.Execute _
("SELECT [Rcv Date], [Item No] FROM TblReceiving WHERE Lot_ID= " &
me![Lot_ID])

With rst
Me![Rcv Date] = ![Rcv Date]
Me![Item No] = ![Item No]
End With
rst.Close
End Sub
 
Back
Top