Solved

Need help opening a subform

Posted on 2013-12-19
16
294 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
Comment Utility
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
Comment Utility
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
Comment Utility
So you want to, from a current form,  supply field 1 to open NewProspect  and field2 to land on specific record in NewQuotes?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
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
Comment Utility
We cross-posted...

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

Expert Comment

by:hnasr
Comment Utility
Check mbizup's  comment  http:#a39732030 ?  first
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:pcalabria
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
mbizup

Works perfectly!

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

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
Perfect solution, and thanks for explaining close and set to nothing!
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now