We help IT Professionals succeed at work.

Access 2007 - Open up record from subform in a new form

thegreals
thegreals asked
on
Medium Priority
948 Views
Last Modified: 2013-11-27
Hi Experts,

Can you please assist with a macro that I am struggling with in Access 2007:

I have a button on a main form called Form1 and a Subform called "Subform_Mistakes" and I need to be able to open the selected record in the subform to a new form called "Form2.

Thanks for you help,
Regards,

the greals
Comment
Watch Question

Commented:
Hi thegreals,

Something like this:

Private Sub cmdB_Click()
DoCmd.OpenForm Form2, , , ID=" & Me.Subform_Mistakes.form.ID
End Sub

Good Luck!

Gary
CERTIFIED EXPERT
Top Expert 2016

Commented:
slight correction with the syntax

if ID is number data type
DoCmd.OpenForm "Form2", , , "ID=" & Me.Subform_Mistakes.form.ID

if ID is Text data type
DoCmd.OpenForm "Form2", , , "ID='" & Me.Subform_Mistakes.form.ID &"'"

Author

Commented:
Hi Guys,

That's good but I do not know how to find the ID of the record in the subform, to then open the record in another form.

Regards,

the greals

Commented:
The record has an ID which makes it unique called a Primary Key, sometime will be an autonumber.
Can be called anything in place of "ID"

Author

Commented:
Thanks,

I understand what an ID is, I just want the selected record in the subform to open in a new form.

I don't know what the ID will be when I select the record. Is there a function that works out what the LoanID is based on the selection and then replaces the ID in the macro with the relevant number.

Regards,

the greals
CERTIFIED EXPERT
Top Expert 2016

Commented:
is the LoanID your unique record identifier?

if ID is number data type
DoCmd.OpenForm "Form2", , , "LoanID=" & Me.Subform_Mistakes.form.LoanID

if ID is Text data type
DoCmd.OpenForm "Form2", , , "LoanID='" & Me.Subform_Mistakes.form.LoanID &"'"

note**
your "Form2" record source must be based on the same table thet your subform gets the LoanID

Author

Commented:
Yes, LoanID is the primary key. But I do not know what the loan ID record number is when I want to send it to the new form to edit the record set.

Is there a function that will work to find the LoanID of the selected record in the subform and then open it up in Form2?


Regards,

the greals
CERTIFIED EXPERT
Top Expert 2016

Commented:
answer this question...what is the recordsource of Form2?

Author

Commented:
The record source is the same record source as the subform. I will add more fields on Form2, but the table that is used in the Subform will be the same source table in Form2.

ie in Subform I show the Loan name and Account number from the Loans table.

In Form 2 once the relevant Loan record is selected it will put all of the selected loan record information from the loans table onto Form2 for me to edit.


regards,


the greals.
CERTIFIED EXPERT
Top Expert 2016

Commented:
is  Account number the real name of the field?
if not, post the actual name of the field from Loans table



DoCmd.OpenForm "Form2", , , "[Account number]=" & Me.Subform_Mistakes.form.[Account number]

Author

Commented:
Ok,

This is the code I have entered:

Private Sub Open_Form_Edit_Your_Loan_Click()
DoCmd.OpenForm "Form2", , , "[loanname]=" & Me.Lloans_subform.Form.[loanname]
End Sub

When I run the procedure I get an input box:
Enter Parameter Value
Home Loan 1 (This is the value of "loanname" field in the subform

When I type in Home loan 1 it shows this in the "loanname" field in Form2.

Is there a way to pass this parameter automatically based on the selection?

Have I done soemthing wrong?

Regards,

the greals.
CERTIFIED EXPERT
Top Expert 2016

Commented:
yes, you are not responding to the questions asks..

Author

Commented:
Sorry,

I thought I answered it in the code I modified.

The field name is "loanname".

Thanks,


the greals

Commented:
try

Private Sub Open_Form_Edit_Your_Loan_Click()
DoCmd.OpenForm "Form2", , , "[loanname]='" & Me.Subform_Mistakes.Form.[loanname] &"'"

End Sub

Author

Commented:
Hi Guys,

Don't know why, but it still doesn't work.


I have checked the field and it is a memo type, I then changed it to text and used your suggestions.

I have tried using another field that is an auto number with your number combinations, and I am still getting the parameter box come up.


Please help.

Regards,

the greals
CERTIFIED EXPERT
Top Expert 2016

Commented:
post a screen shot of the following
Loans table in design view.


and the codes you are using.

Author

Commented:
HI,

Please find attached the relevant screen grabs. The code I'm using is:

Private Sub Open_Form_Edit_Your_Loan_Click()
DoCmd.OpenForm "Form2", , , "[loanname] =" & Me.Lloans_subform.Form.[loanname] & ""

End Sub

Regards,

the greals
Lloans-screen-capture.png
Lloans-in-form-view.png

Author

Commented:
Hi Guys,

Here is the enter parameter box that comes up.

Regards,

the greals.
Enter-Parameter.png
CERTIFIED EXPERT
Top Expert 2016

Commented:
better if you will upload your db.
do a compact and repair first to reduce the size of the db.
if db > 4mb  zip it.

check Attach File box below to upload the db

Author

Commented:
Hi,

I have deleted the majority of my database and attached the relevant tables and forms.

Please use the Frm_Open_Form and it will open Frm_My_Loan_Info

Still getting the same issue.

I hope you can assist me.

Regards,


the greals


blah.mdb
CERTIFIED EXPERT
Top Expert 2016
Commented:
you have to include the loanID in the recordsource of the subform and Frm_My_Loan_Info
blahrev.mdb

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks so much, I really appreciate your help.

This has helped me a great deal.

Regards,

the greals
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.