Solved

Open Form pass record id

Posted on 2013-11-18
3
793 Views
Last Modified: 2013-11-18
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?
0
Comment
Question by:gogetsome
  • 2
3 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39657343
How are you creating the new record (what code are you using)?
0
 

Author Comment

by:gogetsome
ID: 39657403
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

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39657418
Assuming the new ID is numeric, your syntax looks good.

Alternatively:

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

Open in new window


OR if the code is on the SAME FORM that you are trying to open:
Me.Requery
Me.Filter = "[DMR#] =" & NewMDRNumber
Me.FilterOn = true

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best way to create dynamic, short cut menu 7 28
Modal form 11 30
date criteria to pull up records for the last 3 months 39 42
Switch 5 17
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

777 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