SelText and SetFocus expressions

[lmcc007]

I am using Access 2007. Attached is the db saved as 2002.

I created this expression to do what my sister needs, but she would like for it to do two more things—that is, select text and cancel when you click cancel. The expression I created is:

Private Sub HomonymWord_DblClick(Cancel As Integer)

DoCmd.Requery
​

End Sub
​

I need the expression to do the following:

1. When I double-click a HomonymWord that the insertion point is on, I want Access to select the word and then requery. Basically, I do not want to have to retype the word in the query box.
2. If I hit cancel, I want Access to do nothing—set focus on the word or remain on the word.

I tried SelText but it didn't work and I really don't understand it.

I need something very very simple.

Thanks!

 

[wiklendt]

had a quick look, but haven't got it working (still asking for paramater value) and i really need to get back to (my real) work. not sure if i'm barking up the wrong tree, but i'm trying to change the recordsource of the form to a new query that is filtered by your word. see if this triggers any ideas for you (or others on the forum):

i don't know why, but it doesn't seem to realise it already has it's parameter. i don't know much about changing the recordsource of the form on-the-fly, like this, but it could be a start for you.

(edit: when i remove the "WHERE..." part, it changes the record source fine....)

Private Sub HomonymWord_DblClick(Cancel As Integer)
On Error GoTo Err_HomonymWord_DblClick
    
    Dim strNewRecord As String
    
    strNewRecord = "SELECT * FROM tblHomonyms WHERE MainWord = Me!HomonymWord.Value"
    Me.RecordSource = strNewRecord
    
Exit_HomonymWord_DblClick:
    Exit Sub

Err_HomonymWord_DblClick:
    Select Case Err.Number
    Case 2001
    'ingore, user cancelled
    Case Else
        Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
        MsgBox Msg, vbOKOnly, "Homonyms", Err.HelpFile, Err.HelpContext
    End Select
    Resume Exit_HomonymWord_DblClick
End Sub

 

[wiklendt]

for example, if i use

    strNewRecord = "SELECT * FROM tblHomonyms WHERE MainWord Like 'A' & '*'"

it returns to the form (without propting) all your "a" words (adapter and adaptor; i change the navitaion buttons to yes, so i could see if it was returning the correct thing).

so you just need to play with that setup until it recognises that the paramter (the bit after WHERE) is your Homonym...

sorry i can't help more..

 

[wiklendt]

oh, i just looked into your tblHomonyms. i see one word has two homonyms, but they're both in one field separated by a comma. this will make it extremely tricky, if not impossible to discern at double-click time which of the two you mean.

i would suggest the quickest and easiest way to get around this is to have a junction table with a one to many relationship to you main words table, and another one to many relationship with a new (true) homonyms table. this in effect creates a many-to-many relationship between homonyms.

then your form could return a list of homonyms in a subform (continuous form or datasheet view) and clicking on one of them would be more obvious which you mean.

 

[lmcc007]

oh, i just looked into your tblHomonyms. i see one word has two homonyms, but they're both in one field separated by a comma. this will make it extremely tricky, if not impossible to discern at double-click time which of the two you mean.

i would suggest the quickest and easiest way to get around this is to have a junction table with a one to many relationship to you main words table, and another one to many relationship with a new (true) homonyms table. this in effect creates a many-to-many relationship between homonyms.

then your form could return a list of homonyms in a subform (continuous form or datasheet view) and clicking on one of them would be more obvious which you mean.

Thanks Wiklendt,

For informing me regarding

"i see one word has two homonyms, but they're both in one field separated by a comma."
​

That is incorrect. I need to change it. We were using Excel to enter the words.


Also, I tried the expression you sent and it works. The only thing is it does not select the word; I have to type the word in the parameter dialog box. The cancel works great.


Actually, John Big Booty did what you are suggesting but I can't figure out how to get it to work with my form. My sister has no idea either.


Honestly, I really don't understand junction tables--that's way above my head. Something I am missing that I just do not get it. Everytime I tried to do something simple it seems so hard. Is there a video on the Internet on junction tables?



I am trying to do something like when you look at an online dictionary and click on a word it shows that word meaning. Anyway, I didn't plan on spending another weekend trying to figure this out, but I did.


Where can I go to learn to Code like the expression you wrote? I can't find the meanings for the Code I want. Any suggestions for dummies?

Thanks again!

 

[wiklendt]

got it

    strNewRecord = "SELECT * FROM tblHomonyms WHERE MainWord Like Forms!frmHomonyms.HomonymWord"

this will work only for those instances where your homonym field has only one word in it - it can't distinguish that a comma means two words.

if you don't want to make a junction table, search the forums on how to separate words in a comma delimited list (or to that effect).

that's beyone my skill, but i've seen it on the forums many a time...

 

[RuralGuy]

Imcc007:

Maybe Crystal has created something that will help.
http://www.youtube.com/user/LearnAccessByCrystal

 

[wiklendt]

a junction table allow you to make combinations of things that can be many-to-many (e.g., one parent can have two children, but one child can also have two parents)

PK = primary key; FK = foreign key.

tblParents
----------
ParentID (PK)
ParentName

tblChildren
---------
ChildID (PK)
Child Name

tblJunction
----------
JunctionID (PK)
ParentID (FK)
ChildID (FK)

so when you enter data in the table for Junction, you can do this:

ParentID column | ChildID Column
Parent 1 | Child 1
Parent 1 | Child 2
Parent 1 | Child 3
Parent 2 | Child 1
Parent 3 | Child 2
Parent 3 | Child 3

when you then do a query on, say, parent 2, you can see that theire children are child 1 (only). the query will return this:

ParentID colum | ChildID Column
Parent 2 | Child 1

when you then do a query on, say, parent 3, you can see that theire children are child 2 and 3. the query will return this:

ParentID column | ChildID Column
Parent 3 | Child 2
Parent 3 | Child 3

you can make THIS query as the record source for your subform, and it will return all the homonyms (children) for the mainword (parent). in the subform, you can hide the parent column, so that it only displays the children. so a parent word in the main form (frmHomonyms) will have displayed all it's children (all the homonyms for that word)

but i suggest (if you go that way) you rename your form to frmMainWord and then the subform can be frmMainWord_Homonym (my preference) or frmHomonym (everyone else's preference LOL).

as for resources - well, i learnt everything i know about access from four sources:

1) microsoft access 2000 for dummies (as a starter, got me through day one)
2) this forum (got me through the next year and a half)
3) press "F1" - brings up access help. after a while you get to understand what's going on.
4) google (helped where i had no idea what access help was trying to say)

but there are lots of books out there. and lots of internet resources. for me, i learnt things on a 'need-to-know' basis, LOL - e.g., needed to change the recordsource of a form, and learnt it

 

[wiklendt]

the beauty of a junciton table, of course, is you can reverse the query: you can ask what parents child1 has:

ParentID column | ChildID Column
Parent 1 | Child 1
Parent 2 | Child 1

oh, and of course i learn best by example.

pat hartman has a very good junction table example that i looked at recently.

 

[lmcc007]

got it

    strNewRecord = "SELECT * FROM tblHomonyms WHERE MainWord Like Forms!frmHomonyms.HomonymWord"

this will work only for those instances where your homonym field has only one word in it - it can't distinguish that a comma means two words.

if you don't want to make a junction table, search the forums on how to separate words in a comma delimited list (or to that effect).

that's beyone my skill, but i've seen it on the forums many a time...

Thanks, the words are separated.

 

[lmcc007]

Imcc007:

Maybe Crystal has created something that will help.
http://www.youtube.com/user/LearnAccessByCrystal

Thanks, this is something I will definitely look into.

 

[lmcc007]

Thanks Wiklendt, I am just not getting junction tables and need for them. Perhaps I have been at the computer too long. I will go over it again tomorrow and hopefully I will then get it.

 

[lmcc007]

the beauty of a junciton table, of course, is you can reverse the query: you can ask what parents child1 has:

ParentID column | ChildID Column
Parent 1 | Child 1
Parent 2 | Child 1

oh, and of course i learn best by example.

pat hartman has a very good junction table example that i looked at recently.

This is a good! I think I am beginning to understand--not there yet.

 

[wiklendt]

junction tables allow your data to be normalised.

think of a movie database. you can have many actors, and many movies. now, some actors appear in the same movies, while other movies have different actors.

instead of making one big table for which actors are in movies, you make three tables: one for actors, one for movies, and one to join them: ActorsInMovies (or something)

the records in the third table (the junction table) have it's own junctionID (so you can refer to the combination), a lookup field for an actor and a lookup field for a movie.

you would then enter data per actor per movie... so, let's say you wanted to enter all the movies angelina jolie starred in you would do it like this:

record 1: angelina jolie | tomb raider
record 2: angelina jolie | sky captain and the world of tomorrow
record 3: angelina jolie | hackers

and so on.

but those movies have other actors in them too. so you'll want to put them in too. in your actors table, you put in all the actors you know you'll need. then, you link them to your movies in your junction table like this:

record 4: jonny lee miller | hackers
record 5: jessie bradford | hackers
record 6: mathew lillard | hackers
record 7: jon voight |tomb raider
record 8: noah taylor | tomb raider
record 9: gwenyth paltrow | sky captain and the world of tomorrow
record 10: jude law | sky captain and the world of tomorrow

and so forth...

see, in one big, ugly, non-normalised table you'd probably do this:

MovieName |Actors
Tomb Raider | Angelina Jolie, Jon Voight, Noah Taylor

and then for hackers, you'd have to type in angelina again, and then again in other movies she's starred in.

or bad again:
MovieName | Actor1 | Actor2 | Actor 3 ......
Tomb Raider | Angelina Jolie | Jon Voight | Noah Taylor
Sky Captain | Jude Law | Gweneth Paltrow | Angelina Jolie
Hackers | Jessie Bradford | Angelina Jolie | ....

- now how would you search all the movies Angelina's been in? you'd have to come up with a pretty ugly and complex query, or several queries, or worse: count them by hand!

with the junction table, it takes it actor names from the actor table, so you only have to type "angelina jolie" once: in the actor table. then a lookup is used in the junction table to simplify.

it makes data entry even easier if you've got a form to do it with: make a form frmMovies, and another form frmJunction. now, put frmJunction INSIDE frmMovies - you have just made a subform

access will ask you how to define the relationships... you say "define my own" then select MovieID in the 'parent' form, and MovieID in the subform, and now they are linked. when you view Tomb raider in the parent form, all you have to do it put in the actors (and access puts in the movieID into the junction table automatically).

and when you want to find all the movies angelina's been in, you only have to query the ActorID field in the Junction table and it will pull all movies that are linked with her out for you!

 

[lmcc007]

junction tables allow your data to be normalised.

think of a movie database. you can have many actors, and many movies. now, some actors appear in the same movies, while other movies have different actors.

instead of making one big table for which actors are in movies, you make three tables: one for actors, one for movies, and one to join them: ActorsInMovies (or something)

the records in the third table (the junction table) have it's own junctionID (so you can refer to the combination), a lookup field for an actor and a lookup field for a movie.

you would then enter data per actor per movie... so, let's say you wanted to enter all the movies angelina jolie starred in you would do it like this:

record 1: angelina jolie | tomb raider
record 2: angelina jolie | sky captain and the world of tomorrow
record 3: angelina jolie | hackers

and so on.

but those movies have other actors in them too. so you'll want to put them in too. in your actors table, you put in all the actors you know you'll need. then, you link them to your movies in your junction table like this:

record 4: jonny lee miller | hackers
record 5: jessie bradford | hackers
record 6: mathew lillard | hackers
record 7: jon voight |tomb raider
record 8: noah taylor | tomb raider
record 9: gwenyth paltrow | sky captain and the world of tomorrow
record 10: jude law | sky captain and the world of tomorrow

and so forth...

see, in one big, ugly, non-normalised table you'd probably do this:

MovieName |Actors
Tomb Raider | Angelina Jolie, Jon Voight, Noah Taylor

and then for hackers, you'd have to type in angelina again, and then again in other movies she's starred in.

or bad again:
MovieName | Actor1 | Actor2 | Actor 3 ......
Tomb Raider | Angelina Jolie | Jon Voight | Noah Taylor
Sky Captain | Jude Law | Gweneth Paltrow | Angelina Jolie
Hackers | Jessie Bradford | Angelina Jolie | ....

- now how would you search all the movies Angelina's been in? you'd have to come up with a pretty ugly and complex query, or several queries, or worse: count them by hand!

with the junction table, it takes it actor names from the actor table, so you only have to type "angelina jolie" once: in the actor table. then a lookup is used in the junction table to simplify.

it makes data entry even easier if you've got a form to do it with: make a form frmMovies, and another form frmJunction. now, put frmJunction INSIDE frmMovies - you have just made a subform

access will ask you how to define the relationships... you say "define my own" then select MovieID in the 'parent' form, and MovieID in the subform, and now they are linked. when you view Tomb raider in the parent form, all you have to do it put in the actors (and access puts in the movieID into the junction table automatically).

and when you want to find all the movies angelina's been in, you only have to query the ActorID field in the Junction table and it will pull all movies that are linked with her out for you!

This is very good!


You know I used MS Video Collection DB template and I don't see any junction tables. Oops, I'm wrong! I guess the VideoActorJoin is a junction table. I have to look at it some more because I never thought of it as a junction table.

 

[wiklendt]

I guess the VideoActorJoin is a junction table.

LOL i would say so happy learning lmcc007....

 

[wiklendt]

Also, I tried the expression you sent and it works. The only thing is it does not select the word; I have to type the word in the parameter dialog box. The cancel works great.

hopefully you found the one i posted later, the one that ACTUALLY works.

yeah, the cancel thing i needed to put in there coz it was driving me up the wall when i was trying out different ways of putting in the code...

 

[lmcc007]

hopefully you found the one i posted later, the one that ACTUALLY works.

yeah, the cancel thing i needed to put in there coz it was driving me up the wall when i was trying out different ways of putting in the code...

Yes, and it is exactly what we need.

Why is there a txtHomonymValue unbound text box on the form?

 

[wiklendt]

Yes, and it is exactly what we need.

Why is there a txtHomonymValue unbound text box on the form?

oops - sorry, you can delete that now. that was a left over from one unsuccessful way of passing the value to the query that i tried... it's not used in the final version...

 

[lmcc007]

oops - sorry, you can delete that now. that was a left over from one unsuccessful way of passing the value to the query that i tried... it's not used in the final version...

Thank you! I will delete it.