?
Solved

Open Form pass record id

Posted on 2013-11-18
3
Medium Priority
?
849 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 2000 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

593 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