Solved

Open Form pass record id

Posted on 2013-11-18
3
786 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

862 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now