Add new record without prompt.

  • Thread starter Thread starter Jarryd
  • Start date Start date
J

Jarryd

Hi,

How do i add a new record without being prompted. I have a form and I click
a button and it updates and adds but asks me if I want to, and i want it to
just do it.

TIA.

Jarryd
 
Jarryd,

I presume you're using DoCmd.RunSQL to do the insert. Use db.Execute
instead; it doesn't show any messages, and you can also trap any errors if
you use the dbFailOnError argument:
Dim db As Database
Set db = CurrentDb
On Error Resume Next
db.Execute strSQL, dbFailOnError
If Err<>0 Then MsgBox "Error"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
 
Hi,

This is what I have got:

Private Sub btnApply_Click()
Dim Weight As Double
Dim Quantity As Long
Dim ordLink As Long
Dim Orig_Unique As Long
Dim Q_U As Long
Dim W_U As Double
Dim qrySplit As String
Dim qryUpd_Orig As String

Q_U = Form_frmSplit.txtRolls
W_U = Form_frmSplit.txtkilos
ordLink = Form_Orders.txtOrd_Det_Lnk
Quantity = Form_Order_Details.txtQty - txtRolls
Weight = Form_Order_Details.txtWeight - txtkilos
Orig_Unique = Form_Order_Details.txtUnique
qrySplit = "INSERT INTO Order_Details ( Orders_Link, Quantity, Weight )
VALUES (" & ordLink & "," & Quantity & "," & Weight & ");"
qryUpd_Orig = "UPDATE Order_Details SET Quantity = (" & Q_U & "), Weight =
(" & W_U & ") WHERE Unique = " & Orig_Unique & ";"
DoCmd.RunSQL qrySplit
DoCmd.RunSQL qryUpd_Orig
Form_Orders.Refresh
DoCmd.Close
End Sub

TIA,

Jarryd
 
Jarryd,

I'm assuming you need to make sure that BOTH queries run successfully, or
neither of them should run. If that's the case, then you need to wrap them
in a transaction.

Private Sub btnApply_Click()
Dim ws As Workspace
Dim db As Database
Dim Weight As Double
Dim Quantity As Long
Dim ordLink As Long
Dim Orig_Unique As Long
Dim Q_U As Long
Dim W_U As Double
Dim qrySplit As String
Dim qryUpd_Orig As String

On Error Goto Proc_Err

Set ws = DbEngine(0)
Set db = CurrentDb

Q_U = Form_frmSplit.txtRolls
W_U = Form_frmSplit.txtkilos

ordLink = Form_Orders.txtOrd_Det_Lnk
Quantity = Form_Order_Details.txtQty - txtRolls
Weight = Form_Order_Details.txtWeight - txtkilos
Orig_Unique = Form_Order_Details.txtUnique

qrySplit = "INSERT INTO Order_Details " & _
"(Orders_Link, Quantity, Weight) " & _
"VALUES (" & ordLink & "," & Quantity & "," & Weight &
");"

qryUpd_Orig = "UPDATE Order_Details " 7 _
"SET Quantity = (" & Q_U & "), " & _
"Weight = (" & W_U & ") " & _
"WHERE Unique = " & Orig_Unique & ";"

ws.BeginTrans
db.Execute qrySplit, dbFailOnError
db.Execute qryUpd_Orig, dbFailOnError
ws.CommitTrans

Form_Orders.Refresh
DoCmd.Close

Proc_Exit:
Exit Sub

Proc_Err:
ws.Rollback
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume Proc_Exit
End Sub

Just as a side note, if Form_Orders is the current form, you can use the Me
keyword to refer to Form_Orders:
Me.txtOrd_Det_Lnk
instead of
Form_Orders.txtOrd_Det_Lnk

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
---------------------------
 
Back
Top