Open form based on newly created record

[JackieEVSC]

I have a form that creates a new record in a table with vba code. The code works and creates the record. However, I need to open a different form using ID of this new record, and I can't seem to get the form to open to that record.

The table name where the record is created is tbl_RepairInfo.
The field name is TicketNum (auto-numbered).
The form I need to open to that record is frm_StudentInfo.

This is what I tried (that didn't work) ... DoCmd.OpenForm "frm_RepairTicket", , , "TicketNum = " & Me!TicketNum. What am I doing wrong?

 

[MazeWorX]

Take a look here it is quite simple dhookom just answered this

http://www.tek-tips.com/viewthread.cfm?qid=1675967

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

Have you attempted anything to debug your code such as Debug.Print, MsgBox(), setting a breakpoint, ...? There are some tips in the FAQs of this forum.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

You probably need to add
me.dirty = false
before trying to open the form. Me.dirty = false, will commit the autonumber.

 

[TheAceMan1]

How are ya JackieEVSC . . .

The key is getting a field value you can ping on for the newly added record. This value needs to be held while the new record is updated. The following example uses [blue]ID[/blue] the primarykey (autonumber) of a table.

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim [purple][b]hldID[/b][/purple] As Long, Cri As String
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("[b]TableName[/b]", dbOpenDynaset)
   
   With rst
      .AddNew
      !EmpID = 54321
      !qName = "Horace"
      [purple][b]hldID[/b][/purple] = !ID [green]'hold the ID before update[/green]
      .Update
   End With
   
   Cri = "[ID] = " & [purple][b]hldID[/b][/purple]
   DoCmd.OPenForm "FormName", , , Cri
   
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JackieEVSC]

At the end of this post is the original code (that works) for this button. It creates a record in a table, and opens a second form based on that info. When this form is filled out, techs print a bar-coded form of the info.

We used to manually enter the ticket number into the form and I would use that number to open the second form. Now I need the second form to open by a field auto-numbered when users click a button to write to the table. I can write to the table, I just can't get the second form to open based on the TicketNum field (which is an autonumber field).

For the ticket number, we used to use the number on a pre-printed repair ticket, and attached the printout to the ticket. Instead of ordering more tickets, we ordered clear sleeves to slip the printout into.

Here is my original code: The TicketNum field we used to fill in is now an auto-numbered field. I don't know how to grab that info when creating the record in order to use it to open the second form.

Private Sub Command54_Click()
Dim strSQL As String
strSQL = "INSERT INTO RepairInfo(TicketNum,FirstName,LastName,StudentSite,AssetNumber,SvcTag,EquipDesc,SubmittedBy,School,StudentID) VALUES(" & "'" & TNum & "'," " & "'" & FN & "'," & "'" & LN & "'," & "'" & SSite & "'," & "'" & Ass & "'," & "'" & Svc & "'," & "'" & Equip & "'," & "'" & Tech & "'," & "'" & Schl & "'," & "'" & SID & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

DoCmd.OpenForm "frm_RepairTicket", , , "TicketNum = " & Me.TNum)

DoCmd.Close acForm, "frm_StudentInfo"

End Sub

 

[dhookom]

If TicketNum is an Autonumber, you must remove it from the strSQL.
After running the strSQL, you can grab the maximum autonumber value using DMax("TicketNum","RepairInfo").

If you have lots of people entering tickets, you could add a WHERE CLAUSE to the DMax() to filter for some of the other field values.

Duane
Hook'D on Access
MS Access MVP