Help with a form vba code (1 Viewer)

[noe956]

I have a table called tblWrecker and a table called accidents attach to form called accidents. What I am trying to accomplish is get the data from the tblWrecker and display it in the form in accidents.
For example, I have 3 companies in tblWrecker called wrecker company a, wrecker company b, and wrecker company c in the field company from the tblWrecker. My goal is when every time I open the form it will automatic get the data from tblWrecker and insert company a. Then when the user opens the form again it should get company b, and then get company c when they open the form again. Lastly I want it to repeat this method over and over again. The reason is because we want to be fair with this three companies we want to make sure that we give every company a fair shot. So I don't want to be remembering what was the last company i send out.
I need help I have no clue where to begin with the code so I hope someone can help me.

 

[CraigDolphin]

There are potentially many ways of handling this.

One would be to use a random number generator function to randomly select which WreckerID was 'selected' and used to populate the WreckerID control/field when you start filling in your accident form.

[COLOR="YellowGreen"]'Air Code assumes the table name is tblWrecker and this table has an 
'autonumber primary key field called WreckerID[/COLOR]

Public Function GetRandomWrecker() As Long
  Dim WreckerCount As Long
  Dim strSQL As String
  Dim rs As DAO.Recordset
  Dim i As Integer

  WreckerCount = Int((DCount("*", "tblWrecker") - 1 + 1) * Rnd + 1)
  i = 0
  strSQL = "Select WreckerID from tblWrecker;"
  Set rs = CurrentDb().openrecordset(strSQL, dbopendynaset)
  If Not rs.EOF Then rs.MoveFirst
  Do While i < WreckerCount - 1
    If Not rs.EOF Then rs.MoveNext
    i = i + 1
  Loop
  GetRandomWrecker = Nz(rs!WreckerID, 0)
  Set rs = Nothing

End Function

The above code would be placed either in a public module or in your accidents form vba module. You could then use something like:

Me.WreckerID = GetRandomWrecker()

placed in the On_Dirty Event of your Accident form, or in the default value of the WreckerID combo control on your accident form.

This code has the advantage that if you add more wreckers to your wrecker table you don't need to modify the code to include the new wrecker.

If you're hung up on maintaining a particular rotation order then you'd need to:
1. Somewhere define which WreckerId is 1st, second, third etc.
2. Write a code function to lookup the last WreckerID used on the most recent Accident record (use functions like Dlookup and DMax to obtain this datapoint)and then return the ID of the next Wrecker in the rotation.
3. Make sure to provide a mechanism to change the rotation order if new wreckers ever need to be added, or current wreckers ever need to be removed from the list, etc.

 

[CJ_London]

If I understand you correctly, every time there is an accident that requires the use of a wrecker company you want to ensure that the form displays the 'next' wrecker company as a default.


A way this could be achieved is as follows:

1. In your wrecker company table have a field type integer called SelectedOrder (do not use 'order' - it is a reserved word). Ensure the field is indexed as 'indexed - no duplicates' or you have some other method of ensuring you cannot have two records with the same selectedorder value
2. Populate this field for each company 0,1,2 respectively
3. In your accident form open event put the following code - you'll need to change field and table names to suit your situation

Dim Rst as Recordset
 
Set Rst=currentdb.openrecordset("SELECT SelectedOrder From tblWrecker INNER JOIN Accidents WHERE ID=(SELECT Max(ID) FROM Accidents as tmp)")
Me.WreckerID=(rst.fields(0)+1) MOD 3
Set Rst=Nothing

WreckerID and ID are assumed to be fields in your accident table and ID is assumed to be an autonumber field. It is also assumed that each record in the accident table requires a wrecker company.

Using this technique, if more than one person is completing these forms at the same time, there is a risk that the same last record will be picked up for the next accident thereby allocating the same wrecker to more than one 'concurrent' accident. Since you are saying 'So I don't want to be remembering what was the last company ' I'm assuming this is not the case.

Note, if you add a 4th wrecker company, you will need to change MOD3 to MOD 4, alternatively to make it future proof you could use another query (or the DCount function) to count the number of wrecker companies in your table to determine the value of 3