Private Sub cmdRankChange_Click()
On Error GoTo Error_Change
Dim indicator As VbMsgBoxResult
indicator = MsgBox("Are you sure this soldier's rank should be changed?", vbYesNo, "Promotion")
If indicator = vbNo Then
Dim The_SQL As String
Dim The_Date As Date
The_Date = Date
The_SQL = "INSERT INTO tblRankChange ( SoldierCode, OriginalRank, DateOriginated, DateFinalized ) " & _
"SELECT " & Me![SoldierCode] & ", " & Me![RankCode] & ", Date(), Date()"
Dim The_Form As String
Dim The_Filter As String
The_Form = "frmRankChange"
The_Filter = "[SoldierCode]=" & Me![SoldierCode] & " And [DateOriginated]='" & Date & "' And [OriginalRank]=" & Me![RankCode]
DoCmd.OpenForm The_Form, , , The_Filter
The form frmRankChange open fines if I hide the SQL, but if the SQL is visible, a message pops up saying the OpenForm Action was Canceled or something like that. How do I set it up where both run just fine. I want to only open the form for one particular record, so I created pretty strict criteria when it opens. The record I am "looking for" was just generated by the SQL that is run prior to the form being opened. The long and short of it: I need to generate a new record in my tblRankChange table, and be able to open a form with the information in the new record I just created, so it can be properly input. Any assistance would be extremely helpful.