Output of function shown for each record of subform?

[Vie]

Hi all,

How do you show a value that is generated on the fly (not stored in a table) in a column in a subform? The value is generated in a function:

Public Function GetApartmentDesignation()
Dim intOption As Integer
Dim i As Integer
Dim strApt As String

intOption = Forms!frmProjectApartments.oleApartmentDesignation

Select Case intOption

Case 1 'Apartments are designated by letter
    For i = 1 To intHowManyApartments
        strApt = i + 64
        GetApartmentDesignation = Chr(strApt)
    Next i
    
Case 2 'Apartments are designated numerically
    For i = 1 To intHowManyApartments
        If intHowManyApartments < 10 Then
            strApt = "0" & i
            GetApartmentDesignation = strApt
        Else
            strApt = i
            GetApartmentDesignation = strApt
        End If
    Next i

End Select
  

End Function

It returns either letters A-Z or numbers 01, 02, 03...10, 11, 12, etc.

So if the user enters that there are 3 apartments (intHowManyApartments) on Floors 3 - 4 and the apartments are letter designated, I want the subform to output:

Floor Apt
3 A
3 B
3 C
4 A
4 B
4 C

where the values in Apt are from the function.

I get all the correct values for apt just fine (debug.print to immediate window) but don't know how to show them in the subform.

Is there a way to do this or am I going about this entirely the wrong way?

As always, thank you tek-tipsters!
Vie

 

[Rolliee]

Forms do not 'store' anything. Database tables store things. To understand, we would need to see the call to your subroutine/ function.


rollie@bwsys.net

 

[Vie]

I'm aware of that. What I am asking is where to call the function from.

 

[Rolliee]

If the table that is tohold the information is bound to the form, (let the wiz develop the form for you) you could for instance use a command button to get the input values and place them in the proper fields on the form. If the field were AptNum then there would b e a textbox control on the form whose name was AptNum and access to set that value might well be me.APtNum = 'the input value'

It would then be in the form and in the table.

You need to look at some examples.

rollie@bwysy.net

 

[Vie]

Your responses so far make me think you don't understand the question. I don't know how I can make it any more clear but I will try again. Note that, this form and its subform are unbound until run-time.

The Apt value does not need to be and shouldn't be saved in a table. For instance, the value in the table field from which the "Apt" is derived is 3 and the apartments are designated by letters. That makes it Apt C. C doesn't go into the table, 3 does. C, however, should be what appears on the subform. On the other hand, if apartments are designated by numbers then Apt 03 should appear in the subform (if concatenated these give Apt 3C and Apt 303, respectively).

The subform is in datasheet view and shows two columns, Floor and Apt. Floor is bound to a field in a table, Apt is not.

Say floor is 3 and there are six apartments on the third floor. In the table, there will be six records where Floor = 3 and Apartment (this is generated elsewhere) is 1 through 6.

In the subform I need to show all six apartments at once but not in the table form of Floor 3, Apartment 1...etc. It needs to be like Floor 3, Apartment A (mostly they will be in this alphanumeric format) or Floor 3, Apartment 01, depending on which way the apartments are designated for the given building.

There is a command button on the form that populates the subform on the fly (the table is being created from this form). What I don't understand is how to get each i from the function to output into the unbound Apt textbox field of the subform as it fills in each consecutive row of the table.