Using SQL inside a Form

Hello,

I have a form that has two fields in it (FILE NUMBER and CASE NUMBER) - I 
would like to set up the form so that when the form loads, it queries the 
PERSON table for those two parameters, then retrieves the First and Last 
name from the PERSON table, deletes all the records from another table 
called PERSON_DROPDOWN, and then inserts the First and Last name values into 
that PERSOn_DROPDOWN table. Any ideas? 

How do the values get into that form when it loads?  Are you sure it's a 
LOAD event that you want or the CURRENT event as you move from record to 
record?

If the data is already in text boxes on the form, then you can use an event 
such as CURRENT to run some code that does what you want.  For example, you 
could create SQL strings to do what you want and then invoke them in the 
code using docmd.runsql sqlstring.  You could also get fancy and create 
queries with parameters that point back to the form, and then just invoke 
the queries through docmd.openquery queryname.

If you're truly talking about the data being there at load, before any data 
actually appears on the form, then you would pick up the FILE NUMBER and 
CASE NUMBER through code in the LOAD event (e.g., passed as open arguments, 
picked up from a global variable).

Given that you are calling this the PERSON_DROPDOWN, I suspect that you're 
using it as the controlsource for a dropdown (e.g., combobox) on this form. 
If that's the case, you don't have to do any of that and you don't need the 
PERSON_DROPDOWN table.  Instead, you just need to make the controlsource of 
the dropdown control a string that extracts from table PEOPLE based on the 
values of the FILE NUMBER and CASE NUMBER.

For example, if the controls on your form are called txbFILE_NUMBER and 
txbCASE_NUMBER, then the controlsource for the dropdown would be

"select * from PERSON where [FILE NUMBER] = '"  & ME.txbFILE_NUMBER  & "' 
AND [CASE NUMBER] = '" & ME.txbCASE_NUMBER & "'"

NOTE:  I'm assuming that FILE NUMBER and CASE NUMBER are text fields and not 
number fields such as integers.  If they are number fields, you would leave 
out the single apostrophes (').

If you do that, the only people who will show up in the dropdown are those 
in table PERSON who are associated to the FILE NUMBER and CASE NUMBER.  This 
would be much faster than what you are now doing.


-------------
Chaos, panic, & disorder - my work here is done.

Oooops

Minor error

If you're setting the control source in the combobox itself, you would not 
have "me."  It would just be


You would have the "me." if you were creating a string in event code.

-------------
Chaos, panic, & disorder - my work here is done.

The default in Access is optimistic locking, which means that the PERSON 
table is not locked, but rather conflicts in updates are detected.  Since 
you'll only be reading it, it won't be affected by having it open for 
update.

If it is actually locked, is it locked for update or actually locked for 
read?  More often than not, even when a table is locked, it's locked for 
update and not for read.  Check and see if the table PERSON is truly locked 
when you have that form open.   If it is truly locked, you have to ask 
yourself why.  It sounds as if you're in a situation where any one 
individual user is the only user that should be touching that particular 
PERSON/FILE NUMBER/CASE NUMBER at a time.

Be that as it may, to deal with your PERSON_DROPDOWN table,

the delete of PERSON_DROPDOWN would be two lines of VBA code

ssql = "delete from person_dropdown"
docmd.runsql ssql

That deletes all the records from person_dropdown.

Then you would want a basic extract/insert would be two lines of VBA code

ssql = "INSERT INTO PERSON_DROPDOWN (LAST, FIRST) SELECT LAST, FIRST FROM 
PERSON WHERE  [CASE NUMBER] = casenumber AND [FILE NUMBER] = filenumber"
docmd.runsql ssql

Replace the lower case casenumber and filenumber with the variable or 
control name that has the case number and file number.  Don't forget the 
single apostrophes if it's text data.

What you're doing is putting a SQL command in the string (ssql) and then 
using that string as the parameter to the docmd.runsql command.

-------------
Chaos, panic, & disorder - my work here is done.