VBA to Open Form and Add Record

[shelby55]

Hello

I am working in an Access 2010 database. On my splash screen I have a frame with 4 options for the type of patient being entered. I have a button that I would like the user to select and it will open up the correct form, add a new ID number and enter the patient type just selected to go in the trans_type field in the table.

I have the code to open up the correct form:

Private Sub cmdAddRec_Click()
Dim strForm As String

If Me.frmTrans = 1 Then
strForm = "frmIntakeRepat"

ElseIf Me.frmTrans = 2 Then
strForm = "frmPtTsfronly"

ElseIf Me.frmTrans = 3 Then
strForm = "frmConsults"

ElseIf Me.frmTrans = 4 Then
strForm = "frmHCSSQry"

End If

DoCmd.OpenForm strForm, acNormal

End Sub

I also have code for a button on each of the forms that is used to Add (so the user doesn't have to got back to the splash screen to select the same form if wanting to add another patient) and the code that is working is:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

Dim AddMsg, AddStyle, AddTitle, AddResponse, AddMyString
AddMsg = "Do you wish to add new record?"
AddStyle = vbOK + vbCancel
AddTitle = "MsgBox Add Record"
AddResponse = MsgBox(AddMsg, AddStyle, AddTitle)
If AddResponse = vbYes Then
DoCmd.GoToRecord , , acNewRec
[DATE REQUEST RECEIVED].SetFocus
Me.ID_Number = NewCustID()
Else
   MsgBox ("Add Record Procedure cancelled")
   Exit Sub
   
End If
    
Exit_cmdAddRecord_Click:
    Exit Sub

Err_cmdAddRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddRecord_Click
End Sub

I would just like to combine them so it can happen from the splash and also add the transtype to the table. Thanks for any assistance.

 

[dhookom]

You can run an append query to add a new record and then immediately retrieve the primary key value so you can open a form filtered to the new record.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Thanks Duane...and how would I do that?

 

[dhookom]

What part don't you understand?

To run an append query your code might look something like:

Dim strSQL as String
Dim lngNewID as long
strSQL = "INSERT INTO ...."
Currentdb.Execute strSQL, dbFailOnError
lngNewID = DMax("ID...","YourTable")
DoCmd.OpenForm...

Duane
Hook'D on Access
MS Access MVP