can access display a next out record

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

Guest

I have 10 hot shot companies that I use. I have a database that I use to
list the companies. However is there a way I can design a form that would
keep them in order and display the next one up and keep them going in a
continuous order for each time I enter an order?
 
Hi, Jennifer.

You could base your form on a query linking the companies to the orders you
place with them, using a subquery as criteria to select the most recent order
placed with each. If you sort in Ascending order, the next one up will be at
the top of the list. Something like:

SELECT Hotshots.HotshotID, Hotshots.HotshotName, Orders.OrderDate
FROM Hotshots INNER JOIN Orders ON Hotshots.HotshotID = Orders.HotshotID
WHERE (((Orders.OrderDate)=(Select Max(OrderDate) From Orders As T Where
T.HotshotID = Orders.HotshotID)));

Hope that helps.
Sprinks
 
Sprinks I need a little more layman terms, I am not that advanced. I have a
table that I have created a form for. It links only to a column that
specifies my drivers as a combo box in the second field the first is of
course the auto id field then the item they picked up. All I want to do is
cycle through the companies to open to company 3 after 2 has been used.... I
think I may need to go back to school to figure this stuff out it is not as
simple as I thought it was going to be....

So to bother Jen
 
Hi, Jennifer.

A simpler solution would be to sort your Hotshot combo box alphabetically,
and your form by a date/time timestamp that is assigned when you select a
driver. Then your form would display previous records in time order, and
each group of ten would be in alphabetical order. Then you could see easily
which one was up next. For example, it might look like:

RecordID Driver Record Date/Time
----------------- ----------------- -----------------------
1 Al 3/11/05 2:30:05 pm
2 Bob 3/11/05 2:31:10 pm
3 Charlie 3/11/05 3:00:05 pm

Looking at the list, it's easy to see that whoever's after Charlie is the
next one to select.

To implement the timestamp, first add a field to your table. Avoid the
reserved word Date or Time as a field name, say for example, OrderTime. Then
in the AfterUpdate event of the combo box, assign the current time to your
OrderTime field:

Me![OrderTime] = Now()

To sort your Hotshot combo box alphabetically, add an ORDER BY clause to the
Row Source statement:

[Current SELECT statement minus terminating semicolon]
ORDER BY Hotshot.DriverName;

Hope that helps.
Sprinks
 
Back
Top