Select Record and set as Forms Current Record (Bookmark)

[Shokoya]

Hi Guys,

Ok...this problem looks like it might be harder to xplain than it may actually be to solve it, so pls bear with me.

I have a FrmEnquiry on a tab page. Related to it, and on the 2nd tab page is FrmQuotation. Now, one Enquiry can have many Quotations associated with it(i.e Client may refuse 1st Quote, so a second one would be created.)Once a Quotation is finally accepted a Yes/No Accepted box is ticked.Still with me??? Ok, heres where it gets complicated;

I'm having problems coming up with a code that will do the following; go through all Quotation records in tblQuote that have the current Enquiry number, and select the record whos' Accepted field = True, then set this as the current record of FrmQuotation. In this way, the user will not need to go through all Quotations records to loacte the one that was accepted.

Began to come up with this but....

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Quote WHERE Accepted = True AND Enquiry_Id = " & Me.Enquiry_Id, dbOpenDynaset)

If .NoMatch Then
Beep
MsgBox "The record cannot be found in the source of this form!"
Exit Sub
Else
Forms![Enquiry].Bookmark = .Bookmark ‘ set forms record to display rst
End If

Can anybody pls help??

 

[TheAceMan1]

How are ya Shokoya . . . . .

Your just about there . . .

Realize that the Bookmark of the Quote recordset and the bookmark of the Enquiry Form, are not the same!

What you neet to do is open a RecordsetClone of the Enquiry, rst.FindFirst for ID of the Quote recordset, and then set the BookMark via the clone.

TheAceMan

 

[hneal98]

Here is a module i did for a project a long time ago:

Private Sub SearchCrite_AfterUpdate()
'This procedure tries to find the matching IPA record.
'If the matching record is found, the procedure goes to it.
'If the record is not found, the focus stays on the current record.

Dim Criteria As String 'This is the argument to the FindFirst Method.
Dim MyRS As Recordset 'Recordset used to search.
Dim SearchString As String 'The name of the company to search for.

Set MyRS = Me.RecordsetClone
'Build the criteria.
SearchString = Screen.ActiveControl
Criteria = "[IPAName] Like" & Chr(39) & SearchString & Chr(42) & Chr(39)
'Perform the search.
MyRS.FindFirst Criteria
If MyRS.NoMatch Then
MsgBox "Could not Locate IPA: " & SearchString
Else
'Syncronize the form's record to the dynaset's record.
Me.Bookmark = MyRS.Bookmark
End If
Me!SearchCrite = Null

MyRS.Close

' srchCriteria = (SearchString & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))

End Sub

Just make the changes according to your needs and let me know if you have questions about any of the code.

 

[Shokoya]

Hey AceMan and hneal98...thanx for your guidance thus far.

Have tried to adjust hneals' code to my requirements and have come out with the following;

Private Sub Form_Current()
Dim Criteria As String 'This is the argument to the FindFirst Method.
Dim MyRS As Recordset 'Recordset used to search.


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Quotation WHERE Enquiry_Id= " & Me.Enquiry_Id, dbOpenDynaset) 'get the records from table Quote that have the same Enquiry_Id as the current records

Set MyRS = Me.RecordsetClone
Criteria = "[Accepted] = True" 'go through rst and slect the record who's Accepted field = True
MyRS.FindFirst Criteria
If MyRS.NoMatch Then
MsgBox "No record "
Exit Sub
Else
Forms![Enquiry].Bookmark = MyRS.Bookmark
End If
MyRS.Close

End Sub

However...getting the following error message in regards to Criteria = "[Accepted] = True" line of code;
'The Microsft Jet Database engine does not recognise 'Accepted' as a valid field, name or expression'
However, 'Accepted' is definately a Yes/No field in table Quotation.With my minimal knowledge of programming I'm assuming its not performing a criteria search on the rst??

Pls instruct...

again

 

[TheAceMan1]

Shokoya! . . . .

I reread your originating post. You said:

I'm having problems coming up with a code that will do the following; go through all Quotation records in tblQuote that have the current Enquiry number, and select the record whos' Accepted field = True, then set this as the current record of FrmQuotation. . . . which makes sense!

So far from what I see we are setting bookmarks for the Enquiry form. This is the wrong form. We should be setting the bookmark of the Quotation form. This invalidates my prior post.

2nd, The PK of Quotations should be used in setting the bookmark!, not the EnquiryID. Do you see what I mean? With that I've derived the following code:

Dim db As Database, SQL As String
   Dim rstQuote As DAO.Recordset, rstClone As DAO.Recordset
   
   Set db = CurrentDb()
   Set rstClone = Me.RecordsetClone
   SQL = "SELECT * " & _
         "FROM Quote " & _
         "WHERE Accepted = True " & _
         "AND Enquiry_Id = " & frm.Enquiry_Id
   Set rstQuote = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rstQuote.BOF Then
      'No Records message
   Else
      rstClone.FindFirst "QuoteID=" & rstQuote!QuoteID
      Me.Bookmark = rstClone.Bookmark
   End If
   
   Set rstClone = Nothing
   Set rstQuote = Nothing
   Set db = Nothing

I don't no the actual name of the PK for your Quotations Form, but replace QuoteID with whatever it is.

I don't know how your triggering the code, but do not use the Current Event, as changing bookmarks triggers the Current Event an could cause recursion. Also my use of Me assumes the code is in the Quotations Form Module.



TheAceMan