How insert field value from current record in lookup field SQL?

[Bazsl]

I need to use the value from the Customer Number field in the current record in the WHERE clause of the SQL statement for a lookup field. The SQL is:



SELECT [Event].[Event Number], [Event].[Event Name] FROM Event WHERE [Event].[Customer Number] = <value from customer number field in current record> ORDER BY [Event Name];

What do I put in the WHERE clause in place of <value from customer number field in current record> to get the value from the Customer Number field in the current record? Thanks.

[RuralGuy]

If you are really using a "LookUp Field" then you should read this: http://access.mvps.org/access/tencommandments.htm

[Bazsl]

Thanks for the reference, however, assuming that I do have a good reason for providing the user with a combo box from which to select a value, is there a way to use the value from a field in the current row in the SQL statement in the Row Source property?

[RuralGuy]

Is it a "LookUp Field"? If so, the value you see is not really there.

[Bazsl]

I think we are having a semantic problem. I open a table in Design View. I click on a field. In the bottom pane I click on the Lookup tab. In the Row Source field on the Lookup tab is a SQL statement. I want to use the value from a field in the current row of the table as a parameter in that SQL statement. I hope that is clear.

[RuralGuy]

That is a real "Lookup Field". Having a ComboBox on a form is just fine and works as you would expect. Having them in a record can be a nightmare. Sorry.

[pbaldy]

If it's the ID you want anyway, how about:

SELECT [Event].[Event Number], [Event].[Event Name] FROM Event WHERE [Event].[Customer Number] = Forms!FormName.ComboName ORDER BY [Event Name];

[Bazsl]

Thanks Paul. I assume that Forms!FormName.ComboName will only work with a combo box on a form and not with a lookup at the table level. Is that correct? If so, I can live with that.

[Bazsl]

RG, The problem with using lookups only in forms is that someone may, for whatever reason, edit the tables directly and I would like to make that as easy and safe as possible. I should mention that this is not an app. This database if for a very small not-for-profit organization. It will be used by people who can find the on/off switch on their PCs but not much more. There will be no programming. There will be no macros if I can possibly avoid it because there will not be anyone capable of maintaining macros or code. The lookups and RI declarations will be the most sophisticated part of this database. If table level lookups are so buggy that data corruption is possible I will not use them but otherwise I am inclined to do so.

[ssanfu]

I to agree with RG.
And users should never be allowed to edit the tables directly.

[Bazsl]

There is no way to prevent a user from editing tables directly in this situation. You are the second person that is opposed to table level lookups. What is the problem with table level lookups? Just curious.

[orange]

I tried this search in google and got several responses:

ms access how to prevent the user from interacting directly with tables

Good luck