Solved

Need help opening a subform

Posted on 2013-12-19
16
313 Views
Last Modified: 2013-12-20
Hello Experts,

I am using Access 2K on a Windows XP Pro platform.

I have a form named NewProspect which has a subform named NewQuotes.
The forms are linked by a field called CustomerNumber.

The recordsource for NewProspect is a table that includes our customers name, and various customer information.

The recordsource for NewQuotes is our Quotes table.  One of the fields in the Quotes table is CustomerNumber.

I would like to open the two forms to display a specific customer's information in the NewProspect form, and a specific quote in the NewQuotes form.

I can open the NewProspect for to a specific customer using the following code without problem:
        DoCmd.OpenForm "NewProspect", , , "[CustomerNumber]=" & Me.CustomerNumber

The Quote displayed is then the first quote in the Quotes table for this customernumber.
 
I can also use the code below to display the last quote for this CustomerNumber in the NewQuotes table using the code:

        DoCmd.OpenForm "NewProspect", , , "[CustomerNumber]=" & Me.CustomerNumber
       
        DoCmd.GoToRecord , , acLast      
   
All works well.  But instead of opening to the last quote, I want to open to a specific quote,  for example, QuoteNumber=12345.  How would I do this?



Thanks.

By the way, QuoteNumber is unique field in the NewQuotes table.
0
Comment
Question by:pcalabria
  • 6
  • 5
  • 3
  • +1
16 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39731415
You can use bookmarks to locate and set the subform record:

Dim rs as DAO.recordset

DoCmd.OpenForm "NewProspect", , , "[CustomerNumber]=" & Me.CustomerNumber
set rs = Forms!NewProspect.NewQuotes.RecordsetClone

rs.FindFirst "QuoteNumber=12345"
if rs.Nomatch = true then
      msgbox "QuoteNumber 12345 was not found"
      exit sub
end if
Forms!NewProspect.BookMark = rs.Bookmark

Open in new window


The exact syntax of course will be different depending on your naming conventions, and how you are actually determining which quotenumber you want to find.
0
 

Author Comment

by:pcalabria
ID: 39731851
Thanks but I'm getting an error message..

Line 4 above causes the following error:

Runtime error 438
Object doesn't support this property or method
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39732005
So you want to, from a current form,  supply field 1 to open NewProspect  and field2 to land on specific record in NewQuotes?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 61

Expert Comment

by:mbizup
ID: 39732030
Try this correction (see the inline notes):

Dim rs as DAO.recordset

DoCmd.OpenForm "NewProspect", , , "[CustomerNumber]=" & Me.CustomerNumber
set rs = Forms!NewProspect.NewQuotes.Form.RecordsetClone  '**** Needed a .Form after the subform name

rs.FindFirst "QuoteNumber=12345"
if rs.Nomatch = true then
      msgbox "QuoteNumber 12345 was not found"
      exit sub
end if
Forms!NewProspect.BookMark = rs.Bookmark

Open in new window

0
 

Author Comment

by:pcalabria
ID: 39732047
Yes.  
The current form contains the text boxes CustomerNumber and QuoteNumber.

When I click a command button, I want to open another form, called NewProspect to the customer indicated in me. CustomerNumber.

The NewQuotes form, which is a subform of NewProspect, should then open to Me.QuoteNumber


Here is my code that causes the error described above:

    'Open NewProspect form
        DoCmd.OpenForm "NewProspect", , , "[CustomerNumber]=" & Me.CustomerNumber

       
    'Open a specific Quote

    Set rs = Forms!NewProspect.newquotes.RecordsetClone
   
    rs.FindFirst "QuoteNumber=" & Me.QuoteNumber
   
    If rs.NoMatch = True Then
          MsgBox "QuoteNumber " & Me.QuoteNumber & " was not found"
          Exit Sub
    End If
   
    Forms!NewProspect.Bookmark = rs.Bookmark
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39732055
We cross-posted...

Did you see my correction at http:#a39732030 ?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39732209
Check mbizup's  comment  http:#a39732030 ?  first
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39732242
You don't need all that extra code.  You just need to add QuoteNumber to the criteria.


DoCmd.OpenForm "NewProspect", , , "[CustomerNumber]=" & Me.CustomerNumber & " AND QuoteNumber = " & Me.QuoteNumber
0
 

Author Comment

by:pcalabria
ID: 39732401
Still need help...

PatHartman... tried your code first because it seemed like a wonderful solution, but it did not work.  I suspect because the NewProspect form is based on a table that does not include the QuoteNumber field.  

mbbizup... I'm getting a new error: Not a valid bookmark.

My code looks like this;
DoCmd.OpenForm "NewProspect", , , "[CustomerNumber]=" & Me.CustomerNumber
set rs = Forms!NewProspect.NewQuotes.Form.RecordsetClone  '**** Needed a .Form after the subform name

rs.FindFirst "QuoteNumber=" & me.quotenumber
if rs.Nomatch = true then
      msgbox "QuoteNumber " & me.quotenumber & " was not found"
      exit sub
end if
Forms!NewProspect.BookMark = rs.Bookmark

Sorry, I had to repost.
Copy and pasted did not work correctly for me.
The last line causes the message to be displayed.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39732437
Ok! Try this:

'current form, has f_main, f_sub, command button cmdCheck
Private Sub cmdCheck_Click()
    ' open main form
    ' aid == CustomerNumber
    'bdesc == QuoteNumber 
    ' if type is numeric remove ' from statement.
    DoCmd.OpenForm "a", acNormal, , "aid='" & f_main & "'", , , "bdesc='" & f_sub & "'"
Exit Sub

'sub form
Private Sub Form_Load()
    Me.Filter = Me.Parent.OpenArgs
    Me.FilterOn = True
End Sub

Open in new window

'using your names
'current form, has f_main, f_sub
Private Sub cmdCheck_Click()
    ' open main form
    ' if type is numeric remove ' from statement.
    DoCmd.OpenForm "NewProspect", acNormal, , "CustomerNumber='" & Me.CustomerNumber & "'", , , "QuoteNumber='" & Me.QuoteNumber & "'"
Exit Sub

'sub form NewQuotes
Private Sub Form_Load()
    Me.Filter = Me.Parent.OpenArgs
    Me.FilterOn = True
End Sub

Open in new window

0
 

Author Comment

by:pcalabria
ID: 39732438
Sorry, I had to edit my last comment as I had a copy and paste problem when it was posted.  (( For some reason, copy and paste does not always work for me correctly when I try to copy from my Remote Desktop machine to my local machine.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39732479
Whoops - I was setting the book mark on the main form, not the subform.  Try this:


DoCmd.OpenForm "NewProspect", , , "[CustomerNumber]=" & Me.CustomerNumber
set rs = Forms!NewProspect.NewQuotes.Form.RecordsetClone  '**** Needed a .Form after the subform name

rs.FindFirst "QuoteNumber=" & me.quotenumber
if rs.Nomatch = true then
      msgbox "QuoteNumber " & me.quotenumber & " was not found"
      exit sub
end if
Forms!NewProspect.NewQuotes.Form.BookMark = rs.Bookmark 

Open in new window

0
 

Author Comment

by:pcalabria
ID: 39732508
mbizup

Works perfectly!

Is there any memory clean up required..
closing or setting an object to nothing?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39732519
Always a good practice with recordset code, as is error handling.

Since you're not actually "Opening" a recordset, you don't need a Close statement, but you should set it to NOTHING at all exit points.
On Error Goto PROC_ERR

DoCmd.OpenForm "NewProspect", , , "[CustomerNumber]=" & Me.CustomerNumber
set rs = Forms!NewProspect.NewQuotes.Form.RecordsetClone  '**** Needed a .Form after the subform name

rs.FindFirst "QuoteNumber=" & me.quotenumber
if rs.Nomatch = true then
      msgbox "QuoteNumber " & me.quotenumber & " was not found"
      Goto PROC_EXIT
end if
Forms!NewProspect.NewQuotes.Form.BookMark = rs.Bookmark 

PROC_EXIT:
      set rs = Nothing
      Exit Sub

PROC_ERR:
     msgbox "ERROR " & Err.Number & ": " & Err.Description
     Resume PROC_Exit
End Sub

Open in new window

0
 

Author Closing Comment

by:pcalabria
ID: 39732548
Perfect solution, and thanks for explaining close and set to nothing!
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39732575
tried your code first because it seemed like a wonderful solution, but it did not work.  I suspect because the NewProspect form is based on a table that does not include the QuoteNumber field
I missed the fact that you wanted to position your subform rather than the main form you were opening.  Using the Where argument as I suggested only works on main forms so stick it in your toolbox for later.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

786 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