Removing Strings 1

[BotCow]

Okay this might be a complex question (though easy to answer) depends on how well I explain it hehe:

Okay, I have a combination drop down menu that displays different titles of books. Each book will have it's own table that is named "tblBook" 'Book' being the first word of the title. So if the book's title was, "Rabbits Eat Lettuce" the name of it's table would be "tblRabbits". For the drop down menu I want it to change the source when you select it. Okay, not a problem, a helpful Tek-Tip guy helped me earlier today with the code, it looks like this:

Docmd.Echo False Me.Recordsource = Me!ComboName Docmd.Echo True

Okay so naturally if I do this: Me.Recordsource = "tbl" & Me!ComboName, it will give me the recordsource as "tblBook Title". But to keep naming standards in place, I just want the first word, so the source will be tblBook. I'm really new at VB and these basic things of string manipulation evades me.

 

[grtammi]

Not a problem, that's what we're here for.

Using the InStr function, we can retrieve the first name for your book with ease (I've broken the code up into step by step instructions for you - I was going to nest them all, but seeing that you're new to VB ... )

Post this code inside your subroutine, or function, or where-ever you need to retrieve the name.

Dim strTblName as string   ' variable to hold final name of table
dim strBookTitle as string ' variable to hold first part of book title
dim lngBreakingSpace as long ' variable to hold where we find the first space in the book title

' first, find out where the space in the book title is
lngBreakingSpace = InStr(1, Me!ComboName, " ")
' make sure there actually was a space there
if lngBreakingSpace > 0 then
   ' use left function to retrieve upto the space character
   strBookTitle = Left(Me!ComboName, lngBreakingSpace-1)
else
   ' no space, so use whole title - or whatever else you 
   ' want to do in the case that there is no space in the
   ' title of the book.
   strBookTitle = Me!ComboName
end if

' now that we've got the first word preceding the space
' in the book title, append it to a "tbl"
strTblName = "tbl" & strBookTitle

' rest of your code here

HTH

Greg



Boss quote from an office meeting: We're going to continue to have these meetings until we figure out why no work is getting done ...

 

[BotCow]

Freakin' awesome, you guys are great! Now I wish my Comp Sci class was this helpful, they're all stingy bastards that think code is a gold ingot.