Update sub before record loads

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi Groupies:

My database is keeping track of inventory. My main form lists the customer,
the subform shows what they have rented and allows them to select items that
are currently available.

My problem is I need to have my list of what is available update
immediately. The preference would be after an item is selected in the
subform but even after moving between customers is OK. At the moment, I have
a make table query run in the main forms On Current event so that my subform
has an up to date list of available data but because the make table query is
using the datasource for the subform I get the message that the 'table can
not be locked because it is in use' and my data can not be updated.

Is there a better event that I should have this query run in or how can I
work around this?

Thanks for your useful suggestions....
 
Hi CJ

I think the make table query is way overkill.

Assuming you have gone with my suggestion from yesterday of adding a
DateReturned field to your Assignments table, you can base your "Available"
subform on a filtered recordset from your Items table:

Select * from Items
where NOT Exists
(Select AssignmentID from Assignments
where Items.ItemID=Assignments.AssignmentsID
and DateReturned is Null);

Of course you will need to tweak this for your own table and field names.

To list only those items which are currently "out", remove the "NOT".

Then a simple Requery of the subform will refresh the list.
 
where Items.ItemID=Assignments.AssignmentsID

Oops! Of course this should be:

where Items.ItemID=Assignments.ItemID

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham Mandeno said:
Hi CJ

I think the make table query is way overkill.

Assuming you have gone with my suggestion from yesterday of adding a
DateReturned field to your Assignments table, you can base your
"Available" subform on a filtered recordset from your Items table:

Select * from Items
where NOT Exists
(Select AssignmentID from Assignments
where Items.ItemID=Assignments.AssignmentsID
and DateReturned is Null);

Of course you will need to tweak this for your own table and field names.

To list only those items which are currently "out", remove the "NOT".

Then a simple Requery of the subform will refresh the list.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...
 
Well, well, well, it really pays to know your SQL. I have never had to
create a query with 2 Select and 2 Where statements, very cool. The whole
NOT issue was the reason that I had to go the long way and ended up with a
make table query...I wasn't impressed with it either. If I had been able to
work with Is Not Null or Not Is Null I would have been fine.

However, you have once again proven that asking for assistance is much more
efficient than banging your head against the wall.

For those that might be interested, here is the SQL for my query:

SELECT tblInventory.*, tblInventoryTrackingHistory.dtmDateOut,
tblInventoryTrackingHistory.dtmDateIn,
tblInventoryTrackingHistory.CurrentRig
FROM tblInventory INNER JOIN tblInventoryTrackingHistory ON
tblInventory.[SKU Number] = tblInventoryTrackingHistory.[SKU Number]
WHERE (((Not Exists (select [SKU Number] from tblInventoryTrackingHistory
WHERE tblInventory.[SKU Number] = tblInventoryTrackingHistory.[SKU Number]
AND [dtmDateIn] Is Null))=False));

To see all of the inventory that is currently available, I change the JOIN
and remove the NOT so it becomes:

SELECT tblInventory.*, tblInventoryTrackingHistory.dtmDateOut,
tblInventoryTrackingHistory.dtmDateIn,
tblInventoryTrackingHistory.CurrentRig
FROM tblInventory LEFT JOIN tblInventoryTrackingHistory ON tblInventory.[SKU
Number] = tblInventoryTrackingHistory.[SKU Number]
WHERE ((( Exists (select [SKU Number] from tblInventoryTrackingHistory
WHERE tblInventory.[SKU Number] = tblInventoryTrackingHistory.[SKU Number]
AND [dtmDateIn] Is Null))=False));


Graham, if you were on my back deck, I would give you a beer!! (or the cold
beverage of your choice)
Thanks a bunch, again.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!


Graham Mandeno said:
Hi CJ

I think the make table query is way overkill.

Assuming you have gone with my suggestion from yesterday of adding a
DateReturned field to your Assignments table, you can base your
"Available" subform on a filtered recordset from your Items table:

Select * from Items
where NOT Exists
(Select AssignmentID from Assignments
where Items.ItemID=Assignments.AssignmentsID
and DateReturned is Null);

Of course you will need to tweak this for your own table and field names.

To list only those items which are currently "out", remove the "NOT".

Then a simple Requery of the subform will refresh the list.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...
 
Hi CJ

Thanks for the feedback.

I don't know where your back deck is, but I can safely assume it's a long
way from where I am!! The thought is much appreciated anyway :-)
--
Cheers!

Graham Mandeno [Access MVP]
Auckland, New Zealand

[snip]
 
Graham-

My situation is similar. I track inventory but want to see the history of a
serial number in the subform (instead of the availability) as the item goes
from user and office and back.
My problem:
1. The SN history subform is based on a query that prompts for the serial
number, and the form to receive or send inventory also needs to prompt for
the serial number, but I'm getting two prompts. How do I get it to filter
for the serial number in the form and the subform while only getting one
prompt?
2. I would also like my subform to update based on the information being
typed into the form above. It worked for like 5 minutes yesterday where it
would update after I got to the last field and pressed tab, but I didn't do
it on purpose and I tweaked something and it stopped working. It would be
great if it would update after every field.

Thanks again!
-Amber



Graham Mandeno said:
Assuming you have gone with my suggestion from yesterday of adding a
Click to expand...
DateReturned field to your Assignments table, you can base your "Available"
subform on a filtered recordset from your Items table:

Select * from Items
where NOT Exists
(Select AssignmentID from Assignments
where Items.ItemID=Assignments.ItemID
and DateReturned is Null);

Of course you will need to tweak this for your own table and field names.

To list only those items which are currently "out", remove the "NOT".

Then a simple Requery of the subform will refresh the list.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...
 
Hi Amber

For Q1: You shouldn't base your subform on a query that asks for a
parameter. Instead, remove the parameter-driven WHERE condition from your
query so it returns records for all serial numbers. Then set the properties
of your subform control as follows:

LinkMasterFields: <name of main form control that holds the SN>
LinkChildFields: <name of SN field in the subform's recordsource query>

Then the mainform/subform link will automatically do the filtering for you.

For Q2: I don't really understand what you are asking. What do you mean by
"update based on the information being typed into the form above"?

--
:-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

AdminAmber said:
Graham-

My situation is similar. I track inventory but want to see the history of
a
serial number in the subform (instead of the availability) as the item
goes
from user and office and back.
My problem:
1. The SN history subform is based on a query that prompts for the serial
number, and the form to receive or send inventory also needs to prompt for
the serial number, but I'm getting two prompts. How do I get it to filter
for the serial number in the form and the subform while only getting one
prompt?
2. I would also like my subform to update based on the information being
typed into the form above. It worked for like 5 minutes yesterday where
it
would update after I got to the last field and pressed tab, but I didn't
do
it on purpose and I tweaked something and it stopped working. It would be
great if it would update after every field.

Thanks again!
-Amber



Graham Mandeno said:
Assuming you have gone with my suggestion from yesterday of adding a
Click to expand...
DateReturned field to your Assignments table, you can base your
"Available"
subform on a filtered recordset from your Items table:

Select * from Items
where NOT Exists
(Select AssignmentID from Assignments
where Items.ItemID=Assignments.ItemID
and DateReturned is Null);

Of course you will need to tweak this for your own table and field names.

To list only those items which are currently "out", remove the "NOT".

Then a simple Requery of the subform will refresh the list.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...
Click to expand...
 
Thanks once again Graham!

Removing the parameter and linking the master/child fields actually fixed
both of my problems at once!
 
Hi Amber

That's great! Good to know you've got it all working. Thanks for the
feedback.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

AdminAmber said:
Thanks once again Graham!

Removing the parameter and linking the master/child fields actually fixed
both of my problems at once!

Graham Mandeno said:
Hi Amber

For Q1: You shouldn't base your subform on a query that asks for a
parameter. Instead, remove the parameter-driven WHERE condition from
your
query so it returns records for all serial numbers. Then set the
properties
of your subform control as follows:

LinkMasterFields: <name of main form control that holds the SN>
LinkChildFields: <name of SN field in the subform's recordsource query>

Then the mainform/subform link will automatically do the filtering for
you.

For Q2: I don't really understand what you are asking. What do you mean
by
"update based on the information being typed into the form above"?

--
:-)

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...
Click to expand...
 
Back
Top