Double Click Event but Wrong Record Displays

[cindyareed]

I have set up my subform with a combo box that will allow the user to create a new record if "not in list". I also want the user to beable to update an existing record if they double click the combo box.

The correct form displays on both of the above conditions except I want the update to show the record that was double clicked on. It is going to the first record in the table. How do I force it to bring in the record that was double clicked on?

Thanks for any help

Cindy

 

[David R]

Look up the Access help file, 'synchronize records between two forms'

 

[cindyareed]

OK I got that but now I am getting a enter parameter value which I don't want the user to have to enter. Can you point me in the right direction on having the value from the original form passing the value to the new form.

Thanks very much for your help, I appreciate that you told me where to look instead of just how to do it as I will retain more if I do the research myself. I have trouble with some of the phraseology that is needed to look some of this stuff up to thanks for your guidance.
Cindy

 

[David R]

No problem. Depending on the parameter you're passing (i.e. whether you have it stored somewhere on the current form, or it's just a value you're using) you can either use the WHERE clause of OpenForm when you open your second form, or use the OpenArgs clause of OpenForm (search help on OpenForm method and hopefully you'll see what I mean).

Good luck,
David R

 

[cindyareed]

David,

ok I need some luck...I am not getting this!

I have a form with student info (FStud). The form has 2 tabs and each of those has a subform on it, one with parent info (FStudParSF). If the user double clicks on the ParParID field the parent form will display (FPar). I have tried different variations in the where condition of the action arguments but still am not getting what I think I should be getting.

This is what I have tried:
[ParParID]=[Forms]![FPar]![ParParID]
this will bring up the parent form with no data.

Also:
[Forms]! [FStudParSF]![ParParID]=[Forms]![FPar]! [ParParID]
this brings up the enter parameter value display.

I have tried a couple of other variations of the above but with the same results.

I know I am missing something here but can't figure out what. Any direction you give me is appreciated.

Thanks Cindy

 

[David R]

Here's the code I have behind a button to pop up a record in a small subform:

Private Sub button_History_Click()
        DoCmd.OpenForm "subformChanges", acNormal, , "[tableParticipants]![ParticipantID]=[Forms]![ParticipantInformation]![ParticipantID]"
End Sub

See if that helps. You may need to explicitly state the table name in the WHERE clause if the control on the form is named the same as the field..maybe?

 

[cindyareed]

David,

I did as you have above and switched from a Macro to the code and I am still getting the "enter parameter value' window. How do I get it to bypass that and go directly to the form in edit mode. (if I enter the Parent ID into the window and press enter I am getting the correct record in the form).

Thanks for your help,
Cindy

 

[David R]

Post your code that you're using now, as well as the table/query names involved, and the form name. My guess is if you're getting a "Enter Parameter Value" prompt then you didn't spell/refer to a field correctly.

 

[cindyareed]

David,
Here is what I have:

1. Form name: Form - Student/Parent Query Form
Query name: Student Query

2. Subform name: Form - Student/Parent query Subform
Query name: Form - ParentToStudent query
Field name: ParParID

3. From called on double click: Form - Parent query
Query name: Form - Parent query
Field name: ParParID

here is my code:
Private Sub ParParID_DblClick(Cancel As Integer)
DoCmd.OpenForm "Form - Parent query", acNormal, , "[Form - Parent query]![ParParID] = [Forms]![Form - Student/Parent query Subform]![ParParID]"
End Sub

Thanks for your help
Cindy




[This message has been edited by cindyareed (edited 05-08-2002).]

 

[cindyareed]

David,
Still hoping you can help me on the code from my last post.

Thanks,
Cindy

 

[David R]

Sorry about the delay, I've been out of town on business.

Try this instead:
DoCmd.OpenForm "Form - Parent query", acNormal, , "[Form - Parent query]![ParParID] = " & [Forms]![Form - Student/Parent query Subform]![ParParID]

If ParParID is a text field you'll have to enclose it in single quotes: "...= '" & blahblah![ParParID] & "'"

HTH,
David R

 

[cindyareed]

David,

Thanks for your help but I am still having trouble. The field ParParID is a combo box. I am getting a run time error:
2450 it can not find form 'Form - Student/Parent query Subform' referred to in a macro or VB code.

I have tried a couple of other things and get other errors or this same error. This is definetly the name of the subform on the form.

I copied what you keyed in directly to my code. What am I still missing here?

Thanks for your time
Cindy

 

[David R]

Spaces and unusual characters in field and object names can cause all sorts of problems. I would strongly recommend that you go through and change your fields to use underscores instead of spaces, and remove things like slashes, periods, etc. Use the Caption property to display the name you want, don't try to make the name itself fully correct English. It will play havoc with your code and references for a while until you get all the names changed over effectively but in the long run I think you will be much happier.

Microsoft Support has an article listing the characters that should not be used (and while spaces are permissible, they are troublesome):
The following symbols should not be used as part of a field name:
. / * : ! # & - ? ; " ' $

 

[cindyareed]

You are right, I should have done that to begin with...If I change the names of my forms and querys will it propigate/ripple though what I have already set up?

I appreciate your time on this,
Cindy

 

[cindyareed]

David,

I have gone though my code, query and forms and renamed all to get rid of "/" and spaces. However I am back to the beginning again. How do I get the value from the ParParID field (which is a combo box) on the subform to the form displayed when a double click is performed on that field without the window "Enter Parameter value" displaying and requiring entry from a field that I should be able to put into the form being called.

I have tried using a macro and VB.

here is the code:

Private Sub ParParID_DblClick(Cancel As Integer)
DoCmd.OpenForm "FPar", acNormal, , "[FPar]![ParParID] = [Forms]![FSStuPar]![ParParID]"
End Sub

or Macro

[Forms]![FSStuPar]![ParParID]=[Forms]![FPar]![ParParID]

I get the same seniario with both.

Thanks Cindy

 

[David R]

Look again at the example I gave you. You should be matching the value from your underlying table/query from the initial form, to the field on your intiial form that currently holds that value.
In my example, I want only the record from tableParticipants where ParticipantID is equal to the ParticipantID currently shown in the field on my form; that would be the current record.