Using SQL inside a Form

[Charles]

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?

 

[JP]

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.

 

[JP]

Oooops

Minor error

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

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

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

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

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.

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?

 

[Charles]

Yes, thanks- I am using it as a source for the combobox - the problem is
this - while this form is open, I will have to have another form open that
edits records on the PERSON table - and using the PERSON table as the source
for the combo boxes isnt an option as it's locking the records for editing,
thereby preventing the other form from making changes to the PERSON table.

Hence my problem.

If I could use another source for the comboboxes than the PERSON table (say,
truncatting and populating another table with just the entries I need based
on the FILE NUMBER and CASE NUMBER, then I shouldnt run into this problem.
I'm just stuck on how to do the VB code in the form to truncate, select and
insert the records I need.

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.

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?

 

[JP]

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.