Access 2002-2003 file format--code question

[lmcc007]

Hello everyone,

I think I am beginning to understand setting up tables properly in Access. I read Access 2003 Inside Out twice and I just purchased the book Access 2007 Inside Out. I redid my Homophones database. I hope I set the tables, relationship, queries, and forms up correctly.

Anyway, from the old database I copied a code to do the following while in form view: After double-clicking the Homonym field, select it, and then display it with its meaning and homonym. But, I keep getting "Enter Parameter Value ? dialog box"

I have attached the database for your review. Can’t figure out what’s wrong.

Any suggestions would be great!

Thanks!

 

[ezfriend]

That happen because in the form record source, the query ask for user input.

See image attached.

 

[lmcc007]

That happen because in the form record source, the query ask for user input.

See image attached.

No, that's not it. I just removed the parmater in the query and it still does it. This code worked in the old db that needed to be redone and stuff.

 

[RuralGuy]

Take a look at your qryMainWordDef_Search query in SQL view.

 

[lmcc007]

Take a look at your qryMainWordDef_Search query in SQL view.

What I am I looking for--don't understand. Novice! Please tell me in very simple, basic instructions. I copied this code from another d/b and tried to make it work here.

Thanks!

 

[RuralGuy]

Did you look at that query in SQL view? Delete the top line that has the parameter in it.

 

[lmcc007]

Did you look at that query in SQL view? Delete the top line that has the parameter in it.

Tried it three times and got the same thing.

 

[RuralGuy]

You have other issues but your reference to the control on the SubForm was not correct.
This link may help in that area.

 

[lmcc007]

You have other issues but your reference to the control on the SubForm was not correct.
This link may help in that area.

Do not understand Syntax for main/subforms.

 

[RuralGuy]

What part do you not understand?

 

[lmcc007]

What part do you not understand?

All of it.

 

[lmcc007]

I found how to reference the subform control in MS Access 2007 Inside Out on page 920. The only thing it is not displaying the selected word after double clicking. Below is the code.

Option Compare Database

Private Sub Homonym_DblClick(Cancel As Integer)
On Error GoTo Err_Homonym_DblClick

Dim strNewRecord As String

strNewRecord = "SELECT * FROM tblMainWordDef WHERE MainWord Like Forms![frmMainWordDef]![frmHomonym_Subform].Form![Homonym]"

Me.RecordSource = strNewRecord

Exit_Homonym_DblClick:
Exit Sub
Err_Homonym_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_Homonym_DblClick
End Sub


Any suggestions on why when I double click the Homonym it clears the Homonym and does not display the word (requery)?

 

[RuralGuy]

Sorry but I have no idea what you are trying to accomplish.

 

[lmcc007]

Sorry but I have no idea what you are trying to accomplish.

I need the expression to do:

1. After double-clicking the Homonym that the insertion point is on, I want Access to select the word and then requery. 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 the focus on the MainWord or remain on the MainWord.

"Wiklendt" created this code for me in another database and it worked, but the database was not set up properly. Attached is the database that "Wiklendt" added to the code to. You can see that it works.

I can't figure out why it won't work in the new database.

 

[RuralGuy]

We'll have to wait for someone with broader talents than mine to drop by and offer a solution.

 

[lmcc007]

We'll have to wait for someone with broader talents than mine to drop by and offer a solution.

Well, guess what, my dumb butt found the answer.

I needed to tell it what form because "Me.RecordSource = strNewRecord" is not what Access wanted.

When I put in

"Forms![frmMainWordDef].RecordSource = strNewRecord"
​

it is working.

Wow, it is always something simple. But, I thought you could use "Me." when referring to a form. Maybe because I had a subform too Access needed more info.

Thanks anyway!

 

[RuralGuy]

Glad to hear you got it sorted.