Find another record and get data from it

[krispetrie]

In a form I want to enter a name and, on exit, I want to do a find on that name, first in the current table and if not found in a different table. Once a record is found in either table, I want to copy data to the current table record. If no record is found, just continue and enter manually.

 

[charityg]

Are you familiar with DAO, and how to set up recordsets? If not, email me and I'll tell you how to solve your problem.

 

[charityg]

dim db as database
dim strName as string
dim rstcurr, rstdiff as recordsets
set rstcurr=db.openrecordset
("currenttablename",dbopendynaset)
set rstdiff=db.openrecordset
("differenttablename", dbopendynaset)
strname="Name = '" & Me!Name & "'"
with rstcurr
.movelast
.findfirst strname
me!field1=!field1
me!field2=!field2
me!field3=!field3
If .nomatch then
end with
with rstdiff
.movelast
.findfirst strname
me!field1=!field1
me!field2=!field2
me!field3=!field3
if .nomatch
end with
msgbox "Name not found"
endif
endif
This won't work if your using a table to enter information. You need to set up a form based on the current table. Me!Name is the field name that contains the data. Basically what you are doing is setting up recordsets to search based on the two search tables. Then you implement findfirst using the current name on the form. If not found in the first table, you search the second table and if the value still isn't found you are given a message box. If the name is found, it assigns the found values to the fields on the form.