Open form to create new record for selected customer on current form (1 Viewer)

[c_willis]

I need for my users to be able to rate a company. My plan was to have a table that would keep each rating in a record for the specific company. When the company information is displayed, a query would average the rates for that company and display on the form, report. (This works from my test database because I have dummy information in it)
From the company list form I'd like a button to open the rating form based on the selected company.
The problem I'm having is, if there isn't already a record for the company in the rating table, I cannot get the rating table to open to a new record for the selected company.
I hope this makes sense. Thanks in advance for your help.

 

[June7]

What does 'cannot' mean - error message, wrong result, nothing happens? Post your code.

Could do a DLookup() on table to first see if there is a record and open form accordingly.

Why would average calculation display on table? This calculated result should not be saved to table.

 

[c_willis]

Here's the code for the button:
Private Sub SubmitRatingBtn_Click()
DoCmd.OpenForm "CompanyListF", , , "CompanyID = " & Me!CompanyID, acFormAdd
End Sub

There may not be a record for the company in the rating table if it hasn't been rated yet.

Calculations would be made with a query and displayed on a form.

 

[June7]

Try:

Dim booHasRating As Boolean
If DCount("*", "Ratings", "CompanyID=" & Me!CompanyID) > 0 Then booHasRating = True
DoCmd.OpenForm "CompanyListF", , , IIf(booHasRating, "CompanyID = " & Me!CompanyID, ""), acFormAdd

 

[c_willis]

The form opens but is blank.

If I add the company ID and Name to the Ratings Table - it works because it has record to open.

Maybe I can INSERT INTO the Ratings table the CompanyID and Name when I add the companies.

 

[June7]

Sorry, the form is opened in acFormAdd mode so no records will be retrieved. Instead, use:

DoCmd.GoToRecord , , acNewRec

Yes, first creating record in Ratings table is an option.

Another approach is to pass CompanyID to form via OpenArgs and have code behind that form manage how it is displayed to user. Would use code similar to above to test for existing record(s) and apply filter if records exist and then move to New Record row.

But none of this will save the CompanyID as foreign key into Ratings table. That requires additional code.

Or use form/subform arrangement.

 

[arnelgp]

how do you add your records?
is this similar to what you are doing?

 

[bastanu]

In the ratings form set the Default Value for the company ID (and company name - but you should really just store the ID) to the value of the ID on the calling form's current ID.

 

[c_willis]

arnelgp - this is exactly what I need it to do!

Thank you all for helping me with this issue.

 

[Mike Krailo]

That demo by Arnel was interesting enough to get me to play around with it. There are a few things in there that I didn't know about. Thanks.