Avatar of gogetsome
gogetsomeFlag for United States of America asked on

Open Form pass record id

Hello, I'm trying to open a form with a particular recordset via a record id passed in like this:

DoCmd.OpenForm "DMR Tracking_Edit", , , "[DMR#] =" & NewMDRNumber & ""

When the above executes I'm asked to provide the record id. The user will not know the record id as the record was just created in another process.

How can I pass the record id to open a specific record on a form?
Microsoft Access

Avatar of undefined
Last Comment
mbizup

8/22/2022 - Mon
mbizup

How are you creating the new record (what code are you using)?
ASKER
gogetsome

This is all the code. I get the next record id, then fill the current forms record set with the record I'm duplicating. I then clone the record giving it the next record Id. Finely I want to open another form and display the cloned record.

The record is being duplicated and created.

If MIRROR.Value > "" Then

Dim NewMDRNumber As Integer

'get the selected mdr
Dim myDMR As Integer
myDMR = MIRROR.Value

'fill NewMDRNumber number with next mdr number
NewMDRNumber = DLookup("myDMR", "GetNextDMRID")


 Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   'Use the ADO connection that Access uses
   Set cn = CurrentProject.AccessConnection

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "Select * from [DMR Tracking] Where [DMR#] = " & myDMR & ""
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs

   Set rs = Nothing
   Set cn = Nothing


'Duplicate the record
With Me.RecordsetClone
.AddNew
![DMR#] = NewMDRNumber
![DATE Issued] = Me![DATE Issued]
![TYPE OF DMR] = Me![TYPE OF DMR]
![PART NUMBER] = Me![PART NUMBER]
![PART NAME] = Me![PART NAME]
![Program] = Me![Program]
![Customer] = Me![Customer]
'![SUPPLIER] = Me![SUPPLIER]
![Area] = Me![Area]
![LOT NUMBER] = Me![LOT NUMBER]
![QTY SUSPECT] = Me![QTY SUSPECT]
![PPM QUANTITY] = Me![PPM QUANTITY]
![WHERE LOCATED] = Me![WHERE LOCATED]
![WHO PREPARED] = Me![WHO PREPARED]
![QA Eng] = Me![QA Eng]
![JobNumber] = Me![JobNumber]
![PINNumber] = Me![PINNumber]
![PartCost] = Me![PartCost]
![REASON FOR REJECTION] = Me![REASON FOR REJECTION]
.Update

DoCmd.OpenForm "DMR Tracking_Edit", , , "[DMR#] =" & NewMDRNumber & ""

End With

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler

Else

MsgBox "Select the MDR to duplicate."

End If

 

Open in new window

ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck