?
Solved

Create new record in a form based on data entered in an unbound form

Posted on 2006-07-06
20
Medium Priority
?
350 Views
Last Modified: 2010-05-18
We have an unbound form called frmNewPolicy containing the following fields:
ContactID  (a combo box)
ProviderID (a combo box)
ProductID (a combo box)
FundID (a combo box)
KFDRef (a text box)

Also on the form is a control button which opens the form frmPolicy (which has a subform called subPolicyFund - linked by PolicyID

On opening that form it should
1) Go to a new record (creating a new PolicyID)
2) Put the ContactID from frmNewPolicy in to the ContactID combo box in frmPolicy
3) Put the ProviderID from frmNewPolicy into the ProviderID combo box in frm Policy
4) Put the ProductID from frmNewPolicy into the ProductID combo box in frmPolicy
5) Put the FundID from frmNewPolicy into OriginalFundID combo box in the SUBFORM subPolicyFund
6) Put the KFDRef from frmNewPolicy into the KFDRef text field in frmPolicy



0
Comment
Question by:Ludique
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
20 Comments
 
LVL 85
ID: 17050713
Your code in the button would be like this:

DoCmd.OpenForm "frmPolicy", , , , acFormAdd
Forms("frmPolicy").ContactID = Me.ContactID
Forms("frmPolicy").ProviderID= Me.ProviderID
etc etc
Forms("frmPolicy").subPolicyFund.Form.OriginalFundID= Me.FundID

Of course, your combos will have to be setup properly in frmPolicy with the correct .RowSources
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17050721
do something like this on form load:
docmd.GoToRecord , , acNewRec
me.contactID = forms!frmNewPolicy!ContactID
'repeat for other comboboxes
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17050753
for the subform one you can do something like:

me.subformname.controls("OriginalfundID") = forms!frmNewPolicy!FundID
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17050815
one issue with putting all of this in the on load of the form is that you might want to open frmPolicy without opening it via frmNewPolicy, if that is the case, then do something like this:

frmNewPolicy's button to open frmPolicy

onclick:
DoCmd.OpenForm "frmPolicy", , , , , , "frmNewPolicy"

that will pass "frmNewPolicy" to frmPolicy when it opens, so you can do this:

frmPolicy's onload:
if me.openArgs = "frmNewPolicy" then
   docmd.GoToRecord , , acNewRec
   me.contactID = forms!frmNewPolicy!ContactID
   me.providerID = forms!frmNewPolicy!ProviderID
   me.productID = forms!frmNewPolicy!ProductID
   me.KFDRef = forms!frmNewPolicy!KFDRef
   me.subpolicyfund.controls("OriginalfundID") = forms!frmNewPolicy!FundID
end if

not sure exactly what your control names are, so you may need to change that
0
 

Author Comment

by:Ludique
ID: 17050857
Thank you thank you thank you
We're trying out options now - back with points in a bit!
thank you thank you thank you
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17050954
your welcome your welcome your welcome
either solution will work, LSM's will do all the work in frmNewPolicy, mine will do all of the work in frmPolicy
your welcome your welcom your welcome :) :)
0
 

Author Comment

by:Ludique
ID: 17051079
Good so far..... the code looks like this:

Private Sub Form_Load()
If Me.OpenArgs = "frmNewPolicy" Then
  DoCmd.GoToRecord , , acNewRec
  Me.ContactID = Forms!frmNewPolicy!ContactID
  Me.ProviderID = Forms!frmNewPolicy!ProviderID
  Me.ProductID = Forms!frmNewPolicy!ProductID
  Me.Kfdreference = Forms!frmNewPolicy!Kfdreference
  Me.subPolicyFund.Controls("OriginalFundID") = Forms!frmNewPolicy!FundID
End If
 
End Sub

BUT..........
the PolicyID (autonumber) which is created when the new record is created in frmPolicy does not get refreshed/updated in subPolicyFund

If we refresh the form subPolicyFund manually the PolicyID is put into the field but
we'd like it to happen automatically
We've tried putting
me.Refresh into the OnLoad of frmPolicy and subPolicyFund
but it didn't work

Could we put a refresh command into the code above
or should it be on another control on frmPolicy
or on subPolicyFund?
0
 
LVL 6

Accepted Solution

by:
yhwhlivesinme earned 2000 total points
ID: 17051105
Private Sub Form_Load()
If Me.OpenArgs = "frmNewPolicy" Then
  DoCmd.GoToRecord , , acNewRec
  Me.ContactID = Forms!frmNewPolicy!ContactID
  Me.ProviderID = Forms!frmNewPolicy!ProviderID
  Me.ProductID = Forms!frmNewPolicy!ProductID
  Me.Kfdreference = Forms!frmNewPolicy!Kfdreference
  Me.subPolicyFund.Controls("OriginalFundID") = Forms!frmNewPolicy!FundID
  Me.subpolicyfund.refresh '<---try this
End If
End Sub
0
 

Author Comment

by:Ludique
ID: 17051118
We get a compile error "Method or data member not found"
and it highlights
.refresh
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17051196
anyway you could upload a copy of your database to www.ee-stuff.com ?
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17051215
in the meantime you could also try:

me.subpolicyfund.Requery
0
 

Author Comment

by:Ludique
ID: 17051335
: (

Still works if you refresh subPolicyFund manually but doesn't work otherwise.

What's the difference between requery and refresh?
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17051380
requery actually runs the backend query again and refreshes the recordset, refresh just refreshes the screen....
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17051403
try moving all the on_load code to the on_open event, see if that makes a difference
0
 

Author Comment

by:Ludique
ID: 17051610
We think you're lovely.
But it still doesn't work.
0
 

Author Comment

by:Ludique
ID: 17051652
We think you're lovely.
But it still doesn't work.
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17051682
haha, any chance of getting the database uploaded? you could remove all the data if confidentiality is an issue....

either way let me make sure that I'm understanding you correctly,

you have a textbox on the main form that has PolicyID (autonumber) as it's controlsource.  you also have another textbox in your subform that has PolicyID as it's controlsource.  After the code is executed the policyID in the subform has not been updated with the same number in the parent form. those PolicyID's are in the same table correct?
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17051684
thanks for the points, do you have it working?
0
 

Author Comment

by:Ludique
ID: 17052290
No - it's gone very very wrong
getting the usual run of database is corrupt messages eg
error loading dll
id not an index on this table
invalid argument
etc

Just going to recover everything - this could take a while.
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17052302
oh geez! your problem might be something deeper than just the coding behind the form
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

800 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