Insert Records Warning Yes/No

[niuginikiwi]

Hi,
I have a command button that inserts my selections on values in txt fields,
combo boxes and list boxes into different tables...
What I would like to do is before inserts a done, I would like a customised
warning saying that you are trying to insert the selected values into the
database and should give me an option of saying YES or NO. If yes is clicked,
it should insert, if No is clicked, nothing happens.
Im pretty sure its simple but I can't figure it out myself.
Below is the code on the command button.

Private Sub btnAppend_Click()
Dim DB As DAO.Database
Dim rsApplication As DAO.Recordset
Dim rsApplicationDetails As DAO.Recordset
Dim vPlanting As Variant, i As Integer
Dim vProduct As Variant
Dim lApplicationID As Long
Set DB = CurrentDb
Set rsApplication = DB.OpenRecordset("tblApplications")
Set rsApplicationDetails = DB.OpenRecordset("tblApplicationDetails")
For Each vPlanting In lstPlantings.ItemsSelected
With rsApplication
.AddNew
!ApplicationDate = txtApplicationDate
!OperationID = cboOperation
!EmployeeID = cboEmployee
!PlantingDetailsID = lstPlantings.ItemData(vPlanting)
!WaterRate = txtWaterRate
!TankSize = txtTankSize
lApplicationID = !ApplicationID
.Update
End With
For Each vProduct In lstRates.ItemsSelected
With rsApplicationDetails
.AddNew
!ApplicationID = lApplicationID
!ProductDetailsID = lstRates.ItemData(vProduct)
.Update
End With
Next vProduct
Next vPlanting

rsApplication.Close
rsApplicationDetails.Close

End Sub

 

[Corey-g via AccessMonster.com]

I think that you want to have an IF statement after the 'For Each' statements.
You can either have the msgbox return the result to a variable, or use it
directly in the if statement.

So, as it loops through the selected items, it would prompt the user before
the setting of values and updating your recordset. (See below)

Private Sub btnAppend_Click()
Dim DB As DAO.Database
Dim rsApplication As DAO.Recordset
Dim rsApplicationDetails As DAO.Recordset
Dim vPlanting As Variant, i As Integer
Dim vProduct As Variant
Dim lApplicationID As Long
Set DB = CurrentDb
Set rsApplication = DB.OpenRecordset("tblApplications")
Set rsApplicationDetails = DB.OpenRecordset("tblApplicationDetails")
For Each vPlanting In lstPlantings.ItemsSelected

' Start here, and use either:
IF msgbox("You are about to add " & lstPlantings.ItemData(vPlanting) & " to
the database", ...) = Yes then

With rsApplication
.AddNew
!ApplicationDate = txtApplicationDate
!OperationID = cboOperation
!EmployeeID = cboEmployee
!PlantingDetailsID = lstPlantings.ItemData(vPlanting)
!WaterRate = txtWaterRate
!TankSize = txtTankSize
lApplicationID = !ApplicationID
.Update
End With

end if

For Each vProduct In lstRates.ItemsSelected

And do it again here...

With rsApplicationDetails
.AddNew
!ApplicationID = lApplicationID
!ProductDetailsID = lstRates.ItemData(vProduct)
.Update
End With

End IF

 

[Corey-g via AccessMonster.com]

Please note that this is more 'Air Code' - not tested and not syntactically
correct - so you will need to code it correctly.

 

[niuginikiwi]

Thanks corey,
I have also tried include a single selected value of a combo to appear as
part of the message as well but im getting error message saying "Type
Mismatch"
Here is the MsgBox code:

If MsgBox("You tried to add " & Me.cboOperation.Value(1) & " to the
database." & vbCrLf & "Are you sure you want to apply these values?" _
, vbYesNo, "Confirm Operation Application") = vbYes Then

 

[J. Goddard]

I think Me.cboOperation.Value(1) should read
Me.cboOperation.Column(1)

John

 

[niuginikiwi]

Thanks John,
How could I miss that?