Rotating List on Form 2

[MelissaKT]

Hi! I'd really appreciate any help on this:
I have two tables with the following fields
AbandonedVehicles Wreckers
WreckerID WreckerID
Date WreckerName
Agency And Officer PhoneNumber
Location
VehicleInfo
Other

I need to set up a form that automatically rotates to the next wrecker in the table based on [WreckerID] and shows that wrecker as the only choice. However, the form will also have to have the AbandonedVehicle table information in it. I hope this makes sense. I just can't seem to come up with a good idea of how to do this. Thank you in advance

 

[MoLaker]

I'm assuming you are trying to establish a way to assign jobs to various wrecker services. It would probably be best to include a date/time field in the AbandonedVehicles table and enter the current date/time when you make an assignment. That way you have a reference with which to base the next assignment on. By finding the most recent date/time when you open your file, you can then find who was the last service assigned. With that you could select the next service in line to be displayed based on ID sort order.

There will be quite a bit of behind the scenes programming to be done to accomplish this, but nothing too complicated.

As for showing only the next wrecker service in line, this may prove to be a problem in the event that the service displayed might not be able to take the job and you would not have the option to select another - just a thought.

 

[MelissaKT]

That makes sense. I'll change my date field to default to =now(), but I'm kinda a newbie at programming. Would you requery it based on the date/time field? Would you put it in the Form_AfterUpdate() event? I appreciate your help with this!! As far as the wrecker not being available, unfortunately, they will have to keep track of that also. The wrecker services want to make sure that they are called and check the stats to make sure they called in order. Looks like I need to enter two new fields in my AbandonedVehicle table: Refused (Yes/No) and ReasonRefused(Text).

 

[MoLaker]

First some policy matters must be defined. I get the impression you are writing this for a police dept. or at least some public entity that must treat all wrecker services equally. Wrecker services can be very competitive and just plague the heck out of your client if consistent and fair treatment is not given. At the same time, they will pull every trick in the book to get the good assignments. So, you need to ask some more questions up front.

1. If a service refuses an assignment, what happens to that service? Do they go to the back of the line or are they offered the next assignment?

2. What is the policy concerning refusals? Who has authority on the part of the service? Should you record the person who refused the assignment as well as why?

3. What measures will be taken if contact with the service next in line cannot be made? Treat like a refusal?

4. Do wrecker services need to be rated? Are some services more capable than others? Most can tow a Chevy Malibu, but what about a 5 ton truck? What if the wheels are missing, etc?

This may be overkill, but it may also save a lot of application revisions and rewrites.

As to your question about where and when to requery...
Use a LEFT JOIN query to find the next in line. This should be done in the form's OnOpen event.

"SELECT Wreckers.WreckerID, AbandonedVehicles.LastAssign_Date
FROM Wreckers LEFT JOIN AbandonedVehicles ON Wreckers.WreckerID = AbandonedVehicles.WreckerID ORDER BY AbandonedVehicles.LastAssign_Date,Wreckers.WreckerID;"

Open a recordset using this SQL. If I'm thinking right, the last (bottom) record returned would be the last assignment with the associated wreckerID. The first record would be the earliest date and associated wrecker service - the one you want. Or, if there are wrecker services that have yet to receive an assignment, the first record would be the wrecker service with no assignment with the lowest wreckID number.

Anyway, the WreckerID you want would be the first record so you would store this value in a variable - say varWreckID. Then close the recordset.

While still in the form's OnOpen event, change the recordsource of the form to reflect the next in line.

Me.recordsource = "SELECT Wreckers.* FROM Wreckers WHERE Wreckers.WreckerID = " & varWreckID

(assuming varWreckID is a number)

To display the assignments for this WreckID, you could use a subform using AbandonedVehicles table as a recordsource and linking Child and Master with WreckID.

Okay, if you're still with me, what we have done so far is simply demonstrate how to show the record of the next in line service. I think after you get the policy matters established, you may have more/other ideas on how to approach the main form. But, perhaps this will get you started.

 

[MelissaKT]

Thanks a bunch for your help. Yes, this is for a local sheriff's office. All of the questions have been worked out, we are going to have it pull up the next in line regardless. There are actually going to be several different areas (the county is broken down into zones and each zone with have its own wrecker table and Vehicle table)I'm not familiar with sql except as a row source. Even then I use the sql query builder that looks like a query. I understand that it goes in the form's onopen event, but do you open up your code window to do it or do you just type the select statement in it? Also, how do you open a new recordset? I thought if I kept fiddling with it, I could figure it out, but it's not happening. Thanks again for your help!

 

[MoLaker]

First, create your initial form using the Wrecker table as a recordsource. Add the fields you want so you can add or edit the records. If the wreckerID number assignment is not critical except to identify the wrecker service within your database, you could make it an auto-number field. Leave room at the bottom of the form for a sub-form later.

You will have to provide a means for your client to add wrecker services. This can be done with the main form or you could create a separate form that does not have a sub-form, your choice. But, it would be helpful to have a table of several wrecker services and some AbandonedVehicle records (bogus or real) to work with while you develop your application.

Developing SQL code can be fairly simple if you can create and test a query using the query design wizard. Once you get your query doing what you want, just go to View|SQL View and you will see the actual code behind your query. This can be copied and pasted if needed in a form module (code).

Open a new query design and switch to the SQL view. Copy and paste the following into the query.

Me.recordsource = "SELECT Wreckers.* FROM Wreckers WHERE Wreckers.WreckerID = " & varWreckID

In place of the variable varWreckID, enter one of the wreckerID's in your wrecker table. Then run the query. This should bring up only the record containing the wrecker service with the specific wreckerID that you entered.

Now open the form in design view and access the form's OnOpen event

Private Sub Form_Open(Cancel As Integer)
   ' assuming wreckID is an Integer
   Dim varWreckID as Integer
   ' temporarily store one of the wreckerID's in varWreckID for testing purposes
   varWreckID = 2
   ' change the recordsource to the desired SQL
   Me.recordsource = ""SELECT Wreckers.* FROM Wreckers WHERE Wreckers.WreckerID = " & varWreckID
   'requery the data so the new recordsource is displayed
   Me.requery
End Sub

You should have a single wrecker service displayed.

Now we'll work on automatically selecting a wrecker service. Go back to the query designer and create a new select query. Assuming the date field you added to the AbandonedVehicle table is called LastAssign_Date (change it if you wish), copy and paste the following code into a blank SQL view of the new query

"SELECT Wreckers.WreckerID, AbandonedVehicles.LastAssign_Date
FROM Wreckers LEFT JOIN AbandonedVehicles ON Wreckers.WreckerID = AbandonedVehicles.WreckerID ORDER BY AbandonedVehicles.LastAssign_Date,Wreckers.WreckerID;"

This query should bring up all wrecker services and the AbandonedVehicle records associated with them. Save this query and call it something which indicates all records - such as qryAllRecs. We're going to use this query to look for the next-in-line wrecker service. To test this query, run it and look at the first record. This should be the Wrecker service that is next in line. I'm not testing this as I go, so you must test it.

Now, back to the form's OnOpen event, make the following changes

Private Sub Form_Open(Cancel As Integer)
   ' assuming wreckID is an Integer
   Dim varWreckID as Integer
   [red]' find the next-in-line service and store ID in varWreckID
   varWreckID = DLookUp("WreckID","qryAllRecs")[/red]
   ' change the recordsource to the desired SQL
   Me.recordsource = ""SELECT Wreckers.* FROM Wreckers WHERE Wreckers.WreckerID = " & varWreckID
   'requery the data so the new recordsource is displayed
   Me.requery
End Sub

That's enough for this posting. For simplicity, try to get it working before you add the sub-form displaying the AbandonedVehicles and you begin refining the form. Feel free to ask questions as I'm sure I've bungled something along the way.

 

[MelissaKT]

It is working and it looks great! I want to thank you, not only for helping with my problem, but also for teaching me something about sql!

 

[MelissaKT]

Hello! Sorry to bother you again with the same problem, but I have another question. I have put all of the code in and it works fine until all of the wreckers have a LastAssign_date. Once they do, it will only pull up one wrecker (the one with a wreckerID of 1). Thanks in advance for any help!

 

[MoLaker]

If you look at the raw data, is the wrecker service with the wreckerID of 1 also the first one to have received an assignment? If that is the case, wouldn't it be the next-in-line to receive a new assignment if all others have received assignments?

 

[MelissaKT]

Yes, it would. Sorry, I didn't explain myself very well. Once all of the wreckers in the wrecker rotation have been used, it goes to WreckerID1 (like it should). The problem is that it keeps WreckerID1 instead of going to WreckerID2. The dates that I entered into the LastAssign_Date field were all the same date, I wasn't sure if that had something to do with it?

I want you to know that I really appreciate your help!

 

[MoLaker]

For the rotation to be the way you want, the LastAssign_Date field must have legitimate entries. This would mean time as well. That way assignments made on the same day would be sorted in the correct order. If you use the same date, the sort result would be the order of the ID's and the date would have no impact. Your LastAssign_Date field should be an automated date/time stamp by inserting now() into the field. That way none of the dates would ever be the same and they would be first in first out.

 

[MelissaKT]

The lastassign_date was formatted for =Now(), but it still wasn't working. After the query went through all of the wreckers with no dates included, it would stay on WreckerID 1. I've put the following statement into my query; do you think this will work? It appears to be working so far, but if you see a potential problem, I'd love to know!

SELECT WreckerAbandoned.WreckerID, Last(AbandonedVehicle.LASTASSIGN_DATE) AS LastOfLASTASSIGN_DATE, WreckerAbandoned.WreckerID
FROM WreckerAbandoned LEFT JOIN AbandonedVehicle ON WreckerAbandoned.WreckerID = AbandonedVehicle.WRECKERID
GROUP BY WreckerAbandoned.WreckerID, WreckerAbandoned.WreckerID
ORDER BY Last(AbandonedVehicle.LASTASSIGN_DATE), WreckerAbandoned.WreckerID;

Thanks again for your help!

 

[MelissaKT]

Okay, now this isn't working. It went through the list about 3 times before it started messing up, but now it's stuck on a particular record. Do you have any ideas?

 

[MoLaker]

My original query would only give you the oldest LastAssign_Date - regardless of how many assignments each wreckerID had - like you found out. Sorry 'bout that.

You're on the right track with your query, but not quite there. I've created a couple of queries to try to make it a little more simple.

The first query will yield the lastest date/time assigned to each wreckerID. Create a query definition named LastAssign using this select statement.

SELECT Max(AbandonedVehicle.lastassign_date) AS LastDate, AbandonedVehicle.wreckerID
FROM AbandonedVehicle
GROUP BY AbandonedVehicle.wreckerID
ORDER BY Max(AbandonedVehicle.lastassign_date), AbandonedVehicle.wreckerID;

Actually, this query would work all by itself once all wreckerIDs have been assigned at least once. But, we need to be able to take care of those which have not yet been assigned and therefore do not have a record in the AbandonedVehicle table. This is why we have a LEFT JOIN with the WreckerAbandoned table. So, the next query definition would use the following select statement.

SELECT WreckerAbandoned.wreckerID, LastAssign.LastDate
FROM WreckerAbandoned LEFT JOIN LastAssign ON WreckerAbandoned.wreckerID = LastAssign.wreckerID
ORDER BY LastAssign.LastDate, WreckerAbandoned.wreckerID;

I think you could do this in one complex query, but it is easier to understand and manage by using 2 queries.

Now, the DLookUp would be performed on this last query. The 1st record in this last query should now be the next-in-line wreckerID (I hope).

I was developing this in my head before and obviously that doesn't work well at my age. Now that I've created actual tables and queries, it looks like it should work.

 

[MelissaKT]

Hello! Sorry it took so long to get back to you. We had a long weekend! Anyway, I had constructed this query and it appeared to be working :

SELECT Last(AbandonedVehicles.LASTASSIGN_DATE) AS LastOfLASTASSIGN_DATE, WreckerAbandoned.WreckerID
FROM WreckerAbandoned LEFT JOIN AbandonedVehicles ON WreckerAbandoned.WreckerID = AbandonedVehicles.WRECKERID
GROUP BY WreckerAbandoned.WreckerID
ORDER BY Last(AbandonedVehicles.LASTASSIGN_DATE), WreckerAbandoned.WreckerID;

However, I'm going to change it to the query construction that you had suggested. I again want to thank you! You've been a tremendous help with this project!

 

[PHV]

Anyway I suggest you to replace this:
SELECT Last(
By this:
SELECT Max(

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MoLaker]

PHV is correct and I had suggested as much in my last response. You should use Max() instead of Last(), since you are dealing with a date/time stamp, particularly. Last() only works with MS Access. Should you ever have need to upgrade your application to MySQL or SQL Server, etc., Last() is not a valid function. By using Max(), you ensure the return will be the latest date entered, regardless of when it was entered.