Updating empty field in access table with text

[jabenj]

Hi. I'm a novice trying to update a field (LotNum) in an access table (Product Input) using an update statement. I'm updating through a form that the user fills out under two conditions: 1. the LotNum field is empty, and 2. the appropriate tank is selected. First I was getting a Write conflict error until i entered the null statement. Now i get an "object required" error.



Dim stSql As String
Dim rs As Object
Dim con As Object

Set con = Application.CurrentProject.Connection
txtLotNum.SetFocus
stSql = "Update [Blendsheet Input] "
stSql = stSql & "set [LotNum] = '" & txtLotNum.Text & "'"
stSql = stSql & "where [TankNum] = '" & cboTankNum.Value & "';" & LotNum is Null

Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 3 ' 3 = adModeReadWrite

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunCommand acCmdClose

Set rs = Nothing
Set con = Nothing

Exit_cmdSave_Close_Click:
Exit Sub

Err_cmdSave_Close_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Close_Click
End Sub


Any help would be greatly appreciated

 

[randy700]

Perhaps change this

stSql = stSql & "where [TankNum] = '" & cboTankNum.Value & "';" & LotNum is Null

to this

stSql = stSql & "where [TankNum] = " & cboTankNum.Value & "

Can't see any reason to include LotNum Is Null.
The single quotes are delimiters for text fields.
You don't want them if cboTankNum is a number.


Randy

 

[jabenj]

Thanks for your help. cboTankNum.Value is a combo box that is selected by the user in the form to update the appropriate table row. I only wanted the row to update if the LotNum field did not have a value. If the LotNum field contains a value, this means it was updated before and shouldn't be updated again. I tried what you suggested but I get a "No value given" message

 

[dhookom]

Try this:

'[green] txtLotNum.SetFocus[/green] [red]why[/red]
'[green] assumes LotNum and TankNum are both text[/green]
 stSql = "Update [Blendsheet Input] "
 stSql = stSql & "set [LotNum] = '" & Me.txtLotNum & "'[highlight] [/highlight]"
 stSql = stSql & "where [TankNum] = '" & cboTankNum.Value & "' AND LotNum is Null"

Duane
Hook'D on Access
MS Access MVP

 

[jabenj]

Thanks all for help. I got it to work. The problem was that "txtLotNum" was a bounded field. I changed it to an unbounded text and it works now. Thanks