Insert from table and form

[Guest]

Hello all

I'm wanting to get data from a table and from a textbox on a form. This code works, but all text fields must not be null, or it doesn't work. How can I have the fields null and still insert the data, or is there a better way to do this
Curdate, JobNum, Qty, Notes, and PONumber are all fields on the form, the rest is from the table

Private Sub btAdd_Click(

Dim wrkJet As Workspace
Dim db As DAO.Databas
rs As DAO.Recordset
sql As String
vdoSql As Strin

'Get info from item record to add to orde
Set db = CurrentDb(
sql = "select * from tbItemDetail where ItmNum = '" + [cbitem] + "'
Set rs = db.OpenRecordset(sql, dbOpenSnapshot
If rs.RecordCount > 0 The
rs.MoveFirs
vdoSql = "INSERT INTO tbOrd ( Curdate, JobNum, Qty, ItmNum, ItmDesc, ItmCst, UOM, Cont, Notes, PONumber ) values ('" & Me.txtDate.Value & "','" & Me.txtjobcnt.Value & "','" & Me.txtQty.Value & "','" & rs![ItmNum] & "','" & rs![Desc] & "'," & rs![Price] & ",'" & rs![UOM] & "','" & rs![Packing] & "','" & Me.txtNote.Value & "','" & Me.txtPO.Value & "');

DoCmd.RunSQL vdoSq

End I

rs.Close: db.Clos

End Sub

 

[Michel Walsh]

Hi,

Try:


vdoSql = "INSERT INTO tbOrd ( Curdate, JobNum, Qty, ItmNum, ItmDesc,
ItmCst, UOM, Cont, Notes, PONumber ) values ('" & Nz(Me.txtDate.Value,
"NULL") & "','" & Nz(Me.txtjobcnt.Value, "NULL") & "','" &
Nz(Me.txtQty.Value, "NULL") & "','" & Nz(rs![ItmNum], "NULL") & "','" &
Nz(rs![Desc], "NULL") & "'," & Nz(rs![Price], "NULL") & ",'" & Nz(rs![UOM],
"NULL") & "','" & Nz(rs![Packing], "NULL") & "','" &Nz( Me.txtNote.Value,
"NULL") & "','" & Nz(Me.txtPO.Value, "NULL") & "');"




Hoping it may help,
Vanderghast, Access MVP

Hello all,

I'm wanting to get data from a table and from a textbox on a form. This

code works, but all text fields must not be null, or it doesn't work. How
can I have the fields null and still insert the data, or is there a better
way to do this?

Curdate, JobNum, Qty, Notes, and PONumber are all fields on the form, the rest is from the table.

Private Sub btAdd_Click()

Dim wrkJet As Workspace
Dim db As DAO.Database
rs As DAO.Recordset
sql As String
vdoSql As String

'Get info from item record to add to order
Set db = CurrentDb()
sql = "select * from tbItemDetail where ItmNum = '" + [cbitem] + "'"
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
If rs.RecordCount > 0 Then
rs.MoveFirst
vdoSql = "INSERT INTO tbOrd ( Curdate, JobNum, Qty, ItmNum, ItmDesc,

ItmCst, UOM, Cont, Notes, PONumber ) values ('" & Me.txtDate.Value & "','"
& Me.txtjobcnt.Value & "','" & Me.txtQty.Value & "','" & rs![ItmNum] & "','"
& rs![Desc] & "'," & rs![Price] & ",'" & rs![UOM] & "','" & rs![Packing] &
"','" & Me.txtNote.Value & "','" & Me.txtPO.Value & "');"