Comparing form fields to query 2

[cldh00]

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?

 

[formerTexan]

Hi,

Are the first, middle and last names in seperate fields. If so, create a query with the pertinent fields and set the field criteria to the controls on your form (as in: Forms!frmWhatever!txtFirstName)

Cheers,
Bill

 

[cldh00]

Yes, these are all in seperate fields (all different text boxes). Okay, I created a query named qryFirstMiddle and in the criteria for the FirstName, I put Forms!frmEmployee!FirstName and the criteria for the MiddleInitial I put Forms!frmEmployee!MiddleI. From here, what needs to be done so when I tab out of the MiddleI field, a query will run that will show all listings of, say, Sally F's in the company? If my question is unclear, I'll try to explain better. Thanks!

 

[formerTexan]

Docmd.openquery or docmd.runsql depending on whether you are using a named query or a SQL string. Your first post indicated you wanted to use the OnExit event. BeforeUpdate (before any records are changed) might be more appropriate.

I suggest creating a recordset from the query results in code and then

If rs.recordcount > 0 then
' notify the user
End If

You will have to decide how you want to present any notice to the user and how to control the user's subsequent actions.

But all of that said, I am puzzled as to how this will prevent duplicate entries. Will the user somehow know that Sally F Johnson and Sally F Anderson are the same people? What if there is more than one employee named Sally F. I think you need a more explicit way of tracking any name changes.

Cheers,
Bill

 

[cldh00]

Thanks for the tip on the BeforeUpdate procedure. That is a good point. Below is the code I have wrote for the BeforeUpdate procedure. I have a few bugs in it yet. For example, if there isn't a first name and middle initial, the query still pops up and warns that "This name is already on file", however, that name doesn't exist yet. Also, I would like the query to open strictly to just the Sally F's in the database, not starting with the A's (Abby B., for example). Could you point me in the right direction?

Private Sub MiddleI_BeforeUpdate(Cancel As Integer)
Dim rs As Recordset, db As 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

This procedure will help to prevent duplicate entries because I (the IT staff) will be using it. If we see that the first name and middle initial already exist, we will look at our Active Directory listing of employees and check their username, since when their last name changes, their username doesn't!

 

[formerTexan]

Hello,

Your code will fly as it is. From a standpoint of coding, it is generally recommended style to declare each variable on a seperate line, particularly if the variables are different types. Also also a good idea to remove any coding ambiguities and declare objects as either DAO or ADO. Speeds things up a bit.

Dim db as DAO.database
Dim rs as DAO.database

You may wish to consider passing the query info to a listbox or form rather than just opening the query, since queries don't provide any hooks for control of user interaction.

And on a last note, perhaps consider assigning a unique employee code or number. Ghastly reductionism, but in the long run will help keep employees sorted out.

CHeers,
Bill

 

[cldh00]

I don't suppose you could show me how to pass the query info to a listbox or form? Are you saying that there is no way for a query to just list the assigned info (like starting with Sally F., instead of the entire listing starting with Abby B.)?

Also, I put the criteria back into the query (Forms!frmEmployee!FirstName and Forms!frmEmployee!MiddleI) and when I tab out of the MiddleI field, I get an error that reads
"Run time error 3061: Too few parameters. Expected 2."
And the line
Set rs = db.OpenRecordset("qryEmployeeFirstMiddle")
is highlighted. Ideas?

 

[formerTexan]

Looks like you are making progress. You can pass the query by setting the rowsource or recordsource of the listbox or form and that can also be done from the BeforeUpdate event.

Now to error 3061. Sorry, I should have anticipated it as I have been bit often enough. The parameter values have to be supplied to the query and the following code is the most flexible way of doing so.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Dim iCt As Integer
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryEmployeeFirstMiddle")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
If Not rs.EOF And Not rs.BOF Then
rs.movelast
If rs.recordcount > 1
' Do something
End If
End If

' explicitly clean up
qdf.Close
rs.Close
db.Close
Set qdf = Nothing
Set rs = Nothing
Set db = Nothing

Cheers,
Bill

 

[cldh00]

Bill-

Thanks a lot for all of your help! The form is working great and is exactly what I wanted! I really appreciate it!

Chad