Jumping to a record in a sub form to a value selected in a data sheet

[nikmav]

Hi guys,

What I have is a Job List form where a user clicks on a QuoteNumber fieldwhich then opens the Customers form based on the CustomerID.

This is done by:

Private Sub QuoteNumber_Click()

DoCmd.OpenForm "Customers", acNormal, , "CustomerID=" & Me.CustomerID, acFormEdit, acDialog

End Sub

This works fine.

On the Customers form I have 2 sub forms being a datasheet JobDataSheet which has all the jobs allocated to the customer and JobForm which is the detail of the selected job from JobDataSheet.

What I would like the above on click to jump to the job in JobDataSheet where the QuoteNumber is found in the record and subsequently populate the JobForm with the details of the record.

I am using the following to no avail:

Private Sub QuoteNumber_Click()

DoCmd.OpenForm "Customers", acNormal, , "CustomerID=" & Me.CustomerID, acFormEdit, acDialog

'Dim Variables
Dim rst As Recordset
Dim strSearchName As String

'Set recordSet
Set rst = Forms!Customers.JobDataSheet.Form.RecordsetClone

'assign QuoteNUmber to variable
strSearchName = Str(Me.QuoteNumber)

'find QuoteNumber
rst.FindFirst "QuoteNumber = " & strSearchName

'if no match found then go to first record otherwise go to the job that matches the quote number

If rst.NoMatch Then
DoCmd.GotoRecord acDataForm, Forms!Customers.JobDataSheet, acFirst
Else
Forms!Customers.JobForm.Form.Bookmark = rst.Bookmark
End if

rst.close
Set rst = Nothing

End Sub

A couple of problems with it.

1. It doesnt do what its supposed to do! It opens the Customers form but doesnt go to the appropriate record.

2. If I then close the Customer form I get a Runtime error 2450 - Microsoft Access cannot find the reference form Customers

Any ideas?

 

[vbaInet]

Is the data type of QuoteNumber a string?

strSearchName = Str(Me.QuoteNumber)

Str also returns a leading space.

 

[nikmav]

Hi vbaInet,

Thanks for the reply.

QuoteNumber is type Text as they can be alphanumeric.

I modified the code to trim the leading space:

strSearchName = Str(Me.QuoteNumber)
strSearchName = LTrim(strSearchName)

The subform still isnt going to the right record.

 

[vbaInet]

Replace these lines:

'assign QuoteNUmber to variable
strSearchName = Str(Me.QuoteNumber)

'find QuoteNumber
rst.FindFirst "QuoteNumber = " & strSearchName

with these:

'find QuoteNumber
 rst.FindFirst "QuoteNumber = '" & Me.QuoteNumber & "'"

 

[nikmav]

Still no joy. The customer form pops up but still on the very first record.

Would it help if I send through my file?

 

[Thales750]

I cheat.
I always have a bunch of invisible text boxes on my forms. I assign values to them and use those as criteria for queries.

In 15 years of developing in Access I have never had one fail.

 

[nikmav]

Hi Thales750,

Im not sure how invisibe text boxes are going to help me. I can get the value that is clicked on to come through on a msg box after it has been clicked. The problem is setting the current record on the form that is opened.

 

[vbaInet]

This should be all you need:

Private Sub QuoteNumber_Click()
    
    DoCmd.OpenForm "Customers", acNormal, , "CustomerID=" & Me.CustomerID, acFormEdit, acDialog

    Forms!Customers!JobDataSheet.Form.Recordset.FindFirst "QuoteNumber = '" & Me.QuoteNumber & "'"

End Sub

If nothing is found, it jumps to the first record. That's how a search using FindFirst works.

If the JobForm is linked properly then it will show the correct record too.

 

[nikmav]

Hi vbaInet,

Same problem, doesnt change record and still comes up with the runtime errror when I close the form.

They are both linked to the Jobs record source.

I thought that because they are in a tab it may not have been referenced but I tried:

Private Sub QuoteNumber_Click()
    
    DoCmd.OpenForm "Customers", acNormal, , "CustomerID=" & Me.CustomerID, acFormEdit, acDialog

    Forms!Customers!Tabs.JobDataSheet.Form.Recordset.FindFirst "QuoteNumber = '" & Me.QuoteNumber & "'"

End Sub

and that didnt work either.

 

[vbaInet]

Perhaps you can upload a stripped down version of your db and I'll take a quick look.