viewing and adding data to history

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Everyone
I have a service history form where we keep track of drivers and locations
they visit.On main form I have a driver and a date. On a subform I have a
list of locations + some other stuff. It's the location ID that I have a
problem with.Sometime I need to add locations to this history, so I made it
into a combo box based on Currently active locations ( ID hidden, Name and
address visible). But then when I look into history 2 years ago I have a lot
of empty holes ( I closed the locations). If I based this combo box on all
locations, there are no holes , but my list of choices include closed
locations.

Thanks
Barb
 
Add the currently selected location to the query.

WHERE LocationID = Your current Where Clause OR LocationID = [Address of
subform]!LocationID

I usually save all the hassle of trying to get the current address of the
subform especially if it is deeply nested by making a function that returns
the value of the control:

WHERE LocationID = Your current Where Clause OR LocationID = MyFunction()

MyFunction would be

Function MyFunction()
MyFunction = Me.LocationID
End Function

HTH;

Amy
 
Hi Amy
Thanks for your response, but you've lost me. I'm really new at this. This
is my query behind LocationID, could you please help me a bit more ?

SELECT tblLocations.LocationID, [LocationName] & " , " & [Address1] & " , "
& [City] & " , " & [SectorNameLong] & " , " & [Program] AS Location
FROM tblSectors INNER JOIN tblLocations ON tblSectors.GPNB =
tblLocations.SectorName
WHERE (((tblLocations.ClosedDate)>Date()) AND
((tblLocations.Division)=[Forms]![frmDivision]![txtDivision]) AND
((tblLocations.OnHold)=No))
ORDER BY [LocationName] & " , " & [Address1] & " , " & [City] & " , " &
[SectorNameLong] & " , " & [Program];

thanks
Barb


Amy Blankenship said:
Add the currently selected location to the query.

WHERE LocationID = Your current Where Clause OR LocationID = [Address of
subform]!LocationID

I usually save all the hassle of trying to get the current address of the
subform especially if it is deeply nested by making a function that returns
the value of the control:

WHERE LocationID = Your current Where Clause OR LocationID = MyFunction()

MyFunction would be

Function MyFunction()
MyFunction = Me.LocationID
End Function

HTH;

Amy

Barb said:
Hi Everyone
I have a service history form where we keep track of drivers and locations
they visit.On main form I have a driver and a date. On a subform I have a
list of locations + some other stuff. It's the location ID that I have a
problem with.Sometime I need to add locations to this history, so I made
it
into a combo box based on Currently active locations ( ID hidden, Name and
address visible). But then when I look into history 2 years ago I have a
lot
of empty holes ( I closed the locations). If I based this combo box on all
locations, there are no holes , but my list of choices include closed
locations.

Thanks
Barb
Click to expand...
Click to expand...
 
Put an open paren right after the word WHERE (so instead of three, there
will be four). Put a close paren after the ones that are before ORDER By.
So now there will be three instead of two. Between these parentheses is
what I referred to in my post as "Your current where clause" Why? Er,
because it is the where clause you are currently using. Hope that's clear
now.

Now, after that put
OR tblLocations.LocationID = {an expression that evaluates to the LocationID
that is currently selected for the form}

What you replace everything after the equals with will either be the full
address of the LocationID control where this query that is the data source
for the drop down OR you can use the function I described before (see
myFunction()).

For more information on how to sleuth out the address of the control, see
http://www.mvps.org/access/forms/frm0031.htm.

At this point, I've been as excruciatingly detailed as I can be without
taking you on as a client and getting on the phone with you with your
specific database in front of me.

Have a great weekend!

-Amy

Barb said:
Hi Amy
Thanks for your response, but you've lost me. I'm really new at this. This
is my query behind LocationID, could you please help me a bit more ?

SELECT tblLocations.LocationID, [LocationName] & " , " & [Address1] & " ,
"
& [City] & " , " & [SectorNameLong] & " , " & [Program] AS Location
FROM tblSectors INNER JOIN tblLocations ON tblSectors.GPNB =
tblLocations.SectorName
WHERE (((tblLocations.ClosedDate)>Date()) AND
((tblLocations.Division)=[Forms]![frmDivision]![txtDivision]) AND
((tblLocations.OnHold)=No))
ORDER BY [LocationName] & " , " & [Address1] & " , " & [City] & " , " &
[SectorNameLong] & " , " & [Program];

thanks
Barb


Amy Blankenship said:
Add the currently selected location to the query.

WHERE LocationID = Your current Where Clause OR LocationID = [Address of
subform]!LocationID

I usually save all the hassle of trying to get the current address of the
subform especially if it is deeply nested by making a function that
returns
the value of the control:

WHERE LocationID = Your current Where Clause OR LocationID = MyFunction()

MyFunction would be

Function MyFunction()
MyFunction = Me.LocationID
End Function

HTH;

Amy

Barb said:
Hi Everyone
I have a service history form where we keep track of drivers and
locations
they visit.On main form I have a driver and a date. On a subform I have
a
list of locations + some other stuff. It's the location ID that I have
a
problem with.Sometime I need to add locations to this history, so I
made
it
into a combo box based on Currently active locations ( ID hidden, Name
and
address visible). But then when I look into history 2 years ago I have
a
lot
of empty holes ( I closed the locations). If I based this combo box on
all
locations, there are no holes , but my list of choices include closed
locations.

Thanks
Barb
Click to expand...
Click to expand...
Click to expand...
 
Back
Top