Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help opening a subform

Posted on 2013-12-19
16
Medium Priority
?
344 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 31

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 31

Expert Comment

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

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 31

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 2000 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 40

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

963 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