I am looking for a function to automatically number a record in a
group based upon groupings by data. I suppose the best way I can
explain it is to type some sample data (the result I am looking for is
to automatically populate the OrderNumber field as I already have the
first three fields - the result it should be is included below):

------------------------------------------------
Group 1    Group 2    Last Name    OrderNumber
-------    -------    ---------    -----------
  AS        BIOL      Jones             1
  AS        BIOL      Smith             2
  AS        BIOL      Warner            3
  AS        CHLT      Azevedo           1
  AS        CSTC      Johnson           1
  AS        CSTC      Schubert          2
  CO        BOMG      Hadley            1
  CO        BOMG      Jones             2
  CO        BOMG      Jones             3
  CO        COMP      Dunn              1
.
.
.
------------------------------------------------

    Basically, when the Group 2 field changes, the OrderNumber
initializes back to 1.  I would like to do this by utilizing a
function that I can place into a query field (column) to be returned
with the rest of the record, if possible.  Thanks!

Here's your function:

Function GetOrderNumber(g1 As String, g2 As String)
    Dim lNum As Variant
    lNum = DMax("OrderNumber", "tblMyTable", "Group1=""" & g1 _
      & """ and Group2=""" & g2 & """")
    If IsNull(lNum) Then
        GetOrderNumber = 1
    Else
        GetOrderNumber = CLng(lNum) + 1
    End If
End Function

If you need, I can help you figure out how to call the function from your query.

Unfortunately, it is returning all 1's in the OrderNumber field.  Any suggestions?

I created a table and tested everything and it works okay.  The query
for inserting a new row should look like this:

INSERT INTO tblTest ( Group1, Group2, OrderNumber ) SELECT "abc" AS
Expr1, "xyz" AS Expr2, GetOrderNumber("abc","xyz") AS Expr3;

Are you including the new group1 and group2 values in the call to the
GetOrderNumber function as I have done?
      GetOrderNumber("abc","xyz")
                       ^     ^     Notice the new group1 and group2 values
                                   get passed to the function.

It is still returning all 1's using the GetOrderNumber function.  I
have copied your code exactly.  This is how I am calling the function:
"GetOrderNumber([Group1], [Group2])"  When I use a Select query and
report identify Group1 and Group2 along with the function call, it is
returning the Group1 and Group2 variables accurately.  Any other
suggestions?  I appreciate your effort!

Do you actually have a table called tblMyTable which includes a field
called OrderNumber? If not, you need to adjust willcodeforfood-ga's
code to match your actual table and field names.

Note to willcodeforfood-ga:
Probably nothing will come of it, but I am impressed by the quality
and professionalism of your answers. I recommended to the Editors that
you be considered if they start activating new Researchers.

- Hammer

I figured it out by changing the VBA code:

Public Oldg1g2 As String
Public OrderNum1 As Long

Function GetOrderNumber(g1 As String, g2 As String)
    Newg1g2 = g1 & g2
    If Oldg1g2 = Newg1g2 Then
       OrderNum1 = OrderNum1 + 1
    Else
       Oldg1g2 = Newg1g2
       OrderNum1 = 1
    End If
    GetOrderNumber = OrderNum1
End Function

Thank you for your help!  It got my wheels turning.  Thanks!

You do understand that your Order Number is going to start over again
at 1 every time you close and reopen the database, right?

- Hammer

Since I am using it as a function in a Create Table query that has
mostly static data (read: dynamic), that is perfectly fine for my
needs.  Out of curiosity, how would I make it not do that if I ever
needed to store the number in the future?  Thanks!

<< Out of curiosity, how would I make it not do that if I ever needed
to store the number in the future? >>

willcodeforfood-ga's way. :)

- Hammer

Unfortunately, I could not get willcodeforfood-ga's way to work...

Do you actually have a table called tblMyTable which includes a field
called OrderNumber? If not, you need to adjust willcodeforfood-ga's
code to match your actual table and field names.

- Hammer

Yes, I had a table (tblMyTable) which had that field (OrderNumber). I
copied everything exactly.  I'm not new to VB(A) or Access, I just
couldn't think of how to do this.  Yeah, I'm still scratching my head
too as to why that code didn't work for me.  However, I did end up
with the results that I needed.