Format Text Box

[Curry]

Hi All,

Is there a way to format a text box to allow a space at the very end of a alpha/Numeric entry...ie "@@@@ ". Every time I type the space at the end it drops off however I want the space to be a part of the Text box entry.

Thanks All.

 

[twoplustwo]

Hey.

What's this for?? I only ask as it doesn't sound standard.

You could concatenate it after entering it I guess... Concatenate([fieldname] & "SPACE")

 

[dreamdelerium]

try this

well, i dont know if you can do it right in the textbox (im sure im wrong). but what are you doing with the string? why not try this, when you send the form, take the text from the text box and concat the three space.
ex:

Dim GetString as string
GetString = me.textbox1.text & "   "
 
'now do what you want with it

 

[twoplustwo]

well, i dont know if you can do it right in the textbox (im sure im wrong). but what are you doing with the string? why not try this, when you send the form, take the text from the text box and concat the three space.

This, too. I'm not sure if/why you'd want to actually *enter* the space at the end, seems prone to error without some form of input mask??

 

[Curry]

I am using this command

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblSales_YTD", strFileName, True, Worksheet & "!"


where Worksheet is the name of a Worksheet on an Excel spreadsheet which is importing into the Database. The user puts the name of the worksheet they want imported into the Text Box but without the space at the end the Worksheet cannot be found. The Excel spreadsheet is coming from a 3rd party and they have labeled the worksheet with a space at the end. I guess I could get the user to rename the Worksheet in Excel and remove the space however I am trying to make the process as simple as possible. I could also add the space permanantly as has been suggested b y doing this

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblSales_YTD", strFileName, True, Worksheet & " !" (Added space before the !)

however if the 3rd party decides to remove the space later on I will have to modify the code. I want the user to be able to type the worksheet name as its presented to them. I can do this using an input Box instead of the Text box however they then need to type the worksheet name everytime they import. My problem would be resolved if the text box would store the space.

Thanks All.

 

[Curry]

Hi All,

I came up with a solution which will change the Worksheet name in the Excel Spreadsheet removing the space at the end or will ignore if the space is removed down the track.

Dim db As Database
Set db = CurrentDb

Dim xlApp As Object, xlWrkb As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWrkb = xlApp.Workbooks.Open(strFileName)


On Error GoTo Skip_Error
xlApp.Sheets(Worksheet & " ").select
xlApp.Sheets(Worksheet & " ").Name = Worksheet


Skip_Error:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblSales_YTD", strFileName, True, Worksheet & "!"

xlApp.ActiveWorkbook.Save
xlApp.Quit