Comparing form fields to query

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

Guest

I have an Access form in which you input employee info. I enter a first name
(in a text box), last name (in a text box), and a middle initial (in a text
box). I want to edit code for the "On Exit" of the middle initial text box to
go and compare to a query in which the first name and the middle initial are
the same. If they are the same, I want a query to be pulled up in which it
lists all people with that first name and middle initial. For example, all of
the Sally F employees. If it doesn't find any, that I want to just continue
entering info into the form. This is done to assure duplicate entries aren't
made in case an employee's last name changes. (I don't want a Sally F Johnson
and a Sally F Anderson in the database, if they are the same employee, but
changed their last name). I need some sort of If..Else statement but am
stumped from there. Any suggestions?
 
I created a query called "qryFirstMiddle" in which it shows all First name
and Middle initials in my company. Then, in the MiddleI on exit, I wrote what
is below. After I tab out of the MiddleI textbox, I get the following error:
"Run time error 3078, The Microsoft Jen database engine cannot find the input
table or query 'qryFirstMiddle'. Make sure it exists and that its name is
spelled correctly" I know that it is created and spelled properly, so I'm
assuming it's an error in my code. Any ideas?

Private Sub MiddleI_Exit(Cancel As Integer)
Dim rs As Recordset, db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("qryFirstMiddle")
rs.MoveNext
If rs.RecordCount > 1 Then
DoCmd.OpenQuery "qryFirstMiddle"
End If
End Sub
 
I can't see anything in your code that would cause the error. You're sure
it's not just mispelled? Or that it's not just opening, but behind your
form?

For reference, forms opening queries in datasheet view is not the way to go.
You should base another form on the query and open that instead.
 
My fault. I was a character off. When I tab out of the Middle Initial text
box, the query does pop up, however, it pops up whether or not there is a
match between the first name and middle initial or if they are different.
Also, how do I get it so the query will only show the matches for the first
name and middle initial, and not every employee at my company? One more
thing, why isn't a form referencing a query a good idea?
 
Could you post the SQL of the query please.

It's not a good idea to give users access to tables (and queries) because
you will get the occasional person who decides that that is the best way to
edit data. That will circumvent any checks that you have in place through
forms and code. Given that it's not difficult to just create a new form
based on that query and open that instead it is much better to do it that
way.
 
The SQL for qryEmployeeFirstMiddle consists of the criteria for both fields:
FirstName = Forms!frmEmployee!FirstName
MiddleI = Forms!frmEmployee!MiddleI

Then, in my frmEmployee for the Middle I field, I have:
Private Sub MiddleI_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("qryEmployeeFirstMiddle")
If rs.RecordCount > 0 Then
MsgBox "This Name is Already on File!"
DoCmd.OpenQuery "qryEmployeeFirstMiddle"
End If
End Sub

However, when I tab out of the MiddleI field, I get the following error:
"Run time error 3061: Too few parameters. Expected 2."
And the line
Set rs = db.OpenRecordset("qryEmployeeFirstMiddle")
is highlighted. Ideas?
 
I'll take a look at that link. In the meantime, here is the SQL. Let me know
what you find! Thanks for continuing to look into this!

SELECT tblEmployee.FirstName, tblEmployee.MiddleI, tblEmployee.LastName
FROM tblEmployee
WHERE (((tblEmployee.FirstName)=[Forms]![frmEmployee]![FirstName]) AND
((tblEmployee.MiddleI)=[Forms]![frmEmployee]![MiddleI]))
ORDER BY tblEmployee.FirstName, tblEmployee.MiddleI, tblEmployee.LastName;
 
That looks fine. And it's definitely the issue in the other thread that
you're running into.


Chad said:
I'll take a look at that link. In the meantime, here is the SQL. Let me know
what you find! Thanks for continuing to look into this!

SELECT tblEmployee.FirstName, tblEmployee.MiddleI, tblEmployee.LastName
FROM tblEmployee
WHERE (((tblEmployee.FirstName)=[Forms]![frmEmployee]![FirstName]) AND
((tblEmployee.MiddleI)=[Forms]![frmEmployee]![MiddleI]))
ORDER BY tblEmployee.FirstName, tblEmployee.MiddleI, tblEmployee.LastName;


Rob Oldfield said:
Take a look at my responses in this:

http://makeashorterlink.com/?R3A222A7C

If it's popping up an incorrect list after you get over that one, then I'd
still like to see all of the SQL. View, SQL View in the design of the
query.


way
to Initial
text there
is a behind
your the
way to all
First its
name properly,
so pulled up
in to
just
Click to expand...
Click to expand...
 
Back
Top