Solved: Proper use of Requery in a form

[Gingertrees]

My tblEmployee links to my tblBusiness (I already know what business the employees belong to, I'm just using the businessID to join the two records).

I want to enter the employee's info:
name / other irrelevant fields / city of employ
John Smith / ... ... ... / New York
and based on the city name, I want a multi-column dropdown of all the businessIDs, businessnames, and businesscity 's in my tblbusiness that match that city
(ideal: "close to" that city, so I could enter "New" and get "New Brunswick", "New York", etc, but I may be hoping for too much there):
201 / Acme Inc. / New York
322 / Rabbit Co. / New Yorkshire
etc

So eventually my record could appear as
John Smith / ... ... ... / New York / 201

Currently, I have a form based on a query that limits cities as follows: "WHERE [tblBusiness].[businesscity]=[tblEmployee].[city]"

But what that does is create a parameter box, which is a pain and doesn't work when I move to a new record. (e.g. Adam Jones of Portland would still have only the New York-like options appear for his record)

I believe Requery is my answer, but I don't know how to phrase that correctly. Do I put it in a function, or a private sub, or just in the After Update property? I have no idea. Please help. (BTW, for whatever reason, this crazy database that I inherited does NOT create new modules when I create a new form).

[hansup]

(BTW, for whatever reason, this crazy database that I inherited does NOT create new modules when I create a new form).

If you're using Access 2003, from the main menu choose Tools->Options. Then on the Options dialog, select the Forms/Reports tab and check the box next to "Always use event procedures".

If you're using a different Access version, look for a similar option.

Sorry I can't help with the rest of your question because I don't understand it. Good luck.

[Gingertrees]

OK, perhaps I didn't explain myself well. I've attached a sample database with just the two tables (business and employee). The problem is currently in the Lookup field in the Employee table - as this is what throws the Parameter pop-up, and doesn't update when I go to a different record.

1)I do not want the parameter box - I just want access to recognize that person one's city is New York, and therefore only show businesses located in new york in the following column. When I go to person two, I want access to recognize that city, and show only associated businesses for that person.

2)Perhaps I need to add Requery somewhere in the coding to do this? I do not know.

3)The point is to link the employees to the businesses, so all things that employees do (go to trainings, attend conferences, receive awards) can be associated with the business. So I could eventually pull numbers like "3 employees from Corporation X received the Excellence in Business award in year ___."

[hansup]

tblEmployee includes a field, BusIDqry, which is a Lookup field. That violates the second commandment of The Ten Commandments of Access.

http://access.mvps.org/access/tencommandments.htm

For a detailed explanation of why Lookup fields are evil, see

The Evils of Lookup Fields in Tables.


http://access.mvps.org/access/lookupfields.htm

After eliminating that evil, I think you want a combo box "cboBusID" on your form. Then include this procedure in your form's code module to adjust the row source for cboBusID to include only those business cities which match the current employee's EmpCity. (Note that I renamed the text box for EmpCity to txtEmpCity)

Private Sub AdjustBusIdCombo()
Dim strSql As String
strSql = "SELECT b.BusID, b.BusName, b.BusCity, b.BusState" & vbCrLf & _
"FROM tblBusiness AS b" & vbCrLf & _
"WHERE b.BusCity = """ & Me.txtEmpCity & """;"
'Debug.Print strSql
Me.cboBusID.RowSource = strSql
End Sub
Then call that procedure in your form's On Current event.

Private Sub Form_Current()
AdjustBusIdCombo
End Sub
You probably want to do the same thing whenever the user changes EmpCity for the current employee.

Private Sub txtEmpCity_AfterUpdate()
AdjustBusIdCombo
End Sub

[Gingertrees]

Thanks for the info Hansup. I knew Lookup Wizards were secretly evil, but I didn't know the VBA to take it in the Combo direction. I'll bookmark this for future use, too :)