Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Requery underlying query for another form

Posted on 2010-01-07
16
Medium Priority
?
822 Views
Last Modified: 2013-11-28
I have a form [frmClaimInformation]. I open a popup form from it, to add a new claim. On closing that popup window, I want the original form to be set with the ClaimNumber from the popup, and then requery the underlying query to populate the rest of the form. I am posting the code I have now, which does not work. It is attached on the Close Button of the popup.
The Requery statement is obviously wrong..... what syntax do I need for it?

qry_ClaimInformation is the name of the underlying query.
[Forms]![frmClaimInformation]![ClaimNumber] = Me.ClaimNumber
[Forms]![frmClaimInformation]![qry_ClaimInformation].Requery

DoCmd.Close

Open in new window

0
Comment
Question by:4charity
  • 9
  • 5
  • 2
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26202074
open the popup form in dialog mode, this will halt all the codes and will resume only when  the popup form is closed

docmd.openform "popupForm",,,,,acdialog
0
 

Author Comment

by:4charity
ID: 26203240
How do I change this line of code so that it still contains my link criteria to the underlying form?
Right now I have:

Dim stDocName As String
Dim stLinkCriteria As String

    stDocName = "AddClaimNumber"
   
    DoCmd.OpenForm stDocName, , , stLinkCriteria
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26203277

DoCmd.OpenForm stDocName, , , stLinkCriteria,, acdialog
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:4charity
ID: 26203279
Ignore the last question, this link works fine - I have no criteria I am linking to on the popup.
0
 

Author Comment

by:4charity
ID: 26203385
Thanks for the fix on that.
What I am looking to accomplish still doesn't happen though.

Let's say I start with a blank main form. The ClaimNumber I am looking for to fill in this form for doesn't exist, so I open the popup, and create a new ClaimNumber, with some information.
When I close the popup, I want the information I just created to populate the main form.

(The reason these are two separate forms is that there is a lot more info to fill out on the main form, so I am not doing it on the popup. And.... the other info on the popup relates to other tables that we do not usually have access to on the main form, so it is easier to organize it this way.)

So I can get the ClaimNumber to populate in the main form with the code I gave above, but then I need the form to requery, to add the new info from the popup.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26203445
upload your db
0
 

Author Comment

by:4charity
ID: 26203957
Uploaded the relevant forms, tables, queries.
Select a Claim on the Splash Screen
Click on Claim Maintenance (frmClaimMaintenance)
The Button "Add New Claim" is the link to the popup (AddClaimNumber)
SampleClaims.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26204203
so when you open the popup, you want to automatically load the claim number from the main form . is that what you want?
if not please post the step by step process
0
 

Author Comment

by:4charity
ID: 26204256
Sorry I am making this so difficult.
No, that's backwards.

The popup should be a blank record. We are creating a new claim record here.

When the popup is saved and closed, I want the ClaimNumber, PolicyNumber, InsuredName and ExaminerName from the popup to populate the frmClaimInformation form.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26204310
so what happens to current record displayed in the main form?
0
 

Author Comment

by:4charity
ID: 26204464
I want it to go away.
Replace old claimnumber with new claimnumber.
0
 

Author Comment

by:4charity
ID: 26204483
In past attempts, this just wiped out the claimnumber and kept the rest of the record there.
0
 

Author Comment

by:4charity
ID: 26204746
I was able to move the button to Add Claim onto the "ChooseClaim" screen, and when I set the ClaimNumber on that screen, it does not wipe out the existing ClaimNumber. Perhaps because it is a listbox? Actually this works fine for me to have it here. Don't need you spending alot more time on it.....

Code on the "Save & Close" Button on the AddNewClaim screen:

Forms]![Splash]![Combo0] = Me.ClaimNumber
DoCmd.Close
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26211790
The last will just overwrite the ClaimNumber value on the current record, which is not what you need here.  Assuming that the new ClaimNumber record is saved from the popup form, you could just use this syntax to go to the appropriate record on the main form (from the Save & Close button) (assuming that ClaimNumber is a Text field):
strSearch = "[ClaimNumber] = " & Chr$(39) & Me![ClaimNumber] & Chr$(39)
[Forms]![Splash].Recordset.FindFirst strSearch

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26211803
And please -- give your controls meaningful names.  When looking at the code, Combo0 isn't very helpful in identifying which control you are writing code for.
0
 

Accepted Solution

by:
4charity earned 0 total points
ID: 26213130
Thanks for the reply, Helen.
Actually, I moved the button to the Splash screen, as mentioned in my last post, and the code I had posted works well. I also added a open form string to the code, so it only uses the [Combo0] as a place holder to pass that information back to the ClaimInformation screen. The claimnumber is not overwritten - it works very nicely.

I did try the code you had posted - it did not return a new claimnumber. I also changed the form name in the code to [frmClaimInformation] (from [Splash]), since that was the original underlying form I was talking about. Nothing happened there either. Since I have code that is working well for me, I think we should just close this and  not spend any more time on it.

Attached is the code.
Thanks.
[Forms]![Splash]![Combo0] = Me.ClaimNumber
DoCmd.Close
Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmClaimInformation"
    
    stLinkCriteria = "[ClaimNumber]=" & "'" & [Forms]![Splash]![Combo0] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question