Moving to record

[kermit5]

I have a control on my form that copies the contents of a form and a subform to a new record. Here is my code. On my form, how do I find the new record? That is to say, once I copy the record, the "old record" is still the one on my form. I want to move to the "new record" as part of the copy function.


'copy record to new Quote record
intQuoteID = Forms![Bid - Master Form]![Bid - Quote]![Quote ID]
Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("Bid - Quote")
rstQuote.AddNew
intNewQuoteID = rstQuote![Quote ID]
Debug.Print "New Quote ID " & intNewQuoteID
Debug.Print "New Quote ID " & rstQuote![Quote ID]
Debug.Print "Old Quote ID " & intQuoteID
rstQuote![Master Bid ID] = Forms![Bid - Master Form]![Master Project ID]
rstQuote![Other Mfr] = Forms![Bid - Master Form]![Bid - Quote]![Other Mfr]
rstQuote![Comments] = Forms![Bid - Master Form]![Bid - Quote]![Comments]
rstQuote.Update
Forms![Bid - Master Form]![Bid - Quote].Requery

'copy quote items to new quote items records
strSQL = "Select * " & _
"FROM [Bid - Quote Items] " & _
"WHERE [Quote ID] = " & intQuoteID
Set rstOldQuoteItem = dbs.OpenRecordset(strSQL)
Set rstNewQuoteItem = dbs.OpenRecordset("Bid - Quote Items")
Do While Not rstOldQuoteItem.EOF
rstNewQuoteItem.AddNew
rstNewQuoteItem![Quote ID] = intNewQuoteID
Debug.Print "New Quote ID " & rstNewQuoteItem![Quote ID]
rstNewQuoteItem![Quantity] = rstOldQuoteItem![Quantity]
Debug.Print "Qty " & rstOldQuoteItem![Quantity]
rstNewQuoteItem![Description] = rstOldQuoteItem![Description]
Debug.Print "Description " & rstNewQuoteItem![Description]
rstNewQuoteItem![Finish] = rstOldQuoteItem![Finish]
rstNewQuoteItem![Price] = rstOldQuoteItem![Price]
rstNewQuoteItem![Comments] = rstOldQuoteItem![Comments]
Debug.Print "Comment " & rstNewQuoteItem![Comments]
rstNewQuoteItem.Update
rstOldQuoteItem.MoveNext
Loop
Forms![Bid - Master Form]![Bid - Quote]![Bid - Quote Items]


Thanks is advance
Scott

 

[ghudson]

DoCmd.RunCommand acCmdRecordsGoToNew

That command will take the form to a new record.

 

[kermit5]

This does go to a new record, but it does not goto the new record created by my code. I do not just want to go to a new one, I want THE record that I copied to a new record to be the record on the form.

Does that make sense?

 

[Travis]

'copy record to new Quote record 
intQuoteID = Forms![Bid - Master Form]![Bid - Quote]![Quote ID] 
Set dbs = CurrentDb 
Set rstQuote = dbs.OpenRecordset("Bid - Quote") 
rstQuote.AddNew 
intNewQuoteID = rstQuote![Quote ID] 
Debug.Print "New Quote ID " & intNewQuoteID 
Debug.Print "New Quote ID " & rstQuote![Quote ID] 
Debug.Print "Old Quote ID " & intQuoteID 
rstQuote![Master Bid ID] = Forms![Bid - Master Form]![Master Project ID] 
rstQuote![Other Mfr] = Forms![Bid - Master Form]![Bid - Quote]![Other Mfr] 
rstQuote![Comments] = Forms![Bid - Master Form]![Bid - Quote]![Comments] 
rstQuote.Update 
Forms![Bid - Master Form]![Bid - Quote].Requery 

[i]
Dim rstClone as DAO.recordset
rstClone=Forms![Bid - Master Form]![Bid - Quote].RecordsetClone
rstClone.MoveFirst
Do While Not rstClone.EOF
    If rstClone!QuoteID=intQuoteID then
       Forms![Bid - Master Form]![Bid - Quote].Recordset.BookMark=rstClone!BookMark
       Exit Do
    End If
    rstClone.MoveNext
Loop
[/i]

 

[kermit5]

Here are a couple of hitches:

With the line:
rstClone=Forms![Bid - Master Form]![Bid - Quote].RecordsetClone
i get a compile error:Invalid use of property
rstClone=

With the code:
Set rstClone=Forms![Bid - Master Form]![Bid - Quote].RecordsetClone
I get error # 438:Object doesn't support this property or method.

What am I missing?

 

[Travis]

What version of Access are you using?

Do you have the DAO 3.6 Reference in your MDB file?

 

[kermit5]

I am using Access2K2. I do have that referenced.

 

[Travis]

Change this:

Dim rstClone as DAO.recordset
rstClone=Forms![Bid - Master Form]![Bid - Quote].RecordsetClone
rstClone.MoveFirst
Do While Not rstClone.EOF
    If rstClone!QuoteID=intQuoteID then
       Forms![Bid - Master Form]![Bid - Quote].Recordset.BookMark=rstClone!BookMark
       Exit Do
    End If
    rstClone.MoveNext
Loop

To This:

Dim rstClone as DAO.recordset
rstClone=Forms![Bid - Master Form][b].Form.[/b][Bid - Quote].RecordsetClone
rstClone.MoveFirst
Do While Not rstClone.EOF
    If rstClone!QuoteID=intQuoteID then
       Forms![Bid - Master Form][b].Form.[/b][Bid - Quote].Recordset.BookMark=rstClone!BookMark
       Exit Do
    End If
    rstClone.MoveNext
Loop

 

[kermit5]

I am still getting a compile error:Invalid use of property at the line:

rstClone = Forms![Bid - Master Form].Form.[Bid - Quote].RecordsetClone

 

[Pat Hartman]

' Find the record that matches the new id.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Quote ID] = " & intNewQuoteID
Me.Bookmark = rs.Bookmark