Put in a value into a table from VBA

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Hi guru's. A simple yet frustrating question (at least for
me). Right now, I have in my program that when a user
clicks exit, it closes out of the form. Recently, I have
added a request that takes into account that when a user
clicks on the exit button, that it asks if the user has
ended their test. If yes, then I want to put the date in
the EndDate field of my AnalysisTestDate table. I have
code, but I am unsure if this is correct.

Private Sub ctrlExit_Click()
Dim msg1 As Integer

msg1 = MsgBox("Is the Test Number " &
Me.TestGroupID.Value & " complete?", vbYesNo)
If msg1 = vbYes Then
AnalysisTestDate.EndDate.Value = Now()
End If
DoCmd.Close , , acSaveNo
End Sub

Thanks in advance.
 
you can use update query ti do so:
If msg1 = vbYes Then
docmd.runsql "Update AnalysisTestDate Set EndDate.Value = Now()"
End If
 
Eric,

If your form is bound to the same table, AnalysisTestDate
table, your code would be:

msg1 = MsgBox("Is the Test Number " &
Me.TestGroupID.Value & " complete?", vbYesNo)
If msg1 = vbYes Then
Me.EndDate = Now()
End If

If it isn't then the simplest way you can achieve this is
to have a DoCmd run an append query to add a record entry
into the AnalysisTestDate table.

If msg1 = vbYes Then
DoCmd.OpenQuery "AppendQryName",
acViewNormal,acEdit
End If

Sample append query is sql view:

INSERT INTO Table2 ( enddate )
SELECT Date() AS Expr1;

JimB
 
Back
Top