Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 928
  • Last Modified:

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

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
0
thegreals
Asked:
thegreals
  • 11
  • 8
  • 3
1 Solution
 
tbsgadiCommented:
Hi thegreals,

Something like this:

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

Good Luck!

Gary
0
 
Rey Obrero (Capricorn1)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 &"'"
0
 
thegrealsAuthor 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
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
tbsgadiCommented:
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"
0
 
thegrealsAuthor 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
0
 
Rey Obrero (Capricorn1)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
0
 
thegrealsAuthor 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
0
 
Rey Obrero (Capricorn1)Commented:
answer this question...what is the recordsource of Form2?
0
 
thegrealsAuthor 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.
0
 
Rey Obrero (Capricorn1)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]
0
 
thegrealsAuthor 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.
0
 
Rey Obrero (Capricorn1)Commented:
yes, you are not responding to the questions asks..
0
 
thegrealsAuthor Commented:
Sorry,

I thought I answered it in the code I modified.

The field name is "loanname".

Thanks,


the greals

0
 
tbsgadiCommented:
try

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

End Sub
0
 
thegrealsAuthor 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
0
 
Rey Obrero (Capricorn1)Commented:
post a screen shot of the following
Loans table in design view.


and the codes you are using.
0
 
thegrealsAuthor 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
0
 
thegrealsAuthor Commented:
Hi Guys,

Here is the enter parameter box that comes up.

Regards,

the greals.
Enter-Parameter.png
0
 
Rey Obrero (Capricorn1)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
0
 
thegrealsAuthor 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
0
 
Rey Obrero (Capricorn1)Commented:
you have to include the loanID in the recordsource of the subform and Frm_My_Loan_Info
blahrev.mdb
0
 
thegrealsAuthor Commented:
Thanks so much, I really appreciate your help.

This has helped me a great deal.

Regards,

the greals
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 11
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now