Search code examples
vbams-accesssubform

Text property changes all records in a subform

I am working on an MS-Access2000 database to organize our university's courses. Each course can have several component, such as a textbook, instructor's guide, etc. which have an electronic file. I am writing a VBA on a subform to put a suggested filename for these files based on the course number , language, edition and other characteristics into a textbox (Text36). I have set it to run on the Current and AfterUpdate events as well as the click of a button (Command35).

So far so good, except that it puts that filename into ALL the records for that course, and not just the one record for the component that the textbox is in.

How can I make this work that each record will have its own suggested filename.

Private Sub Command35_Click()
ECMFilename1 = Me!COURSENUMBER & PreferredLanguageCode
If Not IsNull(Me!SourceEdition) Then ECMFilename1 = ECMFilename1 & "-" & Me!SourceEdition
If Not IsNull(Me!LanguageEdition) Then ECMFilename1 = ECMFilename1 & Me!LanguageEdition
ECMFilename1 = ECMFilename1 & "-" & [PRODUCT TYPE]
If IsNull(Me![MaterialTitle]) Then ECMFilename1 = ECMFilename1 & "-" & Me![CourseTitle] Else ECMFilename1 = ECMFilename1 & "-" & [MaterialTitle]
Text36.SetFocus
Me.Text36.Text = ECMFilename1
End Sub

Thanks, Videot

Solution

  • Programmatically setting value property of an UNBOUND textbox will show same value for ALL records because there is only one textbox. Have a function calculate a value based on dynamic input of data from records and call function from textbox ControlSource.

    If function is in form module:

    Function GetName() As String
Dim ECMFilename1 As String
With Me
ECMFilename1 = .COURSENUMBER & PreferredLanguageCode
If Not IsNull(.SourceEdition) Then ECMFilename1 = ECMFilename1 & "-" & .SourceEdition
If Not IsNull(.LanguageEdition) Then ECMFilename1 = ECMFilename1 & .LanguageEdition
ECMFilename1 = ECMFilename1 & "-" & .[PRODUCT TYPE]
If IsNull(.MaterialTitle) Then 
    ECMFilename1 = ECMFilename1 & "-" & .CourseTitle 
Else 
    ECMFilename1 = ECMFilename1 & "-" & .MaterialTitle
End If
End With
GetFile = ECMFilename1
End Function

    Call from textbox: =GetName()

    If code is placed in a general module:

    Function GetName(sC, sS, sL, sM, sP) As String
Dim ECMFilename1 As String
ECMFilename1 = sC & PreferredLanguageCode
If Not IsNull(sS) Then ECMFilename1 = ECMFilename1 & "-" & sS
If Not IsNull(sL) Then ECMFilename1 = ECMFilename1 & sL
ECMFilename1 = ECMFilename1 & "-" & sP
If IsNull(sM) Then 
    ECMFilename1 = ECMFilename1 & "-" & sC 
Else 
    ECMFilename1 = ECMFilename1 & "-" & sM
End If
GetName = ECMFilename1
End Function

    Textbox expression:
    =GetName([CourseNumber], [SourceEdition], [LanguageEdition], [MaterialTitle], [Product Type])