Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help opening a subform

Posted on 2013-12-19
16
Medium Priority
?
348 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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
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…

580 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