Using user-entered data in unbound fields as Variables to build a phrase.

[IncidentalProgrammer]

So the books are great at telling me how to use input boxes to collect data to use as variables to build a sentence, but not so much on using actual form fields. I am beating my head against the wall!



I have a very small form accessed by a button on one of my main forms, who's sole purpose is to generate file names to be copied and pasted onto files in the shared drive. My boss has a very strict naming convention for these files, and this is just one of those nifty little features that I wanted to add to our database to help with uniformity. The form takes the policy number and date from the main Policy form it was entered through, and then there are unbound fields for the user to enter an endorsement number, and up to 5 short descriptive items (in combo boxes).

Upon clicking a button, the procedure needs to take that data, and format it into a file name that the user can copy. I'm having some trouble pulling the data into variables, though. I've tried a couple of different formats, and here's where I'm at now:

Private Sub btnFileName_Click()
     'Generates file name based on data in the generator.
     'Declare variables.
    Dim PolNum As String
    Dim EndNum As Byte
    Dim EffDate As Date
    Dim desc1 As String
    Dim desc2 As String
    Dim desc3 As String
    Dim desc4 As String
    Dim desc5 As String
    
    PolNum = Forms!frmFileName!ctlPolNum
    EndNum = Forms!frmFileName!ctlEndNum
    EffDate = Forms!frmFileName!ctlPolEffDt
    desc1 = Forms!frmFileName!ctlDesc1
    desc2 = Forms!frmFileName!ctlDesc2
    desc3 = Forms!frmFileName!ctlDesc3
    desc4 = Forms!frmFileName!ctlDesc4
    desc5 = Forms!frmFileName!ctlDesc5

I'm also having some difficulties formatting the naming structure into code. It goes like this: policynumber(space)(space)endorsementnumber(space) (space)effectivedate(space)(space)descriptions. Again, the books are great about telling me how to do this in a message box...but I need this to populate another unbound text field on that form. Is this stuff doable?

Also, she wants the dates formatted as YYYY.MM.DD. Can I do that with code?

[ssanfu]

It would help to see an example of a file name. But try this

****Untested****

Private Sub btnFileName_Click()
     'Generates file name based on data in the generator.
     'Declare variables.
   
    dim sFileName as string

    Dim PolNum As String
    Dim EndNum As Byte
    Dim EffDate As Date
    Dim desc1 As String
    Dim desc2 As String
    Dim desc3 As String
    Dim desc4 As String
    Dim desc5 As String
    
    PolNum = Forms!frmFileName!ctlPolNum
    EndNum = Forms!frmFileName!ctlEndNum
    EffDate = Forms!frmFileName!ctlPolEffDt
    desc1 = Forms!frmFileName!ctlDesc1
    desc2 = Forms!frmFileName!ctlDesc2
    desc3 = Forms!frmFileName!ctlDesc3
    desc4 = Forms!frmFileName!ctlDesc4
    desc5 = Forms!frmFileName!ctlDesc5

   ' policynumber(space)(space)endorsementnumber(space) (space)effectivedate(space)(space)descriptions
    sFileName = PolNum & "  " & EndNum & "  " & Format(EffDate, "YYYY.MM.DD") & "  " & desc1 & desc2 & desc3 & desc4 & desc5 

    Me.TextBoxName = sFileName

End Sub

Tell your boss (or show her this) that having spaces in names is not good practice. If you MUST have separation between parts of the name, use the underscore:

sFileName = PolNum & "__" & EndNum & "__" & Format(EffDate, "YYYY, MM, DD") & "__" & desc1 & desc2 & desc3 & desc4 & desc5

Much easier name to work with!


If this is going to be a computer file name, the dots in the date are going to also be a problem. I'm just saying...........

[hansendl]

Are you having trouble with pulling the data from all of the controls into variables, or only certain types of controls (e.g., just the combo boxes)?

Also, as an aside, you may want to consider using the Me reference in your code instead of the full form reference to point to your controls--e.g., use "Me.ctlDesc1" instead of "Forms!frmFileName!ctlDesc1". This way, you won't have to rewrite your code if you ever change the form name--and it's just easier to type!

[IncidentalProgrammer]

It works! Thanks so much, Ssanfu!

An example would be something like: "123456789 End#01 2014.10.21 Renewal, Add ETS, Add Privacy Notice"

The only problems I'm having with it now are when I put the endorsement number in, it won't take the 0 if 0 is the first digit (like 03). I tried using Format like you did for the date, but would it be ## to tell it to use a 2 digit number? Or am I off on that? And is there a way to make the descriptions optional? It gives me an error if I don't use all 5, but they won't always all be needed. (Sorry, I am SO new to all of this.)

She's very particular about the names, and when I started saving these documents to the file, she told me she didn't want underscores. She has something against underscores. I am not in IT, so I didn't know that could cause problems. The names I'm getting the database to generate are not going to be stored or used in the database itself, they're made for the sole purpose of copying and pasting onto PDFs in client folders on the server. I know about spaces in names in Access and code being problems, but would they cause problems on PDFs? And the dots too? I know we use those because we can't put backslashes in the names on our PDFs.


Thanks, Hansendl! I changed them to Me.'s, and yeah, that's so much easier to type. I was kind of basing this off some code I found in a tutorial online, and they were using the Forms! setup.

[IncidentalProgrammer]

Actually, I just figured out how to make it handle there being no data in some of the descritptive fields. So the only thing I'm still struggling with on it is getting that endorsement number to show two digits when the first is a 0.

[hansendl]

Check out the Format function. Using that, I believe you can include the leading zero.

[IncidentalProgrammer]

Got it! Thanks so much, y'all!