Link to home
Start Free TrialLog in
Avatar of 4charity
4charity

asked on

Requery underlying query for another form

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

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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
Avatar of 4charity
4charity

ASKER

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

DoCmd.OpenForm stDocName, , , stLinkCriteria,, acdialog
Ignore the last question, this link works fine - I have no criteria I am linking to on the popup.
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.
upload your db
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
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
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.
so what happens to current record displayed in the main form?
I want it to go away.
Replace old claimnumber with new claimnumber.
In past attempts, this just wiped out the claimnumber and kept the rest of the record there.
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
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

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.
ASKER CERTIFIED SOLUTION
Avatar of 4charity
4charity

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial