Data Type for Table and/or Query

[kh59743]

I'm at a loss. I found the following code which I am hoping will work for me, but it was written for finding a value in a form. The data I need is located in a table or a query (depending on when I'm running it).

What data type can I use to pass the name of the table or query (currently referred to as F) to find the value I need?

Many thanks in advance!

'*************************************************************
' FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Function PrevRecVal(F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim RS As DAO.Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0

' Get the form recordset.
Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
'Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal = RS(FieldNameToGet)

Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function​

 

[The_Doc_Man]

What are you trying to do?

The code looks like it finds a record (underlying that form) with criteria F and then returns the value from that field from the previous record. But there is a hidden "gotcha" that is implied by the operation.

The code you are looking at implies a recordset order because it comes from whatever record source that drives the form. If that is a table, you get things in the order of the table's prime key. If it has one. But it might not, which means the order is chronological by the times at which each record was entered. Which could even be effectively random with respect to whatever you really wanted to see.

If it is a query, you get things dependent on whether the query has an order-by clause or is accepting records from the table based on that table's prime key.

Now, in the code, you cannot see the table or query that is the form's recordsource, so you don't have any way to know what "previous record" really means. "Previous" in terms of what sorting order? Primary key or some other key?

Define what you want as the sort order so that "before" becomes a meaningful term. Until then, "before" means "randomly chosen."

Write a query that implements the sort order. Open the query as a recordset (which it is anyway...). There are ways to quick-search for a given value in a recordset. Look in the Help Files for recordset operations including FindFirst. (Sanity check: Until the recordset order is defined, you cannot define "first" either...)

Once you have found the record containing your desired value, you can do the .MovePrevious method to get the value you wanted. Sanity check 2: If you are at the record for which .BOF is true, you have no previous record.

Now, the good news. Once you have a recordset based on query or table, the .Type property is defined properly in the same sense as the example you were considering.

You could use the structure rs-name.Fields("F").Type to find the type of the field named "F" and this would work correctly. The greater trick would be to know the type of the incoming comparator value.

 

[kh59743]

This is my goal:

I have a form in which users type "issues" that need to be researched. Then the clerical staff needs to assign that issue to a specific subgroup of people - preferably spreading the work among those who are available.

In order to accomplish this, I have added an "Auto Assign" button to the form which in an ideal world would do the following.

1. Store the key field value from the record currently showing on the form.
2. Pass that value to the function I've copied above in order to go to the table where these "issues" are stored and find the value which represents the person to whom the previous issue was assigned.
3. Pass the key value representing the person assigned to the previous "issue" to a similar function which would look up the key value representing the next person from a query which represents the subgroup of potential researchers.
4. Finally, assign the key value representing that person to the current record in the form on which the "Auto Assign" button is located.

Hopefully, that makes some sense.

I think this code will accomplish what I need it to if I can figure out a way to tell it where to look (i.e. which table or query), but for that I need an appropriate data type.

However, if you can recommend a better way to accomplish the task I've outlined above, I would like to hear about it.

Thanks!